You need to enable JavaScript to run this app. Oracle SQL 带PARTITION的LAG函数实现价格几何均值与汇总计算
优惠活动
大模型
产品
解决方案
定价
更多

Oracle SQL 带PARTITION的LAG函数实现价格几何均值与汇总计算

分区域商品价格统计SQL可落地方案

针对现有表结构(字段包含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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.10 05:15:02