CODEWITHSUNDEEP
sqlpostgresqldatetimecountdate-arithmetic
Matt
Matt

Reputation: 15061

PostgreSQL combining a count, a last 7 days count and a last 30 days count Grouped by days

I have a small table (TABLEA) which has two columns IDA & DATETIMEA.

TABLEA

IDA  DATETIMEA
1    2020-03-16 13:15:00
2    2020-03-17 15:25:00
3    2020-03-18 17:10:00
5    2020-03-19 11:44:00
5    2020-03-20 12:55:00
5    2020-03-21 19:35:00
7    2020-03-22 10:13:00
8    2020-03-22 15:25:00
8    2020-03-28 12:12:00
9    2020-03-29 17:55:00
10   2020-03-30 11:54:00
12   2020-03-30 15:35:00
12   2020-03-31 13:19:00

I am trying to get the total IDA's per day, Total in Last 7 days and total in last 30 days.

Expected Output

DATE       DAY L7 L30
2020-03-16 1   1  1
2020-03-17 1   2  2
2020-03-18 1   3  3
2020-03-19 1   4  4
2020-03-20 1   5  5
2020-03-21 1   6  6
2020-03-22 2   8  8  
2020-03-28 1   3  9
2020-03-29 1   4  10
2020-03-30 2   6  12
2020-03-31 1   7  13

I have tried putting the date related outputs in sub queries but they return 0.

SELECT t.DATETIMEA::date date,
COUNT(t.*) "day",
(SELECT COUNT(w.*) FROM TABLEA w WHERE w.DATETIMEA::date BETWEEN w.DATETIMEA::date AND w.DATETIMEA::date - 7) week,
(SELECT COUNT(m.*) FROM TABLEA m WHERE m.DATETIMEA::date BETWEEN m.DATETIMEA::date AND m.DATETIMEA::date - 30) "month"
FROM TABLEA t
GROUP BY t.DATETIMEA::date
ORDER BY t.DATETIMEA::date

Upvotes: 4

Views: 1994

Answers (2)

GMB
GMB

Reputation: 222582

If your data may have gaps in days, then you need a range frame specification rather than a rows frame. Happily Postgres supports this specification, so you can do:

select
    datetimea::date date,
    count(*) "day",
    sum(count(*)) over(
        order by datetimea::date
        range between '7 day' preceding and current row
    ) l7,
        sum(count(*)) over(
        order by datetimea::date
        range between '30 day' preceding and current row
    ) l30
from mytable
group by datetimea::date
order by datetimea::date

Demo on DB Fiddle:

date       | day | l7 | l30
:--------- | --: | -: | --:
2020-03-16 |   1 |  1 |   1
2020-03-17 |   1 |  2 |   2
2020-03-18 |   1 |  3 |   3
2020-03-19 |   1 |  4 |   4
2020-03-20 |   1 |  5 |   5
2020-03-21 |   1 |  6 |   6
2020-03-22 |   2 |  8 |   8
2020-03-28 |   1 |  4 |   9
2020-03-29 |   1 |  4 |  10
2020-03-30 |   2 |  4 |  12
2020-03-31 |   1 |  5 |  13

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1270523

I think window functions do what you want:

select t.datetimea::date, count(*) as on_day,
       sum(count(*)) over (order by t.datetimea::date rows between 6 preceding and current row) as sum_7,
       sum(count(*)) over (order by t.datetimea::date rows between 29 preceding and current row) as sum_30
from tablea t
group by t.datetimea::date;

By "last 7 days", I assume you mean today and the preceding 6 days. If you mean 7 days before today, the window frame can easily be adjusted.

Upvotes: 2

Related Questions