Skip to main content

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 לפתרון בעייה קלאסית במדעי המחשב – בעיית הסוכן הנוסע : קישור.




Comments

Popular posts from this blog

Jenkins error: groovy.lang.MissingPropertyException

I tried to run groovy build step and got below error. This post will describe how I solved the problem.

Caught: groovy.lang.MissingPropertyException: No such property: hudson for class: script


SSL in pictures

Here is my summary on SSL (or as I like to call it 'SSL for dummies')