如何基于app_code和year生成Flag字段？求SQL实现方案

原数据表 #

预期输出表 #

规则说明 #

• 若某一年的所有app_code均为LF，则Flag为1；
• 若某一年的所有app_code均为EBB，则Flag为2；
• 若某一年同时存在LF和EBB两种app_code，则Flag为3。

SQL实现语句 #

方法一：使用窗口函数（无需子查询） #

SELECT 
    ID,
    org_id,
    app_code,
    year,
    CASE
        -- 当年仅存在LF类型
        WHEN COUNT(DISTINCT app_code) OVER (PARTITION BY year) = 1 
             AND MAX(app_code) OVER (PARTITION BY year) = 'LF' THEN 1
        -- 当年仅存在EBB类型
        WHEN COUNT(DISTINCT app_code) OVER (PARTITION BY year) = 1 
             AND MAX(app_code) OVER (PARTITION BY year) = 'EBB' THEN 2
        -- 当年同时存在两种类型
        ELSE 3
    END AS Flag
FROM your_table_name;

方法二：使用子查询统计年度信息后关联 #

WITH year_app_stats AS (
    SELECT 
        year,
        COUNT(DISTINCT app_code) AS distinct_app_count,
        MAX(app_code) AS dominant_app
    FROM your_table_name
    GROUP BY year
)
SELECT 
    t.ID,
    t.org_id,
    t.app_code,
    t.year,
    CASE
        WHEN s.distinct_app_count = 1 AND s.dominant_app = 'LF' THEN 1
        WHEN s.distinct_app_count = 1 AND s.dominant_app = 'EBB' THEN 2
        ELSE 3
    END AS Flag
FROM your_table_name t
JOIN year_app_stats s ON t.year = s.year;

注：请将your_table_name替换为实际的表名。

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