Oracle如何查询产品连续状态记录的日期范围？

核心思路 #

通过窗口函数标记状态变化的分界点，将连续相同状态的记录归为同一组，再筛选出每个产品最新状态组的最早日期。

实现代码 #

WITH ranked_data AS (
    -- 按产品分组、状态日期倒序排序，同时标记状态变化点
    SELECT 
        product_id,
        status,
        status_date,
        -- 标记当前状态与上一条是否不同（不同则为1，相同为0）
        CASE 
            WHEN LAG(status) OVER (PARTITION BY product_id ORDER BY status_date DESC) != status 
            THEN 1 
            ELSE 0 
        END AS status_change,
        -- 累计状态变化次数，生成连续状态组的ID
        SUM(CASE 
            WHEN LAG(status) OVER (PARTITION BY product_id ORDER BY status_date DESC) != status 
            THEN 1 
            ELSE 0 
        END) OVER (PARTITION BY product_id ORDER BY status_date DESC) AS group_id
    FROM product
),
latest_groups AS (
    -- 筛选每个产品最新的状态组，取组内最早日期
    SELECT 
        product_id,
        status,
        MIN(status_date) AS latest_continuous_start_date
    FROM ranked_data
    WHERE group_id = (SELECT MIN(group_id) FROM ranked_data WHERE product_id = ranked_data.product_id)
    GROUP BY product_id, status
)
-- 输出最终结果
SELECT * FROM latest_groups;

代码说明 #

1. ranked_data 子查询：

   • 用LAG(status)获取当前记录的上一条（日期更早的）状态，对比后标记状态是否发生变化。
   • 通过SUM() OVER()累计状态变化次数，连续相同状态的记录会被分配同一个group_id（最新的连续组group_id为0，因为倒序排序后第一条记录无前置状态，状态变化标记为0，累计和也为0）。

2. latest_groups 子查询：

   • 每个产品的最新状态组对应最小的group_id，取该组内的最小status_date，即为该连续状态的起始日期。

示例验证 #

• 产品123自2024-07-15起状态持续为A：所有对应记录的group_id均为0，取MIN(status_date)得到2024-07-15。
• 产品245最新状态为I且前一状态非I：仅最新一条记录的group_id为0，取该条的status_date即2024-08-24。

边界情况处理 #

• 产品仅一条记录：LAG(status)返回NULL，status_change为0，group_id为0，最终取该条记录的日期，符合需求。
• 产品所有记录状态一致：所有status_change为0，group_id统一为0，取最早的状态日期作为起始日期。

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