On Fri, Jan 25, 2019 at 04:31:00PM -0600, Merlin Moncure wrote: > On Fri, Jan 25, 2019 at 3:16 PM David Fetter <da...@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
