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

以下结果集是从带有一些连接和联合的SQL查询派生的. SQL查询已经在日期和游戏上对行进行分组.我需要一个列来描述按日期列分区的游戏尝试次数.

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视为与您要分组的字段类似,然后,当分区值更改时,窗口函数将重新启动为1

编辑
如a_horse_with_no_name所示,为此需要我们需要dense_rank()
与row_number()rank()或dense_rank()不同,重复它指定的数字.对于分区中的每一行,row_number()必须是不同的值. rank()和dense_rank()之间的区别是后者没有“跳过”数字.

对于您的查询尝试：

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

顺便说一下,你不要按相同的字段进行分区和排序;如果需要,只需订购就足够了.它不在这里.

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

猜你在找的Postgre SQL相关文章