2008/9/16 Greg Stark <stark@enterprisedb.com>:
> On Tue, Sep 16, 2008 at 3:02 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> "Pavel Stehule" <pavel.stehule@gmail.com> writes:
>>> select a, b from t group by grouping sets(a, b);
>>
>>> is same as:
>>
>>> select a, NULL from t group by a
>>> union all
>>> select NULL, b from t group by b;
>>
>> Really? That seems utterly bizarre, not to say pointless.
>> You sure you read the spec correctly?
>
> I think that's basically right but IIRC you need another set of
> parentheses so it's GROUPING SETS ((a),(b))
grouping sets ((a),(b)) is same as gs(a,b)
NOTE 165 — The result of the transform is to replace CL with a
<grouping sets specification> that contains a <grouping
set> for all possible subsets of the set of <ordinary grouping set>s
in the <ordinary grouping set list> of the <cube list>,
including <empty grouping set> as the empty subset with no <ordinary
grouping set>s.
For example, CUBE (A, B, C) is equivalent to:
GROUPING SETS ( /* BSLi */ (A, B, C), /* 111 */ (A, B ), /* 110 */ (A, C),
/* 101 */ (A ), /* 100 */ ( B, C), /* 011 */ ( B ), /* 010 */ (
C), /* 001 */ ( )
)
As another example, CUBE ((A, B), (C, D)) is equivalent to:
GROUPING SETS ( /* BSLi */ (A, B, C, D), /* 11 */ (A, B ), /* 10 */ (
C,D), /* 01 */ ( )
)
it's exactly defined in standard WD 9075-2:200w(E) 7.9 <group by
clause> page 354 Foundation (SQL/Foundation)
>
> Basically grouping sets are a generalized form of rollup and cube. If
> you did GROUPING SETS ((a),(a,b),(a,b,c)) you would get the same as
> ROLLUP. And if you listed every possible subset of the grouping
> columns it would be the equivalent of CUBE. But it lets you specify an
> arbitrary subset of the combinations that CUBE would return.
>
> --
> greg
>
