grouping sets

[Arkronus]

It would be helpful if prql could support ROLLUP, CUBE and GROUPING SETS statements.

[aljazerzen]

For anyone wondering what GROUPING SETS do, this:

SELECT SUM(a) FROM tbl GROUP BY GROUPING SETS ((b, c, d), (d), (b, d);

... would be equivalent to this PRQL:

        from tbl | group [b,    c,    d] (aggregate [sum a])
append (from tbl | group [null, null, d] (aggregate [sum a]))
append (from tbl | group [b,    null, d] (aggregate [sum a]))

[ukclivecox]

Is there anyway to work around this at present to allow ROLLUP to be included. I can't see how to accomplish this with the current s-strings?

[aljazerzen]

Yeah, s-string's will not cut it here - the ROLLUP is not an expression, we'd have to put the whole SELECT statement into the s-string, which will cause other problems.

If you want to do ROLLUP of (for example) genre_id, album_id and media_type_id, you can achieve same behavior with this:

from (
        from tracks | group {genre_id,    album_id, media_type_id} (aggregate {count this})
append (from tracks | group {genre_id,    album_id,          null} (aggregate {count this}))
append (from tracks | group {genre_id,        null,          null} (aggregate {count this}))
append (from tracks | group {null,            null,          null} (aggregate {count this}))
)
sort {media_type_id, album_id, genre_id}

Quite verbose and not very DRY, but it does the job.