Hive中如何结合字符串截取与lag函数获取上年对应值？

需求说明 #

需要在Hive中同时实现以下两个操作并整合为一个SQL：

• 对income_total字段截取前两位，按year、benefit_type及截取结果分组，计算household_income的总和与分组内的记录数
• 通过lag函数按primary_key分区，获取每条记录对应上年的benefit_type和income_total值

合并方案 #

可以通过**公共表表达式（CTE）**先完成lag函数的计算，得到包含上年字段的中间数据集，再基于这个中间数据集执行分组聚合操作。这样能在一个SQL中完成两个逻辑，避免生成冗余中间表。

代码实现（聚合结果含lag字段）

WITH lag_income_dataset AS (
    SELECT 
        *,
        lag(benefit_type, 1, 0) OVER (PARTITION BY primary_key ORDER BY year) AS previous_benefit_type,
        lag(income_total, 1, 0) OVER (PARTITION BY primary_key ORDER BY year) AS previous_income_total
    FROM income_dataset
)
CREATE TABLE combined_income_dataset AS
SELECT 
    year,
    substring(income_total, 1, 2) AS income_prefix,
    benefit_type,
    previous_benefit_type,
    previous_income_total,
    sum(household_income) AS total_household_income,
    count(*) AS record_count
FROM lag_income_dataset
GROUP BY year, benefit_type, substring(income_total, 1, 2), previous_benefit_type, previous_income_total;

代码实现（保留原表lag结果+关联聚合统计）

如果需要同时保留原表的所有字段、lag计算结果，以及对应分组的聚合统计，可以用双CTE加关联的方式：

WITH lag_income_dataset AS (
    SELECT 
        *,
        lag(benefit_type, 1, 0) OVER (PARTITION BY primary_key ORDER BY year) AS previous_benefit_type,
        lag(income_total, 1, 0) OVER (PARTITION BY primary_key ORDER BY year) AS previous_income_total
    FROM income_dataset
),
aggregated_income AS (
    SELECT 
        year,
        substring(income_total, 1, 2) AS income_prefix,
        benefit_type,
        sum(household_income) AS total_household_income,
        count(*) AS record_count
    FROM lag_income_dataset
    GROUP BY year, benefit_type, substring(income_total, 1, 2)
)
CREATE TABLE combined_full_income_dataset AS
SELECT 
    l.*,
    a.total_household_income,
    a.record_count
FROM lag_income_dataset l
JOIN aggregated_income a 
    ON l.year = a.year 
    AND l.benefit_type = a.benefit_type 
    AND substring(l.income_total, 1, 2) = a.income_prefix;

关键说明 #

• 原lag函数SQL缺少ORDER BY year，这会导致无法保证取到的是上年的数据，合并后的SQL补充了该排序条件
• 分组字段可根据实际业务需求调整，若不需要将previous_benefit_type和previous_income_total纳入分组，可从GROUP BY子句中移除

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