appkonthaiแอพคนไทย
Language
  • EN
  • TH
Font
  • M
  • L
  • XL
Theme
  • Light
  • Dark

Pivot

20 March 2018

20 มีนาคม 2561

Pivot

Concept

แนวคิด

การนำข้อมูลดิบ (Transactional Data) มาเปลี่ยนทิศทางการแสดงผลของข้อมูลจากแถวเป็นคอลัมน์ และใช้ Aggregate Functions รวมข้อมูลที่สนใจ เพื่อให้ได้ข้อมูลสรุปผล ที่สามารถนำไปใช้เชิงวิเคราะห์ได้ (Analytical Data)

The data transform process on raw data (Transactional Data) to get the summarize data that can use for analytical purposes (Analytical Data) by Change the display direction of the data from row to the column and use aggregate functions to aggregate interesting data.

Keywords

คีย์เวิร์ด

Transactional Dataข้อมูลดิบในตาราง ที่ได้จากการประมวลผลของระบบ
Analytical Dataข้อมูลที่ผ่านการสรุปและจัดรูปแบบที่เหมาะสมแล้ว สามารถนำไปใช้วิเคราะห์ได้
Pivotการเปลี่ยนทิศทางการแสดงผลข้อมูลในตารางจากแถวเป็นคอลัมน์
Unpivotการทำสิ่งตรงกันข้ามกับกับ Pivot คือ เปลี่ยนทิศทางการแสดงผลข้อมูลในตารางจากคอลัมน์เป็นแถว
Aggregate Functionsคำสั่งในการรวมข้อมูลที่อยู่ในหลายๆ แถวเป็นข้อมูลค่าเดียว เช่น Count, Sum, Average, Max, Min
Transactional DataRaw data in the table that is the result of system processing.
Analytical DataThe data that are already summarized and formatted that it can use for analytical purposes.
PivotChange the display direction of the data in the table from row to column.
UnpivotThe opposite of pivot, change the display direction of the data in the table from column to row.
Aggregate FunctionsThe command to aggregate the values in multiple rows to the single value such as Count, Sum, Average, Max, Min.

สมมุติเรามีข้อมูลการขายสินค้า (Transactional Data) ที่มีหน้าตาแบบนี้ในตาราง

Assume we have these sales data (Transactional Data) that look like this.

แต่เราต้องการข้อมูลสรุปเพื่อนำมาเปรียบเทียบยอดขายในแต่ละ Quarter (Analytical Data) ที่มีหน้าตาแบบนี้

But we want the summarize data to compare the sales volume in each quarter (Analytical Data) that look like this.

สิ่งที่เราต้องทำคือ

  • 1. เปลี่ยนทิศทางการแสดงผลข้อมูล Quarter จากแถวเป็นคอลัมน์
  • 2. ใช้ Aggregate Function SUM เพื่อ รวมข้อมูล Qty

What we need to do is.

  • 1. Change the display direction of quarter data from row to column.
  • 2. Use aggregate function SUM to aggregate the quantity data.

เราสามารถทำได้โดยใช้คำสั่ง SQL พื้นฐานดังนี้

We can do it by using the basic SQL command as follow.


SELECT
	Year,
	SUM(CASE Quarter WHEN 'Q1' THEN Qty ELSE 0 END) Q1,
	SUM(CASE Quarter WHEN 'Q2' THEN Qty ELSE 0 END) Q2,
	SUM(CASE Quarter WHEN 'Q3' THEN Qty ELSE 0 END) Q3,
	SUM(CASE Quarter WHEN 'Q4' THEN Qty ELSE 0 END) Q4
FROM SALES
GROUP BY Year

แต่ตัว DBMS ทั่วไปนั้นส่วนใหญ่ รองรับการใช้คำสั่ง PIVOT ซึ่งช่วยให้การเขียน SQL ง่ายและกระชับขึ้น ดังนี้

But most of DBMS support the PIVOT command which help the SQL coding simple and concise.


SELECT * FROM (SELECT Year, Quarter, Qty FROM SALES) A
PIVOT
(
	SUM(Qty)
	FOR Quarter
	IN (Q1, Q2, Q3, Q4)
) P

เราสามารถใช้คำสั่ง UNPIVOT เพื่อเป็นการย้อนกลับการ Pivot แต่ข้อมูลที่ได้จากการ Unpivot นั้นเป็นเพียงข้อมูลที่จัดรูปแบบใหม่เท่านั้น ไม่ได้เป็นข้อมูลต้นฉบับ (ข้อมูลก่อนผ่าน Aggregate Functions) แต่อย่างไร

We can use UNPIVOT command to reverse the pivot operation, but the result data of unpivot operation is not the original data. It's just summarized data with the new display format.


SELECT * FROM SALES_PIVOT
UNPIVOT
(
	SumQty
	FOR Quarter
	IN (Q1, Q2, Q3, Q4)
) U

กรณี DBMS ที่ใช้ไม่รองรับคำสั่ง UNPIVOT เราสามารถทำได้โดยใช้คำสั่ง SQL พื้นฐานดังนี้

If the DBMS does not support UNPIVOT command, we can do it by using the basic SQL command as follow.


SELECT Year, Q1 AS SumQty, 'Q1' AS Quarter FROM SALES_PIVOT
UNION ALL
SELECT Year, Q2, 'Q2' FROM SALES_PIVOT
UNION ALL
SELECT Year, Q3, 'Q3' FROM SALES_PIVOT
UNION ALL
SELECT Year, Q4, 'Q4' FROM SALES_PIVOT
ORDER BY Year, Quarter
ประสมศักดิ์ ขุนหมื่น Prasomsak Khunmuen

แค่โปรแกรมเมอร์คนไทย ที่รักการเขียนโปรแกรม

I'm just a Thai Programmer who loves programming.