按季度拆分month Id并判定ID活动状态（1/0）的实现求助

Got it, let's work through this problem together. First, let's recap what you need to do: we need to pull the year from your month_id column at the quarter granularity, then add an activity flag that's 1 if an ID has any records in that quarter (even just one month), and 0 if it has none.

I'll assume you're using SQL since this is a common data warehousing task—if you're using a different tool (like Pandas in Python), let me know and I can adjust, but let's start with the most common scenario.

Step 1: Extract Year and Quarter from month_id #

First, we need to parse month_id into year and quarter. Let's say your month_id is a numeric value like 202301 (for January 2023). Here's how to split that out:

SELECT
    id,
    month_id,
    -- Grab the first 4 characters as the year
    LEFT(CAST(month_id AS VARCHAR), 4) AS year,
    -- Map the last 2 characters (month) to the correct quarter
    CASE
        WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('01','02','03') THEN 'Q1'
        WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('04','05','06') THEN 'Q2'
        WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('07','08','09') THEN 'Q3'
        ELSE 'Q4'
    END AS quarter
FROM your_table;

If your month_id is a date type instead of a number, this gets even simpler:

SELECT
    id,
    month_id,
    EXTRACT(YEAR FROM month_id) AS year,
    EXTRACT(QUARTER FROM month_id) AS quarter
FROM your_table;

Step 2: Create the activity Flag #

The key here is making sure we capture all possible ID-quarter combinations (even ones where the ID has no records that quarter, so we can set activity to 0). Here's a full CTE-based solution that covers that:

-- Get all unique IDs from your table
WITH unique_ids AS (
    SELECT DISTINCT id FROM your_table
),
-- Get all unique year-quarter pairs present in your data
unique_quarters AS (
    SELECT DISTINCT
        LEFT(CAST(month_id AS VARCHAR), 4) AS year,
        CASE
            WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('01','02','03') THEN 'Q1'
            WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('04','05','06') THEN 'Q2'
            WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('07','08','09') THEN 'Q3'
            ELSE 'Q4'
        END AS quarter
    FROM your_table
),
-- Generate every possible ID + year-quarter combination
all_combinations AS (
    SELECT u.id, q.year, q.quarter
    FROM unique_ids u
    CROSS JOIN unique_quarters q
),
-- Mark which ID-quarter pairs have at least one record
existing_activity AS (
    SELECT
        id,
        LEFT(CAST(month_id AS VARCHAR), 4) AS year,
        CASE
            WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('01','02','03') THEN 'Q1'
            WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('04','05','06') THEN 'Q2'
            WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('07','08','09') THEN 'Q3'
            ELSE 'Q4'
        END AS quarter,
        1 AS has_activity
    FROM your_table
    GROUP BY id, year, quarter
)
-- Combine everything to get the final activity flag
SELECT
    ac.id,
    ac.year,
    ac.quarter,
    -- Use COALESCE to turn NULL (no records) into 0
    COALESCE(ea.has_activity, 0) AS activity
FROM all_combinations ac
LEFT JOIN existing_activity ea
    ON ac.id = ea.id
    AND ac.year = ea.year
    AND ac.quarter = ea.quarter
ORDER BY ac.id, ac.year, ac.quarter;

Quick Simplification (If You Don't Need Zero Rows) #

If you only care about quarters where the ID has at least one record (so you don't need rows where activity is 0), you can skip the cross join and just group directly:

SELECT
    id,
    LEFT(CAST(month_id AS VARCHAR), 4) AS year,
    CASE
        WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('01','02','03') THEN 'Q1'
        WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('04','05','06') THEN 'Q2'
        WHEN RIGHT(CAST(month_id AS VARCHAR), 2) IN ('07','08','09') THEN 'Q3'
        ELSE 'Q4'
    END AS quarter,
    1 AS activity
FROM your_table
GROUP BY id, year, quarter
ORDER BY id, year, quarter;

Let me know if this matches your expected results, or if you're using a different tool and need adjustments!

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