如何用2022年12月员工数填充2023全年YearStartingHeadcount列

需求说明 #

计算年化流失率时，需要将**2022年12月的员工数（Headcount）**作为年初基准值，创建YearStartingHeadcount列，并将该值填充到2023年1月至年末的所有行中。目前使用LAG函数仅能获取2023年1月的对应值，其余月份为NULL，希望动态获取该基准值，避免硬编码具体数值（如1548）。

原尝试代码 #

WITH CTE AS (
  SELECT
   Year_Month,
   SUBSTR(YEAR_MONTH, 1,4) AS Year,
   CAST(right(Year_Month,2)AS NUMBER) AS Month,
   Num_Voluntary_Terminations AS NumVolTerms,
   Headcount
  FROM "HRD"."WORKER"."DD_LITHKRI"
),
YearStartingHeadcount AS(
  SELECT
   NumVolTerms,
   Month,
   Year,
   Headcount,
   CASE
     WHEN Month= 1 THEN LAG(Headcount,1) OVER (ORDER BY Year ,Month) 
     WHEN Year=2022 THEN NULL
     ELSE 1548
   END AS StartingYearHeadcount
  FROM CTE
)

当前执行结果（SYH为目标列） #

期望结果 #

解决方案 #

方法1：子查询直接获取基准值 #

通过子查询提取2022年12月的Headcount，在CASE语句中直接引用该值，实现动态填充：

WITH CTE AS (
  SELECT
   Year_Month,
   SUBSTR(YEAR_MONTH, 1,4) AS Year,
   CAST(right(Year_Month,2)AS NUMBER) AS Month,
   Num_Voluntary_Terminations AS NumVolTerms,
   Headcount
  FROM "HRD"."WORKER"."DD_LITHKRI"
),
YearStartingHeadcount AS(
  SELECT
   NumVolTerms,
   Month,
   Year,
   Headcount,
   CASE
     WHEN Year = 2022 THEN NULL
     ELSE (SELECT Headcount FROM CTE WHERE Year = '2022' AND Month = 12)
   END AS StartingYearHeadcount
  FROM CTE
)
SELECT * FROM YearStartingHeadcount;

说明：子查询直接返回2022年12月的Headcount，所有2023年的行都会复用这个值，无需硬编码。

方法2：窗口函数FIRST_VALUE结合条件筛选 #

利用窗口函数将2022年12月的值作为基准，填充到后续行：

WITH CTE AS (
  SELECT
   Year_Month,
   SUBSTR(YEAR_MONTH, 1,4) AS Year,
   CAST(right(Year_Month,2)AS NUMBER) AS Month,
   Num_Voluntary_Terminations AS NumVolTerms,
   Headcount,
   -- 标记2022年12月的行，作为基准值来源
   CASE WHEN Year = '2022' AND Month = 12 THEN Headcount END AS Dec2022Headcount
  FROM "HRD"."WORKER"."DD_LITHKRI"
),
YearStartingHeadcount AS(
  SELECT
   NumVolTerms,
   Month,
   Year,
   Headcount,
   CASE
     WHEN Year = 2022 THEN NULL
     ELSE FIRST_VALUE(Dec2022Headcount) OVER (ORDER BY Year, Month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
   END AS StartingYearHeadcount
  FROM CTE
)
SELECT * FROM YearStartingHeadcount;

说明：先标记出2022年12月的Headcount，再用FIRST_VALUE将该值传递到后续所有2023年的行中。

方法3：自连接匹配基准值 #

通过自连接将2022年12月的数据关联到2023年的所有行：

WITH CTE AS (
  SELECT
   Year_Month,
   SUBSTR(YEAR_MONTH, 1,4) AS Year,
   CAST(right(Year_Month,2)AS NUMBER) AS Month,
   Num_Voluntary_Terminations AS NumVolTerms,
   Headcount
  FROM "HRD"."WORKER"."DD_LITHKRI"
)
SELECT
  c.NumVolTerms,
  c.Month,
  c.Year,
  c.Headcount,
  CASE WHEN c.Year = '2022' THEN NULL ELSE dec_headcount.Headcount END AS StartingYearHeadcount
FROM CTE c
LEFT JOIN (SELECT Headcount FROM CTE WHERE Year = '2022' AND Month = 12) dec_headcount
ON c.Year = '2023';

说明：将2022年12月的数据单独提取为一个临时表，通过自连接关联到所有2023年的行，实现值的填充。

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