Reputation: 1819
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
Reputation: 1271003
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