Using roll ups and grouping sets

If your work involves heavy OLAP queries, you need to know about grouping sets and roll ups. Unified Analytics supports grouping sets with rollups and grouping functions.

You created a Unified Analytics Virtual Warehouse based on a Database Catalog that loads the sample data to run queries of airline data.
  1. On the Overview page under Virtual Warehouses, click the options menu in the upper right corner, and select Open Hue.
  2. Select a database.
  3. In Hue, select either the Impala or Hive editor.
  4. Run a query that determines the number of flights per country and state.
    SELECT
      a.country,
      a.state,
      grouping(a.country)
      grouping(a.state)
      SUM(f.Distance),
      COUNT(*)
    FROM
      flights f,
      airports a
    WHERE
      f.origin = a.iata
    GROUP BY
      a.country,
      a.state
    WITH ROLLUP;
    Per the SQL standard, you see the grouping function 0 if you’re using the code for the group itself; otherwise, the function is 1. If you group only by country the grouping function is 1.
  5. Run a query that defines custom grouping sets: country and country/state.
    SELECT
      a.country,
      a.state,
      grouping(a.country)
      grouping(a.state)
      SUM(f.Distance),
      COUNT(*)
    FROM
      flights f,
      airports a
    WHERE
      f.origin = a.iata
    GROUP BY
      a.country,
      a.state
    GROUPING SETS (
      (a.country),
      (a.country, a.state)
    );