如何在Presto的SQL(雅典娜)中计算从一年开始的每个月的平均值？

Question

下面是我拥有的表数据的示例

| date       | value |
| 2020-01-01 |  20   |
| 2020-01-14 |  10   |
| 2020-02-02 |  30   |
| 2020-02-11 |  25   |
| 2020-02-25 |  25   |
| 2020-03-13 |  34   |
| 2020-03-21 |  10   |
| 2020-04-06 |  55   |
| 2020-04-07 |  11   |

我想生成一个结果集，如下所示

| date       | value |  average                       |
| 2020-01-01 |  20   |  Jan average                   |
| 2020-01-14 |  10   |  Jan average                   |   
| 2020-02-02 |  30   |  Jan & Feb average             |
| 2020-02-11 |  25   |  Jan & Feb average             |
| 2020-02-25 |  25   |  Jan & Feb average             |
| 2020-03-13 |  34   |  Jan & Feb & Mar average       |
| 2020-03-21 |  10   |  Jan & Feb & Mar average       |
| 2020-04-06 |  55   |  Jan & Feb & Mar & Apr average |
| 2020-04-07 |  11   |  Jan & Feb & Mar & Apr average |

我尝试使用窗口函数 OVER(( 和 PARTITION((，但我设法逐月而不是从一年开始获得平均值。

请提出任何建议。

谢谢

Answer 1

我想你想要：

select 
t.*,
avg(value) over(
partition by year(date)
order by month(date)
) running_avg
from mytable t

这会将每年放在一个单独的分区中，然后按月对分区行进行排序。

Answer 2

以下查询应提供预期的输出-

在这里演示

SELECT A.*,
(
SELECT AVG(Value * 1.00) 
FROM your_table B 
WHERE YEAR(B.Date) = YEAR(A.DAte) 
AND MONTH(B.Date) <= MONTH(A.DAte)
)
FROM your_table A

此查询将使您的产出每年。但是，如果您不想按 YEAR 进行分区，只需从子查询中删除 YEAR 过滤器即可。

以下查询将返回 AVG，而不考虑 YEAR，仅返回月份前所有 AVG -

在这里演示

SELECT A.*,
(
SELECT AVG(Value * 1.00) 
FROM your_table B 
WHERE B.date <= 
(
SELECT MAX(Date) 
FROM your_table C
WHERE YEAR(c.Date) = YEAR(A.Date)
AND MONTH(C.Date) = MONTH(A.Date)
)
)
FROM your_table A

Answer 3

不确定我是否理解您的问题，但是如果您想要的只是按年份限制的每一行的运行平均值：

SELECT date, value, (
SELECT AVG(value)
FROM data ds
WHERE ds.date <= d.date AND YEAR(ds.date) = YEAR(d.date)
) average
FROM data d
ORDER BY d.date ASC;

MySQL 示例(此特定查询的语法相同(

如果要在平均值中包含同月的后续行，请使用WHERE MONTH(ds.date) <= MONTH(d.date)。

Answer 4

SELECT a.date,
a.value,
(Select avg(b.value) from myTable B where b.date < a.date and YEAR(a.date) = YEAR(b.date)) 
From myTable a