sql - 在组内递增计数

我试图在一组结果中获得一个递增的计数器。

例如，假设我有一个 messages 表:

messages
--------
- id (int)
- user_id (int)
- sent_at (date)
- body (text)

我想执行一个查询，给我这样的结果:

+---------+------------+-------------+---------+
| user_id | message_id | sent_at     | counter |
+---------+------------+-------------+---------+
|       1 |          1 |  2017-01-01 |       1 |
|       1 |          3 |  2017-01-15 |       2 |
|       1 |          4 |  2017-01-22 |       3 |
|       2 |          2 |  2017-01-06 |       1 |
|       2 |          6 |  2017-01-22 |       2 |
|       3 |          5 |  2017-01-22 |       1 |
|       3 |          7 |  2017-01-28 |       2 |
|       3 |          8 |  2017-02-03 |       3 |
|       3 |          9 |  2017-02-14 |       4 |
+---------+------------+-------------+---------+

本质上，计数器仅在 user_id 组内递增，每个内部组都按 sent_at 列排序。

我知道我可以使用以下 SQL 轻松获取前三列:

SELECT
   user_id,
   id AS message_id,
   sent_at
FROM messages
ORDER BY
    user_id,
    sent_at

但是我需要第四个 count 列。

我知道我可以使用 ROW_NUMBER() 来获取结果行号:

SELECT
   user_id,
   id AS message_id,
   sent_at,
   ROW_NUMBER() OVER(ORDER BY user_id, sent_at) AS counter
FROM messages
ORDER BY
    user_id,
    sent_at

但这给了我以下结果:

+---------+------------+-------------+---------+
| user_id | message_id | sent_at     | counter |
+---------+------------+-------------+---------+
|       1 |          1 |  2017-01-01 |       1 |
|       1 |          3 |  2017-01-15 |       2 |
|       1 |          4 |  2017-01-22 |       3 |
|       2 |          2 |  2017-01-06 |       4 |
|       2 |          6 |  2017-01-22 |       5 |
|       3 |          5 |  2017-01-22 |       6 |
|       3 |          7 |  2017-01-28 |       7 |
|       3 |          8 |  2017-02-03 |       8 |
|       3 |          9 |  2017-02-14 |       9 |
+---------+------------+-------------+---------+

如果我可以在每个新的 user_id 之后以某种方式重置计数器，我就会得到我正在寻找的结果。

最佳答案

您只需使用 PARTITION BY :

SELECT
   user_id,
   id AS message_id,
   sent_at,
   ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY user_id, sent_at) AS counter
FROM messages
ORDER BY
    user_id,
    sent_at;

关于sql - 在组内递增计数，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/44337334/