3 ms·
Not only the grouping sets alone, the chaining of them. E.g. GROUP BY tenant , GROUPING SETS ( (year, month) , (year)
by MarkusWinand 6y ago
Not only the grouping sets alone, the chaining of them.
E.g.
GROUP BY tenant
, GROUPING SETS ( (year, month)
, (year)
)
is equivalant to
GROUP BY GROUPING SETS ( (tenant, year, month)
, (tenant, year)
)
and
GROUP BY tenant, year
, GROUPING SETS ( (month)
, ()
)
(however, I find the last one odd: I like to keep the "tenant" part seperate from the actual grupings you'd like to do).
- throwaway_pdp09 6y agoNice answer, thanks. Have you ever used this syntax? Has anybody here. I ask because I'm a heavy SQL user when I'm working, and I've never ever found the need (almost used a cube once but not quite) (I guess it's maybe for analysis rather than transactional stuff).
- thom 6y agoYeah, we use CUBE to create caches of aggregate data cut by various combinations of criteria, which allows us to keep UIs with lots of filters snappy, instead of having to hit a view.
- jacobr1 6y agoI abused it in the past for aggregating every bitmask of CIDR, where the underlying data was by IP. So the groups were functions of the IP, one per mask level. We needed aggregations of arbitrary levels and this worked relatively well. We also tried it using it for some reporting that was tied to organizational hierarchy - same intuition - the group sets included org, parent, grandparent, etc ...