PostgreSQL - синтаксис "DISTINCT ON" и "GROUP BY"

Я понял, что запрос базы данных возвращал неожиданные результаты для моего неправильного использования "DISTINCT ON" и "GROUP BY"

Я надеюсь, что кто-то может направить меня прямо на это. Фактический запрос довольно сложный, поэтому я не буду его опускать:

У меня есть таблица/внутренний запрос, который состоит из object_id и метки времени:

CREATE TABLE test_select ( object_id INT , event_timestamp timestamp );
COPY test_select (object_id , event_timestamp) FROM stdin (DELIMITER '|');
1           | 2013-01-27 21:01:20
1           | 2012-06-28 14:36:26
1           | 2013-02-21 04:16:48
2           | 2012-06-27 19:53:05
2           | 2013-02-03 17:35:58
3           | 2012-06-14 20:17:00
3           | 2013-02-15 19:03:34
4           | 2012-06-13 13:59:47
4           | 2013-02-23 06:31:16
5           | 2012-07-03 01:45:56
5           | 2012-06-11 21:33:26
\.

Я пытаюсь выбрать отдельный идентификатор, упорядоченный/дедуплицированный по метке времени на обратном хроном

поэтому результаты должны быть [4, 1, 3, 2, 5]

Я думаю, что это делает то, что мне нужно (кажется):

SELECT object_id  
FROM test_select 
GROUP BY object_id 
ORDER BY max(event_timestamp) DESC
;

Для целей тестирования/аудита я иногда хочу включить поле timestamp. Я не могу понять, как включить другое поле с этим запросом.

Может ли кто-нибудь указать на вопиющие проблемы в моем sql выше или предложения о том, как включить информацию аудита?

Ответ 1

Чтобы иметь возможность выбирать все столбцы, а не только object_id и MAX(event_timestamp), вы можете использовать DISTINCT ON

SELECT DISTINCT ON (object_id) 
    object_id, event_timestamp    ---, more columns
FROM test_select 
ORDER BY object_id, event_timestamp DESC ;

Если вы хотите, чтобы результаты были упорядочены с помощью event_timestamp DESC, а не object_id, вам нужно включить его в производную таблицу или CTE:

SELECT *
FROM 
  ( SELECT DISTINCT ON (object_id) 
        object_id, event_timestamp    ---, more columns
    FROM test_select 
    ORDER BY object_id, event_timestamp DESC 
  ) AS t
ORDER BY event_timestamp DESC ;

В качестве альтернативы вы можете использовать функции окна, например ROW_NUMBER():

WITH cte AS
  ( SELECT ROW_NUMBER() OVER (PARTITION BY object_id 
                              ORDER BY event_timestamp DESC) 
             AS rn, 
           object_id, event_timestamp    ---, more columns
    FROM test_select 
  )
SELECT object_id, event_timestamp    ---, more columns
FROM cte
WHERE rn = 1
ORDER BY event_timestamp DESC ;

или агрегат MAX() с помощью OVER:

WITH cte AS
  ( SELECT MAX(event_timestamp) OVER (PARTITION BY object_id) 
             AS max_event_timestamp, 
           object_id, event_timestamp    ---, more columns
    FROM test_select 
  )
SELECT object_id, event_timestamp    ---, more columns
FROM cte
WHERE event_timestamp = max_event_timestamp
ORDER BY event_timestamp DESC ;

Ответ 2

Вероятно, это не лучший способ справиться с этим, но вы можете попробовать использовать функцию окна:

SELECT DISTINCT object_id, MAX(event_timestamp)
OVER (PARTITION BY object_id)  
FROM test_select ORDER BY max DESC;

С другой стороны, он работает также:

SELECT object_id, MAX(event_timestamp) as max_event_timestamp
FROM test_select 
GROUP BY object_id 
ORDER BY max_event_timestamp DESC;