CODEWITHSUNDEEP
sqlpostgresqlsum
hahakomisch
hahakomisch

Reputation: 49

SQL sum over a time interval / rows

the following code

SELECT distinct DATE_PART('year',date) as year_date,
DATE_PART('month',date) as month_date,
count(prepare_first_buyer.person_id) as no_of_customers_month
FROM
(
SELECT DATE(bestelldatum)  ,person_id
,ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id)
FROM ani.bestellung
) prepare_first_buyer
WHERE row_number=1
GROUP BY DATE_PART('year',date),DATE_PART('month',date)
ORDER BY DATE_PART('year',date),DATE_PART('month',date)

gives this table back:

| year_date | month_date | no_of_customers_month |
|:--------- |:----------:| ---------------------:|
| 2017      | 1          | 2                     |
| 2017      | 2          | 5                     |
| 2017      | 3          | 4                     |
| 2017      | 4          | 8                     |
| 2017      | 5          | 1                     |
| .         | .          | .                     |
| .         | .          | .                     |

where als three are numeric values. I need now a new column were i sum up all values from 'no_of_customers_month' for 12 months back. e.g.

| year_date | month_date | no_of_customers_month | sum_12mon |
|:--------- |:----------:| :--------------------:|----------:|
| 2019      | 1          | 2                     |    23     |

where 23 is the sum from 2019-1 back to 2018-1 over 'no_of_customers_month'.

Thx for the help.

Upvotes: 0

Views: 2094

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use window functions:

SELECT DATE_TRUNC('month', date) as yyyymm,
       COUNT(*) as no_of_customers_month,
       SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', date) RANGE BETWEEN '11 month' PRECEDING AND CURRENT ROW)
FROM (SELECT DATE(bestelldatum), person_id,
             ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id)
      FROM ani.bestellung
     ) b
WHERE row_number = 1
GROUP BY yyyymm
ORDER BY yyyymm;

Note: This uses date_trunc() to retrieve the year/month as a date, allowing the use of range(). I also find a date more convenient than having the year and month in separate columns.

Some versions of Postgres don't support range window frames. Assuming you have data for each month, you can use rows:

SELECT DATE_TRUNC('month', date) as yyyymm,
       COUNT(*) as no_of_customers_month,
       SUM(COUNT(*)) OVER (ORDER BY DATE_TRUNC('month', date) ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)
FROM (SELECT DATE(bestelldatum), person_id,
             ROW_NUMBER() OVER (PARTITION BY person_id ORDER BY person_id)
      FROM ani.bestellung
     ) b
WHERE row_number = 1
GROUP BY yyyymm
ORDER BY yyyymm;

Upvotes: 2

Related Questions