Oracle SQL中创建哑变量识别企业薪资发放状态从P转为NP的实现方法

First, let's clarify the core requirement: We need a dummy column where the value is 1 if a company (grouped by company_id and obs_period) ever switches from payroll status P to NP in its sequence of hist_period entries. If no such exit occurs (like switching from NP to P, or staying in a single status), the dummy should be 0 for all rows in that group.

Approach 1: Step-by-Step with CTEs (Easy to Debug) #

This method breaks the logic into clear stages, making it simpler to adjust or troubleshoot later:

WITH status_changes AS (
    SELECT 
        company_id,
        obs_period,
        hist_period,
        is_payroll,
        -- Flag rows where the current status is NP and the prior status was P
        CASE 
            WHEN LAG(is_payroll) OVER (
                PARTITION BY company_id, obs_period 
                ORDER BY TO_DATE(hist_period, 'YYYY/MM')
            ) = 'P' AND is_payroll = 'NP' 
            THEN 1 
            ELSE 0 
        END AS switch_to_np
    FROM your_table_name -- Replace with your actual table name
),
group_switch_flags AS (
    SELECT 
        company_id,
        obs_period,
        MAX(switch_to_np) AS has_exit_event
    FROM status_changes
    GROUP BY company_id, obs_period
)
SELECT 
    sc.company_id,
    sc.obs_period,
    sc.hist_period,
    sc.is_payroll,
    CASE WHEN gsf.has_exit_event = 1 THEN 1 ELSE 0 END AS dummy
FROM status_changes sc
JOIN group_switch_flags gsf 
    ON sc.company_id = gsf.company_id 
    AND sc.obs_period = gsf.obs_period
ORDER BY sc.company_id, sc.obs_period, TO_DATE(sc.hist_period, 'YYYY/MM');

Approach 2: Concise Single-Query Version #

If you prefer a more compact solution, you can compute the dummy directly using nested window functions:

SELECT 
    company_id,
    obs_period,
    hist_period,
    is_payroll,
    CASE 
        WHEN MAX(
            CASE 
                WHEN LAG(is_payroll) OVER (
                    PARTITION BY company_id, obs_period 
                    ORDER BY TO_DATE(hist_period, 'YYYY/MM')
                ) = 'P' AND is_payroll = 'NP' 
                THEN 1 
                ELSE 0 
            END
        ) OVER (PARTITION BY company_id, obs_period) = 1 
        THEN 1 
        ELSE 0 
    END AS dummy
FROM your_table_name -- Replace with your actual table name
ORDER BY company_id, obs_period, TO_DATE(hist_period, 'YYYY/MM');

Key Details to Note: #

• We use TO_DATE(hist_period, 'YYYY/MM') in the ORDER BY clause to ensure historical periods are sorted chronologically—this is critical for accurate LAG() function results.
• The LAG() window function checks the previous row's is_payroll value within the same company_id and obs_period group.
• Aggregating with MAX() lets us check if any exit event occurred in the group, then we apply that flag to all rows in the group.

Matching Your Sample Data: #

For company_id = 11 (obs_period 2020/09), there’s no P → NP switch (only NP → P), so all dummy values are 0. For company_id = 12, the switch from P to NP occurs, so all rows get dummy = 1—exactly matching your expected output.

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