Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C33BF6344D9 for ; Thu, 25 Feb 2010 05:21:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 23552-02-4 for ; Thu, 25 Feb 2010 09:20:50 +0000 (UTC) X-Greylist: delayed 00:07:40.766684 by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 56C776345B2 for ; Thu, 25 Feb 2010 05:20:28 -0400 (AST) Received: by bwz8 with SMTP id 8so544249bwz.3 for ; Thu, 25 Feb 2010 01:20:26 -0800 (PST) Received: by 10.204.32.196 with SMTP id e4mr508537bkd.131.1267089165663; Thu, 25 Feb 2010 01:12:45 -0800 (PST) Received: from SQUID02 (mol92-8-88-166-5-153.fbx.proxad.net [88.166.5.153]) by mx.google.com with ESMTPS id 13sm3324599bwz.11.2010.02.25.01.12.43 (version=SSLv3 cipher=RC4-MD5); Thu, 25 Feb 2010 01:12:43 -0800 (PST) From: "Julien Theulier" To: "'Mose'" , "'Dave Crooke'" Cc: "'pgsql-performance'" References: <9eb6f9411002241350m4532305ai88c5640f0e3dd864@mail.gmail.com> In-Reply-To: <9eb6f9411002241350m4532305ai88c5640f0e3dd864@mail.gmail.com> Subject: Re: Extracting superlatives - SQL design philosophy Date: Thu, 25 Feb 2010 10:20:45 +0100 Organization: SQUID Solutions Message-ID: <000c01cab5fb$d03e4100$70bac300$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_000D_01CAB604.3202A900" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: Acq1m3Txd0pTtzQ4QQG7Kuob31LixwAGKvWw Content-Language: fr X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.598 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201002/355 X-Sequence-Number: 37776 This is a multi-part message in MIME format. ------=_NextPart_000_000D_01CAB604.3202A900 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi all, =20 Just for your information, and this is not related to PG directly: Teradata provides a =93qualify=94 syntax which works as a filtering = condition on a windowed function result. This is the only DB allowing this direct filtering on windowed functions, from what I know. So, as an example, the query you ask for becomes very easy on this = database: select=20 city, temp, date=20 from bar=20 qualify row_number() over (partition by city order by temp desc)=3D1 =20 This is very practical indeed (you can mix it also with classical where/having/group by syntaxes). On postgres, you may get the same result using an inner query (sorry, I can=92t test it for now) such as: select=20 city, temp, date=20 from (select city, temp, date, row_number() over (partition by city order by = temp desc) as nr=20 from bar ) a1 where nr=3D1 =20 Julien Theulier =20 De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] De la part de Mose Envoy=E9 : mercredi 24 f=E9vrier 2010 22:50 =C0 : Dave Crooke Cc : pgsql-performance Objet : Re: [PERFORM] Extracting superlatives - SQL design philosophy =20 Can you try using window functions? =20 Something like this: =20 select distinct city, first_value(temp) over w as max_temp, first_value(date) over w as max_temp_date from cities window w as (partition by city order by temp desc) =20 http://www.postgresql.org/docs/current/static/tutorial-window.html =20 - Mose =20 On Wed, Feb 24, 2010 at 1:31 PM, Dave Crooke < = dcrooke@gmail.com> wrote: This is a generic SQL issue and not PG specific, but I'd like to get an opinion from this list. Consider the following data: # \d bar Table "public.bar" Column | Type | Modifiers --------+-----------------------------+----------- city | character varying(255) | temp | integer | date | timestamp without time zone | # select * from bar order by city, date; city | temp | date -----------+------+--------------------- Austin | 75 | 2010-02-21 15:00:00 Austin | 35 | 2010-02-23 15:00:00 Edinburgh | 42 | 2010-02-23 15:00:00 New York | 56 | 2010-02-23 15:00:00 New York | 78 | 2010-06-23 15:00:00 (5 rows) If you want the highest recorded temperature for a city, that's easy to do, since the selection criteria works on the same column that we are extracing: # select city, max(temp) from bar group by city order by 1; city | max -----------+----- Austin | 75 Edinburgh | 42 New York | 78 (3 rows) However there is (AFAIK) no simple way in plain SQL to write a query that performs such an aggregation where the aggregation criteria is on one column and you want to return another, e.g. adding the the *date of* that highest temperature to the output above, or doing a query to get the most recent temperature reading for each city. What I'd like to do is something like the below (and I'm inventing mock syntax here, the following is not valid SQL): -- Ugly implicit syntax but no worse than an Oracle outer join ;-) select city, temp, date from bar where date=3Dmax(date) group by city, temp order by city; or perhaps -- More explicit select aggregate_using(max(date), city, temp, date) from bar group by city, temp order by city; Both of the above, if they existed, would be a single data access followed by and sort-merge. The only way I know how to do it involves doing two accesses to the = data, e.g. # select city, temp, date from bar a where date=3D(select max(b.date) from bar b where a.city=3Db.city) order by 1; city | temp | date -----------+------+--------------------- Austin | 35 | 2010-02-23 15:00:00 Edinburgh | 42 | 2010-02-23 15:00:00 New York | 78 | 2010-06-23 15:00:00 (3 rows) # explain select * from bar a where date=3D(select max(b.date) from bar b where a.city=3Db.city) order by 1; QUERY PLAN -------------------------------------------------------------------------= - Sort (cost=3D1658.86..1658.87 rows=3D1 width=3D528) Sort Key: a.city -> Seq Scan on bar a (cost=3D0.00..1658.85 rows=3D1 width=3D528) Filter: (date =3D (subplan)) SubPlan -> Aggregate (cost=3D11.76..11.77 rows=3D1 width=3D8) -> Seq Scan on bar b (cost=3D0.00..11.75 rows=3D1 width=3D8) -- would be an index lookup in a real scenario Filter: (($0)::text =3D (city)::text) (8 rows) -- Sent via pgsql-performance mailing list ( = pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance =20 ------=_NextPart_000_000D_01CAB604.3202A900 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hi all,

 

Just for your information, and this is not related to PG directly:

Teradata provides a “qualify” syntax which = works as a filtering condition on a windowed function result. This is the only DB allowing this direct filtering on windowed functions, from what I = know.

So, as an example, the query you ask for becomes very = easy on this database:

select

city, temp, date

from bar

qualify row_number() over (partition by city order by temp = desc)=3D1

 

This is very practical indeed (you can mix it also with classical where/having/group by syntaxes).

On postgres, you may get the same result using an inner = query (sorry, I can’t test it for now) such as:

select

city, temp, date

from

(select city, temp, date, row_number() over (partition by city order by temp = desc) as nr

from bar ) a1

where nr=3D1

 

Julien Theulier

 

De : pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@postgresql.org] De la part de = Mose
Envoy=E9 : mercredi 24 f=E9vrier 2010 22:50
=C0 : Dave Crooke
Cc : pgsql-performance
Objet : Re: [PERFORM] Extracting superlatives - SQL design philosophy

 

Can you try using window = functions?

 

Something like = this:

 

select = distinct

  city,

  first_value(temp) = over w as max_temp,

  first_value(date) = over w as max_temp_date

 from

  cities

  window w as = (partition by city order by temp desc)

 

 

- Mose

 

On Wed, Feb 24, 2010 at 1:31 PM, = Dave Crooke <dcrooke@gmail.com> wrote:

This is a generic SQL issue and = not PG specific, but I'd like to get
an opinion from this list.

Consider the following data:

# \d bar
            &= nbsp;   Table "public.bar"
 Column |            Type           &nb= sp; | Modifiers
--------+-----------------------------+-----------
 city   | character = varying(255)      |
 temp   | integer           =           |
 date   | timestamp without time zone |

# select * from bar order by city, date;
  city    | temp |        date
-----------+------+---------------------
 Austin    |   75 | 2010-02-21 15:00:00
 Austin    |   35 | 2010-02-23 15:00:00
 Edinburgh |   42 | 2010-02-23 15:00:00
 New York  |   56 | 2010-02-23 15:00:00
 New York  |   78 | 2010-06-23 15:00:00
(5 rows)

If you want the highest recorded temperature for a city, that's easy
to do, since the selection criteria works on the same column that we
are extracing:

# select city, max(temp) from bar group by city order by 1;
  city    | max
-----------+-----
 Austin    |  75
 Edinburgh |  42
 New York  |  78
(3 rows)


However there is (AFAIK) no simple way in plain SQL to write a query
that performs such an aggregation where the aggregation criteria is = on
one column and you want to return another, e.g. adding the the *date
of* that highest temperature to the output above, or doing a query = to
get the most recent temperature reading for each city.

What I'd like to do is something like the below (and I'm inventing
mock syntax here, the following is not valid SQL):

-- Ugly implicit syntax but no worse than an Oracle outer join ;-)
select city, temp, date from bar where date=3Dmax(date) group by = city,
temp order by city;

or perhaps

-- More explicit
select aggregate_using(max(date), city, temp, date) from bar group = by
city, temp order by city;

Both of the above, if they existed, would be a single data access
followed by and sort-merge.

The only way I know how to do it involves doing two accesses to the = data, e.g.

# select city, temp, date from bar a where date=3D(select = max(b.date)
from bar b where a.city=3Db.city) order by 1;
  city    | temp |        date
-----------+------+---------------------
 Austin    |   35 | 2010-02-23 15:00:00
 Edinburgh |   42 | 2010-02-23 15:00:00
 New York  |   78 | 2010-06-23 15:00:00
(3 rows)


# explain select * from bar a where date=3D(select max(b.date) from = bar
b where a.city=3Db.city) order by 1;
                    =            QUERY PLAN
-------------------------------------------------------------------------= -
 Sort  (cost=3D1658.86..1658.87 rows=3D1 width=3D528)
  Sort Key: a.city
  ->  Seq Scan on bar a  (cost=3D0.00..1658.85 = rows=3D1 width=3D528)
        Filter: (date =3D (subplan))
        SubPlan
          ->  Aggregate  (cost=3D11.76..11.77 rows=3D1 width=3D8)
                ->  Seq = Scan on bar b  (cost=3D0.00..11.75 rows=3D1
width=3D8)     -- would be an index lookup in a real = scenario
                    =   Filter: (($0)::text =3D (city)::text)
(8 rows)

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance<= /a>

 

------=_NextPart_000_000D_01CAB604.3202A900--