From: air <mojaveranger7@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Calling the CTE for multiple inputs
Date: Thu, 4 Oct 2012 12:31:49 -0700 (PDT)
Message-ID: <1349379109760-5726661.post@n5.nabble.com> (raw)
I have a CTE that takes top left and bottom right latitude/longitude values
along with a start and end date and it then calculates the amount of user
requests that came from those coordinates per hourly intervals between the
given start and end date. However, I want to execute this query for about
2600 seperate 4-tuples of lat/lon corner values instead of typing them in
one-by-one. How would I do that? The code is as below:
WITH cal AS (
SELECT generate_series('2011-02-02 00:00:00'::timestamp ,
'2012-04-01 05:00:00'::timestamp ,
'1 hour'::interval) AS stamp
),
qqq AS (
SELECT date_trunc('hour', calltime) AS stamp, count(*) AS zcount
FROM mytable
WHERE calltime >= '2011-02-13 11:59:11'
AND calltime <= '2012-02-13 22:02:21'
AND (calltime::time >= '11:59:11'
AND calltime::time <= '22:02:21')
AND ((extract(DOW from calltime) = 3) /*OR (extract(DOW from calltime) =
5)*/)
AND lat BETWEEN '40' AND '42'
AND lon BETWEEN '28' AND '30'
GROUP BY date_trunc('hour', calltime)
)
SELECT cal.stamp, COALESCE (qqq.zcount, 0) AS zcount
FROM cal
LEFT JOIN qqq ON cal.stamp = qqq.stamp
WHERE cal.stamp >= '2011-02-13 11:00:00'
AND cal.stamp <= '2012-02-13 22:02:21'
AND ((extract(DOW from cal.stamp) = 3) /*OR (extract(DOW from cal.stamp) =
5)*/)
AND (
extract ('hour' from cal.stamp) >= extract ('hour' from '2011-02-13
11:00:00'::timestamp) AND
extract ('hour' from cal.stamp) <= extract ('hour' from '2012-02-13
22:02:21'::timestamp)
)
ORDER BY stamp ASC;
And the sample output for the query above:
calltime zcount
"2011-02-16 11:00:00" 0
"2011-02-16 12:00:00" 70
"2011-02-16 13:00:00" 175
"2011-02-16 14:00:00" 97
"2011-02-16 15:00:00" 167
.
.
.
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Calling-the-CTE-for-multiple-inputs-tp5726661.html
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: Calling the CTE for multiple inputs
In-Reply-To: <1349379109760-5726661.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