Having, a less understood SQL clause

Post by ttfkam » Sun, Aug 07, 2022, 3:18 PM UTC

ttfkam wrote: post #80

CodeIsTheEnd wrote: post #13

Earlier quoted context omitted.

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…

Not a pivot table equivalent. Most useful for calculating multiple related aggregates at once for reporting purposes, but ROLLUP doesn't substitute values for columns, ie. it doesn't pivot results on an axis. MS SQL Server has native pivot support, and Postgres has a limited emulation of it through the crosstab(...) function. https://stackoverflow.com/a/11751905 https://www.postgresql.org/docs/current/tablefunc.html…

Post by fuy » Mon, Aug 08, 2022, 9:58 AM UTC

noir_lord wrote: post #32

pedrow wrote: post #31

My SQL knowledge is very limited - I had heard of HAVING but not GROUP BY CUBE or COALESCE - but one thing stood out: "The rewritten sql ... ran in a few seconds compared to over half an hour for the original query." I know there were four million rows in the dataset, but is 30 minutes the kind of run-time you would expect for a query like this?

> I know there were four million rows in the dataset, but is 30 minutes the kind of run-time you would expect for a query like this?. Clearly not since they got it down to a few seconds ;). But tongue in cheek aside, it's incredibly dependent on what is in those 4M rows, how big they are, if they are indexed, whether you join in weird ways, whether the query planner does something unexpected. SQL tooling is by and la…