Advanced Analytics—Technology and Tools: In-Database Analytics - Data Science and Big Data Analytics - page 466

clause to supply the number of items ordered by year for the top three items
ordered overall. The ROLLUP operator provides the subtotals, which match the
previous output for each product_id , as well as the grand total.
SELECT r.product_id,
DATE_PART('year', r.order_datetime) AS year,
SUM(r.item_quantity) AS total
FROM orders_recent r
WHERE r.product_id IN (SELECT o.product_id
FROM orders_recent o
GROUP BY o.product_id
ORDER BY SUM(o.item_quantity) DESC
LIMIT 3)
GROUP BY ROLLUP( r.product_id, DATE_PART('year',
r.order_datetime) )
ORDER BY r.product_id,
DATE_PART('year', r.order_datetime)
product_id year total
15060 2013 5996
15060 2014 57
15060 6053
15066 2013 6030
15066 2014 52
15066 6082
15072 2013 6023
15072 2014 66
15072 6089
18224
The CUBE operator expands on the functionality of the ROLLUP operator by
providing subtotals for each column specified in the CUBE statement. Modifying
the prior query by replacing the ROLLUP operator with the CUBE operator results
in the same output with the addition of the subtotals for each year.
SELECT r.product_id,
DATE_PART('year', r.order_datetime) AS year,
SUM(r.item_quantity) AS total
FROM orders_recent r