Oracle：值交替时如何获取特定值的当前连续最早记录？

你的问题核心是要找到当前处于不合规状态的连续时间段的起始日期，而不是历史上所有不合规记录的最早日期——这也是直接取MIN(date)会出错的原因，因为它会把之前被合规状态打断的旧不合规记录也算进去。

下面给你两种实用的SQL方案，都能精准定位到本次不合规的起始日期：

方案一：基于「最后一次合规日期」筛选 #

这个思路很直观：先找到每个资产最后一次合规的日期，然后取该日期之后的最早不合规日期（如果资产从未合规过，就直接取最早的不合规日期）。同时我们还要确保资产的最新状态确实是不合规，避免把已经变回合规的资产也纳入结果。

假设你的表名为asset_compliance，字段是asset_name（资产名称）、record_date（记录日期）、compliance_status（0=不合规，1=合规），SQL代码如下：

WITH asset_last_compliant AS (
    -- 先找出每个资产最后一次合规的日期
    SELECT 
        asset_name,
        MAX(record_date) AS last_compliant_date
    FROM asset_compliance
    WHERE compliance_status = 1
    GROUP BY asset_name
)
SELECT 
    ac.asset_name,
    MIN(ac.record_date) AS current_non_compliant_start_date
FROM asset_compliance ac
LEFT JOIN asset_last_compliant alc ON ac.asset_name = alc.asset_name
WHERE 
    ac.compliance_status = 0
    -- 筛选出最后一次合规之后的不合规记录，或者从未合规的资产的所有不合规记录
    AND (ac.record_date > alc.last_compliant_date OR alc.last_compliant_date IS NULL)
    -- 确保资产最新状态是不合规
    AND ac.asset_name IN (
        SELECT asset_name
        FROM (
            SELECT 
                asset_name,
                compliance_status,
                ROW_NUMBER() OVER (PARTITION BY asset_name ORDER BY record_date DESC) AS rn
            FROM asset_compliance
        ) t
        WHERE rn = 1 AND compliance_status = 0
    )
GROUP BY ac.asset_name;

用你的示例数据测试：

• 资产NAME的最后一次合规日期是31-JAN-18，筛选出该日期之后的不合规记录是1-FEB-18和2-FEB-18，取MIN(date)就是1-FEB-18，完全符合你的期望。

方案二：用窗口函数划分连续状态段 #

如果需要更灵活地分析所有状态变化的时间段，可以用窗口函数把连续相同状态的记录归为同一个分组，然后找到最新的状态分组（如果是不合规），取该组的最早日期。

SQL代码如下：

WITH ranked_records AS (
    -- 给连续相同状态的记录分配同一个分组ID
    SELECT 
        asset_name,
        record_date,
        compliance_status,
        SUM(
            CASE 
                WHEN compliance_status = LAG(compliance_status) OVER (PARTITION BY asset_name ORDER BY record_date) 
                THEN 0 
                ELSE 1 
            END
        ) OVER (PARTITION BY asset_name ORDER BY record_date) AS status_group
    FROM asset_compliance
),
latest_status_groups AS (
    -- 找到每个资产最新的状态分组（按状态段的结束日期降序排序）
    SELECT 
        asset_name,
        status_group,
        compliance_status,
        ROW_NUMBER() OVER (PARTITION BY asset_name ORDER BY MAX(record_date) DESC) AS rn
    FROM ranked_records
    GROUP BY asset_name, status_group, compliance_status
)
SELECT 
    rr.asset_name,
    MIN(rr.record_date) AS current_non_compliant_start_date
FROM ranked_records rr
JOIN latest_status_groups lsg ON rr.asset_name = lsg.asset_name AND rr.status_group = lsg.status_group
-- 只取最新状态为不合规的分组
WHERE lsg.rn = 1 AND lsg.compliance_status = 0
GROUP BY rr.asset_name;

用你的示例数据测试：

• 状态分组会被划分为：30-JAN-18（0）是组1，31-JAN-18（1）是组2，1-FEB-18（0）、2-FEB-18（0）是组3。最新的组是组3（状态0），取该组的最小日期就是1-FEB-18。

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