2008/9/16 Greg Stark <[EMAIL PROTECTED]>: > On Tue, Sep 16, 2008 at 3:02 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> "Pavel Stehule" <[EMAIL PROTECTED]> 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 > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
