Oracle SQL 带PARTITION的LAG函数实现价格几何均值与汇总计算
针对现有表结构(字段包含YEAR、MONTH、PERIOD、REGION_CODE、ITEM_CODE、CURRENT_PRICE,覆盖2020年11月、2020年12月、2021年1月三个统计周期的样本数据,行排列无固定顺序)的三类计算需求,以下是可直接落地的实现逻辑,同时说明此前LAG函数使用失败的核心原因:
LAG函数使用失败的核心原因
绝大多数跨年度时间序列的LAG匹配失败,都是因为直接使用MONTH字段做排序维度:由于MONTH的取值是1-12的数字,2021年1月(MONTH=1)会被错误排序到2020年11月、12月前面,导致上期值匹配错位。正确的排序维度必须是跨年度连续的时间键,即将YEAR和MONTH拼接为可排序的月初日期,保证时间序列的连续性。
全流程实现步骤
步骤1:计算基础分组的当期几何平均值
SQL无内置几何平均函数,需通过对数转换实现:对每个有效价格取自然对数,求和后除以样本数得到平均对数,再取指数得到几何平均。计算过程中需过滤价格为0、空的异常值,避免触发对数计算报错。
本步骤先按要求的分组维度(YEAR、MONTH、REGION_CODE、ITEM_CODE),计算每个分组的当期价格几何平均值:
-- 基础分组层:各区域分商品的当期几何平均 WITH region_group_base AS ( SELECT YEAR, MONTH, REGION_CODE, ITEM_CODE, EXP(SUM(LN(NULLIF(CURRENT_PRICE, 0))) / COUNT(CURRENT_PRICE)) AS CUR_GEO_MEAN FROM 替换为你的实际表名 WHERE CURRENT_PRICE > 0 GROUP BY YEAR, MONTH, REGION_CODE, ITEM_CODE )
步骤2:用修正后的LAG逻辑匹配上期几何平均
在月度粒度的分组结果上,按「同商品、同区域」做分区,用连续日期做排序,匹配对应的上期几何平均,避免在样本粒度先做LAG再聚合带来的粒度错位问题:
-- 分区域结果匹配上期值 , region_with_prev AS ( SELECT YEAR, MONTH, REGION_CODE, ITEM_CODE, CUR_GEO_MEAN, LAG(CUR_GEO_MEAN) OVER ( PARTITION BY ITEM_CODE, REGION_CODE -- 锁定同商品、同区域的独立时间序列 ORDER BY TO_DATE(YEAR || '-' || MONTH || '-01') ASC -- 连续时间排序,替代直接用MONTH排序 ) AS PREV_GEO_MEAN FROM region_group_base )
步骤3:生成商品维度的TOTAL汇总行
按「年、月、商品」维度跨所有区域计算几何平均值,生成REGION_CODE为'TOTAL'的汇总行,同时为TOTAL行匹配对应的上期TOTAL均值。注意:汇总值需直接在原表上按跨区域维度聚合,不能在分区域的结果上做二次平均,否则会因各区域样本数不同导致均值偏差:
-- 汇总层:每个月份下跨所有区域的商品维度均值 , total_group_base AS ( SELECT YEAR, MONTH, 'TOTAL' AS REGION_CODE, ITEM_CODE, EXP(SUM(LN(NULLIF(CURRENT_PRICE, 0))) / COUNT(CURRENT_PRICE)) AS CUR_GEO_MEAN FROM 替换为你的实际表名 WHERE CURRENT_PRICE > 0 GROUP BY YEAR, MONTH, ITEM_CODE ), -- TOTAL结果匹配上期值 , total_with_prev AS ( SELECT YEAR, MONTH, REGION_CODE, ITEM_CODE, CUR_GEO_MEAN, LAG(CUR_GEO_MEAN) OVER ( PARTITION BY ITEM_CODE ORDER BY TO_DATE(YEAR || '-' || MONTH || '-01') ASC ) AS PREV_GEO_MEAN FROM total_group_base )
步骤4:合并结果并按规则排序
将分区域的统计结果和TOTAL汇总行合并,按要求的结构排序,保证最终输出的字段完全匹配需求(YEAR、MONTH、REGION_CODE、ITEM_CODE、CUR_GEO_MEAN、PREV_GEO_MEAN):
SELECT YEAR, MONTH, REGION_CODE, ITEM_CODE, CUR_GEO_MEAN, PREV_GEO_MEAN FROM region_with_prev UNION ALL SELECT YEAR, MONTH, REGION_CODE, ITEM_CODE, CUR_GEO_MEAN, PREV_GEO_MEAN FROM total_with_prev ORDER BY -- 按时间从早到晚排序 TO_DATE(YEAR || '-' || MONTH || '-01') ASC, -- 同月份下先展示TOTAL汇总行,再展示各区域明细 CASE WHEN REGION_CODE = 'TOTAL' THEN 0 ELSE 1 END, -- 同类型行下按商品编码排序 ITEM_CODE ASC
常见踩坑提醒
- LAG排序不能直接使用MONTH字段,否则跨年度的时间序列会发生错位
- 几何平均计算前必须过滤价格为0、空的异常值,否则会触发对数计算的语法报错
- 汇总行的均值计算需直接基于原表,不能对分区域的均值做二次算术/几何平均,避免计算偏差
内容的提问来源于stack exchange,提问作者ince

