Message-ID: <506E5549.2070204@ringerc.id.au>
Date: Fri, 05 Oct 2012 11:34:33 +0800
From: Craig Ringer <ringerc@ringerc.id.au>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:14.0) Gecko/20120717 Thunderbird/14.0
MIME-Version: 1.0
To: air <mojaveranger7@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Calling the CTE for multiple inputs
References: <1349379109760-5726661.post@n5.nabble.com>
In-Reply-To: <1349379109760-5726661.post@n5.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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.
>
>