Re: crosstab/repivot...any interest?

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(at)fetter(dot)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(at)fetter(dot)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
>
>