On 14 Mar 2005, Greg Stark wrote:
> select distinct on (x) x,y,z
> order by x,y,z
>
> You can do the equivalent:
>
> select x, first(y), first(z)
> order by x,y,z
> group by x
>
> But you can also handle the more general case like:
>
> select x, first(y), first(z), avg(a), sum(s)
> order by x,y,z
> group by x
>
> I don't really care one way or the other about the "first" function per se.
The standard (sql2003) have what is called windows where one can do these
things and much more.
A window is like a group by, but you keep all rows in the result. This can
be used to for example enumrate the rows within a window partition using
ROW_NUMBER(). It can later can be used in a WHERE to select the top 3 rows
in each window, or something like that.
Here is an example that calculate the avg and sum for each window. It
return all the rows (x values) in the window together with a row number
(within the window) and the 2 aggregate results. In this case the
aggregates will be the same for all rows in the partition but one can also
get it to do a kind of of sliding window aggregate (for example the
avarage of the row before and the row after the current row):
SELECT ROW_NUMBER() OVER bar AS num, x, avg(a) OVER bar, sum (a) OVER bar
FROM foo
WINDOW bar AS PARTITION BY x ORDER BY x, y, z;
and then one can put that whole thing as a subselect and just select the
rows with num = 1.
This doesn't mean that we don't want functions like first() and last(),
they are also be useful. I just wanted to inform that with sql2003 one can
write queries with the same effect as the above (but much more
complicated, of course :-).
ps. All I know about the window functions is from what I've read in the
draft of the sql2003 standard. It's not the perfect way to learn about new
features so I wont bet my life on that the above example works as is. If
someone knows better I'd like to hear about it.
ps2. I'd love to read a book that discusses the sql2003 (or even sql99)
that explain features, give examples, and so on. But i guess the market
don't want books that explain things that no database have implemented yet
(Oracle have window functions but i've never used that).
--
/Dennis Björklund