absolutelearners.com

CUBE — Multi-Dimensional Cross-Tabulation

CUBE — Multi-Dimensional Cross-Tabulation

CUBE generates every possible combination of the listed dimensions, including all subtotals and the grand total. If ROLLUP is a hierarchy (removes from the right), CUBE is a hypercube (removes every combination).

For CUBE(a, b, c), the generated groupings are all 2ⁿ subsets: (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c), ()

That's 8 groupings for 3 dimensions. With 4 dimensions you get 16. CUBE is best used when your dimensions are genuinely independent and analysts want to slice the data along any axis — not just a hierarchy.

When to use CUBE vs ROLLUP

  • Use ROLLUP for hierarchies (year → quarter → month, country → city)
  • Use CUBE for independent dimensions where any cross-section is meaningful (product × region × channel × time period)

The price of CUBE is the combinatorial explosion: 3 dimensions = 8 groups, 4 = 16, 5 = 32. For 5+ dimensions the result set becomes extremely large and is almost never fully useful. In practice, CUBE is most useful for 2–3 dimensions.

Example 1: 3-dimensional revenue cube

This query builds a full revenue cube across region, category, and year. Any slice of this cube — "all revenue for Electronics across all years across all regions", "North region revenue in 2024 across all categories", etc. — appears as a row in the result.

SELECT
    CASE WHEN GROUPING(c.country)  = 1 THEN 'ALL' ELSE c.country  END AS country,
    CASE WHEN GROUPING(p.category) = 1 THEN 'ALL' ELSE p.category END AS category,
    CASE WHEN GROUPING(EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE)) = 1 THEN 0
         ELSE EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE)::INT END AS year,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue,
    COUNT(DISTINCT o.order_id) AS orders,
    GROUPING(
        c.country,
        p.category,
        EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE)
    ) AS combo_code
FROM orders o
JOIN order_items oi ON oi.order_id  = o.order_id
JOIN products p    ON p.product_id  = oi.product_id
JOIN ec_customers c   ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY CUBE(
    c.country,
    p.category,
    EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE)
)
ORDER BY combo_code, country, category, year;

What This Returns

With 3 dimensions (country, category, year), you get 8 combo_code values:

  • 0 = all three dimensions — fully granular rows
  • 1 = country × category (year collapsed) — per country/category total
  • 2 = country × year (category collapsed) — per country/year total
  • 3 = country only
  • 4 = category × year
  • 5 = category only
  • 6 = year only
  • 7 = grand total (all dimensions collapsed)

To build a specific slice, filter WHERE combo_code = 5 to get all category totals.

Example 2: Filtering a specific cube cross-section

Rather than returning all 8 combinations, you can use HAVING or a wrapping WHERE to extract only the intersections you care about:

WITH revenue_cube AS (
    SELECT
        c.country,
        p.category,
        ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue,
        GROUPING(c.country)  AS g_country,
        GROUPING(p.category) AS g_category
    FROM orders o
    JOIN order_items oi ON oi.order_id  = o.order_id
    JOIN products p    ON p.product_id  = oi.product_id
    JOIN ec_customers c   ON c.customer_id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY CUBE(c.country, p.category)
)
SELECT
    COALESCE(country, 'ALL COUNTRIES') AS country,
    COALESCE(category, 'ALL CATEGORIES') AS category,
    revenue,
    -- Share of country total
    ROUND(100.0 * revenue / NULLIF(SUM(CASE WHEN g_country = 0 AND g_category = 1
        THEN revenue END) OVER (PARTITION BY country), 0), 1) AS pct_of_country,
    -- Share of category total
    ROUND(100.0 * revenue / NULLIF(SUM(CASE WHEN g_country = 1 AND g_category = 0
        THEN revenue END) OVER (PARTITION BY category), 0), 1) AS pct_of_category
FROM revenue_cube
WHERE g_country = 0 AND g_category = 0   -- only the fully detailed rows
ORDER BY country, category;

This extracts only the (country, category) intersection rows (the finest grain), but enriches each row with its percentage of the country total and percentage of the category total. The CUBE computation provides both marginal totals in a single scan; the window functions reference them in the outer query.

Example 3: Partial CUBE

Like ROLLUP, you can apply CUBE to only a subset of your dimensions while keeping others fixed:

-- Year is fixed; CUBE applies to country and category only
SELECT
    EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE)::INT AS year,
    COALESCE(c.country, 'ALL') AS country,
    COALESCE(p.category, 'ALL') AS category,
    ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id  = o.order_id
JOIN products p    ON p.product_id  = oi.product_id
JOIN ec_customers c   ON c.customer_id = o.customer_id
WHERE o.status = 'completed'
GROUP BY
    EXTRACT(YEAR FROM DATE_TRUNC('day', o.created_at)::DATE),  -- fixed, not cubed
    CUBE(c.country, p.category)       -- cubed: all 4 combos of these two
ORDER BY year, GROUPING(c.country, p.category), country, category;

This gives 4 groupings per year (instead of 8), because the year dimension is always fixed. Useful when you always want to see year in the output but want all combinations of the other two dimensions.

Example 4: Building a pivot table from a CUBE

One of the most useful applications of CUBE is building dynamic pivot tables. After the CUBE computation, use FILTER or CASE WHEN to reshape rows into columns:

WITH cube_data AS (
    SELECT
        p.category,
        c.acquisition_channel AS channel,
        ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue,
        GROUPING(p.category)           AS g_cat,
        GROUPING(c.acquisition_channel) AS g_chan
    FROM orders o
    JOIN order_items oi ON oi.order_id  = o.order_id
    JOIN products p    ON p.product_id  = oi.product_id
    JOIN ec_customers c   ON c.customer_id = o.customer_id
    WHERE o.status = 'completed'
    GROUP BY CUBE(p.category, c.acquisition_channel)
),
-- Pivot: one column per channel
pivot AS (
    SELECT
        COALESCE(category, 'TOTAL') AS category,
        SUM(revenue) FILTER (WHERE channel = 'organic')     AS organic,
        SUM(revenue) FILTER (WHERE channel = 'paid_search') AS paid_search,
        SUM(revenue) FILTER (WHERE channel = 'social')      AS social,
        SUM(revenue) FILTER (WHERE channel = 'email')       AS email,
        SUM(revenue) FILTER (WHERE channel = 'referral')    AS referral,
        SUM(CASE WHEN g_chan = 1 THEN revenue END)           AS all_channels
    FROM cube_data
    WHERE g_cat IN (0, 1)
    GROUP BY category, g_cat
)
SELECT * FROM pivot
ORDER BY category = 'TOTAL', category;

Key Takeaway

CUBE is the "give me everything" aggregation — perfect for OLAP-style analysis where analysts need to freely drill up and down across multiple independent dimensions. Limit it to 2–3 dimensions to avoid result set explosion, and combine with GROUPING() to correctly identify each level. The FILTER clause (covered in the next lesson) is CUBE's natural companion for pivoting results.

← Previous Next →