PostgreSQL窗口函数：row_number（）以上（由col2划分col顺序） [英] PostgreSQL window function: row_number() over (partition col order by col2)
问题描述
以下结果集是从带有几个联接和联合的sql查询派生的。 sql查询已将日期和游戏上的行分组。我需要一列来描述按日期列划分的游戏尝试次数。
用户名游戏ID日期
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_1 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
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 2014年7月22日3:54
我有以下sql查询枚举了其中的行分区，但并非完全正确，因为我想根据日期和游戏来计算该游戏的实例数。在这种情况下，johndoe1已尝试在Game_1中按时间戳划分五次。
此查询返回以下结果集
select *
，row_number（）结束（按ct。 date的顺序按ct。 date的顺序），作为尝试
来自csv_temp，作为ct
用户名游戏ID日期尝试（期望的尝试） 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 3 5
任何指针都会有很大帮助。
考虑 partition by 类似于您将 group by 分组的字段，然后，当分区值更改时，窗口函数将从1重新启动。 / p>
编辑
，如a_horse_with_no_name所示，为此，我们需要 dense_rank（）
与 row_number（） rank（）或 dense_rank（）重复它分配的数字。对于分区中的每一行， row_number（）必须是不同的值。 rank（）和 dense_rank（）之间的区别在于后者不会跳过数字。
为您的查询尝试：
dense_rank（）结束（按用户名，游戏划分按ct。 date的顺序排序为尝试
您不按分区进行排序顺便说一句如果需要的话，只需订购即可。它不在这里。
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.
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.
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.
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
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.
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.
这篇关于PostgreSQL窗口函数：row_number（）以上（由col2划分col顺序）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
