PostgreSQL窗口函数：row_number（）以上（由col2划分col顺序）
技术问答
310 人阅读
|
0 人回复
|
2023-09-14
|
|
以下结果集是从带有几个联接和一个并集的sql查询中得出的。sql查询已将“日期”和“游戏”上的行分组。我需要一列来描述按日期列划分的游戏尝试次数。3 L2 D @: m0 N) {( j
Username Game ID Date& i7 S! H9 E \: y/ L0 B
johndoe1 Game_1 100 7/22/14 1:52 AM
X. d+ w& A3 }/ \# i- j) Pjohndoe1 Game_1 100 7/22/14 1:52 AM
6 s2 C: _ ~! T4 ?johndoe1 Game_1 100 7/22/14 1:52 AM
( H* y }8 W5 C7 V6 }9 djohndoe1 Game_1 100 7/22/14 1:52 AM4 e8 l* i! c! c! B
johndoe1 Game_1 121 7/22/14 1:56 AM0 `2 x$ f9 A+ H6 A" K( d5 V/ b
johndoe1 Game_1 121 7/22/14 1:56 AM
1 e: e0 p% a; E6 Jjohndoe1 Game_1 121 7/22/14 1:56 AM
( f6 K! ?6 ?8 Q# i0 w/ E mjohndoe1 Game_1 121 7/22/14 1:56 AM
; y! _' p) E ?johndoe1 Game_1 121 7/22/14 1:56 AM
+ d! H5 v; e U$ {. F }7 g; Rjohndoe1 Game_1 130 7/22/14 1:59 AM0 Z9 I$ a% m2 S+ H0 |, Y0 j W/ [
johndoe1 Game_1 130 7/22/14 1:59 AM
: z2 l' q' Y3 ]# ]4 Ijohndoe1 Game_1 130 7/22/14 1:59 AM3 [9 P; W: C; B$ Q( ?" g1 H
johndoe1 Game_1 130 7/22/14 1:59 AM. c* W2 H) |. |9 ?
johndoe1 Game_1 130 7/22/14 1:59 AM4 {5 u: Z+ D" b' ~
johndoe1 Game_1 200 7/22/14 2:54 AM( ~! z/ L: I+ K1 P9 o# s
johndoe1 Game_1 200 7/22/14 2:54 AM
* e8 `+ F% \: T5 d! C7 {8 S: Ujohndoe1 Game_1 200 7/22/14 2:54 AM/ [' q% l6 Y; x9 ~. Q$ w- _7 k
johndoe1 Game_1 200 7/22/14 2:54 AM) a+ Z, }! R2 V, p
johndoe1 Game_1 210 7/22/14 3:54 AM; X+ \: V _& c) P$ x
johndoe1 Game_1 210 7/22/14 3:54 AM
' s5 R* L* U/ bjohndoe1 Game_1 210 7/22/14 3:54 AM3 ?& \+ F1 |# g4 C
johndoe1 Game_1 210 7/22/14 3:54 AM
c5 |; U; t. Y. U我有以下sql查询，该查询枚举了分区中的行，但并不完全正确，因为我想根据日期和游戏来计算该游戏的实例数。在这种情况下，johndoe1已尝试在Game_1中按时间戳划分五次。
( m2 @! t- f/ B6 e1 y" a$ U9 T该查询返回以下结果集
# E2 x5 l' D( U" F8 Yselect *
+ c! f7 z. G: S7 k7 T, r2 a, row_number() over (partition by ct."date" order by ct."date") as "Attempts"* T6 p& Z1 i, p, N
from csv_temp as ct
) D( C( f' t8 Z" ?, I; I8 [Username Game ID Date Attempts (Desired Attempts col.)1 f9 |+ L2 j5 K3 K: ~/ _
johndoe1 Game_1 100 7/22/14 1:52 AM 1 1- F" ~& x" D& n5 F; P) d2 G+ v
johndoe1 Game_1 100 7/22/14 1:52 AM 2 1# E P& n4 \' L- v6 d n
johndoe1 Game_1 100 7/22/14 1:52 AM 3 1
3 J: n2 ?2 s9 j2 Q& zjohndoe1 Game_1 100 7/22/14 1:52 AM 4 1
" b P3 p: J/ h, }* F8 G" g: f7 Ujohndoe1 Game_1 121 7/22/14 1:56 AM 1 2+ x( k, t; F- v: l, W2 N
johndoe1 Game_1 121 7/22/14 1:56 AM 2 2- }4 t/ t! f% }; e6 n" k4 K# o' `4 H
johndoe1 Game_1 121 7/22/14 1:56 AM 3 2
; h9 G: F5 o% }) _2 {johndoe1 Game_1 121 7/22/14 1:56 AM 4 2, }0 ?# k* m4 }
johndoe1 Game_1 121 7/22/14 1:56 AM 5 2
2 y% p; K9 n: I& z( f2 s) Y) ^johndoe1 Game_1 130 7/22/14 1:59 AM 1 3
* ]1 [& h5 x- \! [. r, h$ X! X2 Gjohndoe1 Game_1 130 7/22/14 1:59 AM 2 3
7 M3 M) B; Z! M" Xjohndoe1 Game_1 130 7/22/14 1:59 AM 3 30 N9 s, i9 H( A a. \, w
johndoe1 Game_1 130 7/22/14 1:59 AM 4 3 ]# f% p: P c/ s. U- c9 p& M0 b7 x6 x
johndoe1 Game_1 130 7/22/14 1:59 AM 5 38 L% N9 _" u6 @! y5 Y
johndoe1 Game_1 200 7/22/14 2:54 AM 1 4; N/ X+ G4 J7 X
johndoe1 Game_1 200 7/22/14 2:54 AM 2 4) M" m; B4 k, E/ w- v
johndoe1 Game_1 200 7/22/14 2:54 AM 3 4
9 T& @5 j7 _: b' b q4 sjohndoe1 Game_1 200 7/22/14 2:54 AM 4 4
$ T* k# [/ m7 I0 t! C: Xjohndoe1 Game_1 210 7/22/14 3:54 AM 1 5: a# d5 ?# ^/ G1 C- D' ~
johndoe1 Game_1 210 7/22/14 3:54 AM 2 5
& Y+ `. S; k& ^& b$ D- a3 W& u. Xjohndoe1 Game_1 210 7/22/14 3:54 AM 3 50 F8 P, ~2 M i2 F
johndoe1 Game_1 210 7/22/14 3:54 AM 4 5
1 t9 I/ D! R1 t/ V; r7 O( x; N任何指针都会有很大帮助。
% X# z3 a( [( n4 U
4 m0 n i( w- C7 @- W I/ Y解决方案:2 w8 n& p) H( ]) o
0 ^9 X" H/ ^! ^- k
0 _% Z9 ]+ c% C; d" O5 N3 [8 V7 @+ N5 W' [& v+ ^. t
考虑partition by与您想要的字段相似group by，然后，当分区值更改时，窗口函数将从1重新启动。
1 j5 p- f) ], b: G$ ?如a_horse_with_no_name所指示的那样进行编辑，为此，我们需要与之dense_rank() 不同row_number()8 X1 S1 Y9 b7 j2 ?6 B4 F
rank()或dense_rank()重复其分配的数字。row_number()对于分区中的每一行，其值必须不同。rank()和之间的区别dense_rank()是后者不会“跳过”数字。
% p2 t2 w# R, w6 V* M8 Y对于您的查询，请尝试：
, E! y# a4 U* ^- o" e& I; \3 _1 c( pdense_rank() over (partition by Username, Game order by ct."date") as "Attempts", } I; ]/ _' Y
顺便说一下，您不会按相同的字段进行划分和排序。如果需要的话，只需订购即可。不在这里 |
|
|
|
|
|
|
|
|
|
