Re: crosstab/repivot...any interest?

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.

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