查询历史数据

Question

为了描述我的查询问题，以下数据是有用的：

一张单独的表包含ID（整数）、VAL（字符型）和ORD（整数）这些列。

VAL的值可能会随时间变化而更改，因此由ID标识的旧项目不会被更新，而是追加。对于ID，最后一个有效项目由最高的ORD值（随时间递增）确定。

T0、T1和T2是数据输入的时间点。

如何以有效的方式获得结果集？

解决方案不应涉及物化视图等内容，但应能够在单个SQL查询中表示。使用Postgresql 9.3。

- JohnDoe

---

可能是重复的问题：如何在PostgreSQL中按类别选择具有最大日期的id？ - Jakub Kania

Answer 1

选择在postgres中进行分组最大值的正确方法是使用DISTINCT ON。具体操作请参考DISTINCT ON。

SELECT DISTINCT ON (id) sysid, id, val, ord
FROM my_table
ORDER BY id,ord DESC;

Fiddle

- Jakub Kania

Answer 2

您想获取所有没有更新记录的记录：

select *
from mytable
where not exists
(
  select *
  from mytable newer
  where newer.id = mytable.id
  and newer.ord > mytable.ord
)
order by id;

您可以使用行号完成相同的操作。将每个ID的最新条目标记为1并保留这些内容：

select sysid, id, val, ord
from
(
  select 
    sysid, id, val, ord, 
    row_number() over (partition by id order by ord desc) as rn
  from mytable
)
where rn = 1
order by id;

- Thorsten Kettner

Answer 3

将表(A)与自身的表(B)进行左连接，条件是B比A更新。仅选择B不存在的行（即A是最新行）。

SELECT last_value.* 
FROM my_table AS last_value
  LEFT JOIN my_table
    ON my_table.id = last_value.id
    AND my_table.ord > last_value.ord
WHERE my_table.id IS NULL;

SQL Fiddle

的英译中，保留了 HTML 标签，不做解释。

- abl