以下结果集是从带有一些连接和联合的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
原文链接：https://www.f2er.com/postgresql/192029.html编辑
如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"
顺便说一下,你不要按相同的字段进行分区和排序;如果需要,只需订购就足够了.它不在这里.
