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

Following result set is derived from a sql query with a few joins and a union. The sql query already groups rows on Date and game. I need a column to describe the number of attempts at a game partitioned by date column.

以下结果集是从带有一些连接和联合的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

I've the following sql query that enumerates the rows within the partition but not entirely correct since I want the count of the instances of that game based on the date and game. In this case johndoe1 has attempted at Game_1 five times partitioned by the time stamps.

我有以下sql查询枚举分区内的行但不完全正确,因为我想根据日期和游戏计算该游戏的实例。在这种情况下,johndoe1已在Game_1尝试五次按时间戳划分。

This query returns result set below

此查询返回下面的结果集

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

Any pointers would be of great help.

任何指针都会有很大的帮助。

1 个解决方案

#1

Consider partition by to be similar to the fields that you would group by, then, when the partition values change, the windowing function restarts at 1

将partition by视为与您要分组的字段类似,然后,当分区值更改时,窗口函数将重新启动为1

EDIT as indicated by a_horse_with_no_name, for this need we need dense_rank() unlike row_number() rank() or dense_rank() repeat the numbers it assigns. row_number() must be a different value for each row in a partition. The difference between rank() and dense_rank() is the latter does not "skip" numbers.

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

For your query try:

对于您的查询尝试:

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

You don't partition by, and order by, the same field by the way; just order by would be sufficient if that was the need. It isn't here.

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

#1