SQL PostgreSQL 窗口函数：row_number() over (partition col order by col2)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25562542/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL window function: row_number() over (partition col order by col2)
提问by user1951677
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.
任何指针都会有很大帮助。
回答by Used_By_Already
Consider partition byto be similar to the fields that you would group by, then, when the partition values change, the windowing function restarts at 1
考虑partition by类似于您将要使用的字段group 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.
顺便说一下，您不会按同一字段进行分区和排序；如果需要，只需订购就足够了。它不在这里。
