สายงานดึงข้อมูลเพื่อใช้สำหรับวิเคราะห์ข้อมูล แปลงข้อมูล จัดรูปแบบข้อมูลต่างๆไม่ว่าวัตถุประสงค์ที่จะทำ 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 ลองดูตัวอย่างการทำงานจริงๆครับกว่าจะได้คำตอบมาต้องทำกี่ขั้นดังนี้
จากรูปอธิบายได้ดังนี้
สรุปคือต้องการเอาข้อมูลรายชื่อคณะที่มีค่า totals มากที่สุด 3 ลำดับแรกของแต่ละ data_year ออกมาแสดง
RANK()
ลักษณะการทำงานก็จะเหมือนตัวอย่าง ROW_NUMBER() จะเขียนอธิบายเฉพาะที่แตกต่างกันเท่านั้นดังนี้
เป็นการแป๊ะตัวเลขลำดับให้กับชุดข้อมูลเดียวกัน แต่ค่าข้อมูลที่สนใจได้ลำดับเดียวกัน จะข้ามตัวเลขลำดับถันไปเท่ากับจำนวนลำดับที่เท่ากัน ดังรูปผลของ Query ด้านล่าง จากลำดับที่ 2 ไป 3 rows แล้วจะไปขึ้นลำดับที่ 5 เลย
DENSE_RANK()
จะเหมือนกัน RANK() แต่จะไม่มีการเว้นเลขลำดับ จะมีเลขที่ต่อเนื่องไปเลย ดังรูปผลของ Query ด้านล่าง จากลำดับที่ 2 ไป 3 rows แล้วจะต่อลำดับที่ 3 ต่อไป
การแปลงข้อมูล
CAST
ใช้เพื่อแปลงชนิดของข้อมูลให้เป็นไปตามที่ต้องการ เช่น แปลงวันที่เป็นข้อความ กำหนดชนิดของข้อมูล Column ที่ยังไม่มีข้อมูลให้เป็นไปตามที่ต้องการ ดังตัวอย่าง
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 ต่อไปนะครับ ขอบคุณที่เข้ามาอ่านกันนะครับ
