PostgreSQL – Building a Trending Query

postgresql

Is there a way to build a query that returns a record set consisting of a date and the number of records == that date? For example, I have a DB that is tracking inspections that take place on every day. I want to build a data set to be used for a trending graph to see how many inspections took place on each day streching back 365 days.

Record set desired would look like this:

date | InspectionCount
2013-12-30  265
2013-12-29  123
2013-12-28  425
.
.
.
2013-01-01  15

Is there a way to do that without write code that will simple loop through 365 times and sort of manually generate daily counts like this:

select count(reporting_id) as InspectionCount
from inspection
where close_conf_date >= '2013-12-30'
and close_conf_date < '2013-12-31'

while gradually winding the clock back 365 days? Please let me know if you need more info.

Update

I have just tried this:

WITH RECURSIVE t(n) AS (
VALUES (cast('2013-12-30' as timestamp))
UNION ALL
SELECT n-interval '1 day' FROM t WHERE n >=('2013-12-30'-interval '365 day')
)
SELECT  cast(t.n as date) as date_column, count(reporting_id) 
FROM t
INNER JOIN inspection i ON (cast(t.n as date) = cast(i.close_conf_date as date))
GROUP BY cast(t.n as date)

and I am getting this error i do not really understand:
interval field value out of range: "2013-12-30"

Best Answer

You don't need the recursive CTE, just use generate_series():

You also need to use an outer join in order for the days without inspection to show up as zero:

with days as (
  select day::date
  from generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) day
) 
select d.day as date_column, 
       count(i.reporting_id) as cnt
from days d
  left join inspection i on i.date = d.day
group by d.day

generate_series(date '2013-12-30', date '2013-12-30' + 365, interval '1' day) generates one row for each date between '2013-12-30' and '2013-12-30' + 365. For details on generate_series() see the manual: http://www.postgresql.org/docs/current/static/functions-srf.html

The ::date notation is just a shorthand for cast(.. as date)

Related Question