postgresql-9.3 - 确定 PostgreSQL 中的共轭起始行

Question

我正在尝试确定 PostgreSQL 中的共轭起始行

我的表名 emp 有两列 id(integer) 和 entry_date(date)。像这样的记录

ID  |  entry_date 
----+-------------
1   |  2018-05-03 
5   |  2018-06-10 
6   |  2018-06-11 
1   |  2018-07-13 
5   |  2018-07-14 
5   |  2018-07-15 
5   |  2018-07-16 
5   |  2018-07-17 

现在我想找出开始共轭记录(5, '2018-07-14')和结束记录(5, '2018-07-17')

标签： postgresql-9.3gaps-and-islands

Answer 1

你可以这样做：

select id, min(entry_date) as start_date, max(entry_date) as end_date
from (
  select id, 
         entry_date, 
         entry_date - (row_number() over(partition by id order by entry_date))::int as grp
  from the_table
) t
group by id, grp
having max(entry_date) - min(entry_date) > 1
order by id, grp

该表达式entry_date - (row_number() over(partition by id order by entry_date))::int为所有连续的“组”创建一个 DATE 值。因此内部查询返回以下结果（基于您的示例数据）：

id | entry_date | grp       
---+------------+-----------
 1 | 2018-05-03 | 2018-05-02
 1 | 2018-07-13 | 2018-07-11
 5 | 2018-06-10 | 2018-06-09
 5 | 2018-07-14 | 2018-07-12
 5 | 2018-07-15 | 2018-07-12
 5 | 2018-07-16 | 2018-07-12
 5 | 2018-07-17 | 2018-07-12
 6 | 2018-06-11 | 2018-06-10

通过将该日期 ( grp) 的结果分组，我们将这些日期作为一个组保存在一起。然后最小值和最大值定义连续值列表的开始和结束。然后，have 子句只留下范围大于一天的那些。

然后返回：

id | start_date | end_date  
---+------------+-----------
 5 | 2018-07-14 | 2018-07-17

在线示例