在Amazon Athena中创建滞后年份变量及宽表转长表的技术求助

Amazon Athena基于Presto SQL，可通过两种方式创建滞后年份变量：

• 基于已有年份序列的窗口函数滞后：如果需要按bene_id分组，获取当前行的前N年数据，使用LAG()窗口函数：

  SELECT
    bene_id,
    year,
    -- 滞后1年的年份
    LAG(year, 1) OVER (PARTITION BY bene_id ORDER BY year) AS lag_year_1,
    -- 滞后2年的年份
    LAG(year, 2) OVER (PARTITION BY bene_id ORDER BY year) AS lag_year_2
  FROM tablea;
  

• 基于基准年份直接计算：如果只需从基准年份推导前N年，直接用算术运算即可：

  SELECT
    bene_id,
    year AS base_year,
    year - 1 AS lag_year_1,
    year - 2 AS lag_year_2
  FROM tablea;
  

原代码问题分析 #

你提供的递归CTE存在两个关键问题：

1. 初始查询仅提取了基准年份year，未包含原始表中的y-1和y-2列，导致转换后的长表缺失这两个目标年份。
2. 递归过程中重复关联原表tablea，会生成冗余数据；且终止条件Year > 2016与“扩展至2021年”的需求不匹配。

解决方案 #

方案1：生成从最早年份到2021年的完整连续序列 #

先将宽表转成长表，再为每个bene_id生成从其最早年份（基准年、y-1、y-2中的最小值）到2021年的所有年份：

WITH wide_to_long AS (
  -- 宽表转长表，提取三个目标年份
  SELECT bene_id, year AS target_year FROM tablea
  UNION ALL
  SELECT bene_id, y_minus_1 AS target_year FROM tablea
  UNION ALL
  SELECT bene_id, y_minus_2 AS target_year FROM tablea
),
bene_year_bounds AS (
  -- 确定每个bene_id的年份范围：最早年份到2021年
  SELECT
    bene_id,
    MIN(target_year) AS start_year,
    2021 AS end_year
  FROM wide_to_long
  GROUP BY bene_id
),
recursive_year_sequence AS (
  -- 递归生成年份序列
  SELECT bene_id, start_year AS year
  FROM bene_year_bounds
  UNION ALL
  SELECT rys.bene_id, rys.year + 1
  FROM recursive_year_sequence rys
  JOIN bene_year_bounds byb ON rys.bene_id = byb.bene_id
  WHERE rys.year < byb.end_year
)
SELECT bene_id, year
FROM recursive_year_sequence
ORDER BY bene_id, year;

方案2：仅补充原始三个年份到2021年的缺失年份 #

如果只需保留原始的三个年份，同时补充这些年份到2021年之间的缺失值，可使用交叉连接生成年份范围：

WITH wide_to_long AS (
  SELECT bene_id, year AS target_year FROM tablea
  UNION ALL
  SELECT bene_id, y_minus_1 AS target_year FROM tablea
  UNION ALL
  SELECT bene_id, y_minus_2 AS target_year FROM tablea
),
year_range AS (
  -- 生成2021及之前的所有年份（可根据实际最早年份调整起始值）
  SELECT year
  FROM UNNEST(SEQUENCE(2016, 2021)) AS t(year)
)
SELECT
  wtl.bene_id,
  yr.year
FROM wide_to_long wtl
CROSS JOIN year_range yr
-- 仅保留不早于该bene_id最早年份的记录
WHERE yr.year >= (SELECT MIN(target_year) FROM wide_to_long WHERE bene_id = wtl.bene_id)
ORDER BY wtl.bene_id, yr.year;

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