CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Zio
Zio

Reputation: 99

SQL Average of 40 Quarters

I need the amount of average sales for an article of the last 40 quarters:

select amountofsale, year, quarter, article from table1

+---------+--------+--------+-----------+----
| amountofsale | year | quarter | article   | 
+---------+--------+--------+-----------+----
|      50      | 2019 |   3     | article1  | 
|      40      | 2019 |   2     | article1  | 
|      42      | 2019 |   1     | article1  |
|      ...     | ...  |   ...   | article1  |  
|      2       | 2009 |   4     | article1  |
|      204     | 2009 |   3     | article1  |  
|      150     | 2019 |   3     | article2  | 
|      95      | 2019 |   2     | article2  | 
|      23      | 2019 |   1     | article2  |
|      ...     | ...  |   ...   | article2  |  
|      14      | 2009 |   4     | article2  | 
|      204     | 2009 |   3     | article2  |  
+---------+--------+--------+-----------+-----

Result: Average from 2019Q3 to 2009Q4 =40 Values

+---------+--------+--------+-----------+----
| Avg          | article |
+---------+--------+--------+-----------+----
|      33.5    | article1|  
|      70.5    | article2|

and if 2019Q4 is available the average should be from Q12010 to Q42019

Best Zio

Upvotes: 1

Views: 62

Answers (2)

GMB
GMB

Reputation: 222502

To get the average of the last 40 values per article, you could use row_number() and aggregation:

select
    article,
    avg(amountofsale) avg_amountofsale
from (
    select
        t.*,
        row_number() over(partition by article order by year desc, quarter desc) rn
    from table1 t
) t
where rn <= 40

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270011

I need the amount of average sales for an article of the last 40 quarters:

Does this do what you want?

select article, avg(amountofsale)
from t
where year > 2009 or year = 2009 and quarter >= 3
group by article;

In Postgres, you can simplify the where clause to:

where (year, quarter) >= (2009, 3)

If you specifically want 40 quarters going back in time (which the above does not quite do):

where year * 4 + quarter >= extract(year from now()) * 4 + extract(quarter from now()) - 40;

If you want it 40 quarters back chronologically from the most recent quarter in the data for the article:

select article, avg(amountofsale)
from (select t.*,
             max(year * 4 + quarter) as max_yq
      from t
     ) t
where (year * 4 + quarter) >= max_yq - 40
group by article;

Upvotes: 4

Related Questions