时间戳

首页 > 解决方案 > PostgreSQL：排除按另一个键排序的重复项

postgresql - PostgreSQL：排除按另一个键排序的重复项

问题描述

考虑下表，它存储了某些对象的某些属性的更新历史记录，按effective日期published组织：

create table update_history(
  obj_id integer,
  effective date,
  published date,
  attr1 text,
  attr2 integer,
  attr3 boolean,
  primary key(obj_id, effective, published)
);
insert into update_history values
  (1, '2021-01-01', '2021-01-01', 'foo', null, null),
  (1, '2021-01-01', '2021-01-02', null, 1, false),
  (1, '2021-01-02', '2021-01-01', 'foo', 1, false),
  (1, '2021-01-02', '2021-01-02', 'bar', 1, false),
  (1, '2021-01-03', '2021-01-01', 'bar', 1, true),
  (1, '2021-01-04', '2021-01-01', 'bar', 1, true),
  (1, '2021-01-05', '2021-01-01', 'bar', 2, true),
  (1, '2021-01-05', '2021-01-02', 'bar', 1, true),
  (1, '2021-01-05', '2021-01-03', 'bar', 1, true),
  (1, '2021-01-06', '2021-01-04', 'bar', 1, true)
;

我需要编写一个 PostgreSQL 查询，该查询将obj_id通过排除那些未更改前一个更新中的任何属性的更新记录来简化给定的历史视图，这些更新记录按effective和published列排序。实质上，这些将是第 ## 6、9 和 10 行，在下表中以斜体标记：

# obj_id 有效的 发表 属性1 属性2 属性 3
1 1 2021-01-01 2021-01-01 富 （无效的） （无效的）
2 1 2021-01-01 2021-01-02 （无效的） 1 错误的
3 1 2021-01-02 2021-01-01 富 1 错误的
4 1 2021-01-02 2021-01-02 酒吧 1 错误的
5 1 2021-01-03 2021-01-01 酒吧 1 真的
6 1 2021-01-04 2021-01-01 酒吧 1 真的
7 1 2021-01-05 2021-01-01 酒吧 2 真的
8 1 2021-01-05 2021-01-02 酒吧 1 真的
9 1 2021-01-05 2021-01-03 酒吧 1 真的
10 1 2021-01-06 2021-01-04 酒吧 1 真的

请记住，在现实生活中，有更多的属性需要处理，我不希望查询变得太混乱。

我最接近所需结果的是使用rank窗口函数：

select
  obj_id, effective, published,
  attr1, attr2, attr3
from (
  select *,
    rank() over (
      partition by attr1, attr2, attr3
      order by effective, published
    ) as rank
  from update_history
  where obj_id = 1) as d
where rank = 1
order by effective, published;

结果是：

obj_id 有效的 发表 属性1 属性2 属性 3
1 2021-01-01 2021-01-01 富 （无效的） （无效的）
1 2021-01-01 2021-01-02 （无效的） 1 错误的
1 2021-01-02 2021-01-01 富 1 错误的
1 2021-01-02 2021-01-02 酒吧 1 错误的
1 2021-01-03 2021-01-01 酒吧 1 真的
1 2021-01-05 2021-01-01 酒吧 2 真的

如您所见，原始表中的第 8 行被错误地排除在外，尽管它attr2与之前的第 7 行有所不同。显然，问题在于在窗口定义中排序之前应用了分区。

我想知道是否有另一种方法可以通过单个 PostgresSQL 查询来完成此任务。

标签： postgresqlwindow-functions

解决方案

我会用lag()这个：

select *
from (
  select obj_id, effective, published,
         attr1, attr2, attr3, 
         (attr1, attr2, attr3) is distinct from lag( (attr1,attr2,attr3) ) over (partition by obj_id order by effective, published) as is_different
  from update_history
) t
where is_different

推荐阅读