From: air <mojaveranger7@gmail.com>
To: pgsql-sql@postgresql.org
Subject: How to make this CTE also print rows with 0 as count?
Date: Sat, 6 Oct 2012 05:47:46 -0700 (PDT)
Message-ID: <1349527666985-5726792.post@n5.nabble.com> (raw)
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.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-make-this-CTE-also-print-rows-with-0-as-count-tp57267...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mojaveranger7@gmail.com
Subject: Re: How to make this CTE also print rows with 0 as count?
In-Reply-To: <1349527666985-5726792.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox