BigQuery中SUBSTR函数适配INT64报错及季度工资聚合问题

错误原因 #

BigQuery的SUBSTR函数要求第一个参数必须是字符串类型，而你的wg.yrqtr是INT64类型，直接传入会触发类型不匹配错误。需要先将数值类型的yrqtr转换为字符串，再使用SUBSTR。

修改后的完整SQL #

with TECH as (
SELECT
  certificate.MASTER_PERSON_INDEX tech_mpi,
  certificate.U_COMP_CIP as g_cip,
  case when u_req_hrs <300 then '1A'
       when u_req_hrs >= 300 and u_req_hrs < 900 then '1B'
       when u_req_hrs >= 900 then '2'
       else null end ipeds,
  --u_comp_age, 
  u_issue_date g_date,
  GENDER, U_ETH_RACE_U G_ETHNIC_U, U_ETHNIC_H, U_RACE_A G_ETHNIC_A, U_RACE_B G_ETHNIC_B, U_RACE_I G_ETHNIC_I,
  U_RACE_MULTI, U_RACE_P, U_RACE_W
FROM 
  `STC.STC_CERTIFICATE_DI_DATA_TABLE` certificate
),
ws_data as (
SELECT
        MASTER_PERSON_INDEX AS mpi
        ,SUBSTR(wg.naics, 1, 3) AS NAICS_2
--        ,ncs_tb.TITLE AS NAICS_DESC
        -- 将yrqtr转换为字符串后截取前5位
        ,SUBSTR(STRING(wg.yrqtr), 1, 5) AS quarter
        ,wg.yrqtr
        ,wg.employer
        ,wg.wages
        -- 将yrqtr转换为字符串后截取前4位作为年份
        ,SUBSTR(STRING(wg.yrqtr), 1, 4) AS YEAR
    FROM
        (   SELECT
                *
            FROM
                `WS.WS_UI_WAGE_RECORDS_DI` wsui
            WHERE  -- Putting all the where statements possible here limits search on the DWS table, which is large
                wsui.MASTER_PERSON_INDEX IN (SELECT tech_mpi FROM tech)
                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

        -----grab the naics description
--        LEFT JOIN UDRC_NAICS ncs_tb
--            ON ncs_tb.CODE = SUBSTR(wg.NAICS, 1,3)

    ),
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, dagg.wages_quart ORDER BY dagg.wages_quart  DESC)AS rn
    FROM
        -- 修正表名拼写错误：dws_agg -> ws_agg
        ws_agg dagg
    )
select distinct
  tech_mpi,
  g_cip,
  ipeds,
  u_issue_date g_date,
  GENDER, U_ETH_RACE_U G_ETHNIC_U, U_ETHNIC_H, U_RACE_A G_ETHNIC_A,
  U_RACE_B G_ETHNIC_B, U_RACE_I G_ETHNIC_I,
  U_RACE_MULTI, U_RACE_P, U_RACE_W,
  mpi,
  NAICS_2,
  -- 注释掉不存在的字段，原逻辑中NAICS_DESC未被启用
  -- NAICS_DESC,
  quarter,
  -- 注意：ws_annot中没有yrqtr、employer、wages字段，若需要保留需调整CTE逻辑
  -- wg.yrqtr,
  -- wg.employer,
  -- wg.wages,
  YEAR
from 
  tech l
left join
  ws_annot
on
  l.tech_mpi = ws_annot.mpi

关键修改说明 #

1. 类型转换：使用STRING(wg.yrqtr)将INT64类型的yrqtr转换为字符串，再传入SUBSTR函数。BigQuery中也可以用CAST(wg.yrqtr AS STRING)实现相同效果。
2. 索引修正：BigQuery的SUBSTR函数起始索引为1（而非部分SQL引擎的0），因此将原代码中的0改为1，确保截取逻辑正确。
3. 表名修正：将ws_annot中的FROM dws_agg dagg改为FROM ws_agg dagg，修正拼写错误。
4. 字段调整：最后查询中注释掉了NAICS_DESC以及wg.yrqtr等不存在的字段，因为ws_annot是基于聚合后的ws_agg生成的，不包含原始工资记录的细节字段；若需要保留这些字段，需调整ws_agg和ws_annot的逻辑，避免丢失原始数据。

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