postgresql - 组内的 MIN 记录

Question

我想user_id根据created_at时间戳获取 MIN step per 的记录。

样本数据：

+---------+-------+---------------------+
| user_id | step  |     created_at      |
+---------+-------+---------------------+
| 7bc6de  | step1 | 2021-03-16 14:03:16 |
| 7bc6de  | step2 | 2021-03-16 14:04:07 |
| 7bc6de  | step2 | 2021-03-16 14:03:47 |
| 7bc6de  | step3 | 2021-03-16 14:03:55 |
| 7bc6de  | step3 | 2021-03-16 14:04:00 |
| 7bc6de  | step1 | 2021-03-16 14:04:02 |
| 7bc6de  | step2 | 2021-03-16 14:03:16 |
| 7bc6de  | step3 | 2021-03-16 14:04:07 |
| 7bc6de  | step4 | 2021-03-16 14:04:08 |
| 7bc6de  | step4 | 2021-03-16 14:04:09 |
+---------+-------+---------------------+

所需的输出：

+---------+-------+---------------------+
| user_id | step  |     created_at      |
+---------+-------+---------------------+
| 7bc6de  | step1 | 2021-03-16 14:03:16 |
| 7bc6de  | step2 | 2021-03-16 14:03:16 |
| 7bc6de  | step3 | 2021-03-16 14:03:55 |
| 7bc6de  | step4 | 2021-03-16 14:04:08 |
+---------+-------+---------------------+

标签： postgresqlwindow-functions

Answer 1

这是 PostgreSQL 扩展的一个例子DISTINCT ON：

SELECT DISTINCT ON (user_id, step)
       user_id, step, created_at
FROM atable
ORDER BY user_id, step, created_at;