Re: GROUPING

From: Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: David Fetter <david(at)fetter(dot)org>, PG Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: GROUPING
Date: 2015-05-20 19:17:53
Message-ID: 87617n12yh.fsf@news-spur.riddles.org.uk
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

>> I was thinking it should produce NUMERIC rather than int4 as it does
>> now in order to accommodate large numbers of columns, but the
>> usefulness of the bitmap is greatly increased if there's a simple
>> CAST to bit(n).

Tom> Maybe INT8 would be a better choice than INT4? But I'm not sure
Tom> there's any practical use-case for more than 30 grouping sets
Tom> anyway. Keep in mind the actual output volume probably grows like
Tom> 2^N.

Spec says "The declared type of the result is exact numeric with an
implementation-defined precision and a scale of 0 (zero)." for what
that's worth. It doesn't give any hint that I can see for the max
number of columns; it just defines grouping(a...,z) as being equal to
2*grouping(a...) + grouping(z).

But the number of grouping sets isn't really relevant here, rather the
number of columns used for grouping.

In any case, if 31 isn't enough for you, you can call it multiple times:

select ..., grouping(a,b,...,z), grouping(a1,b1,...z1), ...

I didn't think >31 columns would be an issue, but changing it to bigint
is of course trivial if anyone thinks it necessary.

A possibly more interesting question is whether any other db products
have operations like GROUPING() that we could usefully support?

--
Andrew (irc:RhodiumToad)

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Pavel Stehule 2015-05-20 19:19:13 Re: jsonb concatenate operator's semantics seem questionable
Previous Message Andres Freund 2015-05-20 19:12:01 Re: Issues in Replication Progress Tracking