Pivotering

Analysera affärsdata i SQL

Michel Semaan

Data Scientist

Vad är pivotering?

Pivotering: Rotera en tabell kring en pivotkolumn; transponera en kolumns värden till kolumner
- Omvandlar en "lång" tabell till en "bred"

Fördelar

Styr tabellens form utan att ändra datan
Ostaplad data visad horisontellt är ofta lättare att läsa än staplad data visad vertikalt

Före

meal_id  delivr_month  count_orders
-------  ------------  ------------
0        2018-06-01    39          
0        2018-07-01    47          
1        2018-06-01    25          
1        2018-07-01    55

Efter

meal_id  2018-06-01  2018-07-01
-------  ----------  ----------
0        39          47        
1        25          55

Pivoterad på delivr_month

Tabell före

SELECT
  meal_id,
  DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
  COUNT(DISTINCT orders) :: INT AS revenue
FROM meals
JOIN orders ON meals.meal_id = orders.meal_id
WHERE meals.meal_id IN (0, 1)
  AND order_date < '2018-08-01'
GROUP BY meal_id, delivr_month
ORDER BY meal_id, delivr_month;

CROSSTAB()

CREATE EXTENSION IF NOT EXISTS tablefunc;

CREATE EXTENSION fungerar som import i Python

SELECT * FROM CROSSTAB($$
  TEXT source_sql
$$)

AS ct (column_1 DATA_TYPE_1,
       column_2 DATA_TYPE_2,
       ...,
       column_n DATA_TYPE_N)
;

Använda CROSSTAB()

CREATE EXTENSION IF NOT EXISTS tablefunc;


SELECT * FROM CROSSTAB($$
  SELECT
    meal_id,
    DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
    COUNT(DISTINCT order_id) :: INT AS orders
  FROM orders
  WHERE meal_id IN (0, 1)
    AND order_date < '2018-08-01'
  GROUP BY meal_id, delivr_month
  ORDER BY meal_id, delivr_month $$)

  AS ct (meal_id INT,
         "2018-06-01" INT,
         "2018-07-01" INT)
ORDER BY meal_id ASC;

Tabell före

meal_id  delivr_month  count_orders
-------  ------------  ------------
0        2018-06-01    39          
0        2018-07-01    47          
1        2018-06-01    25          
1        2018-07-01    55

Tabell efter

meal_id  2018-06-01  2018-07-01
-------  ----------  ----------
0        39          47        
1        25          55

Pivoterad på delivr_month

Pivotering

Analysera affärsdata i SQL