Re: Calling the CTE for multiple inputs
От | Craig Ringer |
---|---|
Тема | Re: Calling the CTE for multiple inputs |
Дата | |
Msg-id | 506E5549.2070204@ringerc.id.au обсуждение исходный текст |
Ответ на | Calling the CTE for multiple inputs (air <mojaveranger7@gmail.com>) |
Ответы |
Re: Calling the CTE for multiple inputs
(air <mojaveranger7@gmail.com>)
|
Список | 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. > >
В списке pgsql-sql по дате отправления: