如何在SQL中为无工作季度生成工资为0的观测记录

我用以下SQL获取2010Q1至2020Q4期间个人的季度工资数据。但如果某个人在特定季度未工作，该季度就没有对应记录；我希望为这类缺失的季度生成观测记录，并将季度工资设为0。

示例 #

当前输出 #

期望输出 #

现有代码 #

ws_data AS (
    SELECT
        MASTER_PERSON_INDEX AS mpi
        ,SUBSTR(cast(wg.naics as string), 1, 2) AS NAICS_2
        ,SUBSTR(cast(wg.yrqtr as string), 0,5) AS quarter
        ,wg.yrqtr
        ,wg.employer
        ,wg.wages
        ,SUBSTR(cast(wg.yrqtr as string), 0,4) AS YEAR
    FROM
        (   SELECT
                *
            FROM
                `ws.ws_ui_wage_records_di` wsui
            WHERE  
                wsui.MASTER_PERSON_INDEX IN (SELECT mpi FROM rc_table_ra16_all_grads_1b)
                AND
                wsui.yrqtr IN (20101, 20102, 20103, 20104,
                        20111, 20112, 20113, 20114,
                        20121, 20122, 20123, 20124,
                        20131, 20132, 20133, 20134,
                        20141, 20142, 20143, 20144,
                        20151, 20152, 20153, 20154,
                        20161, 20162, 20163, 20164,
                        20171, 20172, 20173, 20174,
                        20181, 20182, 20183, 20184,
                        20191, 20192, 20193, 20194,
                        20201, 20202, 20203, 20204)
                                             )wg



    ),

    ws_agg AS (
    SELECT
        mpi
--      ,STATS_MODE(NAICS_2) AS NAICS_2 
   --   ,STATS_MODE(NAICS_DESC) AS NAICS_DESC
        ,quarter
        ,SUM(wages) AS wages_quart
    FROM
        ws_data

    GROUP BY
        mpi, quarter
    ),

    ws_annot AS (
    SELECT
        dagg.*
        ,row_number() OVER(PARTITION BY dagg.mpi, cast(wages_quart as string) ORDER BY dagg.wages_quart  DESC)AS rn
    FROM
        ws_agg dagg
    )

解决方案 #

要补全缺失的季度记录，核心是生成所有个人（MPI）和目标季度的笛卡尔积，再与现有聚合数据左连接，最后将空值工资替换为0。修改后的完整SQL如下：

-- 生成2010Q1至2020Q4的所有季度列表
all_quarters AS (
    SELECT 
        CONCAT(CAST(year AS STRING), 'Q', CAST(quarter AS STRING)) AS quarter
    FROM 
        UNNEST(GENERATE_ARRAY(2010, 2020)) AS year
    CROSS JOIN 
        UNNEST(GENERATE_ARRAY(1, 4)) AS quarter
),

-- 获取目标人群的所有唯一个人ID
all_persons AS (
    SELECT DISTINCT 
        MASTER_PERSON_INDEX AS mpi
    FROM 
        rc_table_ra16_all_grads_1b
),

-- 生成每个个人对应所有季度的完整组合
person_quarter_full AS (
    SELECT 
        ap.mpi,
        aq.quarter
    FROM 
        all_persons ap
    CROSS JOIN 
        all_quarters aq
),

ws_data AS (
    SELECT
        MASTER_PERSON_INDEX AS mpi
        ,SUBSTR(cast(wg.naics as string), 1, 2) AS NAICS_2
        ,SUBSTR(cast(wg.yrqtr as string), 0,5) AS quarter
        ,wg.yrqtr
        ,wg.employer
        ,wg.wages
        ,SUBSTR(cast(wg.yrqtr as string), 0,4) AS YEAR
    FROM
        (   SELECT
                *
            FROM
                `ws.ws_ui_wage_records_di` wsui
            WHERE  
                wsui.MASTER_PERSON_INDEX IN (SELECT mpi FROM all_persons)
                AND
                wsui.yrqtr IN (20101, 20102, 20103, 20104,
                        20111, 20112, 20113, 20114,
                        20121, 20122, 20123, 20124,
                        20131, 20132, 20133, 20134,
                        20141, 20142, 20143, 20144,
                        20151, 20152, 20153, 20154,
                        20161, 20162, 20163, 20164,
                        20171, 20172, 20173, 20174,
                        20181, 20182, 20183, 20184,
                        20191, 20192, 20193, 20194,
                        20201, 20202, 20203, 20204)
                                             )wg
),

ws_agg AS (
    SELECT
        mpi
        ,quarter
        ,SUM(wages) AS wages_quart
    FROM
        ws_data
    GROUP BY
        mpi, quarter
),

-- 合并完整组合与现有工资数据，将缺失的工资设为0
final_result AS (
    SELECT
        pq.mpi,
        pq.quarter,
        COALESCE(wa.wages_quart, 0) AS wages_quart
    FROM
        person_quarter_full pq
    LEFT JOIN
        ws_agg wa ON pq.mpi = wa.mpi AND pq.quarter = wa.quarter
)

SELECT * FROM final_result

关键说明 #

• all_quarters用GENERATE_ARRAY动态生成季度，替代硬编码，后续修改时间范围更方便。
• person_quarter_full通过交叉连接构建所有个人-季度的完整记录框架，确保没有遗漏。
• COALESCE(wa.wages_quart, 0)直接将未匹配到工资记录的季度值设为0，满足需求。

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