4 ms·

Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this

by galkk 4y ago

Having is less understood? After 20+ years of SQL usage (as an ordinary dev, not business/reporting/heavy SQL dev), I learned about `group by cube` from this article... "group by cube/coalesce" is much more complicated thing in this article than "having" (that could be explained as where but for group by)

ramraj07 4y ago
The only “standard” feature id rather try not to understand is recursive CTEs lol
- eyelidlessness 4y ago
  they’re just self joins on views of the query.
- conceptme 4y ago
  it's pretty useful when working with hierarchical data, but you do not to put some check for cyclical relations, I have seen those take an application down :D.
  - dspillett 4y ago
    If you aren't careful you can cause that without infinite recursion. If the query optimiser an't see to push relevant predicates down to the root level where needed for best performance, or they are not sargable anyway, or the query optimiser simply can't do that (until v14 CTEs were an optimisation barrier in posrges), then you end up scanning whole tables (or at least whole indexes) multiple times, where a few seeks might be all that is really needed. In fact, you don't even need recursion for this to have a bad effect. CTEs are a great feature for readability, but when using them be careful to test your work on data at least as large as you expect to see in production over the life of the statements you are working on, in all DBMSs you support if your project is multi-platform.
CodeIsTheEnd 4y ago
I had never heard of GROUP BY CUBE either! It looks like it's part of a family of special GROUP BY operators—GROUPING SETS, CUBE, and ROLLUP—that basically issue the same query multiple times with different GROUP BY expressions and UNION the results together. Using GROUP BY CUBE(a, b, c, ...) creates GROUP BY expressions for every element in the power set of {a, b, c, ...}, so GROUP BY CUBE(a, b) does separate GROUP BYs for (a, b), (a), (b) and (). It's like SQL's version of a pivot table, returning aggregations of data filtered along multiple dimensions, and then also the aggregations of those aggregations. It seems like it's well supported by Postgres [1], SQL Server [2] and Oracle [3], but MySQL only has partial support for ROLLUP with a different syntax [4]. [1]: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS https://www.postgresql.org/docs/current/queries-table-expres... [2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16 https://docs.microsoft.com/en-us/sql/t-sql/queries/select-gr... [3]: https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets https://oracle-base.com/articles/misc/rollup-cube-grouping-f... [4]: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.h...
- zweifuss 4y ago
  Yes, PostgreSQL (9 and later), SQL Server (2008 and later), Oracle, SQL Anywhere and DB2 support grouping sets. All had it for longer than a decade. Some also support the MySQL/MariaDB with rollup syntax introduced in 2018.
- nerdponx 4y ago
  I would gladly buy a book of "SQL Recipes" ranging from beginner-level to advanced stuff that uses features like this, ideally with coverage of at least a few popular database systems, but at minimum Postgres. Is there such a book?
  - hobs 4y ago
    In fact, Yugabyte is giving it away for free - https://downloads.yugabyte.com/marketing-assets/O-Reilly-SQL-Cookbook-2nd-Edition-Final.pdf https://downloads.yugabyte.com/marketing-assets/O-Reilly-SQL...
RexM 4y ago
Same! I’ve been a developer for 15 years, consider myself pretty good with SQL, but I’m only now learning about group by cube.
kmac_ 4y ago
SQL opens up when used with OLAP schemas. Most devs are experienced in querying "object mapped" schemas where cube, roll up, etc. are not useful. Nothing bad per se, but it can give an impression that SQL is a bad language, when actually it clicks well with a proper data schema.
- rjbwork 4y ago
  Indeed. I think your mind can really be opened by having to answer complex business questions with an expansive and well designed data warehouse schema. It's a shame it's such a relatively niche and unknown topic, especially in the startup world.
  - nerdponx 4y ago
    This is why the data engineers get paid the big bucks, and also why having a good data engineer is a lot more important than a good data scientist in the early stages of a company.
Simon_O_Rourke 4y ago
Heard of group by cube before, even tried it in production, but it was a total dog of a query to run, so I filed it away to try again in a few years.
cm2187 4y ago
Exactly the same for me. It makes me think I need to start reading the TSQL documentation cover to cover.
- hobs 4y ago
  Not a huge fan of Microsoft but the TSQL documentation is solid. If you're not using CROSS APPLY to tear apart things and put them back together you've not lived.
OJFord 4y ago
I've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly: `group by cube`/`group by coalesce` aren't special advanced features, they're just `group by`. You can group on 'anything', e.g. maybe you want to group on a name regardless of case or extraneous whitespace - you can use functions like `lower` and `strip` in the `group by` no problem, it's not something to learn separately for every function.
- remram 4y ago
  Your suggestion is incorrect. CUBE is not part of the expression.
  - OJFord 4y ago
    Well, I did say I wasn't familiar with it, but it's correct for `coalesce` and I don't think `cube` is different: https://www.postgresql.org/docs/current/cube.html https://www.postgresql.org/docs/current/cube.html In the context of `group by` it's treated as grouping sets, but that's not its only use. (Though that does seem to be special cased in terms of parsing, since afaict - I can't find the full query BNF on mobile - `grouping sets` is not optional.)
    - remram 4y ago
      https://www.postgresql.org/docs/14/sql-select.html https://www.postgresql.org/docs/14/sql-select.html GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] grouping_element can be one of: ( ) expression ( expression [, ...] ) ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) CUBE ( { expression | ( expression [, ...] ) } [, ...] ) GROUPING SETS ( grouping_element [, ...] ) You're right about COALESCE of course.
- zweifuss 4y ago
  Cube gets all possible combinations of grouping sets. It´s like showing all subtotals in a pivot table. That´s different than just grouping on the lowest level without totals.

by galkk 4y ago

ramraj07 4y ago

The only “standard” feature id rather try not to understand is recursive CTEs lol

eyelidlessness 4y ago
they’re just self joins on views of the query.
conceptme 4y ago
it's pretty useful when working with hierarchical data, but you do not to put some check for cyclical relations, I have seen those take an application down :D.
- dspillett 4y ago
  If you aren't careful you can cause that without infinite recursion. If the query optimiser an't see to push relevant predicates down to the root level where needed for best performance, or they are not sargable anyway, or the query optimiser simply can't do that (until v14 CTEs were an optimisation barrier in posrges), then you end up scanning whole tables (or at least whole indexes) multiple times, where a few seeks might be all that is really needed. In fact, you don't even need recursion for this to have a bad effect. CTEs are a great feature for readability, but when using them be careful to test your work on data at least as large as you expect to see in production over the life of the statements you are working on, in all DBMSs you support if your project is multi-platform.

CodeIsTheEnd 4y ago

I had never heard of GROUP BY CUBE either! It looks like it's part of a family of special GROUP BY operators—GROUPING SETS, CUBE, and ROLLUP—that basically issue the same query multiple times with different GROUP BY expressions and UNION the results together. Using GROUP BY CUBE(a, b, c, ...) creates GROUP BY expressions for every element in the power set of {a, b, c, ...}, so GROUP BY CUBE(a, b) does separate GROUP BYs for (a, b), (a), (b) and (). It's like SQL's version of a pivot table, returning aggregations of data filtered along multiple dimensions, and then also the aggregations of those aggregations. It seems like it's well supported by Postgres [1], SQL Server [2] and Oracle [3], but MySQL only has partial support for ROLLUP with a different syntax [4]. [1]: https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-GROUPING-SETS https://www.postgresql.org/docs/current/queries-table-expres... [2]: https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16 https://docs.microsoft.com/en-us/sql/t-sql/queries/select-gr... [3]: https://oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets https://oracle-base.com/articles/misc/rollup-cube-grouping-f... [4]: https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.html https://dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.h...

zweifuss 4y ago
Yes, PostgreSQL (9 and later), SQL Server (2008 and later), Oracle, SQL Anywhere and DB2 support grouping sets. All had it for longer than a decade. Some also support the MySQL/MariaDB with rollup syntax introduced in 2018.
nerdponx 4y ago
I would gladly buy a book of "SQL Recipes" ranging from beginner-level to advanced stuff that uses features like this, ideally with coverage of at least a few popular database systems, but at minimum Postgres. Is there such a book?
- hobs 4y ago
  In fact, Yugabyte is giving it away for free - https://downloads.yugabyte.com/marketing-assets/O-Reilly-SQL-Cookbook-2nd-Edition-Final.pdf https://downloads.yugabyte.com/marketing-assets/O-Reilly-SQL...

RexM 4y ago

Same! I’ve been a developer for 15 years, consider myself pretty good with SQL, but I’m only now learning about group by cube.

kmac_ 4y ago

SQL opens up when used with OLAP schemas. Most devs are experienced in querying "object mapped" schemas where cube, roll up, etc. are not useful. Nothing bad per se, but it can give an impression that SQL is a bad language, when actually it clicks well with a proper data schema.

rjbwork 4y ago
Indeed. I think your mind can really be opened by having to answer complex business questions with an expansive and well designed data warehouse schema. It's a shame it's such a relatively niche and unknown topic, especially in the startup world.
- nerdponx 4y ago
  This is why the data engineers get paid the big bucks, and also why having a good data engineer is a lot more important than a good data scientist in the early stages of a company.

Simon_O_Rourke 4y ago

Heard of group by cube before, even tried it in production, but it was a total dog of a query to run, so I filed it away to try again in a few years.

cm2187 4y ago

Exactly the same for me. It makes me think I need to start reading the TSQL documentation cover to cover.

hobs 4y ago
Not a huge fan of Microsoft but the TSQL documentation is solid. If you're not using CROSS APPLY to tear apart things and put them back together you've not lived.

OJFord 4y ago

I've never used `cube` in any context, but if I may I'd suggest you're parsing this wrongly: `group by cube`/`group by coalesce` aren't special advanced features, they're just `group by`. You can group on 'anything', e.g. maybe you want to group on a name regardless of case or extraneous whitespace - you can use functions like `lower` and `strip` in the `group by` no problem, it's not something to learn separately for every function.

remram 4y ago
Your suggestion is incorrect. CUBE is not part of the expression.
- OJFord 4y ago
  Well, I did say I wasn't familiar with it, but it's correct for `coalesce` and I don't think `cube` is different: https://www.postgresql.org/docs/current/cube.html https://www.postgresql.org/docs/current/cube.html In the context of `group by` it's treated as grouping sets, but that's not its only use. (Though that does seem to be special cased in terms of parsing, since afaict - I can't find the full query BNF on mobile - `grouping sets` is not optional.)
  - remram 4y ago
    https://www.postgresql.org/docs/14/sql-select.html https://www.postgresql.org/docs/14/sql-select.html GROUP BY [ ALL | DISTINCT ] grouping_element [, ...] grouping_element can be one of: ( ) expression ( expression [, ...] ) ROLLUP ( { expression | ( expression [, ...] ) } [, ...] ) CUBE ( { expression | ( expression [, ...] ) } [, ...] ) GROUPING SETS ( grouping_element [, ...] ) You're right about COALESCE of course.
zweifuss 4y ago
Cube gets all possible combinations of grouping sets. It´s like showing all subtotals in a pivot table. That´s different than just grouping on the lowest level without totals.