How to calculate average for every month from start from year in Presto's SQL (Athena)?

slysid Published at Dev

slysid

Below is an example of the table data I have

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

I would like to generate a result set as below

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

I tried to use window function OVER() and PARTITION() but I managed to get average on month by month rather than starting from the year.

Any suggestions, please.

Thanks

GMB

I think you want:

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

This puts each year in a separate partition, and the order partition rows by month.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-12-15

Comments

0 comments

How to calculate the previous month and year from string in Bash

calculate Age from year, month and day fields

Extracting year and month from datekey in my sql

parse year and month from a string SQL BigQuery

How to calculate average for every month from start from year in Presto's SQL (Athena)?

How to calculate average for every month from start from year in Presto's SQL (Athena)?

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Loopback Error: connect ECONNREFUSED 127.0.0.1:3306 (MAMP)

How to import an asset in swift using Bundle.main.path() in a react-native native module

pump.io port in URL

Compiler error CS0246 (type or namespace not found) on using Ninject in ASP.NET vNext

BigQuery - concatenate ignoring NULL

ngClass error (Can't bind ngClass since it isn't a known property of div) in Angular 11.0.3

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

How to remove the extra space from right in a webview?

java.lang.NullPointerException: Cannot read the array length because "<local3>" is null

Jquery different data trapped from direct mousedown event and simulation via $(this).trigger('mousedown');

flutter: dropdown item programmatically unselect problem

How to use merge windows unallocated space into Ubuntu using GParted?

Change dd-mm-yyyy date format of dataframe date column to yyyy-mm-dd

Nuget add packages gives access denied errors

Svchost high CPU from Microsoft.BingWeather app errors

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

12.04.3--- Dconf Editor won't show com>canonical>unity option

Any way to remove trailing whitespace *FOR EDITED* lines in Eclipse [for Java]?

maven-jaxb2-plugin cannot generate classes due to two declarations cause a collision in ObjectFactory class

Any way to remove trailing whitespace FOR EDITED lines in Eclipse [for Java]?