CODEWITHSUNDEEP
sqlpostgresql
Fishtoaster
Fishtoaster

Reputation: 1819

How do I get a trailing week count for every day over a given period (on Postgres)?

Say I’ve got an events table with just the columns id and occurred (which is just a datetime).

I want to get, for every day in a given period, the number of events in the previous week. So, let’s say the period was Jan 1 through April 1. I’d want the results of this query to look like:

_______________
|count | date |
|------|------|
| 3    | 1/1  |
| 2    | 1/2  |
| 0    | 1/3  |
| 4    | 1/4  |
---------------

Where count is, for that date, the number of events that happened in the week prior. So, the 3 count for 1/1 is how many events happened between Dec 25th and Jan 1.

I could do this easily enough in code:

for (date in 1/1 to 4/1) {
  start_date = date - 7 days
  db.query(’SELECT COUNT(1) FROm events WHERE occurred > start_date AND occurred < date`)
}

Unfortunately, this would result in over a hundred separate queries. I’d like to figure out how to do this in one query.

Upvotes: 0

Views: 976

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271121

Hmm, you can generate all the dates in the period using generate_series(). Then then join in the data and do a cumulative sum:

select dd.dte,
       sum(cnt) over (order by dd.dte rows between 6 preceding and current date) as avg_7daymoving
from generate_series('2015-01-01'::timestamp, '2015-04-01'::timestamp, '1 day'::interval) dd(dte) left join
     (select date_trunc('day', occurred) as dte, count(*) as cnt
      from events e
      group by date_trunc('day', occurred)
     ) e
     on e.dte = dd.dte

Upvotes: 1

Related Questions