如何在presto的sql(athena)中计算从年开始的每个月的平均值?

r7knjye2  于 2021-08-09  发布在  Java
关注(0)|答案(4)|浏览(589)

下面是我拥有的表格数据的一个例子

| 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 |

我试着使用window function over()和partition(),但我设法逐月获得平均值,而不是从一年开始。
有什么建议吗。
谢谢

pkln4tw6

pkln4tw61#

我想你想要:

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

这将每年放入一个单独的分区中,并按月份对分区行进行排序。

zpf6vheq

zpf6vheq2#

下面的查询应该给出您期望的输出-
此处演示

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

此查询将使您的输出每年。但是如果您不想按年份划分分区,只需从子查询中删除年份过滤器。
下面的查询将返回不考虑年份的平均值,只返回所有月份之前的平均值-
此处演示

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
kx5bkwkv

kx5bkwkv3#

我不确定我是否理解你的问题,但如果你想要的是每年每行的运行平均值:

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) .

pexxcrt2

pexxcrt24#

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

相关问题