0

I have a CTE based query, to which I pass about 2600 4-tuple latitude/longitude values using joins - these latitude longitude 4-tuples 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).- I am able to get the total requests per day within the timestamps given, that is, the total count of user requests on every specified day. (E.g. user opts to see every Wednesday or Wednesday AND Thursday etc. - between hours 11:55 and 22:04 between dates January 1 and 31, 2012 for every latitude/longitude 4-tuples I pass.) But I cannot view the rows with count 0. My query is as below:

WITH v AS (
   SELECT '2012-01-1 11:55:11'::timestamp AS _from
         ,'2012-01-31 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::date, sum(q.zcount) 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::date
ORDER  BY q.coordinates_id, stamp;

The output I get when I execute this query is basically like this (normally I have about 10354 rows returned excluding the rows with 0 zcount, just providing two coordinates for sake of similarity):

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

However, it should be like this where all rows with zcount 0 should also be printed out along with rows that have nonzero zcounts -E.g. January 25 with zcount 0 for the two coordinates with ID 1 and 2 should also be printed in this small portion of example-:

coordinates_id  | stamp      | zcount
1               ;"2012-01-04";      2
1               ;"2012-01-11";      3
1               ;"2012-01-18";      2
1               ;"2012-01-25";      0
2               ;"2012-01-04";      2
2               ;"2012-01-11";      3
2               ;"2012-01-18";      2
2               ;"2012-01-25";      0

How can I achieve this? Thanks in advance.

2
Contributors
1
Reply
4
Views
4 Years
Discussion Span
Last Post by LastMitch
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.