Postgres SQL query - averages per month

Question

I have two queries which I want to achieve.

I have a table like so...

Date        | Period | Location | Price
2017-01-01      1         A         10
2017-01-01      2         A         15
2017-01-01      1         B         15
2017-01-01      2         B         16

Each date has 48 readings (one every half hour).

Query 1: I wish to get the average prices for any given location within a date range in the following format:

e.g. Between 2017-01-01 and 2017-06-30, average price per period for location 'A'

  Period 1  | Period 2|  Period 3 ...
       10           11          15

Query 2: I wish to have the average price for any given location going back x number of months: (period does not matter)

Month   |  Average price
january        10
february       12
march          16

Any help would be greatly appreciated :)

Answer 1

The second query you listed is a very simple group by operation. The only thing that's slightly tricky about it is that you need to extract the month from the listed date.

select
    date_part('month', t.Date) as month,
    avg(t.Price) as average_price
from
    mytable t
group by
    date_part('month', t.Date)

---

The first query however is significantly more complicated. It involves the crosstab(), which you'll have to enable in your database if you haven't already. The general idea is to calculate the averages for each period, and then pivot the data much like you would in excel.

select * from crosstab(
'   select
        t.Period
        , avg(t.Price) as avg_price
    from
        mytable t
    group by
        t.Period
    order by
        1
        , 2
'
) as ct(
    "Period" text
    , "1"  int
    , "2"  int
    , "3"  int
    , "4"  int
    , "5"  int
    , "6"  int
    , "7"  int
    , "8"  int
    , "9"  int
    , "10" int
    , "11" int
    , "12" int
)

This answer has a great amount of information on Postgre's crosstab()