Calculating averages by quarters

user_12213858 0
Viewed 43

I have a table in presto with 2 columns: date and value.

I want to calculate the average of 2nd Quarter's values so the expected result should be: 15.

How can I do this in presto?

 date         value
2021-01-01      10
2021-01-30      20 
2021-02-10      10
2021-04-01      20
2021-04-02      10
2021-07-10      20
quarter presto date sql
2 Answers

You can divide month by 3 and group by the result:

-- sample data
WITH dataset (date, value) AS (
    VALUES (date '2021-01-01' ,     10),
(date '2021-01-30' ,     20), 
(date '2021-02-10' ,     10),
(date '2021-04-01' ,     20),
(date '2021-04-02' ,     10),
(date '2021-07-10',      20)
) 

--query
SELECT avg(value)
FROM dataset
WHERE  month(date) / 3 = 1
GROUP BY month(date) / 3

Output:

_col0
15.0
user_25012790

Use quarter function:

with mytable as (
SELECT * FROM (
    VALUES
        (date '2021-01-01', 10),
        (date '2021-01-30', 20), 
        (date '2021-02-10', 10),
        (date '2021-04-01', 20),
        (date '2021-04-02', 10),
        (date '2021-07-10', 20)
) AS t (date, value)
)

select quarter(date) as qt, avg(value) as avg
  from mytable
  where quarter(date)=2
 group by quarter(date)

Result:

qt avg
2   15.0
user_27003440
Related