PostgreSQL如何查询连续日期记录中最新的缺货起止时间段

• 这是典型的连续序列分组问题，核心思路是通过「日期减去对应行号」生成连续序列的唯一分组标识，同一段连续日期的计算结果完全相同
• 具体步骤如下：
  1. 先筛选所有stock=0的缺货记录
  2. 按user_id、store_id分区，对分区内的日期升序排列，生成行号
  3. 用日期减去行号对应的天数，得到分组标识grp，连续日期的grp值一致
  4. 按user_id、store_id、grp分组，计算每段连续缺货的开始日期（最小日期）和结束日期（最大日期）
  5. 对每个user_id、store_id下的所有连续缺货段按结束日期降序排序，取排名第一的就是最新的一段

WITH ranked_stock AS (
    -- 给每个用户+门店下的缺货日期排序生成行号
    SELECT 
        user_id,
        store_id,
        date,
        ROW_NUMBER() OVER (PARTITION BY user_id, store_id ORDER BY date ASC) AS rn
    FROM your_table_name
    WHERE stock = 0
),
grouped_stock AS (
    -- 生成连续日期分组标识
    SELECT 
        user_id,
        store_id,
        date,
        date - rn * INTERVAL '1 day' AS grp
    FROM ranked_stock
),
periods AS (
    -- 计算每段连续缺货的起止日期
    SELECT 
        user_id,
        store_id,
        MIN(date) AS startDate,
        MAX(date) AS endDate
    FROM grouped_stock
    GROUP BY user_id, store_id, grp
),
ranked_periods AS (
    -- 给每个用户+门店下的缺货段按结束时间倒序排名
    SELECT 
        user_id,
        store_id,
        startDate,
        endDate,
        RANK() OVER (PARTITION BY user_id, store_id ORDER BY endDate DESC) AS rk
    FROM periods
)
-- 取最新的一段
SELECT user_id, store_id, startDate, endDate
FROM ranked_periods
WHERE rk = 1
ORDER BY user_id, store_id;

注意：请将SQL中的your_table_name替换为实际的业务表名。

按你提供的测试数据执行上述SQL，输出结果除user_id=389、store_id=2的记录会返回2021-10-27作为起止日期（因为该条记录是389在门店2的最新缺货记录，为单日连续段），其余结果均和你给出的期望输出一致。如果你提供的期望输出中389的记录是预期结果，请检查测试数据中2021-10-27这条记录的stock值是否有误，或者是否有其他筛选条件未说明。

内容的提问来源于stack exchange，提问作者OArmas