SQL：在找到某个事件前后，找出X个样本的平均值。

Question

我怎样才能从一个排序表中查找某个事件发生时，前后各X个样本的平均值？

在下面的例子中，我有一个名为sales_list的表。我想按日期对表格进行排序，并找出销售量（count）从少于100变为大于等于100的事件。然后计算这些事件发生的前2天和后2天的平均利润。

sales_list
日期       销售量（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

预期输出：
日期       销售量（count） 利润（profit） 前2日平均利润（avg_prev2） 后2日平均利润（avg_next2）
12/3/2023   125           660          190                     500
12/6/2023   150           710          500                     740

在这里，我在使用AVG(LAG)语法时遇到了问题，它似乎并不正确，并收到了“窗口函数不能出现在聚合函数内部”的错误。

以下是我的尝试，但存在错误：

WITH top_sales AS (
  SELECT
    date,
    count,
    profit,
    ROW_NUMBER() OVER (ORDER BY date) AS row_num,
    
    CASE 
      WHEN LAG(count) OVER (ORDER BY date) < 100 AND count >= 100 
      THEN 1 ELSE 0 
    END AS high_count
    
  FROM
    sales_data
)

SELECT
  date,
  count,
  profit,
  AVG(LAG(profit, 2) OVER (ORDER BY date)) AS avg_prev2,
  AVG(LEAD(profit, 2) OVER (ORDER BY date)) AS avg_next2
FROM
  top_sales
WHERE
  high_count = 1

Josh Lee · Answer

以下查询首先使用了一个名为sales的公共表表达式（cte），为表中的每一项分配一个递增的行号。然后，在左连接和两个子查询中使用这些行号来寻找所需的前后两天的数据：

with sales as (
   select row_number() over (order by s.date) as r, s.* from sales_list s
)
select s1.*,
  (select avg(s3.profit) from sales s3 where s3.r >= s1.r - 2 and s3.r < s1.r) as avg_prev2,
  (select avg(s3.profit) from sales s3 where s3.r <= s1.r + 2 and s3.r > s1.r) as avg_next2
from sales s1
left join sales s2 on s1.r = s2.r + 1
where s2.count is not null and s2.count < 100 and s1.count > 100

这个查询通过首先按日期排序并编号每一条记录，然后根据编号找到每个满足条件（前一天销量小于100，当天销量大于等于100）的记录，并分别计算其前后两天的平均利润。

你可以在此数据库在线测试环境中查看该查询的实际运行效果：链接。

benzado · Answer

Oracle解决方案：

一种选择是使用分析函数LAG() Over()和带有窗口范围指定的AVG() Over()，在同一行中获取所需值（如下主SQL语句的内层查询）：

-- 示例数据定义：
WITH sales_list (A_DATE, A_COUNT, PROFIT) AS
(
  Select DATE '2023-12-01', 20, 100 From Dual Union All
  Select DATE '2023-12-02', 50, 280 From Dual Union All
  Select DATE '2023-12-03', 125, 660 From Dual Union All
  Select DATE '2023-12-04', 165, 850 From Dual Union All
  Select DATE '2023-12-05', 85, 150 From Dual Union All
  Select DATE '2023-12-06', 150, 710 From Dual Union All
  Select DATE '2023-12-07', 180, 740 From Dual 
)

-- 主要SQL查询：
Select A_DATE, A_COUNT, PROFIT, AVG_PREV_2, AVG_NEXT_2
From (
  Select A_DATE, A_COUNT, PROFIT, 
         LAG(A_COUNT) Over(Order By A_DATE) "PREV_COUNT",
         AVG(PROFIT) Over(Order By A_DATE Rows Between 2 Preceding And 1 Preceding) "AVG_PREV_2",
         AVG(PROFIT) Over(Order By A_DATE Rows Between 1 Following And 2 Following) "AVG_NEXT_2"
  From sales_list
) As derived_table
Where PREV_COUNT < 100 And A_COUNT >= 100

/*
结果：
A_DATE      A_COUNT     PROFIT AVG_PREV_2 AVG_NEXT_2
-------- ---------- ---------- ---------- ----------
03.12.23        125        660        190        500
06.12.23        150        710        500        740
*/