sql - PGSQL：查询设备上当前登录的用户

Question

给定一个这样的数据库表：

用户身份	设备编号	使用权	ts
6	200	登录	2021-08-17 05:40:57
2	100	登录	2021-08-17 05:41:57
2	100	登录	2021-08-17 05:42:57
4	100	登录	2021-08-17 05:43:57
4	100	登录	2021-08-17 05:44:57
4	100	登出	2021-08-17 05:45:57
5	100	登录	2021-08-17 05:46:57
5	100	登出	2021-08-17 05:47:57
6	100	登录	2021-08-17 05:48:57

我想找出设备=100 上所有当前登录的用户。登录用户的定义：没有比前一个登录事件的时间戳更大的注销事件。用户可以有多个后续登录事件，但一个最近的注销事件将认为该用户已注销。

预期返回的行：

用户身份	设备编号	使用权	ts
2	100	登录	2021-08-17 05:42:57
6	100	登录	2021-08-17 05:48:57

从上面的数据集中，我希望得到 user_id(s) 2 和 6；所有其他用户都已注销。

查询此聚合数据的最有效方法是什么？谢谢

使用 PostgreSQL 13

标签： sqlpostgresql

Answer 1

您可以找到每个用户的最后访问权限，然后过滤最后访问权限等于“登录”的用户：

WITH last_access AS (SELECT DISTINCT ON (user_id) *
                     FROM t
                     WHERE device_id = 100
                     ORDER BY user_id, ts DESC)
                     
SELECT *
FROM last_access
WHERE access = 'login';