Re: proposal - GROUPING SETS

2008/9/16 Greg Stark <stark(at)enterprisedb(dot)com>:
> On Tue, Sep 16, 2008 at 3:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)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
>