Postgres crosstab

[Ben Hood]

Hey Lukas,

I was wondering if you've come across an example of using crosstabs in
Postgres with JOOQ?

Cheers,

Ben

[Lukas Eder]

Hi Ben,

I wasn't aware of this PostgreSQL. Looks mysterious and powerful! I have registered a feature request to support those functions:

https://github.com/jOOQ/jOOQ/issues/3190

I guess, jOOQ's PostgresDSL could be enhanced to have functions like:

Table<Record> crosstab(String);

Table<Record> crosstab(String, Object...);

Table<Record> crosstab(Select<?>);

... and then, also support for crosstabN and the other overloads.

What's your use-case? Do you know how these functions perform?

Cheers

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Ben Hood]

Hi Lukas,

It is mysterious, but it is not at all powerful :-(

It seems like just a hack in Postgres to emulate pivot tables.

My requirement is to transpose rows in query to columns, taking this
query result as an example

subscription description charge sum count
------------ -------------------- --------- -------- -----
13973 National 0 28686876 4
13973 Mobile National 322416 4836 68
13973 SMS 83600 0 44
13973 International 640 12 4

I'd like to flatten these four rows into one row, grouped by subscription, i.e.

subscription national_charge national_sum national_count
mobile_national_count ....
---------------- ----------------------- -------------------
--------------------- -------------------------------
13973 0 28686876 4
322416

I'm pretty sure I've done this with Oracle before, but I'm stuck with Postgres.

Maybe there's a better way that I can create some kind of dynamic pivot?

Cheers,

Ben

[Lukas Eder]

Hi Ben,

It's actually possible to emulate PIVOT using dynamic SQL.

jOOQ emulates Oracle's / SQL Server's PIVOT clause in Table.pivot(), but the implementation is rather experimental (and not officially supported) - so I wouldn't exclude bugs:

- http://www.jooq.org/javadoc/latest/org/jooq/Table.html#pivot(org.jooq.Field...)

- https://github.com/jOOQ/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/impl/Pivot.java

See the implementation above in org.jooq.impl.Pivot.DefaultPivotTable

Any help to improve this situation is greatly appreciated, of course :-)

Cheers

Lukas

[Ben Hood]

Hey Lukas,

Thanks for that pointer - do you have an example of how to use it?

On a related tangent, I was wondering (under the assumption that the
dataset is small enough) whether I could run an instance of XE instead
of Postgres....

Cheers,

Ben

[Ben Hood]

On Wed, Apr 16, 2014 at 11:19 AM, Ben Hood <0x6e...@gmail.com> wrote:
> Thanks for that pointer - do you have an example of how to use it?

I guess the other option would be be to define views for each column
in the pivot and and then left outer join on each view. But that's no
JOOQ :-(

[Lukas Eder]

Hi Ben,

2014-04-16 12:19 GMT+02:00 Ben Hood <0x6e...@gmail.com>:

Hey Lukas,

Thanks for that pointer - do you have an example of how to use it?

The manual is a bit scarce in that area:

http://www.jooq.org/doc/latest/manual/sql-building/table-expressions/pivot-tables/

We don't have an example using jOOQ for pivot. But usage shoul dbe straight-forward. Except that the FOR keyword was replaced by ON (because "for" is a reserved word in Java)

 

On a related tangent, I was wondering (under the assumption that the
dataset is small enough) whether I could run an instance of XE instead
of Postgres....

Yes, with Oracle XE, you could solve this directly using native PIVOT support. I suspect that this will be faster than actually emulating PIVOT.

[Lukas Eder]

I'm not sure if I understand this correctly? Do you think that such views would be reusable? 

[Ben Hood]

On Wed, Apr 16, 2014 at 11:43 AM, Lukas Eder <lukas...@gmail.com> wrote:
>> I guess the other option would be be to define views for each column
>> in the pivot and and then left outer join on each view. But that's no
>> JOOQ :-(
>
>
> I'm not sure if I understand this correctly? Do you think that such views
> would be reusable?

What I meant was that if there are n attributes to pivot on, then you
define a separate view per attribute using the attribute name in the
where clause for the view definition. Then you could define a query
that does a left outer join over the n views, grouping by the
attribute you want to group on. This would create all of the "pivot"
columns in the result set. But that's a lot of maintenance.

[Ben Hood]

On Wed, Apr 16, 2014 at 11:05 AM, Lukas Eder <lukas...@gmail.com> wrote:
> http://www.jooq.org/javadoc/latest/org/jooq/Table.html#pivot(org.jooq.Field...)

I'm probably being thick here, but I can't seem to find the pivot()
function in the 3.3.1 API. I just checked the master branch on Github,
i.e.

https://github.com/jOOQ/jOOQ/blob/c70202f90be3a236b387002546e7d7bd60575710/jOOQ/src/main/java/org/jooq/Table.java

And the source doesn't seem to contain this type definition. Am I
looking in the wrong place?

[Lukas Eder]

My bad, as we're currently supporting PIVOT only for Oracle 11g and 12c, we've stripped the method out of the API, as we didn't want to support the emulation for other databases.

Sorry about that.

[Ben Hood]

On Wed, Apr 16, 2014 at 12:33 PM, Lukas Eder <lukas...@gmail.com> wrote:
> My bad, as we're currently supporting PIVOT only for Oracle 11g and 12c,
> we've stripped the method out of the API, as we didn't want to support the
> emulation for other databases.
> Sorry about that.

No worries, I thought I was going mad :-)

So I've ended up solving this issue by generating MAX(CASE WHEN THEN)
columns and grouping by the pivot attribute. Very hacky, but it works.

[joe.t...@educatorimpact.com]

For what it's worth, the following utility function is what I use to make use of crosstab combined with JOOQ. 

public class PostgresWrapper
{    

    public static Result<Record> pivot(DSLContext context, Select<?> raw, Select<?> crossTabValues)
    {
        List<Field<?>> resultFields = new ArrayList<Field<?>>();

        //The result will contain all but the last two columns for the raw fields.
 
        Field<?> []rawFields = raw.fields();
        for (int i = 0; i < rawFields.length - 2; i++)
        {
            resultFields.add(rawFields[i]);
        }

        //And then one column for each cross tab value specified
        Result<?> crossTabHeadings = context.fetch(crossTabValues);
        for (Record r : crossTabHeadings)
        {
            resultFields.add
            (
                DSL.field
                (
                    r.getValue(0).toString(),
                    rawFields[rawFields.length - 2].getDataType(context.configuration())
                )
            );
        }

        //And postgres requires that the names of the resultant fields be specified
        // explicitly, using 'ct' <"Name of Field", type> pairs...
        StringBuffer ctList = new StringBuffer();
        for (int i = 0; i < resultFields.size(); i++)
        {
            ctList.append
            (
                "\"" + resultFields.get(i).getName() + "\" "
                     + resultFields.get(i).getDataType(context.configuration())
                                          .getTypeName(context.configuration())
            );

            if (i < resultFields.size() - 1)
            {
                ctList.append(", ");
            }
        }
       
        return
            context
                .fetch
                (
                    "select * from crosstab('"
                        + raw.getSQL(ParamType.INLINED).replace("'", "''") + "', '"
                        + crossTabValues.getSQL(ParamType.INLINED).replace("'", "''")
                        + "') as ct(" + ctList.toString() + " )"
                );
    }
}

The function takes a pair of select queries, corresponding to the two parameters of cross tab. So, for example, here's one from our system (abbreviated for clarity, so it probably doesn't compile now). The example below cross-tabulates participants in the delivery of one of our products. Each participant is in one or more cohorts (DELIVERY_PARTICIPANTS) and this query shows you a table of which participants are enrolled in which cohorts (defined in DELIVERY_COHORTS).

       

DSLContext context = ...
Select<?> raw =
    DSL.select
    (
        DSL.concat(DELIVERY_PARTICIPANTS.DELIVERY_ID, DELIVERY_PARTICIPANTS.EMAIL),
        DELIVERY_PARTICIPANTS.DELIVERY_ID,
        DELIVERY_PARTICIPANTS.EMAIL,
        DELIVERY_PARTICIPANTS.COHORT,
        DSL.val("Yes").as("Enrolled")
    )
    .from(DELIVERY_PARTICIPANTS)
    .where(DELIVERY_PARTICIPANTS.DELIVERY_ID.eq(deliveryID))
    .orderBy(1, 2, 3);

Select<?> crossTabValues =
    DSL.select(DELIVERY_COHORTS.COHORT) //these are is known to be distinct
       .from(DELIVERY_COHORTS)
       .orderBy(DELIVERY_COHORTS.DISPLAY_ORDER);

return PostgresWrapper.pivot(context, raw, crossTabValues);

which gives about the same complexity for usage as the actual Postgres "in raw sql" version, except that the PostgresWrapper.pivot() calculates the 'ct' part for you.

Any feedback on this approach would be appreciated. I couldn't work out a way to make it more type-safe, and I'm not sure that is possible.

Please feel free to use it if you want to.

Cheers,

Joe

 

[Lukas Eder]

Thank you very much for sharing this, Joe - very interesting approach indeed!

I'll comment on some parts, inline

Perhaps, you could use DSL.list(QueryPart...) here. It will construct a QueryPart that models a comma-separated list of other QueryParts. You can then use ordinary DSLContext.renderInlined() API to construct the SQL. Perhaps this makes things simpler...?

 

       
        return
            context
                .fetch
                (
                    "select * from crosstab('"
                        + raw.getSQL(ParamType.INLINED).replace("'", "''") + "', '"
                        + crossTabValues.getSQL(ParamType.INLINED).replace("'", "''")
                        + "') as ct(" + ctList.toString() + " )"
                );
    }
}

That looks fine. Alternatively, you could use jOOQ's plain SQL templating to let jOOQ do the string escaping. Something along the lines of

context.fetch(

    resultQuery(

        "select * from crosstab({0}, {1}) as ct({2})", 

        val(raw.getSQL(ParamType.INLINED)),

        val(crossTabValues.getSQL(ParamType.INLINED)),

        ctList // If you construct it using DSL.list()

    )

);

  

Any feedback on this approach would be appreciated. I couldn't work out a way to make it more type-safe, and I'm not sure that is possible.

I doubt that you can do much more for a PIVOT query. We're facing the same issue with jOOQ's built-in Oracle / SQL Server PIVOT support. It's just not easy to produce type safe Record[N] types for all the possible permutations of pivot columns. Besides, a PIVOT operation might quickly produce more than jOOQ's 22 columns.

In any case, thanks again for your display. We'll certainly take some inspiration when implementing https://github.com/jOOQ/jOOQ/issues/3190

Cheers,

Lukas

[j...@thurbon.com]

Just a quick note to say there's a bug in this code.

        //And then one column for each cross tab value specified
        Result<?> crossTabHeadings = context.fetch(crossTabValues);
        for (Record r : crossTabHeadings)
        {
            resultFields.add
            (
                DSL.field
                (
                    r.getValue(0).toString(),
                    rawFields[rawFields.length - 2].getDataType(context.configuration())
                )
            );
        }

Should be rawFields[rawFields.length - 1] - that is, the type of each of the pivoted fields should be the same as the type of the last field in the raw query.

[j...@thurbon.com]

Thanks for the feedback. Both of your suggestions look like good improvements to me. 

Chers,

Joe

[j...@thurbon.com]

O.K. So I'm having a couple of issues trying to flesh these suggestions out.

Two main things:

(1) I couldn't find DSL.list(QueryPart). There is DSL.listAgg, but that doesn't seem like the right thing. My other attempt at using the templating as per your suggestion ended up quoting the entire contents of the ct() function call as one string. So, I kind of gave up. Ff there's an obvious mistake I'm making, please let me know.

(2) The other thing I'd like to do is grab the resultQuery, and rather than just fetch from it, I'd like to join against it making new query. Is that even possible? What I think I need to do is somehow make a TableLike out of the resultQuery value, but I can't find a way forward there.

I have workarounds to both of these (the workaround for (1) is just to leave things as they are, and the workaround for (2) is to make very complicated raw and crosstabValues queries, which only works sometimes).

Any insight would be appreciated, but I don't want to take up too much of your time.

Cheers,

Joe

On Wednesday, August 26, 2015 at 6:39:30 PM UTC+10, Lukas Eder wrote:

[Lukas Eder]

2015-09-03 3:53 GMT+02:00 <j...@thurbon.com>:

O.K. So I'm having a couple of issues trying to flesh these suggestions out.

Two main things:

(1) I couldn't find DSL.list(QueryPart). There is DSL.listAgg, but that doesn't seem like the right thing.

It's here:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#list-org.jooq.QueryPart...-

It was added in jOOQ 3.6:

https://github.com/jOOQ/jOOQ/issues/4085

 

My other attempt at using the templating as per your suggestion ended up quoting the entire contents of the ct() function call as one string. So, I kind of gave up. Ff there's an obvious mistake I'm making, please let me know.

I'd have to see your attempts...

But it's also OK to just concatenate the relevant strings, if that feels easier. Just be sure to sanitize your inputs and avoid SQL injection vulnerabilities.

 

(2) The other thing I'd like to do is grab the resultQuery, and rather than just fetch from it, I'd like to join against it making new query. Is that even possible? What I think I need to do is somehow make a TableLike out of the resultQuery value, but I can't find a way forward there.

DSL.table(String) and other methods allow you to create a Table<Record> from any SQL string:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#table-java.lang.String-org.jooq.QueryPart...-

 

I have workarounds to both of these (the workaround for (1) is just to leave things as they are, and the workaround for (2) is to make very complicated raw and crosstabValues queries, which only works sometimes).

Workaround #3: Create a table-valued function (a function returning TABLE or SETOF types) and use that.

Any insight would be appreciated, but I don't want to take up too much of your time.

No worries! This could be useful to many other users, I'd like to know more about this. Perhaps, we'll even be able to emulate Table.pivot() via crosstab for PostgreSQL

Cheers,
Lukas 

[joe.t...@educatorimpact.com]

No worries! This could be useful to many other users, I'd like to know more about this. Perhaps, we'll even be able to emulate Table.pivot() via crosstab for PostgreSQL

Cheers,
Lukas 

Thanks Lukas,

I'll try to come up with a small example of what I'm trying to achieve in (2) above, and perhaps that will be a good way to discuss it further. I had a quick go, but I made the example too simple, and so it didn't make much sense. I'll see if I can get to it over the weekend. (We're releasing at the moment, so time's at a bit of a premium).

Cheers,

Joe

[Lukas Eder]

2015-09-11 6:14 GMT+02:00 <joe.t...@educatorimpact.com>:

I'll try to come up with a small example of what I'm trying to achieve in (2) above, and perhaps that will be a good way to discuss it further. I had a quick go, but I made the example too simple, and so it didn't make much sense. I'll see if I can get to it over the weekend. (We're releasing at the moment, so time's at a bit of a premium).

No worries. With these edge-cases, a "whatever works" situation can be good enough some times :)

[joe.t...@educatorimpact.com]

I've made a gist which I think can be accessed here

https://gist.github.com/joe-thurbon/f6bff4732afb8aed609c

It contains a really simple example of sql, and some java that can be compiled against appropriately generated jooq classes.

Can you let me know if I've given enough info and the gist is visible (I've not done it before).

There's a method called demonstrate(DSLContext) which I think shows what I'm trying to do. 

Cheers.

Joe 

[Mark Derricutt]

Did anything come of including PostgreSQL crosstab() support officially inside JOOQ at all?  I've just been working on some code and need just that functionality ( well, it works running 4-5 separate queries, but this is much more performant ) and was wondering if there was a JOOQy way of doing it yet...

[Lukas Eder]

Hi Mark,

From the official side, we haven't investigated much into this topic. There's still this open feature request (which was triggered by this discussion):

https://github.com/jOOQ/jOOQ/issues/3190

But given the fact that a plain SQL workaround is perfectly possible, we haven't implemented it yet. Let me know if you need any help with that workaround.

Cheers,

Lukas

--

You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.