Advanced SQL - Common Table Expression (CTE)

רבים המתכנתים שמכירים את שפת SQL. רבים מהם אף חושבים שהם מכירים היטב את השפה. במאמר הזה אראה שיש בשפה הזו הרבה יותר ממה שרוב המתכנתים מכירים ושווה ללמוד עוד השפה.

SQL היא שפה הדומה לאנגלית שמאפשרת לבצע עיבוד מידע, מה שנקרא בשפת המתכנתים, ביצוע "שאילתות" על נתונים. יש גם חלקים נוספים בשפה, ביניהם חלק שמאפשר שמירה ועדכון נתונים בתוך בסיס הנתונים ועוד חלקים אחרים. מה שהכי חשוב להכיר בשפה הוא היכולת שלה לבצע "שאילתות".

כולם מכירים את השאילתות "הרגילות".

אבל בשפה יש אפשרות לבצע שאילתות רקורסיביות וזה דבר מאוד חזק ולא מוכר. שאילתות רקורסיביות מאפשרות למצוא מידע מתוך בסיס הנתונים שבלעדיהם היה צריך להביא את הנתונים עצמם לשרת עצמו ושם לבצע את החישובים (רק שזה יהיה בדר"כ יותר איטי). 

דוגמא לדברים שאפשר לעשות עם שאילתות רקורסיביות - להביא מידע מעץ של נתונים שנשמרים בטבלה מנורמלת שיש בה שלוש שדות: id, data, parent_id. שאילתא רקורסיבית יכולה לעבור מצומת מסויים למעלה, לכל האבות שלו (כל פעם עלייה אחת). עוד דוגמא היא לעבור על גרף של נתונים.

הכלי שמאפשר שאילתא רקורסיבית נקרא Common Table Expression או בקיצור CTE. נתחיל מהסבר בסיסי של הכלי ונגיע לשימוש בו לביצוע רקורסיות.

מה זה CTE

בצורה בסיסית, אפשר להגדיר Table Expression לשאילתא בצורה הבאה:

WITH CTE1(Address, Name, Age) --Column names for CTE, which are optional

AS

(

SELECT Addr.Address, Emp.Name, Emp.Age

FROM Address Addr INNER JOIN EMP Emp

ON Emp.id = Addr.id

)

SELECT * FROM CTE1

WHERE CTE1.Age > 50

ORDER BY CTE1.NAME

מה ש SQL יעשה הוא מאוד דומה להגדרת view זמני שחי רק בזמן הרצת השאילתא. כמו בדוגמא הבאה:

CREATE VIEW v AS

(

SELECT Addr.Address, Emp.Name, Emp.Age

FROM Address Addr INNER JOIN EMP Emp

ON Emp.id = Addr.id

)

SELECT * 

FROM v

WHERE CTE1.Age > 50

ORDER BY CTE1.NAME

DROP VIEW v

זה גם מאוד דומה לשימוש בתת שאילתה –

SELECT * 

FROM

(

SELECT Addr.Address, Emp.Name, Emp.Age

FROM  Address Addr Inner join Employee Emp

ON Emp.id = Addr.id

) Temp

WHERE Temp.Age > 50

ORDER BY Temp.NAME

מה שזה כן נותן הוא קריאות טובה יותר של השאילתא. במקום להכניס את תת השאילתא באמצע השאילתא הגדולה, מפרקים את השאילתא לחלקים.

אפשר גם לגדיר מספר CTEs:

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
   SELECT
      CategoryID,
      CategoryName,
      (SELECT COUNT(1) FROM Products p
       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
   FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
   SELECT
      ProductID,
      CategoryID,
      ProductName,
      UnitPrice
   FROM Products p
   WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
      p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
   INNER JOIN CategoryAndNumberOfProducts c ON
      p.CategoryID = c.CategoryID

ORDER BY ProductName

אפשר גם להשתמש ב CTE בתוך הגדרה של CTE אחר:

WITH x AS (SELECT * FROM MyTable),

y AS (SELECT * FROM x)

SELECT * 

FROM y

שימוש ב CTE לרקורסיה

עד כאן, מה שראינו מאפשר לכתוב שאילתות יותר ברורות לקריאה (כי מפרידים חלק מהשאילתא לאזור נפרד). עכשיו נראה את הערך המוסף האמיתי של CTE - רקורסיה. למעשה CTE מאפשר פעולה שדומה יותר ללולאה מסוג while.

כדי לבצע רקורסיה, יש לכתוב שאילתא בצורה הבאה:

WITH DepartmentCTE(DeptId, Department, Parent, Lvl)

AS (

      SELECT id AS DeptId,Department,parent,0 AS Lvl

      FROM Departments

      WHERE parent IS NULL

           

      UNION

           

      SELECT d.id AS DeptId,d.Department,d.parent,DepartmentCTE.Lvl+1 AS Lvl

      FROM Departments d

      INNER JOIN DepartmentCTE ON DepartmentCTE.DeptId = d.parent

)

SELECT *

FROM DepartmentCTE

ORDER BY parent;

החלק הראשון (האדום) של ה CTE הוא שאילתא המחזירה את השורה הראשונה בתוצאה של ה CTE. החלק השני (הכחול) ב CTE מתבצע שוב ושוב, בכל פעם על השורה האחרונה בתוצאה של ה CTE. הפעולה הזו תפסיק כאשר החלק השני כבר לא מוסיף עוד שורות.

אז מה אפשר לעשות ברקורסיה

נתחיל מדברים אזוטרים – שימוש ב CTE לחישוב עצרת:

WITH fact as (

      SELECT 1 as fac, 1 as num   -- first line

      UNION

      SELECT fac*(num+1), num+1   -- while loop that work each time on last line

      FROM fact

      WHERE num<12 font="">

)

SELECT fac

FROM fact

WHERE num=5

שימוש ב CTE ליצירת סדרה של מספרים 1 עד 100:

WITH numbers(n) AS

(

SELECT 1 FROM dual

           

UNION

           

SELECT n + 1

FROM numbers

WHERE n < 100

)

SELECT * FROM numbers;

ונעבור לשימושים יותר רציניים - סריקה של עץ עובדים הנשמר בטבלת Employees. כל שורה מכילה מידע על עובד ומזהה של העובד שמנהל אותו.

WITH Employees_CTE AS (      SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate      FROM HumanResources.Employee      WHERE ManagerID IS NULL      UNION      SELECT e.EmployeeID, e.ContactID, e.LoginID, e.ManagerID, e.Title, e.BirthDate      FROM HumanResources.Employee e      INNER JOIN Employees_CTE ecte ON ecte.EmployeeID = e.ManagerID
)

SELECT *
FROM Employees_CTE

במאמר הבא יש שימוש ב CTE לפתרון בעייה קלאסית במדעי המחשב – בעיית הסוכן הנוסע : קישור.