เคล็ดลับและเทคนิค SQL

 (github.com/ben-n93)
7 คะแนน โดย GN⁺ 2024-09-26 | 2 ความคิดเห็น | แชร์ทาง WhatsApp

สารบัญ

การจัดรูปแบบ/ความอ่านง่าย

  • ใช้ comma นำหน้าเมื่อแยกฟิลด์
  • ใช้ค่า dummy ใน WHERE
  • จัดย่อหน้าโค้ดให้เหมาะสม
  • พิจารณาใช้ CTE เมื่อเขียนคิวรีที่ซับซ้อน

ความสามารถที่มีประโยชน์

  • ใช้โอเปอเรเตอร์ :: เพื่อแปลงชนิดข้อมูล
  • ใช้ anti join
  • ใช้ QUALIFY เพื่อกรอง window function
  • สามารถใช้ GROUP BY ด้วยตำแหน่งคอลัมน์ได้

ข้อควรระวังที่ควรหลีกเลี่ยง

  • ระวังเมื่อใช้ NOT IN ร่วมกับค่า NULL
  • เปลี่ยนชื่อฟิลด์ที่คำนวณแล้วเพื่อหลีกเลี่ยงความกำกวม
  • ระบุให้ชัดว่าแต่ละคอลัมน์มาจากตารางใด
  • ทำความเข้าใจลำดับการประมวลผล
  • ใส่คอมเมนต์ในโค้ด
  • อ่านเอกสารทั้งหมด

การจัดรูปแบบ/ความอ่านง่าย

ใช้ comma นำหน้าเมื่อแยกฟิลด์

  • ใน SELECT สามารถใช้ comma นำหน้าเพื่อให้เห็นการแยกคอลัมน์ใหม่ได้ชัดเจน
  • comma นำหน้าเป็นสัญญาณทางสายตาที่ช่วยให้ตรวจพบได้ง่ายว่ามี comma หายไปหรือไม่
SELECT
  employee_id,
  employee_name,
  job,
  salary
FROM employees;

ใช้ค่า dummy ใน WHERE

  • ใช้ค่า dummy ใน WHERE เพื่อเพิ่มหรือลบเงื่อนไขแบบไดนามิกได้
SELECT *
FROM employees
WHERE 1=1 -- 더미 값
  AND job IN ('Clerk', 'Manager')
  AND dept_no != 5;

จัดย่อหน้าโค้ดให้เหมาะสม

  • จัดย่อหน้าโค้ดเพื่อให้อ่านง่ายขึ้น และช่วยให้เพื่อนร่วมงานรวมถึงตัวเราในอนาคตเข้าใจโค้ดได้ง่าย
-- ตัวอย่างที่ไม่ดี:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

-- ตัวอย่างที่ดี:
SELECT
  timeslot_date,
  timeslot_channel,
  overnight_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) > 7,
    LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C7_fta_share,
  IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) >= 29,
    LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),
    NULL) AS C28_fta_share
FROM timeslot_data;

พิจารณาใช้ CTE เมื่อเขียนคิวรีที่ซับซ้อน

  • แทนที่จะซ้อน inline view หลายชั้น สามารถใช้ Common Table Expression (CTE) เพื่อเพิ่มความอ่านง่ายและความเป็นระเบียบของโค้ดได้
-- ใช้ inline view:
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM
  (SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id) AS cs
INNER JOIN
  (SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id) AS vhs
ON cs.movie_id = vhs.movie_id;

-- ใช้ CTE:
WITH cinema_sales AS (
  SELECT
    movie_id,
    SUM(ticket_sales) AS cinema_revenue
  FROM tickets
  GROUP BY movie_id
),
vhs_sales AS (
  SELECT
    movie,
    movie_id,
    SUM(revenue) AS vhs_revenue
  FROM blockbuster
  GROUP BY movie, movie_id
)
SELECT
  vhs.movie,
  vhs.vhs_revenue,
  cs.cinema_revenue
FROM cinema_sales AS cs
INNER JOIN vhs_sales AS vhs
ON cs.movie_id = vhs.movie_id;

ความสามารถที่มีประโยชน์

ใช้โอเปอเรเตอร์ :: เพื่อแปลงชนิดข้อมูล

  • ใน RDBMS บางตัว สามารถใช้โอเปอเรเตอร์ :: เพื่อแปลงค่าไปเป็นชนิดข้อมูลอื่นได้
SELECT CAST('5' AS INTEGER); -- ใช้ฟังก์ชัน CAST
SELECT '5'::INTEGER; -- ใช้ไวยากรณ์ ::

ใช้ anti join

  • anti join มีประโยชน์มากเมื่อเราต้องการคืนค่าเฉพาะแถวที่มีอยู่ในตารางหนึ่งแต่ไม่มีในอีกตารางหนึ่ง
  • แม้จะใช้ subquery ได้เช่นกัน แต่โดยทั่วไป anti join มักเร็วกว่า
-- anti join:
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

-- subquery:
SELECT
  *
FROM video_content
WHERE series_id NOT IN (SELECT DISTINCT series_id FROM archive);

-- correlated subquery:
SELECT
  *
FROM video_content
WHERE NOT EXISTS (
  SELECT 1
  FROM archive a
  WHERE a.series_id = vc.series_id
);

-- EXCEPT:
SELECT series_id
FROM video_content
EXCEPT
SELECT series_id
FROM archive;

ใช้ QUALIFY เพื่อกรอง window function

  • สามารถใช้ QUALIFY เพื่อกรองผลลัพธ์ของ window function ได้
  • มีประโยชน์ในการลดจำนวนบรรทัดของโค้ด
-- ใช้ QUALIFY:
SELECT
  product,
  market,
  SUM(revenue) AS market_revenue
FROM sales
GROUP BY product, market
QUALIFY DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) <= 10
ORDER BY product, market_revenue;

-- ไม่ใช้ QUALIFY:
SELECT
  product,
  market,
  market_revenue
FROM (
  SELECT
    product,
    market,
    SUM(revenue) AS market_revenue,
    DENSE_RANK() OVER (PARTITION BY product ORDER BY SUM(revenue) DESC) AS market_rank
  FROM sales
  GROUP BY product, market
)
WHERE market_rank <= 10
ORDER BY product, market_revenue;

สามารถใช้ GROUP BY ด้วยตำแหน่งคอลัมน์ได้

  • สามารถใช้ตำแหน่งคอลัมน์แทนชื่อคอลัมน์ใน GROUP BY หรือ ORDER BY ได้
  • มีประโยชน์กับคิวรีชั่วคราว แต่ในโค้ด production ควรอ้างอิงชื่อคอลัมน์เสมอ
SELECT
  dept_no,
  SUM(salary) AS dept_salary
FROM employees
GROUP BY 1 -- dept_no คือคอลัมน์แรกใน SELECT
ORDER BY 2 DESC;

ข้อควรระวังที่ควรหลีกเลี่ยง

ระวังเมื่อใช้ NOT IN ร่วมกับค่า NULL

  • NOT IN จะไม่ทำงานตามคาดเมื่อมีค่า NULL
  • ควรใช้ NOT EXISTS แทน
INSERT INTO departments (id)
VALUES (1), (2), (NULL);

-- ไม่ทำงานเพราะมีค่า NULL
SELECT *
FROM employees
WHERE department_id NOT IN (SELECT DISTINCT id FROM departments);

-- วิธีแก้
SELECT *
FROM employees e
WHERE NOT EXISTS (
  SELECT 1
  FROM departments d
  WHERE d.id = e.department_id
);

เปลี่ยนชื่อฟิลด์ที่คำนวณแล้วเพื่อหลีกเลี่ยงความกำกวม

  • หากตั้งชื่อฟิลด์ที่คำนวณแล้วซ้ำกับชื่อคอลัมน์เดิม อาจทำให้เกิดพฤติกรรมที่ไม่คาดคิด
INSERT INTO products (product, revenue)
VALUES ('Shark', 100), ('Robot', 150), ('Alien', 90);

-- window function จัดอันดับสินค้า 'Robot' เป็นอันดับ 1
SELECT
  product,
  CASE product WHEN 'Robot' THEN 0 ELSE revenue END AS revenue,
  RANK() OVER (ORDER BY revenue DESC)
FROM products;

ระบุให้ชัดว่าแต่ละคอลัมน์มาจากตารางใด

  • ในคิวรีที่ซับซ้อน การระบุให้ชัดว่าแต่ละคอลัมน์มาจากตารางใดจะช่วยให้ตามหาปัญหาได้ง่ายขึ้น
SELECT
  vc.video_id,
  vc.series_name,
  metadata.season,
  metadata.episode_number
FROM video_content AS vc
INNER JOIN video_metadata AS metadata
ON vc.video_id = metadata.video_id;

ทำความเข้าใจลำดับการประมวลผล

  • สำหรับผู้ที่กำลังเรียน SQL คำแนะนำที่สำคัญที่สุดคือการเข้าใจลำดับการประมวลผล
  • เมื่อเข้าใจลำดับนี้แล้ว วิธีเขียนคิวรีจะเปลี่ยนไปอย่างสิ้นเชิง

ใส่คอมเมนต์ในโค้ด

  • เวลาที่เขียนโค้ด ควรใส่คอมเมนต์อธิบายเหตุผลไว้ด้วย
  • เพื่อนร่วมงานและตัวเราในอนาคตจะขอบคุณมาก
SELECT
  video_content.*
FROM video_content
LEFT JOIN archive -- 새로운 CMS는 아카이브 비디오 형식을 처리할 수 없음
ON video_content.series_id = archive.series_id
WHERE archive.series_id IS NULL;

อ่านเอกสารทั้งหมด

  • การอ่านเอกสารอย่างครบถ้วนช่วยให้หลีกเลี่ยงปัญหาที่คาดไม่ถึงได้
  • ใช้เวลาเพียงไม่กี่นาที แต่ช่วยแก้ปัญหาที่ไม่คาดคิดได้มาก
-- หากอ่านเอกสารมากกว่านี้ก็จะแก้ปัญหา NULL ได้
SELECT COALESCE(GREATEST(signup_date, consumption_date), signup_date, consumption_date);

-- สามารถใช้ฟังก์ชัน GREATEST_IGNORE_NULLS ได้
SELECT GREATEST_IGNORE_NULLS(signup_date, consumption_date);

สรุปของ GN⁺

  • บทความนี้รวบรวมเคล็ดลับและเทคนิคหลากหลายอย่างสำหรับการเขียน SQL ให้มีประสิทธิภาพและอ่านง่ายขึ้น
  • ไม่ว่าจะเป็นผู้เริ่มต้นเรียน SQL หรือ data analyst ที่มีประสบการณ์ ก็มีข้อมูลที่เป็นประโยชน์อยู่มาก
  • โดยเฉพาะเรื่องการใช้ CTE กับคิวรีซับซ้อน การใช้ anti join และการใช้ QUALIFY ล้วนช่วยงานจริงได้มาก
  • การทำความเข้าใจลำดับการประมวลผลของ SQL การใส่คอมเมนต์ในโค้ด และการอ่านเอกสารอย่างละเอียด เป็นนิสัยที่สำคัญ
  • เครื่องมืออื่นที่มีความสามารถใกล้เคียงกัน เช่น PostgreSQL, MySQL, Oracle

บทความที่เกี่ยวข้อง

2 ความคิดเห็น

 
hiyama 2024-09-26

ในโพสต์นี้ เครื่องหมายจุลภาคนำหน้าทั้งหมดถูกเขียนเป็นเครื่องหมายจุลภาคตามหลังนะครับ ในต้นฉบับใส่ไว้เป็นแบบนำหน้า

-- Good:  
SELECT   
timeslot_date  
, timeslot_channel   
, overnight_fta_share  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt; 7, -- First argument of IFF.  
	LAG(overnight_fta_share, 1) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity), -- Second argument of IFF.  
		NULL) AS C7_fta_share -- Third argument of IFF.  
, IFF(DATEDIFF(DAY, timeslot_date, CURRENT_DATE()) &gt;= 29,   
		LAG(overnight_fta_share, 2) OVER (PARTITION BY timeslot_date, timeslot_channel ORDER BY timeslot_activity),   
			NULL) AS C28_fta_share  
FROM timeslot_data  
;
 
GN⁺ 2024-09-26
ความเห็นบน Hacker News

  • ความจำเป็นในการเข้าใจเซิร์ฟเวอร์ DB ให้ดีและตรวจสอบ query plan อยู่เสมอ

    • หลายกรณี EXISTS มักเร็วกว่า IN
    • NOT EXISTS และ EXCEPT ทำงานต่างกันในการจัดการค่า NULL
    • แนะนำให้ใช้คอลัมน์จาก subquery แทน table join
    • ควรหลีกเลี่ยง table scan และเพิ่มดัชนี
    • เมื่อต้องกรองด้วย expression สามารถใช้ computed column และดัชนีได้
    • UNION ALL อาจเร็วกว่า OR
    • สามารถบังคับลำดับการกรองได้ผ่าน subquery JOIN

  • เคล็ดลับสำหรับงาน stored procedure ที่ซับซ้อน

    • คัดลอกตารางถาวรมาเป็นตารางชั่วคราวและกรองเฉพาะแถวที่ต้องการ
    • จัดการตารางชั่วคราว
    • อัปเดตตารางถาวรภายใน transaction และ rollback หากเกิดข้อผิดพลาด
    • ต้องระวังเมื่อทำงานกับตารางระยะไกล โดยแนะนำให้คัดลอกมาไว้ในตารางชั่วคราวก่อนแล้วค่อยทำงาน
    • query plan อาจทำให้สับสนได้ จึงควรแยกงานออกเป็นขั้นตอนเล็ก ๆ
    • ตรวจสอบ query plan อยู่เสมอ

  • ความเห็นเกี่ยวกับความอ่านง่ายของโค้ด

    • สองตัวอย่างแรกแลกความอ่านง่ายกับความสะดวกในการเขียน
    • ตัวอย่างสุดท้ายการเยื้องไม่ได้ช่วยมากนัก

  • ข้อเสนอให้ใช้ไวยากรณ์แบบ FROM-first และ piping ของ SQL

    • ประสบการณ์ใช้งาน Kusto query language ถือว่าเป็นก้าวกระโดดครั้งใหญ่

  • เคล็ดลับเกี่ยวกับ Anti Join

    • แนะนำให้ใช้ EXISTS เพราะได้เปรียบเมื่อใช้ subquery ตามเงื่อนไขเพื่อตรวจสอบการมีอยู่ของแถว

  • ข้อดีของการใช้ comma นำหน้าในคำสั่ง SELECT

    • สามารถคอมเมนต์แต่ละบรรทัดได้
    • ช่วยให้อ่านง่ายขึ้นด้วยการจัดย่อหน้าโค้ด

  • ใน MSSQL แนะนำให้ใช้ /* */ แทน -- เมื่อต้องใส่คอมเมนต์

    • เพราะ query store จะบันทึกคิวรีโดยไม่มีการขึ้นบรรทัดใหม่

  • แนะนำให้ใช้ window function

  • ประเด็นถกเถียงเรื่องการใช้ 1=1 ใน WHERE clause

  • แนะนำ AI2sql

    • สามารถสร้าง SQL query จากพรอมป์ต์ภาษาอังกฤษแบบ plain text ได้
    • มีประโยชน์เมื่อเขียนคิวรีที่ซับซ้อน