COOL IT IDEAS AND STUFF

See the original english version Click here!

 

สิ่งที่ทำให้การเป็น “Data Engineer ที่ดี ไม่ได้อยู่ที่การเขียน Query ได้เพียงอย่างเดียว แต่คือการเข้าใจ “SQL Patterns” ที่ทำให้ข้อมูลของคุณเชื่อถือได้และดูแลง่ายในทุกระบบ บทความนี้จะมาแนะนำ 10 SQL Patterns ที่ Data Engineer ควรใช้ให้เชี่ยวชาญ

1. Surrogate Key Generation

สถานการณ์: Raw Event Logs ของคุณ ไม่มี Unique ID แต่คุณจำเป็นต้องมีไว้สำหรับการ Join และ Deduplicate ภายหลัง

ทำไมถึงสำคัญ: หากไม่มี Key ที่คงที่ (Stable Key) การ Join ในขั้นตอนถัดไปอาจจะพัง และคุณจะต้องใช้เวลาทั้งสัปดาห์เพื่อไล่ตาม “Ghost Rows” หรือข้อมูลซ้ำที่ไม่ควรจะมี

ตัวอย่างคำสั่ง:

• BigQuery — GENERATE_UUID()
• Snowflake — UUID_STRING()
• Databricks — uuid()

Pro Tip: ใช้ UUID หรือ Hash แทนการใช้ ROW_NUMBER() เว้นแต่จะต้องจัดลำดับแบบกำหนดแน่ชัด

ข้อควรระวัง: Key ที่ไม่กำหนดแน่ชัด อาจทำให้เกิดการ Join ผิดหรือมีข้อมูลซ้ำได้

2. Anti Join (ค้นหารายการที่หายไป)

สถานการณ์: คุณมี User Table และ Events Table และคุณจำเป็นต้องหา User ที่ไม่เคยทำการ Login เลย

แนวทางที่แนะนำ: ใช้ Anti Join เพื่อหา User ที่ “ไม่มีคู่” ใน Events Table

Pro Tip: วิธีนี้ใช้ได้ทั้งใน BigQuery, Snowflake และ Databricks

ข้อควรระวัง: หลีกเลี่ยงการใช้ NOT IN หากมีค่า NULL อยู่ใน Table เพราะมันอาจทำให้ผลลัพธ์ “หายทั้งหมด” ได้

3. Date Binning (จัดกลุ่มตามวัน/สัปดาห์/เดือน)

สถานการณ์: ทีม Marketing มีการร้องขอจำนวน Users ต่อวัน (ตาม Time Zone ของพวกเขา)

แนวทาง:

• ระบุ Time Zone ให้ชัดเจนเสมอ
• ใช้ DATE_TRUNC หรือ TIMESTAMP_TRUNC ตาม Platform ที่คุณใช้งาน

Pro Tip: Time Zone ไม่ใช่แค่รายละเอียดที่เล็กน้อย แต่มันอาจทำให้ยอดรวมรายวันเกิดความคลาดเคลื่อนและ Dashboards ผิดเพี้ยนได้

4. Slowly Changing Dimension (SCD) Type 2

สถานการณ์: คุณจำเป็นต้องเก็บประวัติของที่อยู่ลูกค้าตามช่วงเวลา

ขั้นตอนหลัก:

• ให้เก็บค่า start_date, end_date, และ is_current
• ใช้คำสั่ง MERGE เพื่อจัดการการ Updates และ Inserts

ตัวอย่าง Timeline:

Pro Tip: ใช้ PARTITION BY ID และ CLUSTER BY start_date เพื่อประสิทธิภาพที่ดีกว่า

5. Upsert (Insert หรือ Update)

สถานการณ์: คุณได้รับ Transaction Files รายวันและต้อง Merge เข้า Table หลักโดยที่ไม่ให้ข้อมูลซ้ำ

Pro Tip: ควรลบข้อมูลซ้ำใน Source ก่อนเสมอ เพราะถ้ามีหลาย Row ที่ใช้ Key เดียวกัน คุณจะเจอปัญหาในภายหลังแน่นอน

6. Window Functions (Running Totals, Rank, First/Last)

สถานการณ์: ฝ่ายการเงิน ต้องการยอดใช้จ่ายสะสมของ User แต่ละคน

ทำไมถึงสำคัญ: คุณสามารถคำนวณข้ามหลาย Rows ได้ โดยไม่ต้องรวม Rows เหล่านั้นให้หายไปเหมือนตอนใช้ GROUP BY (ที่ปกติจะรวมข้อมูล)

Pro Tip: กำหนด Window Frame ให้ชัดเจน เพื่อให้ผลลัพธ์สอดคล้องกันในทุกระบบ

7. Pivot (แปลงข้อมูลจาก Row → Column)

สถานการณ์: ต้องการเปรียบเทียบจำนวนการ Login กับจำนวน Purchase ของ User ใน Row เดียว

แนวทาง:

• BigQuery: ใช้ PIVOT Syntax ได้โดยตรง
• Snowflake / Databricks: ใช้ CASE เพื่อความยืดหยุ่นและรองรับหลาย Platforms

8. Exploding JSON & Arrays

สถานการณ์: Event Logs ของคุณ จะเก็บ Array ของ Actions และคุณต้องการให้แต่ละ Action อยู่ใน Row ของมันเอง

Platform Syntax:

• BigQuery: UNNEST()
• Snowflake: FLATTEN()
• Databricks: explode()

Pro Tip: ต้องระวังหาก JSON มีการซ้อนกันหลายชั้น อาจจะต้องใช้ Explode หลายรอบเพื่อให้แต่ละค่าออกมาเป็น Row ที่แยกกัน

9. Deduplication (ลบข้อมูลซ้ำ)

สถานการณ์: ถ้ามีหลาย Rows ต่อ User หนึ่งคน แต่คุณต้องการเก็บเฉพาะ Row ล่าสุดเท่านั้น

แนวทางทั่วไป:

• BigQuery / Snowflake:

• Databricks: ใช้ CTE ร่วมกับ Filter

10. Conditional Aggregation

สถานการณ์: ต้องการนับจำนวนครั้งที่ User ทำการ “Login” และ “Purchase” (สั่งซื้อสินค้า) ใน Query เดียว

คำสั่ง:

• BigQuery: COUNTIF()
• Snowflake: COUNT_IF()
• Databricks: SUM(CASE WHEN ... THEN 1 END)

การทดสอบและตรวจสอบคุณภาพ

ไม่มี SQL Pattern ไหนที่มีความสมบูรณ์ หากขาดการตรวจสอบผลลัพธ์

• ตรวจ Unique Key — ห้ามมี NULL หรือข้อมูลซ้ำ
• ตรวจ SCD2 — ห้ามมีช่วงเวลาที่ทับซ้อนกัน
• ตรวจจำนวนแถว — ทั้งก่อนและหลัง Transformation เพื่อให้มั่นใจว่า ข้อมูลไม่สูญหายหรือซ้ำกัน

เครื่องมืออย่าง dbt และ Great Expectations จะช่วยทำสิ่งเหล่านี้ได้ง่ายขึ้นมาก

เคล็ดลับด้านประสิทธิภาพและต้นทุน

• ใช้ Partition และ Cluster เพื่อลดการ Scan ข้อมูล
• กรองข้อมูลตั้งแต่ต้น เพื่อลดขนาดก่อนทำการ Join
• ใช้ Databricks Z-Order เพื่อเพิ่มความเร็วของ Filter ที่ใช้บ่อย
• ใช้ APPROX_COUNT_DISTINCT เมื่อต้องการความเร็ว มากกว่าความแม่นยำ

สรุป

และนี่ก็คือ 10 SQL Patterns ที่ Data Engineer ควรใช้ให้เชี่ยวชาญ ซึ่งความแตกต่างระหว่าง Data Engineer ที่ “ยังไม่เชี่ยวชาญการใช้ SQL” กับคนที่ “เชี่ยวชาญจริง ๆ” มักจะอยู่ที่การเข้าใจ Pattern พื้นฐานเหล่านี้ เมื่อคุณเชี่ยวชาญ 10 Patterns นี้แล้ว SQL ของคุณจะเร็วขึ้น, Clean ขึ้น และพร้อมใช้งานในทุก Platform ไม่ว่าคุณจะกำลังแก้ Report ที่เสีย หรือเริ่มสร้าง Dashboard ใหม่ตั้งแต่ต้นก็ตาม

ที่มา: https://medium.com/
 

 

รับตำแหน่งงานไอทีใหม่ๆ ด้วยบริการ IT Job Alert

 

อัพเดทบทความจากคนวงในสายไอทีทาง LINE ก่อนใคร
อย่าลืมแอดไลน์ @techstarth เป็นเพื่อนนะคะ

 

บทความล่าสุด

• 7 Python Scripts เล็กๆ ที่ช่วยแก้ปัญหางานประจำวันของ Dev ได้จริง
• 10 เครื่องมือฟรี ที่คนไอทีควรมีติดเครื่องเอาไว้
• 10 Browser Tricks ที่จะช่วยเปลี่ยนวิธีพัฒนา Frontend ของคุณให้ดีขึ้น