sql - 检测历史表中特定列的变化

Question

考虑以下数据:

history.data
=======================================
id |data_id| col1  | col2  | date
---+-------+-------+-------+-----------
1  |1      | 123   | 321   | 2017-08-01
2  |1      | 124   | 321   | 2017-08-03
3  |2      | 222   | 555   | 2017-08-05
4  |2      | 242   | 555   | 2017-08-07
5  |2      | 242   | 333   | 2017-08-11

这是 history_data 表，我将所有更改保存在某个表中。现在我需要获取 col1 列中每个当前 data 条目的最后更改日期。在那种情况下，所需的输出应该是

data_id | date
--------+-----------
1       | 2017-08-03
2       | 2017-08-07

我需要在以下上下文中执行此操作:

with cte1 as (
    select distinct on(data_id)
    data_id,
    date::date

    from data d
    join history.data hd on hd.data_id = d.id
    order by d.id, hd.date desc
)

如您所见，现在我只是获取最后一次记录更改的日期，而不管更改发生在哪一列。

有人可以帮我吗？

Answer 1

您可以使用 lag()得到以前的 prev_col1值(value)和prev_col1 <> col1识别发生更改的所有行:

select distinct on(data_id) * from (
    select lag(col1) over (partition by data_id order by d.id) prev_col1,
    d.id,
    col1,
    data_id,
    date::date
    from data d
    join history.data hd on hd.data_id = d.id
) t where prev_col1 <> col1 or prev_col1 is null
order by id desc

prev_col1 is null每个 data_id 只有 1 个成员的组需要条件并假设第一个成员符合变更条件。