sql - 在 SQL 中按组计算移动平均值

Question

使用 SQL 我可以像这样计算移动平均线

SELECT id,date,var,AVG(var) OVER (ORDER BY  UNIX_DATE(DATE(date)) RANGE BETWEEN 6 PRECEDING AND CURRENT ROW) AS var_mov_avg_7d
FROM table_name
WHERE id LIKE '2001'

但是我怎样才能为我表中的所有 id 单独执行此操作？

标签： sql

Answer 1

你想要partition by：

SELECT id,date,var,
       AVG(var) OVER (PARTITION BY id 
                      ORDER BY UNIX_DATE(DATE(date))
                      RANGE BETWEEN 6 PRECEDING AND CURRENT ROW
                     ) AS var_mov_avg_7d
FROM table_name;