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

illustrate the function GROUP_ID() , both ROLLUP and CUBE are used when only
one specific product_id is being examined.
SELECT r.product_id,
DATE_PART('year', r.order_datetime) AS year,
SUM(r.item_quantity) AS total,
GROUP_ID() AS group_id
FROM orders_recent r
WHERE r.product_id IN ( 15060 )
GROUP BY ROLLUP( r.product_id, DATE_PART('year',
r.order_datetime) ),
CUBE( r.product_id, DATE_PART('year', r.order_datetime) )
ORDER BY r.product_id,
DATE_PART('year', r.order_datetime),
GROUP_ID()
product_id year total group_id
15060 2013 5996 0
15060 2013 5996 1
15060 2013 5996 3
15060 2013 5996 4
15060 2013 5996 5
15060 2013 5996 6
15060 2014 57 0
15060 2014 57 1
15060 2014 57 2
15060 2014 57 3
15060 2014 57 4
15060 2014 57 5
15060 2014 57 6
15060 6053 0
15060 6053 1
15060 6053 2
2013 5996 0
2014 57 0
6053 0
Filtering on the group_id values equal to zero yields unique records. This filtering
can be accomplished with the HAVING clause, as illustrated in the next SQL query.