SQL สำหรับ Data Analyst: Window Functions, CTEs และ Advanced Queries ฉบับสมบูรณ์

SQL Window Functions, CTEs (Common Table Expressions) และรูปแบบ Query ขั้นสูง ถือเป็นรากฐานสำคัญของการเขียน SQL เชิงวิเคราะห์ ไม่ว่าจะกำลังเตรียมตัวสัมภาษณ์งานตำแหน่ง Data Analyst หรือกำลังเผชิญกับ Query ที่ซับซ้อนสำหรับงาน Reporting เทคนิคเหล่านี้จะช่วยเปลี่ยน Subquery ที่ยาวเยิ่นเย้อและอ่านยาก ให้กลายเป็น SQL ที่สะอาด อ่านง่าย และมีประสิทธิภาพสูง

ทำความเข้าใจ SQL Window Functions และ OVER Clause

Window Function คือฟังก์ชันที่ทำการคำนวณข้ามกลุ่มแถว (Window) ที่กำหนดไว้ โดย OVER Clause เป็นตัวควบคุมว่าแถวใดบ้างที่อยู่ใน Window นั้น และจะเรียงลำดับอย่างไร สิ่งที่แตกต่างจาก Aggregate Functions ที่ใช้กับ GROUP BY อย่างชัดเจนก็คือ Window Functions จะยังคงรักษาทุกแถวไว้ในผลลัพธ์ ไม่มีการยุบรวมแถวใดแถวหนึ่งหายไป

รูปแบบ Syntax ทั่วไปของ Window Functions นั้นใช้ได้กับฐานข้อมูลหลักทุกตัว ไม่ว่าจะเป็น PostgreSQL, MySQL 8+, BigQuery, SQL Server หรือ Snowflake

-- window_function_syntax.sql
SELECT
  employee_id,
  department,
  salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_salary_rank,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_salary_total
FROM employees;

PARTITION BY ทำหน้าที่แบ่งข้อมูลออกเป็นกลุ่ม (คล้ายกับ GROUP BY แต่ไม่ยุบแถว) ส่วน ORDER BY กำหนดลำดับภายในแต่ละ Partition และ Frame Clause (ROWS BETWEEN ...) ซึ่งเป็นตัวเลือกเพิ่มเติม จะจำกัดขอบเขตของ Window ให้แคบลงเฉพาะช่วงแถวที่ต้องการ

เปรียบเทียบ ROW_NUMBER, RANK และ DENSE_RANK

ฟังก์ชันจัดอันดับทั้งสามตัวนี้มีลักษณะการทำงานที่คล้ายกันเมื่อมองผิวเผิน แต่จะให้ผลลัพธ์ที่แตกต่างกันเมื่อมีค่าซ้ำ (Ties) เกิดขึ้น การเลือกใช้ผิดตัวเป็นสาเหตุที่พบบ่อยของ Bug ใน Query เชิงวิเคราะห์

-- ranking_comparison.sql
SELECT
  product_name,
  category,
  revenue,
  ROW_NUMBER() OVER (ORDER BY revenue DESC) AS row_num,
  RANK()       OVER (ORDER BY revenue DESC) AS rank_val,
  DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rank_val
FROM products;

| product_name | revenue | row_num | rank_val | dense_rank_val | |-------------|---------|---------|----------|----------------| | Widget A | 50000 | 1 | 1 | 1 | | Widget B | 50000 | 2 | 1 | 1 | | Widget C | 40000 | 3 | 3 | 2 | | Widget D | 30000 | 4 | 4 | 3 |

ROW_NUMBER จะกำหนดเลขลำดับที่ไม่ซ้ำกันเสมอ แม้จะมีค่าซ้ำกัน แถวใดแถวหนึ่งก็จะได้รับเลขที่ต่ำกว่าโดยสุ่ม ส่วน RANK จะให้เลขเดียวกันสำหรับค่าที่ซ้ำกัน แต่จะข้ามเลขถัดไป (1, 1, 3) ในขณะที่ DENSE_RANK ก็จัดการค่าซ้ำเช่นกัน แต่จะไม่ข้ามเลข (1, 1, 2) สำหรับ Query แบบ Top-N ที่ต้องการให้ค่าซ้ำแชร์ตำแหน่งเดียวกัน DENSE_RANK มักเป็นตัวเลือกที่ถูกต้องที่สุด

LAG และ LEAD สำหรับการวิเคราะห์แบบ Period-over-Period

LAG และ LEAD ช่วยให้สามารถเข้าถึงข้อมูลจากแถวก่อนหน้าหรือแถวถัดไปได้โดยไม่ต้องทำ Self-Join ฟังก์ชันเหล่านี้มีความจำเป็นอย่างยิ่งในการคำนวณอัตราการเติบโตแบบ Period-over-Period การตรวจจับแนวโน้ม และการระบุความเปลี่ยนแปลง

-- period_over_period.sql
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS mom_growth_pct,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales
ORDER BY month;

Argument ตัวที่สองของ LAG/LEAD ใช้กำหนดค่า Offset (ค่าเริ่มต้นคือ 1) Argument ตัวที่สามซึ่งเป็นตัวเลือกเพิ่มเติม ใช้กำหนดค่า Default เมื่อไม่มีแถวอยู่ที่ Offset นั้น ซึ่งมีประโยชน์ในการหลีกเลี่ยง NULL ในแถวแรกหรือแถวสุดท้าย ส่วน NULLIF ในการคำนวณอัตราการเติบโตช่วยป้องกันข้อผิดพลาดจากการหารด้วยศูนย์

NTILE และการแบ่งกลุ่มแบบ Percentile

NTILE ทำหน้าที่กระจายแถวออกเป็นกลุ่มย่อยตามจำนวนที่กำหนด โดยแต่ละกลุ่มจะมีขนาดเท่า ๆ กันโดยประมาณ นักวิเคราะห์ข้อมูลใช้ NTILE สำหรับการวิเคราะห์แบบ Quartile การให้คะแนนแบบ Decile และการแบ่งกลุ่มลูกค้า (Customer Segmentation)

-- customer_segmentation.sql
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile,
  NTILE(10) OVER (ORDER BY total_spend DESC) AS spend_decile
FROM (
  SELECT
    customer_id,
    SUM(order_total) AS total_spend
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY customer_id
) customer_totals;

Quartile 1 จะประกอบด้วยลูกค้าที่ใช้จ่ายสูงสุด และ Quartile 4 คือกลุ่มที่ใช้จ่ายต่ำสุด รูปแบบนี้สอดคล้องโดยตรงกับการแบ่งกลุ่มแบบ RFM (Recency, Frequency, Monetary) ที่ใช้กันอย่างแพร่หลายในสาย Marketing Analytics

Common Table Expressions: ทางเลือกแทน Nested Subqueries

CTEs (WITH Clause) ช่วยแบ่ง Query ที่ซับซ้อนออกเป็นขั้นตอนย่อยที่มีชื่อและอ่านเข้าใจง่าย แต่ละ CTE ทำหน้าที่เป็นชุดผลลัพธ์ชั่วคราวที่มีชื่อกำกับ และจะคงอยู่เฉพาะในระหว่างที่ Query นั้นทำงานเท่านั้น นอกจากจะช่วยเรื่องความสามารถในการอ่านแล้ว CTEs ยังทำให้การ Debug ง่ายขึ้น เพราะสามารถทดสอบแต่ละขั้นตอนได้อย่างอิสระ

-- cte_sales_analysis.sql
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    product_category,
    SUM(amount) AS revenue,
    COUNT(DISTINCT customer_id) AS unique_customers
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY DATE_TRUNC('month', order_date), product_category
),
ranked_categories AS (
  SELECT
    month,
    product_category,
    revenue,
    unique_customers,
    RANK() OVER (
      PARTITION BY month
      ORDER BY revenue DESC
    ) AS category_rank
  FROM monthly_revenue
)
SELECT
  month,
  product_category,
  revenue,
  unique_customers,
  category_rank
FROM ranked_categories
WHERE category_rank <= 3
ORDER BY month, category_rank;

วิธีการสามขั้นตอนนี้ช่วยแทนที่สิ่งที่อาจจะเป็น Subquery ซ้อนกันหลายชั้น โดยแต่ละ CTE มีหน้าที่รับผิดชอบที่ชัดเจน ได้แก่ การรวมข้อมูล (Aggregation) การจัดอันดับ (Ranking) และการกรอง (Filtering)

Recursive CTEs สำหรับข้อมูลแบบลำดับชั้น

Recursive CTEs เป็นเครื่องมือที่ช่วยแก้ปัญหาเกี่ยวกับข้อมูลที่มีลักษณะเป็นลำดับชั้น (Hierarchical) หรือแบบกราฟ ไม่ว่าจะเป็นแผนผังองค์กร โครงสร้างหมวดหมู่แบบ Tree, Bill-of-Materials หรือ Query สำหรับค้นหาเส้นทาง

-- recursive_org_chart.sql
WITH RECURSIVE org_hierarchy AS (
  SELECT
    employee_id,
    employee_name,
    manager_id,
    1 AS depth,
    employee_name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.employee_id,
    e.employee_name,
    e.manager_id,
    oh.depth + 1,
    oh.management_chain || ' > ' || e.employee_name
  FROM employees e
  INNER JOIN org_hierarchy oh ON e.manager_id = oh.employee_id
)
SELECT
  employee_id,
  employee_name,
  depth,
  management_chain
FROM org_hierarchy
ORDER BY management_chain;

Base Case จะเลือกโหนดราก (Root Node) ซึ่งก็คือพนักงานที่ไม่มีผู้จัดการ ส่วน Recursive Case จะ Join กลับไปยัง CTE ตัวเอง เพื่อสร้างลำดับชั้นทีละระดับ คอลัมน์ management_chain ทำหน้าที่ต่อชื่อเข้าด้วยกันเพื่อแสดงสายการรายงานทั้งหมด ฐานข้อมูลส่วนใหญ่จะจำกัดความลึกของ Recursion เพื่อป้องกัน Infinite Loop โดย PostgreSQL มีค่าเริ่มต้นอยู่ที่ 100 รอบ

รูปแบบการวิเคราะห์ขั้นสูง: Gaps, Islands และ Running Totals

รูปแบบ Gaps-and-Islands เป็นเทคนิคที่ใช้ระบุลำดับที่ต่อเนื่องกันในข้อมูล ไม่ว่าจะเป็นช่วงเวลาที่ Subscription ยังเปิดใช้งาน วันที่ Login ติดต่อกัน หรือรอบการผลิตที่ไม่หยุดชะงัก เทคนิคนี้ผสมผสานระหว่าง ROW_NUMBER กับการคำนวณวันที่

-- consecutive_login_streaks.sql
WITH login_days AS (
  SELECT DISTINCT
    user_id,
    DATE(login_timestamp) AS login_date
  FROM user_sessions
),
streaks AS (
  SELECT
    user_id,
    login_date,
    login_date - (ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_date
    ))::int AS streak_group
  FROM login_days
)
SELECT
  user_id,
  MIN(login_date) AS streak_start,
  MAX(login_date) AS streak_end,
  COUNT(*) AS streak_length
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 3
ORDER BY streak_length DESC;

หลักการสำคัญคือ สำหรับวันที่ที่ต่อเนื่องกัน การนำ Row Number ที่เพิ่มขึ้นเรื่อย ๆ ไปลบออกจากวันที่ จะได้ค่าเดียวกันเสมอ เมื่อเกิดช่องว่าง (Gap) ค่าที่ได้จะเปลี่ยนไป ทำให้เกิดกลุ่มใหม่ขึ้น Query นี้จะค้นหาช่วงเวลาที่ผู้ใช้ Login ติดต่อกัน 3 วันขึ้นไป

การรวม Window Functions กับ CASE สำหรับการวิเคราะห์แบบมีเงื่อนไข

ในการทำงานจริง Query เชิงวิเคราะห์มักจะรวม Window Functions เข้ากับ CASE Expression เพื่อคำนวณ Metrics ที่มีเงื่อนไขภายใน Query เดียวกัน

-- conditional_analytics.sql
SELECT
  order_date,
  product_category,
  amount,
  AVG(amount) OVER (
    PARTITION BY product_category
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d,
  SUM(amount) OVER (
    PARTITION BY product_category, DATE_TRUNC('quarter', order_date)
    ORDER BY order_date
  ) AS qtd_cumulative,
  CASE
    WHEN amount > 2 * AVG(amount) OVER (PARTITION BY product_category)
    THEN 'OUTLIER'
    ELSE 'NORMAL'
  END AS outlier_flag
FROM daily_sales
ORDER BY product_category, order_date;

Query เดียวนี้คำนวณค่าเฉลี่ยเคลื่อนที่ 7 วัน (Rolling 7-day Average) ผลรวมสะสมตั้งแต่ต้นไตรมาส (Quarter-to-Date) ที่รีเซ็ตทุกไตรมาส และ Flag สำหรับตรวจจับค่าผิดปกติ (Outlier) ทั้งหมดนี้ทำได้โดยไม่ต้องใช้ Subquery หรือ Self-Join

การเพิ่มประสิทธิภาพ Query เชิงวิเคราะห์

Window Functions และ CTEs เป็นเครื่องมือที่ทรงพลัง แต่ก็อาจกลายเป็นจุดคอขวดด้านประสิทธิภาพได้เมื่อทำงานกับตารางขนาดใหญ่ มีเทคนิคหลายประการที่ช่วยรักษาประสิทธิภาพไว้ได้แม้ข้อมูลจะมีขนาดใหญ่

-- named_window.sql
SELECT
  department,
  employee_id,
  salary,
  RANK() OVER dept_window AS dept_rank,
  SUM(salary) OVER dept_window AS dept_total,
  AVG(salary) OVER dept_window AS dept_avg
FROM employees
WINDOW dept_window AS (
  PARTITION BY department
  ORDER BY salary DESC
);

WINDOW Clause (รองรับใน PostgreSQL, MySQL 8+ และ BigQuery) ช่วยให้สามารถกำหนด Window เพียงครั้งเดียวแล้วนำกลับมาใช้ซ้ำกับหลายฟังก์ชันได้ วิธีนี้ช่วยเพิ่มทั้งความสามารถในการอ่านโค้ดและโอกาสที่ Query Planner จะ Optimize ได้ดียิ่งขึ้น

บทสรุป

• Window Functions (ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, NTILE) ทำการคำนวณข้ามชุดแถวโดยไม่ยุบรวมแถว ซึ่งแตกต่างจาก GROUP BY Aggregate
• CTEs ช่วยแบ่ง Query ที่ซับซ้อนออกเป็นขั้นตอนย่อยที่มีชื่อกำกับ ทดสอบได้ง่าย และแทนที่ Subquery ที่ซ้อนกันหลายชั้น
• Recursive CTEs จัดการกับข้อมูลแบบลำดับชั้น เช่น แผนผังองค์กรและ Category Tree แต่ต้องกำหนดขีดจำกัดความลึกและสร้าง Index ที่คอลัมน์ Join
• เทคนิค Gaps-and-Islands (ROW_NUMBER + การคำนวณวันที่) ช่วยระบุลำดับที่ต่อเนื่องกันในข้อมูล Time-Series
• Named Windows (WINDOW Clause) ช่วยลดการเขียนโค้ดซ้ำซ้อนและอาจช่วยให้ Query Plan ถูก Optimize ได้ดีขึ้น
• ควรกรองข้อมูลใน WHERE Clause ก่อนที่ Window Function จะทำงาน เพื่อรักษาประสิทธิภาพบนตารางขนาดใหญ่
• รูปแบบเหล่านี้ปรากฏบ่อยมากในการสัมภาษณ์งาน Data Analytics และสามารถนำไปใช้ได้โดยตรงกับงาน Reporting, Segmentation และการวิเคราะห์แนวโน้มในชีวิตประจำวัน