Grouping the results of a query with CTEs

Question

I have a CTE based query into which I pass about 2600 4-tuple latitude/longitude values - that have been ID tagged and held in a second table called coordinates. These top left and bottom right latitude / longitude values are passed into the CTE in order to display the amount of requests (hourly) made within those coordinates for given two timestamps).

However, I would like to get the total requests per day within the timestamps given. That is, I want to get the total count of user requests on every specified day. E.g. user opts to see every Wednesday or Wednesday AND Thursday etc. - between 11:55 and 22:04 between dates January 1 and 16, 2012 for every latitude/longitude 4-tuples I pass. The output would basically be like:

coordinates_id | stamp       | zcount

1                Jan 4 2012    200 (total requests on Wednesday Jan 4 between 11:55 and 22:04)
1                Jan 11 2012   121 (total requests on Wednesday Jan 11 between 11:55 and 22:04)
2                Jan 4 2012    255 (total requests on Wednesday Jan 4 between 11:55 and 22:04)
2                Jan 11 2012   211 (total requests on Wednesday Jan 11 between 11:55 and 22:04)
.
.
.

How would I do that? My query is as below:

WITH v AS (
   SELECT '2012-01-1 11:55:11'::timestamp AS _from -- provide times once
         ,'2012-01-16 22:02:21'::timestamp AS _to
   )
, q AS (
   SELECT c.coordinates_id
        , date_trunc('hour', t.calltime) AS stamp
        , count(*) AS zcount
   FROM   v
   JOIN   mytable t ON  t.calltime BETWEEN v._from AND v._to
                   AND (t.calltime::time >= v._from::time AND
                        t.calltime::time <= v._to::time) AND 
(extract(DOW from t.calltime) = 3)
   JOIN   coordinates c ON (t.lat, t.lon) 
                   BETWEEN (c.bottomrightlat, c.topleftlon)
                       AND (c.topleftlat, c.bottomrightlon)
   GROUP BY c.coordinates_id, date_trunc('hour', t.calltime)
   )
, cal AS (
   SELECT generate_series('2011-2-2 00:00:00'::timestamp
                        , '2012-4-1 05:00:00'::timestamp
                        , '1 hour'::interval) AS stamp
   FROM v
   )
SELECT q.coordinates_id, cal.stamp, COALESCE (q.zcount, 0) AS zcount
FROM v, cal
LEFT JOIN q USING (stamp)
WHERE (extract(hour from cal.stamp) >= extract(hour from v._from) AND
       extract(hour from cal.stamp) <= extract(hour from v._to)) AND 
(extract(DOW from cal.stamp) = 3)
       AND cal.stamp >= v._from AND cal.stamp <= v._to
GROUP BY q.coordinates_id, cal.stamp, q.zcount
ORDER BY q.coordinates_id ASC, stamp ASC;

And the sample result it yields is like this:

coordinates_id  | stamp                | zcount
1                 2012-01-04 16:00:00    1
1                 2012-01-04 19:00:00    1
1                 2012-01-11 14:00:00    1
1                 2012-01-11 17:00:00    1
1                 2012-01-11 19:00:00    1
2                 2012-01-04 16:00:00    1

So, as I mentioned above, I would like to see this as

coordinates_id  | stamp      | zcount
1                2012-01-04    2
1                2012-01-11    3
2                2012-01-04    1

Erwin Brandstetter · Accepted Answer · 2012-10-05T00:53:23.157

1

Change your final SELECT to:

SELECT q.coordinates_id, cal.stamp::date, sum(q.zcount) AS zcount
FROM   v, cal
LEFT   JOIN q USING (stamp)
WHERE  extract(hour from cal.stamp) BETWEEN extract(hour from v._from)
                                        AND extract(hour from v._to)
AND    extract(DOW from cal.stamp) = 3 
AND    cal.stamp >= v._from
AND    cal.stamp <= v._to
GROUP  BY 1,2
ORDER  BY 1,2;

The crucial part it to cast cal.stamp to date: cal.stamp::date.
That, and sum(q.zcount).

edited Oct 05 '12 at 00:53

answered Oct 04 '12 at 23:26

Erwin Brandstetter

605,456
145
1,078
1,228

I just saw a post where you were called the "Good Guy Greg" of PostgreSQL. I second that. – sm90901 Oct 04 '12 at 23:29
Just noticed a small issue -actually I forgot to ask this in the first place-, in my "initial" version -when I only needed the total query counts and nothing else like lat/lons-, my query also printed the rows with 0 count thanks to the `COALESCE (q.zcount, 0)`. How can I apply it to this case in order to get the rows with zcount 0? I applied the `COALESCE` to the `sum(q.zcount)` but it didn't work as I planned. – sm90901 Oct 05 '12 at 00:01
@sm90901 Are *rows missing* or is `zcount` lower than expected? – Erwin Brandstetter Oct 05 '12 at 00:24
rows missing. I just tried to get all mondays (2,9,16,23,30) between Jan 1 and 31 2012 but only got Jan 16 and 23 rows for some of the coordinate_id values, no rows with zcount 0 appear. – sm90901 Oct 05 '12 at 00:26
@sm90901: That should not occur. Are you sure you used proper timestamps in the CTE `v` **and** for `generate_series()`? – Erwin Brandstetter Oct 05 '12 at 01:01
right now, the `v` is `WITH v AS ( SELECT '2012-01-01 11:55:11'::timestamp AS _from -- provide times once ,'2012-01-31 22:02:21'::timestamp AS _to )` and the `generate_series()` is `SELECT generate_series('2012-01-01 00:00:00'::timestamp , '2012-01-31 23:00:00'::timestamp , '1 hour'::interval) AS stamp` I currently use the Year/month/day format – sm90901 Oct 05 '12 at 01:04
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/17580/discussion-between-erwin-brandstetter-and-sm90901) – Erwin Brandstetter Oct 05 '12 at 01:12

Grouping the results of a query with CTEs

1 Answers1