Tuesday 5 February 2019

postgres tutorial 3 grouping

SELECT
 GROUPING(brand) grouping_brand,
 GROUPING(segment) grouping_segement,
 brand,
 segment,
 SUM (quantity)
FROM
 sales
GROUP BY
 GROUPING SETS (
 (brand, segment),
 (brand),
 (segment),
 ()
 )
ORDER BY
 brand,
 segment;


CUBE (c1,c2,c3) makes all eight possible grouping sets:

(c1, c2, c3)
(c1, c2)
(c2, c3)
(c1,c3)
(c1)
(c2)
(c3)
()

However, the ROLLUP(c1,c2,c3) generates only four grouping sets, assuming the hierarchy c1 > c2 > c3 as follows:

(c1, c2, c3)
(c1, c2)
(c1)
()

SELECT
    EXTRACT (YEAR FROM rental_date) y,
    EXTRACT (MONTH FROM rental_date) M,
    EXTRACT (DAY FROM rental_date) d,
    COUNT (rental_id)
FROM
    rental
GROUP BY
    ROLLUP (
        EXTRACT (YEAR FROM rental_date),
        EXTRACT (MONTH FROM rental_date),
        EXTRACT (DAY FROM rental_date)
    );


No comments:

Post a Comment