PostgreSQL -``令人震惊的ISTINCT ON''和``ROUP BY''语法

吃瓜网友

我意识到数据库查询返回了意外的结果，这是由于我不正确地使用了“ DISTINCT ON”和“ GROUP BY”
我希望有人能使我对此有所了解。实际的查询非常复杂，因此我将其简化：
+ h6 }& O% U4 v, s: w- M: C& X% C我有一个由object_id和时间戳组成的表/内部查询：
CREATE TABLE test_select ( object_id INT , event_timestamp timestamp );
COPY test_select (object_id , event_timestamp) FROM stdin (DELIMITER '|');
" P% Z8 z1 K1 S4 ~$ p  w: I1           | 2013-01-27 21:01:20
6 ^- e. }' f+ S* W2 }8 i0 ~% X1           | 2012-06-28 14:36:26
1           | 2013-02-21 04:16:48
# r' o2 q. V# O1 l& e: P2 g* Q2           | 2012-06-27 19:53:05
2           | 2013-02-03 17:35:58
3           | 2012-06-14 20:17:00
) e$ G3 `: P! o" v& R: x3           | 2013-02-15 19:03:34
4           | 2012-06-13 13:59:47
% U$ N+ \7 u( b% c7 O8 _0 d4           | 2013-02-23 06:31:16
5           | 2012-07-03 01:45:56
5 `4 c: H, r& a! D5           | 2012-06-11 21:33:26
\.
我正在尝试选择一个不同的ID，按反向计时的时间戳排序/去重复
5 T  z* J* H2 }0 E所以结果应该是[4，1，3，2，5]
我认为这可以满足我的需求（似乎）：
SELECT object_id  
FROM test_select
- V, n$ Y& K( w2 x! ?) N; C9 d+ iGROUP BY object_id
ORDER BY max(event_timestamp) DESC
4 [$ }- M& h: t; T* O  m# k;
7 C9 |0 C" M2 X2 b出于测试/审核的目的，有时我想包括timestamp字段。我似乎无法弄清楚如何在该查询中包含另一个字段。
/ s5 o; L" P! H, j任何人都可以指出以上我的sql中明显的问题，或有关如何包括审核信息的建议吗？
               
解决方案:
* m+ B, |0 Z; G& a0 M) {- r               
& N5 \/ s3 C6 Y+ ?- F
4 _) \+ ?$ w' I$ `
                为了能够选择所有列，而不仅是object_id和MAX(event_timestamp)，您可以使用DISTINCT ON
SELECT DISTINCT ON (object_id)
1 L+ }" J, ?( W! g. z* j7 e  B    object_id, event_timestamp    ---, more columns
FROM test_select
2 e3 r+ Z0 x; P% B4 F/ rORDER BY object_id, event_timestamp DESC ;
; W9 `. q8 p  P0 V: I3 D如果希望结果按event_timestamp DESC而不是按进行排序object_id，则需要将其包括在派生表或CTE中：
SELECT *
: A" Q# W$ U4 N% {# l% [; pFROM
' h6 D7 z4 Z  e0 m  ( SELECT DISTINCT ON (object_id)
+ E; R8 ~6 t) ^7 _( V        object_id, event_timestamp    ---, more columns
    FROM test_select
    ORDER BY object_id, event_timestamp DESC
  ) AS t
ORDER BY event_timestamp DESC ;
# c7 B( X, L# Y7 x6 J% e( c+ d, n; A/ }另外，您可以使用窗口功能，例如ROW_NUMBER()：
6 Q  K% @0 x9 }2 a4 }1 uWITH cte AS
  ( SELECT ROW_NUMBER() OVER (PARTITION BY object_id
                              ORDER BY event_timestamp DESC)
             AS rn,
6 X9 Y2 `/ b$ B" V' `  d: u3 w% s           object_id, event_timestamp    ---, more columns
, L/ I( {( E$ B( s8 t2 T0 y    FROM test_select
  )
SELECT object_id, event_timestamp    ---, more columns
FROM cte
0 K. P2 a0 Q9 m4 sWHERE rn = 1
! G. }' R2 o" x4 PORDER BY event_timestamp DESC ;
或MAX()与OVER：
WITH cte AS
) h# h: ?' D. Q' m& {/ P! ^  o  ( SELECT MAX(event_timestamp) OVER (PARTITION BY object_id)
+ G, C& {$ t& |" @) Q             AS max_event_timestamp,
* e9 l% r0 f) T, Y  r# h& l           object_id, event_timestamp    ---, more columns
' @, X* [1 O! E, O* v% [    FROM test_select
( C+ P- P# c5 v; I3 {6 L. j5 T' g  )
. M  O2 H- D5 N  VSELECT object_id, event_timestamp    ---, more columns
2 w, P# l" D3 F! tFROM cte
6 H! ~, Q8 r# ~) CWHERE event_timestamp = max_event_timestamp
ORDER BY event_timestamp DESC ;