Common Table Expressions

การจัดการข้อมูลใน SQL

Mona Khalil

Data Scientist, Greenhouse Software

เมื่อเพิ่ม Subquery...

คิวรีซับซ้อนขึ้นอย่างรวดเร็ว!
- ติดตามข้อมูลได้ยากขึ้น

วิธีแก้: Common Table Expressions!

Common Table Expressions

Common Table Expressions (CTEs)

ตารางที่ประกาศก่อน Query หลัก
ตั้งชื่อและอ้างอิงในคำสั่ง FROM ภายหลัง

การตั้งค่า CTEs

WITH cte AS (
    SELECT col1, col2
    FROM table)

SELECT
    AVG(col1) AS avg_col
FROM cte;

ใช้ Subquery ใน FROM

SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10) AS s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

วางไว้ที่จุดเริ่มต้น

(
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

วางไว้ที่จุดเริ่มต้น

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)

ดู CTE ในการใช้งานจริง

WITH s AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10
)
SELECT
  c.name AS country,
  COUNT(s.id) AS matches
FROM country AS c
INNER JOIN s
ON c.id = s.country_id
GROUP BY country;

| country     | matches |
|-------------|---------|
| England     | 3       |
| Germany     | 1       |
| Netherlands | 1       |
| Spain       | 4       |

ดู CTE ทั้งหมดในการใช้งาน

WITH s1 AS (
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) >= 10),
s2 AS (                              -- New subquery
  SELECT country_id, id 
  FROM match
  WHERE (home_goal + away_goal) <= 1
)
SELECT
  c.name AS country,
  COUNT(s1.id) AS high_scores,
  COUNT(s2.id) AS low_scores         -- New column
FROM country AS c
INNER JOIN s1
ON c.id = s1.country_id
INNER JOIN s2                        -- New join
ON c.id = s2.country_id
GROUP BY country;

ทำไมต้องใช้ CTEs?

รันเพียงครั้งเดียว
- CTE จะถูกเก็บไว้ในหน่วยความจำ
- ช่วยเพิ่มประสิทธิภาพของคิวรี
ช่วยจัดระเบียบคิวรีได้ดีขึ้น
อ้างอิง CTE อื่นได้
อ้างอิงตัวเองได้ (SELF JOIN)

มาฝึกกันเถอะ!

การจัดการข้อมูลใน SQL