Tutorial: Learn complex grouping with SQL #
Welcome to the Tutorial: Learn complex grouping with SQL tutorial, where you explore useful ways to craft your SQL statements using the TPC-H dataset. Using Complex grouping operations, you perform multi-column analysis within each SQL statement.
Requirements #
To complete the tutorial using Starburst Galaxy, you need to create a free trial or activate your user account.
The sample
cluster and tpch
catalog are pre-configured and ready for
querying.
- If you do not see the
tpch
catalog automatically available, create a TPC-H dataset catalog namedtpch
. - If you do not see the
sample
cluster automatically available, create a cluster namedsample
and add thetpch
catalog.
Navigate the query editor #
In the query editor, navigate to the Cluster explorer.
- Select the
sample
cluster dropdown in the left hand navigation to view catalogs. -
Select the
tpch
catalog dropdown.
Select the cluster sample
and the catalog tpch
in the top right in order to
run the queries without specifying the cluster and the catalog before each
table.
The namespace for a table is typically specified as
<catalog_name>.<schema_name>.<table_name>
. For the pre-defined TPC-H dataset,
this configuration is as follows:
tpch.tiny.<table_name>
If you choose to name your catalog differently, either adjust the queries accordingly or select the appropriately catalog in the top right corner.
If you choose to name your cluster differently, select the appropriately named cluster in the top right corner.
Complex grouping operations #
Starburst Galaxy supports complex grouping
operations
using the ROLLUP
, CUBE
, and GROUPING SETS
syntax. This syntax allows users
to perform analysis that requires aggregation on multiple sets of columns in a
single query.
ROLLUP #
The ROLLUP operator generates all possible subtotals for a given set of columns.
Query the customers table and view the data available for analysis.
SELECT
*
FROM
tpch.tiny.customer;
Group the customers by market segment to get the total count in each segment.
SELECT
mktsegment,
count() AS customers
FROM
tpch.tiny.customer
GROUP BY
mktsegment
ORDER BY
mktsegment;
Instead of using the GROUP BY
clause to organize the market segments, use the
ROLLUP
operator. Also, reduce the market segments to focus only on three
distinct categories: AUTOMOBILE, BUILDING, and MACHINERY.
SELECT
mktsegment,
count() AS customers
FROM
tpch.tiny.customer
WHERE
mktsegment IN ('AUTOMOBILE', 'BUILDING', 'MACHINERY')
GROUP BY
ROLLUP(mktsegment)
ORDER BY
mktsegment;
Add another column to the ROLLUP
operator and include the nationkey
grouping
to generate multiple grouping sets.
SELECT
mktsegment,
nationkey,
count() AS orders
FROM
tpch.tiny.customer
WHERE
mktsegment IN ('AUTOMOBILE', 'BUILDING', 'MACHINERY')
AND nationkey BETWEEN 1 AND 2
GROUP BY
ROLLUP(mktsegment, nationkey)
ORDER BY
mktsegment,
nationkey;
CUBE #
The CUBE operator generates all the possible grouping sets for a given set of columns.
Switch to the CUBE
operator to obtain the order count breakdown of each nation
key for the market segments not explicitly specified.
SELECT
mktsegment,
nationkey,
count() AS orders
FROM
tpch.tiny.customer
WHERE
mktsegment IN ('AUTOMOBILE', 'BUILDING', 'MACHINERY')
AND nationkey BETWEEN 1 AND 2
GROUP BY
CUBE(mktsegment, nationkey)
ORDER BY
mktsegment,
nationkey;
GROUPING SETS #
The result of the CUBE
query can also be achieved using GROUPING SETS
.
Grouping sets
allow users to specify multiple lists of columns to group on, so
the manually specifying all the combination of columns can equate to the same
output as the CUBE
operator.
SELECT
mktsegment,
nationkey,
count() AS orders
FROM
tpch.tiny.customer
WHERE
mktsegment IN ('AUTOMOBILE', 'BUILDING', 'MACHINERY')
AND nationkey BETWEEN 1 AND 2
GROUP BY
GROUPING SETS (
(mktsegment),
(nationkey),
(mktsegment, nationkey),
()
)
ORDER BY
mktsegment,
nationkey;
Next steps #
Check out our other tutorials, or dive right into the SQL documentation and experiment with your own data.
Is the information on this page helpful?
Yes
No
Is the information on this page helpful?
Yes
No