<jupyter_start><jupyter_text>Analytics: Cubes and Rollupsor Buying booties for beasts  Setup<jupyter_code>!pip install ipython_sql
!pip install psycopg2
!createdb animals
!psql -f setup.sql animals
%load_ext sql
%sql postgresql://:@/animals<jupyter_output><empty_output><jupyter_text>Data<jupyter_code>%%sql
SELECT a.*,
       s.name AS species
FROM   animal a
JOIN   species s ON (a.species_id = s.id)
%%sql
SELECT SUM(a.limbs), 
       c.name AS class
FROM   animal a
JOIN   species s ON (a.species_id = s.id)
JOIN   bio_class c ON (s.class_id = c.id)
GROUP BY c.name

%%sql
SELECT SUM(a.limbs), 
       a.color,
       c.name AS class
FROM   animal a
JOIN   species s ON (a.species_id = s.id)
JOIN   bio_class c ON (s.class_id = c.id)
GROUP BY (a.color, c.name)
%%sql
SELECT SUM(a.limbs), 
       a.tame,
       c.name AS class
FROM   animal a
JOIN   species s ON (a.species_id = s.id)
JOIN   bio_class c ON (s.class_id = c.id)
GROUP BY (a.tame, c.name)<jupyter_output>4 rows affected.<jupyter_text>So far so good.  But what if - the table is huge- we need to precalculate values- user may need variety of groupings  - species  - class  - color  - tame  - species + color  - color + tame  - class + color + tame  - ...  Cubes<jupyter_code>%%sql
SELECT SUM(a.limbs), 
       a.color,`
       a.tame,
       s.name AS species,
       c.name AS class
FROM   animal a
JOIN   species s ON (a.species_id = s.id)
JOIN   bio_class c ON (s.class_id = c.id)
GROUP BY CUBE (color, tame, species, class)
ORDER BY (a.color, a.tame, s.name, c.name) DESC NULLS FIRST;
%%sql
SELECT * FROM (
    SELECT SUM(a.limbs), 
           a.color,
           a.tame,
           s.name AS species,
           c.name AS class
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY CUBE (color, tame, species, class)
    ORDER BY (a.color, a.tame, s.name, c.name) DESC NULLS FIRST
    ) subcube 
WHERE color = 'gray' AND tame = true AND species = 'horse' AND class = 'mammal';<jupyter_output>1 rows affected.<jupyter_text>All my tame horse mammals, any color (the wrong way)<jupyter_code>%%sql
SELECT * FROM (
    SELECT SUM(a.limbs), 
           a.color,
           a.tame,
           s.name AS species,
           c.name AS class
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY CUBE (color, tame, species, class)
    ORDER BY (a.color, a.tame, s.name, c.name) DESC NULLS FIRST
    ) subcube 
WHERE color IS NULL AND tame = true AND species = 'horse' AND class = 'mammal';<jupyter_output>1 rows affected.<jupyter_text>Why is that the wrong way?Well, let's try buying booties for cuttlefish.<jupyter_code>%%sql
SELECT * FROM (
    SELECT SUM(a.limbs), 
           a.color,
           a.tame,
           s.name AS species,
           c.name AS class
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY CUBE (color, tame, species, class)
    ORDER BY (a.color, a.tame, s.name, c.name) DESC NULLS FIRST
    ) subcube 
WHERE color IS NULL AND tame = false AND species = 'cuttlefish' AND class = 'mollusk';<jupyter_output>2 rows affected.<jupyter_text>One row is `color=NULL` because it represents all cuttlefish colors.One row is `color=NULL` because it represents cuttlefish whose color is  `NULL`. The proper way to distingush these rows is with `GROUPING`.`GROUPING` forms a bitmap; for each position,- `0` if the row represents a specific column value- `1` if the row represents all values of that column<jupyter_code>%%sql
SELECT sc.*, sc.grouping_bitmap::bit(4) FROM (
    SELECT SUM(a.limbs), 
           a.color,
           a.tame,
           s.name AS species,
           c.name AS class,
           GROUPING(a.color, a.tame, s.name, c.name) AS grouping_bitmap
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY CUBE (color, tame, species, class)
    ORDER BY (a.color, a.tame, s.name, c.name) DESC NULLS FIRST
    ) sc 
%%sql
SELECT sc.*, sc.grouping_bitmap::bit(4) FROM (
    SELECT SUM(a.limbs), 
           a.color,
           a.tame,
           s.name AS species,
           c.name AS class,
           GROUPING(a.color, a.tame, s.name, c.name) AS grouping_bitmap
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY CUBE (color, tame, species, class)
    ORDER BY (a.color, a.tame, s.name, c.name) DESC NULLS FIRST
    ) sc 
WHERE grouping_bitmap & 8 = 8
AND   tame = false AND species = 'cuttlefish' AND class = 'mollusk';<jupyter_output>1 rows affected.<jupyter_text>Species and class have a hierarchical relationship.  (No non-mollusk cuttlefish)<jupyter_code>%%sql
SELECT sc.*, sc.grouping_bitmap::bit(2) FROM (
    SELECT SUM(a.limbs), 
           s.name AS species,
           c.name AS class,
           GROUPING(s.name, c.name) AS grouping_bitmap
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY CUBE (species, class)
    ORDER BY (s.name, c.name) DESC NULLS FIRST
    ) sc ;
%%sql
SELECT sc.*, sc.grouping_bitmap::bit(2) FROM (
    SELECT SUM(a.limbs), 
           s.name AS species,
           c.name AS class,
           GROUPING(c.name, s.name) AS grouping_bitmap
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY ROLLUP (class, species)
    ORDER BY (c.name, s.name) DESC NULLS FIRST
    ) sc ;<jupyter_output>9 rows affected.<jupyter_text>Multiple forms of grouping can be combined<jupyter_code>%%sql
SELECT sc.*, sc.grouping_bitmap::bit(4) FROM (
    SELECT SUM(a.limbs), 
           a.color,
           a.tame,
           s.name AS species,
           c.name AS class,
           GROUPING(a.color, a.tame, c.name, s.name) AS grouping_bitmap
    FROM   animal a
    JOIN   species s ON (a.species_id = s.id)
    JOIN   bio_class c ON (s.class_id = c.id)
    GROUP BY CUBE (color, tame), ROLLUP(class, species)
    ORDER BY (a.color, a.tame, c.name, s.name) DESC NULLS FIRST
    ) sc ;<jupyter_output>63 rows affected.
StarcoderdataJupyterStructuredCleanDedup