Re: crosstab/repivot...any interest? - Mailing list pgsql-hackers
| From | Morris de Oryx |
|---|---|
| Subject | Re: crosstab/repivot...any interest? |
| Date | |
| Msg-id | CAKqnccjDPc0ynqNv0zsfhAxv8uWbvAouUHD1VCCw3C4jgKtvbQ@mail.gmail.com Whole thread Raw |
| In response to | Re: crosstab/repivot...any interest? (David Fetter <david@fetter.org>) |
| Responses |
crosstab/repivot...any interest?
(Merlin Moncure <mmoncure@gmail.com>)
|
| List | pgsql-hackers |
Hello, I'm not a C coder and can't help....but I love cross-tab/pivot tables. They're the best, and just fantastic for preparing data to feed into various analysis tools. The tablefunc module is helpful, but a bit awkward to use (as I remember it.)
From a user's point of view, I high-performance cross-tab generator would be just fantastic.
As I understand it, this is what's involved in a pivot:
1. Identify rows that should be grouped (consolidated.)
2. Distinguish the value that identifies each derived column.
3. Distinguish the value that identifies each row-column value.
4. Collapse the rows, build the columns, and populate the 'cells' with data.
In an ideal world, you would be able to perform different grouping operations. Such as count, sum, avg, etc.
If there's a way to do this in a system-wide and standards-pointing way, so much the better.
Apologies if I'm violating list etiquette by jumping in here. I've been lurking on several Postgres lists for a bit and picking up interesting details every day. If I've been Unintentionally and Cluelessly Off, I'm find with being told.
On Sat, Jan 26, 2019 at 10:49 AM David Fetter <david@fetter.org> wrote:
On Fri, Jan 25, 2019 at 04:31:00PM -0600, Merlin Moncure wrote:
> On Fri, Jan 25, 2019 at 3:16 PM David Fetter <david@fetter.org> wrote:
> >
> > On Fri, Jan 25, 2019 at 02:21:55PM -0600, Merlin Moncure wrote:
> > > Hackers,
> > >
> > > We have a strong need to make a variant to the crosstab interface so
> > > that data that is pivoted one way would be sent through a crosstab
> > > like function so that it would be pivoted another way. For example,
> > > if you had
> > >
> > > row 0: a1, a2, a3, k1, c1, c2, ...
> > > row 1: a1, a2, a3, k2, c1, c2, ...
> > > row 2: a1, a2, a3, k3, c1, c2, ...
> > > ...
> > >
> > > where 'a' columns are uninteresting attribute columns, 'k' is the
> > > dimension we want to pivot on, and c1->cN would be stacked vertically,
> > > so that we'd end up with,
> > > row 0: a1, a2, a3, c1, k1, k2, ...
> > > row 1: a1, a2, a3, c2, k1, k2, ...
> > > row 2: a1, a2, a3, c3, k1, k2, ...
> > >
> > > There are various SQL level approaches to this but they tend to be
> > > imperformant with large datasets so that I think a crosstab-like C
> > > implementation ought to be able to do better (or at least I hope so)
> > > since you have to cross product rows and columns in such a way that
> > > you can get a clean join. Cribbing from tablefunc.c I don't think
> > > this is a terrible challenge to do in hash table style.
> > >
> > > Questions on the table:
> > > *) Has anyone done anything like this or know of any current implementations?
> > > *) Would there be any interest in expanding tablefunc along these lines?
> >
> > There's something in SQL:2016 that I read as crosstabs, or at least as
> > enabling crosstabs.
> > https://www.iso.org/standard/69776.html
> >
> > If we're going to put work into crosstabs, it seems to me that the
> > "we" needs to be the project as a whole, and the work should be, to
> > the extent reasonable, toward standard compliance.
>
> Interesting. Do you see that the spec (it makes my brain hurt) can
> handle that kind of repivoting?
I believe the constructs can nest and/or refer to each other, so yes.
Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
pgsql-hackers by date: