聚合连续的日期范围

Question

假设您有以下PostgreSQL稀疏表，列出了预订日期:

CREATE TABLE reserved_dates (
    reserved_days_id    SERIAL  NOT NULL,
    reserved_date       DATE    NOT NULL
);

INSERT INTO reserved_dates (reserved_date) VALUES
    ('2014-10-11'),
    ('2014-10-12'),
    ('2014-10-13'),
    -- gap
    ('2014-10-15'),
    ('2014-10-16'),
    -- gap
    ('2014-10-18'),
    -- gap
    ('2014-10-20'),
    ('2014-10-21');

你如何将这些日期聚合为连续的日期范围（没有间隙的范围）？例如：

 start_date | end_date
------------+------------
 2014-10-11 | 2014-10-13
 2014-10-15 | 2014-10-16
 2014-10-18 | 2014-10-18
 2014-10-20 | 2014-10-21

这是我目前想到的，但我只能以这种方式获取start_date：

WITH reserved_date_ranges AS (
    SELECT reserved_date,
           reserved_date
           - LAG(reserved_date) OVER (ORDER BY reserved_date) AS difference
    FROM reserved_dates
)
SELECT *
FROM reserved_date_ranges
WHERE difference > 1 OR difference IS NULL;

- Linas Valiukas

---

关键词是日历表，在您的情况下::使用generate_series()左连接YourTable。 - wildplasser

好的，我会用中间日期填补空缺。那么我如何从中获取日期范围呢？ - Linas Valiukas

Answer 1

SELECT min(reserved_date) AS start_date
     , max(reserved_date) AS end_date
FROM  (
   SELECT reserved_date
        , reserved_date - row_number() OVER (ORDER BY reserved_date)::int AS grp
   FROM   reserved_dates
   ) sub 
GROUP  BY grp
ORDER  BY grp;

1. 使用窗口函数row_number()按时间顺序计算无间隔连续编号。不允许重复日期。（我在示例中添加了一个UNIQUE约束）。

   如果你的reserved_days_id已经是无间隔且按照时间顺序排列的，那么你可以直接使用它。 但通常情况下并不是这样。

2. 将每行的reserved_date（转换为整数后）减去此前的值。相邻的日期将具有相同的日期值grp——其它目的或含义仅仅是形成组。

3. 在外部查询中进行聚合操作。

db<>fiddle 这里
_{旧版sqlfiddle}

类似案例：

• 基于日期序列的排序
• 按重复属性分组

- Erwin Brandstetter

---

这个A似乎很难在一般情况下应用于具有固定分辨率的时间序列，例如每2天或每10分钟。时间戳需要转换为实际值，并且浮点运算不精确。是否有替代方案？ - mlt

我收回我的话。这个解决方案适用于时间戳列，类似于extract(epoch from timestamp) - extract(epoch from interval '1day')*row_number() over (order by timestamp)。 - mlt

经过这么多年，我意识到它并不是免于重复的证明 https://www.db-fiddle.com/f/31UdD6udUZqwQk43pRbGKZ/1 - mlt

如果您有重复数据，请使用dense_rank而不是row_number。 - mlt