Hive中非空列平均值函数：计算前3年非空收入的平均值

嘿，刚好碰过类似的需求，来给你捋清楚怎么实现！首先得明确核心要求：不管前3年里有多少个非空收入值，最终都是把这些非空值求和后除以3（固定除数是3，不是非空值的数量），这点很关键，别和普通的AVG函数搞混了（普通AVG会除以非空值的个数）。

下面分两种常见的业务场景给你写对应的Hive SQL：

场景1：取每个用户最近3个有非空收入的年份计算平均值 #

如果你的需求是挑出每个用户历史上最近的3条非空收入记录，求和后除以3，用这段代码：

-- 先过滤掉空收入的记录，再按年份倒序取前3条
WITH user_non_null_income AS (
    SELECT 
        user_id,
        year,
        income
    FROM your_table
    WHERE income IS NOT NULL
),
ranked_income AS (
    SELECT 
        user_id,
        income,
        -- 按用户分组，年份倒序排名，最近的年份排第1
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY year DESC) AS rn
    FROM user_non_null_income
)
SELECT 
    user_id,
    -- 求和前3条非空收入，除以3得到平均值
    SUM(income)/3 AS avg_income_last_3_non_null_years
FROM ranked_income
WHERE rn <= 3
GROUP BY user_id;

场景2：取指定时间范围内（比如近3个自然年）的非空收入计算平均值 #

如果是要计算每个用户在最近3个完整自然年里的非空收入之和除以3（哪怕某一年收入为空，也不影响除数是3），用这段：

SELECT 
    user_id,
    -- SUM会自动跳过NULL值，求和后除以3
    SUM(income)/3 AS avg_income_last_3_years
FROM your_table
-- 取当前年份及前两年的记录，你也可以改成固定年份比如BETWEEN 2021 AND 2023
WHERE year BETWEEN YEAR(CURRENT_DATE)-2 AND YEAR(CURRENT_DATE)
GROUP BY user_id;

如果是要对每一条年度记录，计算该年份往前推3年（含当年）的非空收入平均值，用窗口函数更高效：

SELECT 
    user_id,
    year,
    -- 窗口函数取当前用户的当前年份及前两年的所有记录，SUM跳过NULL后除以3
    SUM(income) OVER (
        PARTITION BY user_id 
        ORDER BY year 
        RANGE BETWEEN 2 PRECEDING AND CURRENT ROW
    )/3 AS avg_income_prev_3_years
FROM your_table;

注意：Hive里的SUM函数会自动忽略NULL值，所以不用额外判断，直接求和就行，最后除以3就完美符合你的需求啦！

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