本篇介紹如何使用Oracle 11g才有的PIVOT
查詢語法。
如果有經常使用Excel的人應該多少聽過或用過樞紐分析表(Pivot table),是非常好用的資料摘要分析工具。
Pivot table的特色就是能行列互轉(rows to columns),例如以下工作表為各餐點不同用餐時段銷售數量資料。
餐點名稱 | 用餐時段 | 銷售數量 |
---|---|---|
滷肉飯 | 早餐 | 30 |
陽春麵 | 早餐 | 15 |
乾拌麵 | 早餐 | 18 |
荷包蛋 | 早餐 | 40 |
滷肉飯 | 午餐 | 50 |
陽春麵 | 午餐 | 35 |
乾拌麵 | 午餐 | 30 |
荷包蛋 | 午餐 | 84 |
滷肉飯 | 晚餐 | 60 |
陽春麵 | 晚餐 | 40 |
乾拌麵 | 晚餐 | 24 |
荷包蛋 | 晚餐 | 88 |
使用樞紐分析表可以把橫列的項目轉換為欄,例如下面把用餐時段轉為欄。
餐點名稱 | 早餐 | 午餐 | 晚餐 |
---|---|---|---|
滷肉飯 | 30 | 50 | 60 |
陽春麵 | 15 | 30 | 40 |
乾拌麵 | 18 | 30 | 24 |
荷包蛋 | 40 | 84 | 88 |
下面則是餐點名稱轉為欄。
用餐時段 | 滷肉飯 | 陽春麵 | 乾拌麵 | 荷包蛋 |
---|---|---|---|---|
早餐 | 30 | 15 | 18 | 40 |
午餐 | 50 | 35 | 30 | 84 |
晚餐 | 60 | 40 | 24 | 88 |
Oracle的PIVOT
就提供類似的作用,語法如下。
SELECT * FROM table_name
PIVOT (
pivot_clause
pivot_for_clause
pivot_in_clause
);
pivot_clause
為要被聚合計算(aggregate)的欄位,通常搭配聚合函式(aggregate functions)如COUNT()
、SUM()
等;
pivot_for_clause
為要被GROUP BY
樞紐(pivot)的欄位,也就是由行變列的欄位;
pivot_in_clause
用IN
來篩選pivot_for_clause
中樞紐的欄位。
例如建立一個餐點銷售數量表SALES_STATISTICS
如下。
CREATE TABLE SALES_STATISTICS (
ITEM VARCHAR2(30), -- 餐點名稱
MEAL_TIME VARCHAR2(30), -- 用餐時段
AMOUNT NUMBER -- 銷售數量
);
INSERT INTO SALES_STATISTICS VALUES ('braised-pork-rice', 'BREAKFAST', 30);
INSERT INTO SALES_STATISTICS VALUES ('soup-noodles', 'BREAKFAST', 15);
INSERT INTO SALES_STATISTICS VALUES ('stirred-noodles', 'BREAKFAST', 18);
INSERT INTO SALES_STATISTICS VALUES ('over-egg', 'BREAKFAST', 40);
INSERT INTO SALES_STATISTICS VALUES ('braised-pork-rice', 'LUNCH', 50);
INSERT INTO SALES_STATISTICS VALUES ('soup-noodles', 'LUNCH', 35);
INSERT INTO SALES_STATISTICS VALUES ('stirred-noodles', 'LUNCH', 30);
INSERT INTO SALES_STATISTICS VALUES ('over-egg', 'LUNCH', 84);
INSERT INTO SALES_STATISTICS VALUES ('braised-pork-rice', 'DINNER', 60);
INSERT INTO SALES_STATISTICS VALUES ('soup-noodles', 'DINNER', 40);
INSERT INTO SALES_STATISTICS VALUES ('stirred-noodles', 'DINNER', 24);
INSERT INTO SALES_STATISTICS VALUES ('over-egg', 'DINNER', 88);
+-------------------+-----------+--------+
| ITEM | MEAL_TIME | AMOUNT |
+-------------------+-----------+--------+
| braised-pork-rice | BREAKFAST | 30 |
| soup-noodles | BREAKFAST | 15 |
| stirred-noodles | BREAKFAST | 18 |
| over-egg | BREAKFAST | 40 |
| braised-pork-rice | LUNCH | 50 |
| soup-noodles | LUNCH | 35 |
| stirred-noodles | LUNCH | 30 |
| over-egg | LUNCH | 84 |
| braised-pork-rice | DINNER | 60 |
| soup-noodles | DINNER | 40 |
| stirred-noodles | DINNER | 24 |
| over-egg | DINNER | 88 |
+-------------------+-----------+--------+
使用PIVOT
查詢把用餐時段(MEAL_TIME
)轉為欄。
SELECT * FROM SALES_STATISTICS
PIVOT (
SUM (AMOUNT)
FOR MEAL_TIME
IN ('BREAKFAST' , 'LUNCH', 'DINNER')
);
+-------------------+-------------+---------+----------+
| ITEM | 'BREAKFAST' | 'LUNCH' | 'DINNER' |
+-------------------+-------------+---------+----------+
| braised-pork-rice | 30 | 50 | 60 |
| soup-noodles | 15 | 35 | 40 |
| stirred-noodles | 18 | 30 | 24 |
| over-egg | 40 | 84 | 88 |
+-------------------+-------------+---------+----------+
使用PIVOT
查詢把餐點名稱(ITEM
)轉為欄。
SELECT * FROM SALES_STATISTICS
PIVOT (
SUM (AMOUNT)
FOR ITEM
IN ('braised-pork-rice' , 'soup-noodles', 'stirred-noodles', 'over-egg')
);
+-----------+---------------------+----------------+-------------------+------------+
| MEAL_TIME | 'braised-pork-rice' | 'soup-noodles' | 'stirred-noodles' | 'over-egg' |
+-----------+---------------------+----------------+-------------------+------------+
| LUNCH | 50 | 35 | 30 | 84 |
| BREAKFAST | 30 | 15 | 18 | 40 |
| DINNER | 60 | 40 | 24 | 88 |
+-----------+---------------------+----------------+-------------------+------------+
若本篇有幫助到您還幫忙點個Google廣告,感恩。
沒有留言:
張貼留言