PostgreSQL窗口函数:row_number()over(分区col顺序col2)
发布时间:2020-12-13 16:16:18 所属栏目:百科 来源:网络整理
导读:以下结果集是从带有一些连接和联合的sql查询派生的. sql查询已经在日期和游戏上对行进行分组.我需要一个列来描述按日期列分区的游戏尝试次数. Username Game ID Datejohndoe1 Game_1 100 7/22/14 1:52 AMjohndoe1 Game_1 100 7/22/14 1:52 AMjohndoe1 Game_1
以下结果集是从带有一些连接和联合的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
编辑 对于您的查询尝试: dense_rank() over (partition by Username,Game order by ct."date") as "Attempts" 顺便说一下,你不要按相同的字段进行分区和排序;如果需要,只需订购就足够了.它不在这里. (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |