sql - 从 PostgreSQL 的有序日期开始

Question

我有一张像

group | date
a     | 2002-01-03
a     | 2002-01-17
a     | 2002-03-18
b     | 2001-01-02

我想把它变成一系列跨度，比如

group | start      | stop
a     | 2002-01-03 | 2002-01-17
a     | 2002-01-17 | 2002-03-18
a     | 2002-03-18 |
b     | 2001-01-02 | 

我可以这样做

select distinct on (left.group, left.date) 
    left.group as group,
    left.date as start,
    right.date as stop
from dates as left
left join dates as right
on left.group = right.group
    and left.date < right.date
order by group, left.date, right.date

但是有没有更好的方法来做到这一点（大连接和排序在真实表上很慢）

标签： sqlpostgresql

Answer 1

使用lead()：

select d.*, lead(d.date) over (partition by d.group order by date)
from dates d
order by group, start;