PostgreSQL 窗口函数：row_number() over（按 col2 分区 col 顺序）

以下结果集源自具有一些连接和联合的 SQL 查询。 SQL 查询已经对 Date 和 game 上的行进行了分组。我需要一列来描述按日期列分区的游戏的尝试次数。

Username   Game     ID   Date

johndoe1   Game_1   100  7/22/14 1:52 AM
johndoe1   Game_1   100  7/22/14 1:52 AM
johndoe1   Game_1   100  7/22/14 1:52 AM
johndoe1   Game_1   100  7/22/14 1:52 AM
johndoe1   Game_1   121  7/22/14 1:56 AM
johndoe1   Game_1   121  7/22/14 1:56 AM
johndoe1   Game_1   121  7/22/14 1:56 AM
johndoe1   Game_1   121  7/22/14 1:56 AM
johndoe1   Game_1   121  7/22/14 1:56 AM
johndoe1   Game_1   130  7/22/14 1:59 AM
johndoe1   Game_1   130  7/22/14 1:59 AM
johndoe1   Game_1   130  7/22/14 1:59 AM
johndoe1   Game_1   130  7/22/14 1:59 AM
johndoe1   Game_1   130  7/22/14 1:59 AM
johndoe1   Game_1   200  7/22/14 2:54 AM
johndoe1   Game_1   200  7/22/14 2:54 AM
johndoe1   Game_1   200  7/22/14 2:54 AM
johndoe1   Game_1   200  7/22/14 2:54 AM
johndoe1   Game_1   210  7/22/14 3:54 AM
johndoe1   Game_1   210  7/22/14 3:54 AM
johndoe1   Game_1   210  7/22/14 3:54 AM
johndoe1   Game_1   210  7/22/14 3:54 AM

我有以下 sql 查询，它枚举分区内的行，但并不完全正确，因为我希望根据日期和游戏计算该游戏的实例数。在本例中，johndoe1 已尝试 Game_1 按时间戳划分五次。

该查询返回以下结果集

select *
, row_number() over (partition by ct."date" order by ct."date") as "Attempts"
from csv_temp as ct

Username   Game     ID   Date             Attempts  (Desired Attempts col.)

johndoe1   Game_1   100  7/22/14 1:52 AM  1          1
johndoe1   Game_1   100  7/22/14 1:52 AM  2          1
johndoe1   Game_1   100  7/22/14 1:52 AM  3          1
johndoe1   Game_1   100  7/22/14 1:52 AM  4          1
johndoe1   Game_1   121  7/22/14 1:56 AM  1          2
johndoe1   Game_1   121  7/22/14 1:56 AM  2          2
johndoe1   Game_1   121  7/22/14 1:56 AM  3          2
johndoe1   Game_1   121  7/22/14 1:56 AM  4          2
johndoe1   Game_1   121  7/22/14 1:56 AM  5          2
johndoe1   Game_1   130  7/22/14 1:59 AM  1          3   
johndoe1   Game_1   130  7/22/14 1:59 AM  2          3
johndoe1   Game_1   130  7/22/14 1:59 AM  3          3
johndoe1   Game_1   130  7/22/14 1:59 AM  4          3
johndoe1   Game_1   130  7/22/14 1:59 AM  5          3
johndoe1   Game_1   200  7/22/14 2:54 AM  1          4
johndoe1   Game_1   200  7/22/14 2:54 AM  2          4
johndoe1   Game_1   200  7/22/14 2:54 AM  3          4
johndoe1   Game_1   200  7/22/14 2:54 AM  4          4
johndoe1   Game_1   210  7/22/14 3:54 AM  1          5
johndoe1   Game_1   210  7/22/14 3:54 AM  2          5
johndoe1   Game_1   210  7/22/14 3:54 AM  3          5
johndoe1   Game_1   210  7/22/14 3:54 AM  4          5

任何指示都会有很大帮助。

考虑partition by与您想要的字段相似group by，然后，当分区值发生变化时，窗口函数从 1 重新开始

编辑 如 a_horse_with_no_name 所示，为此我们需要dense_rank() unlike row_number() rank() or dense_rank()重复它分配的数字。row_number()分区中的每一行必须是不同的值。和...之间的不同rank() and dense_rank()后者不会“跳过”数字。

对于您的查询，请尝试：

dense_rank() over (partition by Username, Game order by ct."date") as "Attempts"

顺便说一句，您不按同一字段进行分区和排序；如果需要的话，只需按顺序就足够了。它不在这里。