Hacker News Reader

Hacker News

Comment by throwaway_pdp09 | Hacker News Reader

Parent Full thread

throwaway_pdp09

> Similarily the chaining of grouping sets

Do you mean 'group by grouping sets'? If not, could you clarify.

View on HN

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).

Nice 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).

Yeah, 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.

I 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 ...

Reply on news.ycombinator.com