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


טיפים לטסט על אמבולנס במד"א

אורך הטסט הוא בערך 20-30 דקות בהם הטסטר נותן הוראות ימינה ושמאלה ולפעמים קצת יותר. נראה שהטסטר מחפש להבין שאתה לא נהג חסר אחריות.
הרכב עצמו הוא אוטומט. מוט ההילוכים הוא בהגה ודרכו אפשר לעבור בין המצבים (Drive, Reverse etc).
ההמברקס הוא דוושת הרגל השמאלית.
טיפיםהעצות בלשון זכר רק לצורך פשטות.
בתחילת הנסיעה:
אם אתה הראשון, תדליק אורות דרך.

בדוק איך מעבירים את ההילוך ל 3 בלי להסתכל בידית ההילוכים כי הטסטר עלול לבקש במהלך הנסיעה שתעביר להילוך שלישי.

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

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