如何计算排序表中触发事件前后样本平均值？解决SQL窗口函数报错

问题描述 #

需要从已排序的sales_list表中，找出销售count从低于100变为大于等于100的触发事件，然后计算该事件发生前2天和后2天的利润平均值。

表结构与数据 #

sales_list
date        count   profit
12/1/2023   20      100
12/2/2023   50      280
12/3/2023   125     660
12/4/2023   165     850
12/5/2023   85      150
12/6/2023   150     710
12/7/2023   180     740

预期输出 #

date        count   profit  avg_prev2   avg_next2
12/3/2023   125     660     190        500
12/6/2023   150     710     500        740

错误原因分析 #

你遇到的报错"Windows function may not appear inside an aggregate function"，是因为错误地将窗口函数LAG()/LEAD()嵌套在了聚合函数AVG()内部——SQL不允许这种嵌套用法。

修正后的SQL代码 #

WITH top_sales AS (
  SELECT
    date,
    count,
    profit,
    -- 标记触发事件：前一天count<100且当天count>=100
    CASE 
      WHEN LAG(count) OVER (ORDER BY date) < 100 AND count >= 100 
      THEN 1 
      ELSE 0 
    END AS high_count,
    -- 计算前2天的利润平均值（当前行的前2行到前1行）
    AVG(profit) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING) AS avg_prev2,
    -- 计算后2天的利润平均值（当前行的后1行到后2行）
    AVG(profit) OVER (ORDER BY date ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING) AS avg_next2
  FROM
    sales_list -- 修正原代码中的表名错误
)
SELECT
  date,
  count,
  profit,
  avg_prev2,
  avg_next2
FROM
  top_sales
WHERE
  high_count = 1;

代码说明 #

1. 表名修正：将原代码中的sales_data改为实际表名sales_list，并补充了字段间缺失的逗号。
2. 触发事件标记：保留原逻辑，用LAG()获取前一天的count值，判断是否满足触发条件。
3. 滑动平均值计算：
   • avg_prev2：通过ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING定义窗口范围，取当前行之前的2天数据计算平均值。
   • avg_next2：通过ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING定义窗口范围，取当前行之后的2天数据计算平均值；若后续不足2天，AVG()会自动仅计算存在的非空值（如12/6之后仅12/7一天，平均值即为740）。

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