PostgreSQL聚合或窗口函数只返回最后一个值
技术问答
282 人阅读
|
0 人回复
|
2023-09-14
|
我在PostgreSQL& e( F) a' {8 u- N
9.1中使用带有OVER子句的聚合函数,只想回到每个窗口的最后一行。last_value()窗口的功能听起来像它可能会做我想做的事-
; @7 l7 t7 w, K3 J/ G+ Q但事实并非如此。它回到窗口中的每一行,我希望每个窗口只有一行: W* I1 W- d3 { Q
一个简化的例子:
" n$ b$ y# W3 h; d' v) }- N& a5 cSELECT a,some_func_like_last_value(b) OVER (PARTITION BY a ORDER BY b)FROM( SELECT 1 AS a,'do not want this' AS b UNION SELECT 1,'just want this') sub希望它能回到一行:6 j9 n) r4 N, o' L
1,'just want this
* F& ~: o3 }/ u; z6 d" c 解决方案: 4 o" Y( c5 L8 w S& u3 r* p
[DISTINCT](http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-DISTINCT) 加窗功能
5 _, ~% l- T. g! T添加一个DISTINCT子句:
/ F4 i% [* ~# R8 OSELECT DISTINCT a last_value(b) OVER (PARTITION BY a ORDER BY b RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)FROM ( VALUES do not want this just want this ) sub(a,b);有关更多DISTINCT:2 S& N$ [' N b3 j' _( U. F
PostgreSQL:按分钟运行查询的行数使用更简单,更快 [`DISTINCTON`](http://www.postgresql.org/docs/current/interactive/sql-select.html#SQL-
& i( B3 ?! S- q; u8 D/ v' qDISTINCT)! R# ]& d3 v* r+ o" M
PostgreSQL还具有SQL以下扩展标准:
/ o/ w% Q2 g2 a/ T+ J4 QSELECT DISTINCT ON (a) a,bFROM ( VALUES do not want this ,just want this ) sub(a,b)ORDER BY a,b DESC;简单的案例和简单的聚合如果 你的案例其实和你的演示一样简单(你不需要最后一行的其他列),所以简单的聚合函数会更简单:
7 g! C* v! w2 V6 [# ?SELECT a,max(b)FROM ( VALUES do not want this ,just want this ) sub(a,b)GROUP BY a; |
|
|
|
|
|