甲骨文：需要计算过去3个月的滚动平均值，我们每月有多次提交

3 回答

• 2

  一种方法是将聚合函数与分析函数混合使用 . 平均值的关键思想是避免使用 avg() 而是使用 sum() 除以 count(*) .

  SELECT AREA, TO_CHAR(T.DT, 'MMYY') AS MMYY,
           SUM(SCORE) / COUNT(*) as AvgScore,
           SUM(SUM(SCORE)) OVER (PARTITION BY AREA ORDER BY MAX(T.DT) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) / SUM(COUNT(*)) OVER (PARTITION BY AREA ORDER BY MAX(T.DT) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
    FROM t
    GROUP BY AREA, TO_CHAR(T.DT, 'MMYY') ;
  

  请注意 order by 子句 . 如果您的数据跨越多年，则使用MMYY格式会产生问题 . 最好使用YYYY-MM等格式数月，因为字母顺序与自然顺序相同 .

  回复于 2024-05-02T10:09:05+08:00
• 1

  SQL> WITH DATA AS(
    2  SELECT to_date('01-JUL-14','DD-MON-RR')  dt,     60   score,    'A' area FROM dual UNION ALL
    3  SELECT to_date('01-AUG-14','DD-MON-RR')  dt,       45      score,    'A' area FROM dual UNION ALL
    4  SELECT to_date('01-SEP-14','DD-MON-RR')  dt,       45      score,    'A' area FROM dual UNION ALL
    5  SELECT to_date('02-SEP-14','DD-MON-RR')  dt,       50      score,    'A' area FROM dual UNION ALL
    6  SELECT to_date('01-OCT-14','DD-MON-RR')  dt,       30      score,    'A' area FROM dual UNION ALL
    7  SELECT to_date('02-OCT-14','DD-MON-RR')  dt,       45      score,    'A' area FROM dual UNION ALL
    8  SELECT to_date('03-OCT-14','DD-MON-RR')  dt,      50      score,    'A' area FROM dual UNION ALL
    9  SELECT to_date('01-JUL-14','DD-MON-RR')  dt,       60      score,    'B' area FROM dual UNION ALL
   10  SELECT to_date('01-AUG-14','DD-MON-RR')  dt,       45      score,    'B' area FROM dual UNION ALL
   11  SELECT to_date('01-SEP-14','DD-MON-RR')  dt,       45      score,    'B' area FROM dual UNION ALL
   12  SELECT to_date('02-SEP-14','DD-MON-RR')  dt,       50      score,    'B' area FROM dual UNION ALL
   13  SELECT to_date('01-OCT-14','DD-MON-RR')  dt,       30      score,    'B' area FROM dual UNION ALL
   14  SELECT to_date('02-OCT-14','DD-MON-RR')  dt,       45      score,    'B' area FROM dual UNION ALL
   15  SELECT to_date('03-OCT-14','DD-MON-RR')  dt,       50      score,    'B' area FROM dual)
   16  SELECT   TO_CHAR(T.DT, 'MON-RR') AS MMYY,
   17           round(
   18           SUM(SUM(SCORE)) OVER (PARTITION BY AREA ORDER BY MAX(T.DT) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)/
   19           SUM(COUNT(*)) OVER (PARTITION BY AREA ORDER BY MAX(T.DT) ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),1)
   20           AS avg_score,
   21           AREA
   22    FROM data t
   23    GROUP BY AREA, TO_CHAR(T.DT, 'MON-RR')
   24  /
  
  MMYY    AVG_SCORE A
  ------ ---------- -
  JUL-14         60 A
  AUG-14       52.5 A
  SEP-14         50 A
  OCT-14       44.2 A
  JUL-14         60 B
  AUG-14       52.5 B
  SEP-14         50 B
  OCT-14       44.2 B
  
  8 rows selected.
  
  SQL>
  

  从下次开始，我希望您提供 create 和 insert 语句，这样我们就不必花时间准备 test case .

  而且，为什么 YY 格式？你没见过 Y2K 的错误吗？请使用 YYYY 格式 .

  回复于 2024-05-02T10:09:05+08:00
• 1

  您也可以指定范围，而不仅仅是行 .

  SELECT
    AREA, 
    TO_CHAR(T.DT,'MMYY') MMYY,
    ROUND(AVG(SCORE)
      OVER (PARTITION BY AREA 
        ORDER BY DT RANGE BETWEEN INTERVAL '3' MONTH PRECEDING AND CURRENT ROW))
      AS AVG 
      FROM T
  

  由于 CURRENT ROW 是默认值，因此 ORDER BY DT RANGE INTERVAL '3' MONTH PRECEDING 也可以正常工作 . 也许你必须做一些微调，我没有测试关于每月28/29/30/31天问题的行为 .

  有关详细信息，请查看Oracle Windowing Clause .

  回复于 2024-05-02T10:09:05+08:00