如何在Hive中计算每月的过去季度交易均值与半年度余额方差

嘿，我懂你的困扰！你之前的查询用了普通分组聚合，只能拿到每个用户最后几个月的单一汇总结果，但Hive的窗口分析函数刚好能解决这种「每行对应一个滚动窗口统计」的场景，帮你实现每个月份都输出过去3个月的交易均值和过去6个月的余额方差。

核心思路 #

我们要给每个用户的记录按时间排序，然后为每行定义一个「包含当前月及之前N个月」的窗口，直接在这个窗口上计算聚合函数——这样每一行（每个月份）都会得到对应的统计值，而不是整个用户的单一结果。

完整查询语句（适配你的需求） #

如果你的数据中每个cust_id+year_+month_是唯一的（即每月一条记录），可以直接用下面的查询：

SELECT 
    cust_id,
    year_,
    month_,
    monthly_txn,
    monthly_bal,
    -- 过去3个月（含当前月）的monthly_txn平均值
    AVG(monthly_txn) OVER (
        PARTITION BY cust_id 
        ORDER BY year_, month_ 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS q_avg_txn,
    -- 过去6个月（含当前月）的monthly_bal方差
    VARIANCE(monthly_bal) OVER (
        PARTITION BY cust_id 
        ORDER BY year_, month_ 
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS h_var_bal
FROM mytable
ORDER BY cust_id, year_ DESC, month_ DESC;

关键部分解释 #

• PARTITION BY cust_id：把数据按用户拆分，每个用户单独计算自己的滚动统计，不会和其他用户的记录混在一起。
• ORDER BY year_, month_：确保每个用户的记录按时间从早到晚排序，这样窗口才能正确取到「过去的月份」而不是乱序的行。
• ROWS BETWEEN N PRECEDING AND CURRENT ROW：
  • 过去3个月需要当前行 + 前2行，所以用2 PRECEDING AND CURRENT ROW，总共覆盖3行数据。
  • 过去6个月则是当前行 + 前5行，用5 PRECEDING AND CURRENT ROW，总共覆盖6行数据。
• AVG()和VARIANCE()：直接在窗口范围内计算聚合，每一行都会输出对应窗口的结果，完美覆盖每个月份的需求。

处理同一月份多条记录的情况 #

看你的输入示例里，同一个cust_id+year_+month_有重复记录（比如1号用户2017年2月有两条），这时候需要先按月聚合，把同一个月的交易和余额先做汇总（比如取均值或求和，根据你的业务需求），再做滚动计算：

-- 先按月聚合数据，确保每个用户每月只有一条记录
WITH monthly_agg AS (
    SELECT 
        cust_id,
        year_,
        month_,
        AVG(monthly_txn) AS monthly_txn, -- 或SUM，根据业务逻辑选择
        AVG(monthly_bal) AS monthly_bal  -- 或SUM
    FROM mytable
    GROUP BY cust_id, year_, month_
)
SELECT 
    cust_id,
    year_,
    month_,
    monthly_txn,
    monthly_bal,
    AVG(monthly_txn) OVER (
        PARTITION BY cust_id 
        ORDER BY year_, month_ 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS q_avg_txn,
    VARIANCE(monthly_bal) OVER (
        PARTITION BY cust_id 
        ORDER BY year_, month_ 
        ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
    ) AS h_var_bal
FROM monthly_agg
ORDER BY cust_id, year_ DESC, month_ DESC;

和你原有查询的区别 #

你之前的查询是先筛选每个用户最后3行再分组，只能得到一个汇总值；而窗口函数是对每一行应用窗口内的聚合，所以每个月份都会输出对应的过去N个月统计，完全符合你的期望输出要求。

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