Postgres выбирает все столбцы, но группируется по одному столбцу

У меня есть простая таблица с идентификатором unit_id, временной меткой времени, diag bytea. Первичный ключ - это комбинация времени и unit_id.

Идея этого запроса состоит в том, чтобы получить последнюю строку (наибольшую временную метку) для каждого уникального unit_id. Однако строки для каждого unit_id с последним временем не всегда возвращаются.

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

SELECT DISTINCT ON(unit_id) max(time) as time, diag, unit_id 
FROM diagnostics.unit_diag_history  
GROUP BY unit_id, diag

Ответ 1

В любое время, когда вы начинаете думать, что хотите локализовать GROUP BY, вы должны начать думать о функции окна.

Я думаю, что вы после чего-то вроде этого:

select unit_id, time, diag
from (
    select unit_id, time, diag,
           rank() over (partition by unit_id order by time desc) as rank
    from diagnostics.unit_diag_history
) as dt
where rank = 1

Возможно, вы захотите добавить что-то в ORDER BY, чтобы последовательно разорвать связи, но это не изменило бы общую технику.

Ответ 2

Вы можете присоединиться к сгруппированному элементу с исходной таблицей:

SELECT d.time, d.diag, d.unit_id
FROM(
    SELECT unit_id, max(time) as max_time
    FROM diagnostics.unit_diag_history
    GROUP BY unit_id
) s JOIN diagnostics.unit_diag_history d
ON s.unit_id = d.unit_id AND s.max_time = d.time