PostgreSQL窗口函数：row_number（）以上（由col2划分col顺序）
技术问答
275 人阅读
|
0 人回复
|
2023-09-14
|
|
以下结果集是从带有几个联接和一个并集的sql查询中得出的。sql查询已将“日期”和“游戏”上的行分组。我需要一列来描述按日期列划分的游戏尝试次数。% S/ Q! V5 ?( Y+ `
Username Game ID Date6 c, d+ M8 h/ [) `; F! L0 s: W
johndoe1 Game_1 100 7/22/14 1:52 AM
X1 a# u4 o( ojohndoe1 Game_1 100 7/22/14 1:52 AM
- B9 X( T: T9 J% |johndoe1 Game_1 100 7/22/14 1:52 AM
" O5 V2 S$ w4 V4 q4 C( j+ Njohndoe1 Game_1 100 7/22/14 1:52 AM) h# Q- r# B7 S8 i2 n: [8 }7 s+ W
johndoe1 Game_1 121 7/22/14 1:56 AM
6 ^* L% F1 c& h! D& Ijohndoe1 Game_1 121 7/22/14 1:56 AM; H: ], B1 P& Z' u/ I% c+ Z
johndoe1 Game_1 121 7/22/14 1:56 AM
3 ]' s. T( v) z* z/ @! Kjohndoe1 Game_1 121 7/22/14 1:56 AM e. _# H9 g' O3 R. K) R* ?# i
johndoe1 Game_1 121 7/22/14 1:56 AM
! D+ z# x& `; E: }, H6 H, ^! Njohndoe1 Game_1 130 7/22/14 1:59 AM$ S# z( j# f" a- [7 G; b# B9 j
johndoe1 Game_1 130 7/22/14 1:59 AM
( Z+ b, \1 I- R+ M" B2 Hjohndoe1 Game_1 130 7/22/14 1:59 AM" `" C) C' u) f2 p) x$ O
johndoe1 Game_1 130 7/22/14 1:59 AM
* C4 }# P, E8 [; M6 C d2 Xjohndoe1 Game_1 130 7/22/14 1:59 AM* L& ?" i9 d+ O. }) ]1 b# a% @( n
johndoe1 Game_1 200 7/22/14 2:54 AM
9 D1 X9 g3 Q: R! Gjohndoe1 Game_1 200 7/22/14 2:54 AM
7 ^0 }( Z0 w3 n/ b' Ejohndoe1 Game_1 200 7/22/14 2:54 AM
/ n7 h q; v( I0 h% H( n8 q. S! Bjohndoe1 Game_1 200 7/22/14 2:54 AM
+ N9 M$ M' t ljohndoe1 Game_1 210 7/22/14 3:54 AM0 t( [7 B2 I. E4 q' }& d
johndoe1 Game_1 210 7/22/14 3:54 AM6 Y) {0 J; W* Q4 G+ p3 Z" z5 D
johndoe1 Game_1 210 7/22/14 3:54 AM
9 x7 r* Z" n0 c+ |johndoe1 Game_1 210 7/22/14 3:54 AM
/ e" ]' D9 h4 c+ T* F: ~1 v" G我有以下sql查询，该查询枚举了分区中的行，但并不完全正确，因为我想根据日期和游戏来计算该游戏的实例数。在这种情况下，johndoe1已尝试在Game_1中按时间戳划分五次。
, g: Q+ {0 S* s该查询返回以下结果集
8 a; \; y6 y) H* l# Sselect * D% m3 C1 F7 C* ^
, row_number() over (partition by ct."date" order by ct."date") as "Attempts"& ]$ a1 R8 H# O8 D
from csv_temp as ct
: ]3 p; c8 l. u: X2 H% FUsername Game ID Date Attempts (Desired Attempts col.)
0 N4 Q' ?5 X$ H% Yjohndoe1 Game_1 100 7/22/14 1:52 AM 1 1
# _8 A2 D0 E4 @johndoe1 Game_1 100 7/22/14 1:52 AM 2 19 f. [; D1 Q+ H, _0 U6 [5 q
johndoe1 Game_1 100 7/22/14 1:52 AM 3 1
6 \( y+ S3 @' |& I: j+ g# d2 Ajohndoe1 Game_1 100 7/22/14 1:52 AM 4 1
$ P9 A; C, S- z1 q, B4 |johndoe1 Game_1 121 7/22/14 1:56 AM 1 2
* B$ E& u9 s7 ~. u& cjohndoe1 Game_1 121 7/22/14 1:56 AM 2 20 ^9 p- g3 d" w4 N; L8 |
johndoe1 Game_1 121 7/22/14 1:56 AM 3 2# W1 a$ n2 e7 J
johndoe1 Game_1 121 7/22/14 1:56 AM 4 2' A" D9 z* q* @6 J5 J9 @: p8 b
johndoe1 Game_1 121 7/22/14 1:56 AM 5 27 ~9 ~& {9 ~9 t& `2 ~. [$ N7 }
johndoe1 Game_1 130 7/22/14 1:59 AM 1 3
. O* K7 l4 m6 j6 A% J$ S3 B+ t Djohndoe1 Game_1 130 7/22/14 1:59 AM 2 3
* y2 \" _' Z4 Q1 R! S( f2 I! pjohndoe1 Game_1 130 7/22/14 1:59 AM 3 3" b* o b2 ^" m
johndoe1 Game_1 130 7/22/14 1:59 AM 4 3
+ A; z( \/ `# L: D( _5 f# Rjohndoe1 Game_1 130 7/22/14 1:59 AM 5 3" y3 N5 ~: l6 Y0 X/ Y9 E6 u
johndoe1 Game_1 200 7/22/14 2:54 AM 1 4
$ x+ ^& p& I* jjohndoe1 Game_1 200 7/22/14 2:54 AM 2 4
# [- z2 o' k. t9 Ajohndoe1 Game_1 200 7/22/14 2:54 AM 3 4
- I2 S9 Q5 j S5 _ E7 Mjohndoe1 Game_1 200 7/22/14 2:54 AM 4 4
3 F9 X/ l8 U4 C. W x& Sjohndoe1 Game_1 210 7/22/14 3:54 AM 1 5
$ C: {; N! Y @) F9 qjohndoe1 Game_1 210 7/22/14 3:54 AM 2 5
. |, Y7 P) u ajohndoe1 Game_1 210 7/22/14 3:54 AM 3 5/ c7 _+ D2 k# S
johndoe1 Game_1 210 7/22/14 3:54 AM 4 5
' J! W9 [# ?# h* G# c ]任何指针都会有很大帮助。* N+ ?2 j, T! k1 x' h
, K+ U" M5 @7 o5 |' h S& N
解决方案:2 r# J) Z( `0 A: [. q
" }( y! r( d7 s2 G- F- h# ?+ L" R. B
8 ]: a6 M; v) \8 D, W! G4 @, r& ]" u 考虑partition by与您想要的字段相似group by，然后，当分区值更改时，窗口函数将从1重新启动。+ J" e3 F7 v6 Y* Y( G8 j+ o" V6 ~
如a_horse_with_no_name所指示的那样进行编辑，为此，我们需要与之dense_rank() 不同row_number()$ n+ ~* T8 j( W6 n' ]
rank()或dense_rank()重复其分配的数字。row_number()对于分区中的每一行，其值必须不同。rank()和之间的区别dense_rank()是后者不会“跳过”数字。
8 f L' n( |( A: h7 p. e3 q对于您的查询，请尝试：- `: P- U' ~8 @# g" s0 Z
dense_rank() over (partition by Username, Game order by ct."date") as "Attempts"
$ l% y: Y" t5 N: B% }) h顺便说一下，您不会按相同的字段进行划分和排序。如果需要的话，只需订购即可。不在这里 |
|
|
|
|
|
|
|
|
|
