Re: proposal - GROUPING SETS

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
>