Query ที่ใช้งานบ่อยๆสำหรับทำ ETL , Data Warehouse และ Data Science ตอนที่ 1

by
Post Views: 2,459

สายงานดึงข้อมูลเพื่อใช้สำหรับวิเคราะห์ข้อมูล แปลงข้อมูล จัดรูปแบบข้อมูลต่างๆไม่ว่าวัตถุประสงค์ที่จะทำ ETL, Data warehouse , Data Science, Data Lake สิ่งที่จะเกิดขึ้นบ่อยๆคือ

  • การจัดกลุ่มข้อมูล ROW_NUMBER(), RANK(), DENSE_RANK()
  • การแปลงข้อมูล CAST,CASE
  • การสร้าง View, Sub Table, temp table, Material View ,select ซ้อน select หลายชั้น (with)

การทำงานด้านนี้จะแตกต่างจากการดึงข้อมูลในการทำงานแบบ CRUD (Create, Read, Update, Delete) เป็นงาน Transaction เน้นการทำงานที่เร็วอย่างมีประสิทธิภาพ ส่วนการวิเคราะห์ข้อมูลลืมเรื่อง Performance ไปได้เลยส่วนใหญ่คำสั่งที่ทาง Transaction Performance ต้องการให้เราหลีกเลี่ยงเราก็จะได้นำมาใช้งานอยากสนุกสนาน
เนื่องจากตอนนี้ผมดึงข้อมูลจาก Oracle Database เป็นหลักก็เลยขอเขียนตัวอย่างของ Oracle ก่อนนะครับ ต่อไปค่อยเพิ่มเติม Database อื่นๆต่อไป

การจัดกลุ่มข้อมูล

พื้นฐานสุดๆที่รู้ๆกันคือการทำด้วยคำสั่ง Group By เช่น
ถ้าเราต้องการค่าเดียวจากตารางเลย select sum(a) from table_a แบบนี้ก็จะเป็นการ Group ข้อมูลทั้งตาราง
ถ้าต้องการมีตัวแยกข้อมูลก็จะเป็น select column_a,count(*) from table_a group by column_a แบบนี้ก็จะมีตัวช่วยแบ่งกลุ่มข้อมูลออกมาแล้ว
แต่การทำงานจริงไม่ได้ง่ายดายขนาดนั้น เช่น โจทย์ต้องการเอาค่าที่มากสุด น้อยสุดหรือล่าสุดของข้อมูลในตารางโดยต้องแบ่งข้อมูลออกเป็นส่วนๆตามที่ต้องการก่อน อันนี้ก็จะพอไหว แต่ถ้าบอกว่าต้องการเอาข้อมูลลำดับที่ 1 และลำดับที่ 2 ของข้อมูลมาเปรียบเทียบกันซึ่งไม่สนใจลำดับอื่นๆแบบนี้การแบ่งกลุ่มก็ต้องมาการจัดลำดับ และสามารถดึงลำดับที่ต้องการออกมาได้ อันนี้ยากแล้วเราก็ต้องมาดูว่าฐานข้อมูลแต่ละแบบมีตัวช่วยอะไรให้เราใช้บ้างในส่วนของ oracle จะมีฟังก์ชันกลุ่มนึงที่เป็นการเรียงลำดับข้อมูลนั่นก็คือ ROW_NUMBER(),  RANK(), DENSE_RANK() 

ROW_NUMBER() ใช้สำหรับแป๊ะเลขที่ ของชุดข้อมูลที่สนใจ ถ้าไม่ Partition ข้อมูลก็จะเป็นการ แป๊ะเลขที่ของข้อมูลทั้งหมด ซึ่งใน Oracle จะมี อีกตัวไว้ใช้งานอยู่แล้วคือ ROWNUM ซึ่งเป็น pseudocolumn ไม่ต้องใช้ ROW_NUMBER() แต่ถ้าต้องการแบ่งข้อมูลออกเป็นส่วนๆด้วย ก็ต้องใช้ ROW_NUMBER บวกกับ PARTITION BY ลองดูตัวอย่างการทำงานจริงๆครับกว่าจะได้คำตอบมาต้องทำกี่ขั้นดังนี้

รูป Row_Number()

จากรูปอธิบายได้ดังนี้

ชั้นในสุดต้องการหาผลนับจำนวนข้อมูลที่สนใจ
ชั้นที่ 2 เอาข้อมูลที่ได้มาแบ่ง Partition By ด้วย data_year และเอามาเรียงลำดับ Order By ด้วย Totals แบบเรียงจากมากไปน้อย DESC โดยจะใส่เลขกำกับไว้กับข้อมูลที่ data_year เดียวกันที่มีค่า totals มากที่สุดจะเป็นเลข 1 ไปเรื่อยๆ และเริ่ม 1 อีกครั้งเมื่อ data_year เปลี่ยนไป
ชั้นนอกสุด คือ select * from (…) where row_no < 4 คือเอามาเฉพาะลำดับที่ 1-3 เท่านั้น
สรุปคือต้องการเอาข้อมูลรายชื่อคณะที่มีค่า totals มากที่สุด 3 ลำดับแรกของแต่ละ data_year ออกมาแสดง

RANK()

ลักษณะการทำงานก็จะเหมือนตัวอย่าง ROW_NUMBER() จะเขียนอธิบายเฉพาะที่แตกต่างกันเท่านั้นดังนี้
เป็นการแป๊ะตัวเลขลำดับให้กับชุดข้อมูลเดียวกัน แต่ค่าข้อมูลที่สนใจได้ลำดับเดียวกัน จะข้ามตัวเลขลำดับถันไปเท่ากับจำนวนลำดับที่เท่ากัน ดังรูปผลของ Query ด้านล่าง จากลำดับที่ 2 ไป 3 rows แล้วจะไปขึ้นลำดับที่ 5 เลย

DENSE_RANK()

จะเหมือนกัน RANK() แต่จะไม่มีการเว้นเลขลำดับ จะมีเลขที่ต่อเนื่องไปเลย ดังรูปผลของ Query ด้านล่าง จากลำดับที่ 2 ไป 3 rows แล้วจะต่อลำดับที่ 3 ต่อไป

รูปตัวอย่าง Query
รูปผลของ Query

การแปลงข้อมูล

CAST

ใช้เพื่อแปลงชนิดของข้อมูลให้เป็นไปตามที่ต้องการ เช่น แปลงวันที่เป็นข้อความ กำหนดชนิดของข้อมูล Column ที่ยังไม่มีข้อมูลให้เป็นไปตามที่ต้องการ ดังตัวอย่าง

รูปการแปลงวันที่ปัจจุบันเป็นข้อความ
รูปผลการแปลงวันที่ปัจจุบันเป็นข้อความ
รูปแสดงการสร้างตารางใหม่จากข้อมูลที่มีอยู่และต้องการเพิ่ม column ใหม่เข้าไปเพิ่มเติมแบบต้องการระบุ data type

CASE

ใช้เพื่อจัดการข้อมูลในหลายๆรูปแบบเป็นการกำหนดเงื่อนไขขึ้นมาเพื่อแปลงข้อมูล ดังตัวอย่าง ต้องการ Update Column ให้มีค่าแตกต่างกันให้เป็นไปตามเงื่อนไขที่กำหนด

update student_regist
set WITHDRAWAL_TYPE =  case
                            when lower(WITHDRAWAL_TYPE) = 'w' then  'ถอนติด W' 
                            when lower(WITHDRAWAL_TYPE) = 'c' then  'ถอนเพราะวิชาปิด'
                            when lower(WITHDRAWAL_TYPE) = 'r' then  'ถอน'
                            when lower(WITHDRAWAL_TYPE) = 'n' then  'ถอน'
                            when lower(WITHDRAWAL_TYPE) is null then  'ลงทะเบียนปกติ'
                            else 'xxx'
                            end;

การสร้าง VIEW, Temporary Table, Materialized view

การแยกข้อมูลออกเป็นกลุ่มๆอีกวิธีที่ใช้งานเยอะคือการสร้าง View, Temporary Table, Materialized view

View

มีการเปลี่ยนแปลงตาม Table ต้นทาง View ไม่น่าจะต้องพูดเยอะเพราะน่าจะใช้งานกันเป็นประจำอยู่แล้ว

Materialized view

เป็นตารางที่มีข้อมูลที่ได้มาจากผลของการ Run Query ที่เราต้องการและมีการ Refresh ข้อมูลตามเวลาที่กำหนดไว้ ที่แตกต่างจาก View คือต้องการที่จัดเก็บข้อมูลส่วนตัวนะครับ ตัวอย่างการสร้าง Materialized View

DROP MATERIALIZED VIEW REGIST_ALL;

CREATE MATERIALIZED VIEW REGIST59
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH sysdate
NEXT sysdate+1
WITH PRIMARY KEY
AS
SELECT *
FROM REGIST where year >'2558';

COMMENT ON MATERIALIZED VIEW REGIST59 IS 'snapshot table for snapshot REGIST';

Temporary Table

Temporary Table ใน Oracle ใช้แก้ไขปัญหาที่ซับซ้อนโดยการใช้  Stored Procedure หาข้อมูลแล้วนำไปเก็บไว้ใน Temporary table เพื่อประมวลผลในขั้นถัดๆไป

CREATE GLOBAL TEMPORARY TABLE temp_table (

  student_id NUMBER(1),

  student_name            VARCHAR2(150),

  country_name    VARCHAR2(150)

)
ON COMMIT DELETE ROWS;

ตัวอย่างการสร้าง temporary Table ไว้ใช้งาน ON COMMIT DELETE ROWS; เป็นการบอกว่า เมื่อใช้งานเสร็จให้ลบข้อมูล อย่างเช่นสั่ง insert ข้อมูลใน stored procedure เมื่อ stored procedure ทำงานเสร็จข้อมูลที่ insert ไว้ใน temporary table จะถูกลบทิ้งทั้งหมด เป็นต้น ยังมี option อีกมากมายสำหรับ temporary table ต้องศึกษาจาก Oracle Document

Subquery

subquery เป็นตัวช่วยทำงานในการแยกข้อมูลที่ต้องการออกมาเป็นส่วนๆแล้วนำไปประมวลผลต่อ ในกลุ่มนี้ with … as ถือว่ามีการใช้งานบ่อยครั้ง คำสั่ง With … as ข้อมูลที่ได้มาเปรียบได้กับ local temporary 1 table นำไป query ต่อได้

WITH dept_count AS (
  SELECT deptno, COUNT(*) AS dept_count
  FROM   emp
  GROUP BY deptno)
SELECT e.ename AS employee_name,
       dc.dept_count AS emp_dept_count
FROM   emp e
       JOIN dept_count dc ON e.deptno = dc.deptno;

จากตัวอย่างนี้เป็นการนับจำนวนหน่วยงานตาม deptno แล้วเอามา Join กับ table emp ที่มีค่า deptno ตรงกัน

ตอนที่ 1 นี้ขอจบแต่เพียงเท่านี้ ตอนต่อๆไปจะเรียบเรียงข้อมูลตามที่ใช้ทำงานจริงๆในการทำงานด้าน ETL, Data Science และ Data Lake ต่อไปนะครับ ขอบคุณที่เข้ามาอ่านกันนะครับ