Pivoting

การวิเคราะห์ข้อมูลธุรกิจด้วย SQL

Michel Semaan

Data Scientist

Pivoting คืออะไร?

Pivoting: การหมุนตารางรอบคอลัมน์ pivot; แปลงค่าของคอลัมน์ให้กลายเป็นคอลัมน์ใหม่
- แปลงตาราง "แบบยาว" ให้เป็นตาราง "แบบกว้าง"

ประโยชน์

ควบคุมรูปร่างของตารางโดยไม่สูญเสียข้อมูล
ข้อมูลที่ unstacked แบบแนวนอนมักอ่านง่ายกว่าข้อมูลแบบ stacked แนวตั้ง

ก่อน

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

หลัง

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

Pivot โดยใช้ delivr_month

ตารางก่อน pivot

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 ทำหน้าที่คล้ายกับ import ใน Python

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

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

การใช้ 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;

ตารางก่อน pivot

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

ตารางหลัง pivot

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

Pivot โดยใช้ delivr_month

Pivoting

การวิเคราะห์ข้อมูลธุรกิจด้วย SQL