下面的结果集是从一条带有几个连接和一个联合的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
任何指针都将是极大的帮助。