postgresql 获取group-by子句中未指定的列

yvt65v4c 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(3)|浏览(162)

需要从group-by查询中检索一列，而此列不是group-by子句的一部分。
我有一个从设备接收遥测数据的表：
列：

transmission_tag varchar --这是一种传输包标识符
equipment_id int
measurement int --测量值
uts int --测量的时间戳'unixtime'

然后我需要从每个设备获取最新的测量结果：

SELECT equipment_id, max(uts)
FROM telemetry
GROUP BY equipment_id
-- works OK and quick, the table has ~100mi registers and has index on (equipment_id, uts)

问题是我需要得到与这个寄存器相关的“transmission_tag”。
我尝试了“WHERE EXISTS”/“WITH auxquery（...）SELECT..."，但我无法解决这个问题。
下面的示例检索的正是我需要的（出于测试目的），但它真的很愚蠢。在真实的世界的数据库中，它需要1小时才能运行（它对所有寄存器进行顺序扫描）。

SELECT transmission_tag
FROM telemetry
WHERE CAST(equipment_id as VARCHAR) || '_' || CAST(uts as VARCHAR) IN
 (SELECT CAST(equipment_id as VARCHAR) || '_' || CAST(max(uts) as VARCHAR)
  FROM telemetry
  GROUP BY equipment_id)

表中的行是唯一的，只有一行具有（equipment_id，uts）。
如果答案是特定于数据库的，我使用Postgres。

postgresql

来源：https://stackoverflow.com/questions/76532401/get-a-column-that-is-not-specified-in-group-by-clause

3条答案

按热度按时间

ki0zmccv1#

使用inner join连接数据集和表，然后获取transmission_tag：

SELECT t.equipment_id, s.max_uts, t.transmission_tag
FROM telemetry t
INNER JOIN (
  SELECT equipment_id, max(uts) AS max_uts
  FROM telemetry
  GROUP BY equipment_id
) as s on s.equipment_id = t.equipment_id and s.max_uts = t.uts

赞(0）回复(0）举报 2023-06-29

q5iwbnjs2#

我需要从每个设备中获取最新的测量结果[...]问题是我需要获取与此寄存器相关的transmission_tag。
您希望对表进行“筛选”，而不是聚合它。在Postgres中，我会推荐distinct on，它完全符合您的要求：
SELECT DISTINCT ON ( expression [, ...] )只保留每组行的第一行
这将为您提供每个设备的整个最新行：

select distinct on (equipment_id) t.*
from telemetry t
order by equipment_id, uts desc

赞(0）回复(0）举报 2023-06-29

yvgpqqbh3#

你可以使用distinct on子句;

select distinct on (equipment_id)
    equipment_id, 
    transmission_tag,
    uts
  from telemetry
  order by equipment_id, uts desc;

该查询表示order by equipment_id, uts desc，然后获取每个不同equipment_id的第一行。接下来，您有了自己的行，并且可以从该行获取任何字段值。

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql 获取group-by子句中未指定的列

3条答案

相关问题

热门标签

最新问答