Postgres 选择所有列但按一列分组 [英] Postgres select all columns but group by one column

问题描述

我有一个带有 unit_id oid、时间戳、diag bytea 的简单表.主键是 time 和 unit_id 的组合.

I have a simple table with a unit_id oid, time timestamp, diag bytea. The primary key is a combination of both time and unit_id.

此查询背后的想法是获取每个唯一 unit_id 的最新行(最大时​​间戳).但是，并非总是返回每个 unit_id 的最新时间行.

The idea behind this query is to get the latest row (largest timestamp) for each unique unit_id. However the rows for each unit_id with the latest time are not always returned.

我真的想只按 unit_id 分组，但 postgres 也让我使用 diag，因为我选择了那个.

I really want to group by just the unit_id, but postgres makes me use diag also, since I am selecting that.

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

推荐答案

任何时候您开始考虑需要本地化的 GROUP BY 时，您都应该开始考虑 窗口函数.

Any time you start thinking that you want a localized GROUP BY you should start thinking about window functions instead.

我认为你在追求这样的事情:

I think you're after something like this:

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 中添加一些内容以始终打破平局，但这不会改变整体技术.

You might want to add something to the ORDER BY to consistently break ties as well but that wouldn't alter the overall technique.

这篇关于Postgres 选择所有列但按一列分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！