SQL特殊模式拼接需求求助：跨月Item Code对应月份标识处理

Hey there, I’ve dealt with exactly this kind of consecutive grouping problem before—let’s break down how to solve it efficiently, even for your tens of thousands of rows spanning multiple years.

Core Problem Recap #

Your goal is to:

• Group rows by Team and consecutive runs of the same Item Code
• Assign the earliest month from the start of that consecutive run to all rows in the group, even if the run crosses into later months
• Only switch to a new month when the Item Code changes for the team

For example:

• LL-4545 starts in January 2015 and runs into February—all these rows should use January as their assigned month
• RR-4567 starts in February 2015 and runs into March—all these rows stay with February until Item Code switches to YY-6764 (then use March)

Efficient SQL Solution Using Window Functions #

We’ll use window functions to track when Item Code changes, group consecutive identical codes, and then pull the earliest month for each group. This approach is scalable and works in most modern SQL databases (MySQL 8+, SQL Server, PostgreSQL, etc.).

Step-by-Step Query #

WITH ranked_data AS (
    SELECT 
        Team,
        Date,
        `Item Code`,
        -- Convert date to year-month format (adjust syntax for your SQL dialect)
        DATE_FORMAT(Date, '%Y-%m') AS original_month,
        -- Flag rows where Item Code changed from the previous row
        CASE 
            WHEN LAG(`Item Code`) OVER (PARTITION BY Team ORDER BY Date) != `Item Code` 
            THEN 1 
            ELSE 0 
        END AS is_code_change
    FROM your_table_name
),
grouped_runs AS (
    SELECT 
        *,
        -- Create a unique group ID for each consecutive Item Code run
        SUM(is_code_change) OVER (PARTITION BY Team ORDER BY Date) AS run_group_id
    FROM ranked_data
)
SELECT 
    Team,
    Date,
    `Item Code`,
    -- Assign the earliest month from the start of the run
    MIN(original_month) OVER (PARTITION BY Team, run_group_id) AS assigned_month,
    -- Optional: If you need a concatenated result, uncomment below
    -- CONCAT(`Item Code`, ' - ', MIN(original_month) OVER (PARTITION BY Team, run_group_id)) AS formatted_result
FROM grouped_runs
ORDER BY Team, Date;

Dialect-Specific Adjustments #

• SQL Server: Replace DATE_FORMAT(Date, '%Y-%m') with FORMAT(Date, 'yyyy-MM') or CONVERT(VARCHAR(7), Date, 120)
• PostgreSQL: Replace DATE_FORMAT(Date, '%Y-%m') with TO_CHAR(Date, 'YYYY-MM')
• Oracle: Use TO_CHAR(Date, 'YYYY-MM') and adjust the LAG syntax to match Oracle’s window function rules

How It Works #

1. ranked_data CTE: First, we mark every row where the Item Code is different from the previous row (for the same team). This helps us identify the start of a new consecutive run.
2. grouped_runs CTE: We calculate a running total of these "change flags"—this creates a unique run_group_id for every consecutive set of identical Item Code values.
3. Final Select: For each group, we pull the earliest month (MIN(original_month)), ensuring all rows in the same run get the starting month, even if they cross into later months.

Verification with Your Sample Data #

For your sample rows:

• All LL-4545 rows will have the same run_group_id, so assigned_month will be 2015-01
• RR-4567 rows will share a run_group_id, so assigned_month will be 2015-02
• When Item Code switches to YY-6764, a new run_group_id is created, and assigned_month becomes 2015-03

This logic works seamlessly for large datasets because window functions process data in linear time—no slow self-joins or recursive queries needed.

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