Re: GROUP BY

On 18 May, 19:32, Marshall <marshall.spi..._at_gmail.com> wrote:
> GROUP BY works on a single relation. The input relation's attributes
> are partitioned into one of three categories:
>
> 1) those we ignore
> 2) those we group by
> 3) those we apply the function to
>
> The result relation will have attributes from 2) above, and in
> addition:
>
> 4) those created as a result of the applied function.
>
> The functions mentioned in 4) may only use as arguments the attributes
> listed in 3).
>
> So for example given R(a, b, c):
>
> select b, sum(c) as d from R group by b
>
> will ignore attribute(s) a, group by attribute(s) b, and apply
> a function to attribute(s) c, yielding the additional attribute d.
> The result will have attributes (b, d).
>
> One of my favorite lessons from TTM was always to consider the 0-arity
> case.
>
> It is not unusual to consider the case of 1) or 2) being empty.
> In the first case, we are either grouping by or aggregating over
> all attributes. In the second, we aren't grouping, but are just
> aggregating over the entire relation; the result will have
> a single row.
>
> What if 3) is empty?
>
> In that case, GROUP BY is equivalent to PROJECT!
>

There is the following exception. SQL supports the case where both 2) and 3) are empty but it is not quite a projection since:

SELECT 1
FROM r
GROUP BY (); returns a single tuple even if r is empty. GROUP BY is therefore something like TTM's SUMMARIZE operator where in this case the BY argument is DEE - possibly the only place that SQL explicitly supports a 0-degree relation.

--
David Portas