pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCalling the CTE for multiple inputs
5+ messages / 4 participants
[nested] [flat]
* Calling the CTE for multiple inputs
@ 2012-10-04 19:31 air <mojaveranger7@gmail.com>
2012-10-04 19:51 ` Re: Calling the CTE for multiple inputs David Johnston <polobo@yahoo.com>
2012-10-05 03:34 ` Re: Calling the CTE for multiple inputs Craig Ringer <ringerc@ringerc.id.au>
2012-10-07 01:02 ` Re: Calling the CTE for multiple inputs Jasen Betts <jasen@xnet.co.nz>
0 siblings, 3 replies; 5+ messages in thread
From: air @ 2012-10-04 19:31 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Calling the CTE for multiple inputs
2012-10-04 19:31 Calling the CTE for multiple inputs air <mojaveranger7@gmail.com>
@ 2012-10-04 19:51 ` David Johnston <polobo@yahoo.com>
2 siblings, 0 replies; 5+ messages in thread
From: David Johnston @ 2012-10-04 19:51 UTC (permalink / raw)
To: 'air' <mojaveranger7@gmail.com>; pgsql-sql
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of air
> Sent: Thursday, October 04, 2012 3:32 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] Calling the CTE for multiple inputs
>
> 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:
>
> AND lat BETWEEN '40' AND '42'
> AND lon BETWEEN '28' AND '30'
I don't really follow but if I understand correctly you want to generate
2600 distinct rows containing values like (40, 42, 28, 30)?
You could use "generate_series()" to generate each individual number along
with a row_number and then join them all together:
SELECT lat_low, lat_high, long_low, long_high
FROM (SELECT ROW_NUMBER() OVER () AS index,
generate_series(...) AS lat_low) lat_low_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
lat_high) lat_high_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
long_low) long_low_rel
NATURAL JOIN (SELECT ROW_NUMBER() OVER () AS index, generate_series(...) AS
long_high) long_high_rel
You may (probably will) need to move the generate_series into a FROM clause
in the sub-query but the concept holds.
Then in the main query you'd simply...
AND lat BETWEEN lat_low AND lat_high
AND lon BETWEEN long_low AND long_high
HTH
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Calling the CTE for multiple inputs
2012-10-04 19:31 Calling the CTE for multiple inputs air <mojaveranger7@gmail.com>
@ 2012-10-05 03:34 ` Craig Ringer <ringerc@ringerc.id.au>
2012-10-06 12:56 ` Re: Calling the CTE for multiple inputs air <mojaveranger7@gmail.com>
2 siblings, 1 reply; 5+ messages in thread
From: Craig Ringer @ 2012-10-05 03:34 UTC (permalink / raw)
To: air <mojaveranger7@gmail.com>; +Cc: pgsql-sql
On 10/05/2012 03:31 AM, air wrote:
> 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:
Sometimes it's easiest to just wrap it in an SQL function.
CREATE OR REPLACE FUNCTION some_expr( lat_low IN integer, lat_high IN
integer, lon_low IN integer, lon_high IN integer, calltime OUT
timestamptz, zcount OUT integer) returns setof record as $$
-- ... query text here, using $1 through $4 to refer to parameters
$$ LANGUAGE 'SQL';
... then invoke with something like (untested, from memory):
SELECT (some_expr(lat_low, lat_high, lon_log, lon_high).*)
FROM table_containing_lat_lon_pairs;
Alternately you may be able to rephrase the `qqq` part as a `join` on a
table containing the lat/lon pairs and include those pairs in `qqq`'s
output as well as the rest. You then use those in the outer query where
required. Without a schema to test with and some understanding of what
the query does it's hard to say exactly.
Wrapping it in a function is likely to be less efficient, but probably
easier.
--
Craig Ringer
>
>
> 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.
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Calling the CTE for multiple inputs
2012-10-04 19:31 Calling the CTE for multiple inputs air <mojaveranger7@gmail.com>
2012-10-05 03:34 ` Re: Calling the CTE for multiple inputs Craig Ringer <ringerc@ringerc.id.au>
@ 2012-10-06 12:56 ` air <mojaveranger7@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: air @ 2012-10-06 12:56 UTC (permalink / raw)
To: pgsql-sql
Thanks for the info, used a join in order to do that, now I have a "print
rows with zcount 0" problem in my new question at
http://postgresql.1045698.n5.nabble.com/How-to-make-this-CTE-also-print-rows-with-0-as-count-td57267...
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Calling-the-CTE-for-multiple-inputs-tp5726661p5726793.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Calling the CTE for multiple inputs
2012-10-04 19:31 Calling the CTE for multiple inputs air <mojaveranger7@gmail.com>
@ 2012-10-07 01:02 ` Jasen Betts <jasen@xnet.co.nz>
2 siblings, 0 replies; 5+ messages in thread
From: Jasen Betts @ 2012-10-07 01:02 UTC (permalink / raw)
To: pgsql-sql
On 2012-10-04, air <mojaveranger7@gmail.com> wrote:
> 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:
I see that your're using the CTE only to fill-in the nulls in main query group-by
is it the same plan for the coordinates (non-overlapping ranges)?
how are your coordinates respresented?
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2012-10-07 01:02 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-04 19:31 Calling the CTE for multiple inputs air <mojaveranger7@gmail.com>
2012-10-04 19:51 ` David Johnston <polobo@yahoo.com>
2012-10-05 03:34 ` Craig Ringer <ringerc@ringerc.id.au>
2012-10-06 12:56 ` air <mojaveranger7@gmail.com>
2012-10-07 01:02 ` Jasen Betts <jasen@xnet.co.nz>
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