CTE ב־SQL: מדריך מלא עם דוגמאות

איך ליצור CTE ב-SQL

כאשר יוצרים CTE, אנו משתמשים במילת המפתח WITH כדי להפעיל את תיאור ה-CTE. תחביר הכללי של CTE הוא כדלקמן:

WITH cte_name (column1, column2, ...)
AS (
    -- שאילתה שמגדירה את ה-CTE
    SELECT ...
    FROM ...
    WHERE ...
)
-- שאילתה ראשית
SELECT ...
FROM cte_name;

איפה:

• WITH: מתחיל את ההגדרה של CTE, מציין שהשם שיבוא אחריו מייצג סט תוצאות זמני.

• cte_name: השם מוקצה ל-CTE כדי להתייחס אליו בשאילתת הראשית.

• רשימת עמודות אופציונלית (column1, column2, …): מציין את שמות העמודות עבור סט התוצאות של ה-CTE. זה שימושי כאשר שמות העמודות צריכים להתאים.

• שאילתה שמגדירה את ה-CTE: השאילתה הפנימית שבוחרת נתונים ומעצבת את סט התוצאות הזמני.

• שאילתה ראשית: מתייחסת ל-CTE לפי שמו, ומשתמשת בו כמו בטבלה.

בואו נסתכל על הדוגמה הבאה ליצירת CTE באמצעות גישה בשכבות. נניח שיש לנו טבלה Employees, ורוצים ליצור CTE שמבצעת בחירה של עובדים שמרוויחים משכורת מעל 50,000 דולר.

שלב 1: כתיבת השאילתה הבסיסית

אנו מתחילים על ידי כתיבת השאילתה הבסיסית SELECT:

SELECT EmployeeID, FirstName, LastName, Salary
FROM Employees
WHERE Salary > 50000;

שלב 2: עטיפת השאילתה באמצעות המילה המפתחית WITH כדי ליצור CTE

משתמשים במילת המפתח WITH כדי לתת שם ל-CTE.

WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)

שלב 3: שימוש ב-CTE בשאילתה הראשית

לבסוף, מתייחסים ל-CTE במשפט SELECT על ידי קריאה לשם ה-CTE שהוגדר מראש.

-- הגדרת ביטוי של טבלת שאילתה משותפת (CTE)
WITH HighEarningEmployees AS (
    SELECT EmployeeID, FirstName, LastName, Salary
    FROM Employees
    WHERE Salary > 50000
)
-- שימוש ב-CTE כדי לבחור עובדים שמרוויחים הרבה
SELECT EmployeeID, FirstName, LastName
FROM HighEarningEmployees;

כדי לסכם את השלבים שלמעלה, השתמשנו במילת המפתח WITH כדי להגדיר את ה-CTE בשם HighEarningEmployees. השאילתה הפנימית שימשה ליצירת קבוצת נתונים זמנית. השאילתה הראשית מתייחסת ל-HighEarningEmployees כדי להציג את העמודות המצוינות EmployeeID, FirstName, ו-LastName.

למה CTEs ב-SQL הן שימושיים

מההמשל הנ"ל, תוכלו להתעצבן למה אנו משתמשים ב-CTEs כאשר פונקציות פשוטות יכולות להוביל לתוצאות זהות. הסיבות הבאות הן:

פשיטת ראש שאילתות מורכבות

CTEs פורקים את הצהרות ה-SQL המורכבות לחלקים קטנים יותר וניתנים לניהול, מה שהופך את הקוד לקריא, לכתיב ולתחזק. 

נניח שיש לנו שלוש טבלאות: הזמנות, לקוחות ו-מוצרים. אנו רוצים למצוא את הרווח הכולל שנוצר על ידי כל לקוח שקנה ב-2024. כאשר אנו כותבים את השאילתא בלי להשתמש ב-CTE, היא נראית מסורבלת וקשה לקריאה והבנה.

-- בחר שמות לקוח ורווח כולל מההזמנות שלהם
SELECT c.CustomerName, SUM(p.Price * o.Quantity) AS TotalRevenue
FROM Orders o
-- הצטרף כדי לקבל טבלת לקוחות ומוצרים
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID
WHERE YEAR(o.OrderDate) = 2024
GROUP BY c.CustomerName
HAVING SUM(p.Price * o.Quantity) > 1000;

על ידי שימוש ב-CTE, אנו יכולים להפריד את הלוגיקה לפורמט קריא יותר:

-- הגדר את ה-CTE
WITH OrderDetails AS (
    SELECT o.OrderID, c.CustomerName, p.Price, o.Quantity, o.OrderDate
    FROM Orders o
    JOIN Customers c ON o.CustomerID = c.CustomerID
    JOIN Products p ON o.ProductID = p.ProductID
    WHERE YEAR(o.OrderDate) = 2024
)
-- שאילתא ראשית
SELECT CustomerName, SUM(Price * Quantity) AS TotalRevenue
FROM OrderDetails
GROUP BY CustomerName
HAVING SUM(Price * Quantity) > 1000;

שימושיות בקוד

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

נניח שצריך לחשב את ממוצע וסכום המכירות לכל קטגורית מוצר במסד הנתונים של מסחר אלקטרוני. נוכל להשתמש ב-CTE כדי להגדיר את החישובים פעם אחת ולהשתמש בהם שוב בשאילתות עתידיות.

-- הגדרת CTE כדי לחשב את סך המכירות והממוצע לכל קטגוריה
WITH CategorySales AS (
    SELECT Category, SUM(SalesAmount) AS TotalSales, AVG(SalesAmount) AS AverageSales
    FROM Products
    GROUP BY Category
)
-- בחירת קטגוריה, סך המכירות, והממוצע מה-CTE
SELECT Category, TotalSales, AverageSales
FROM CategorySales
WHERE TotalSales > 5000;

יישומים אחרים

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

• ארגון ונגישות שאילתות: CTEs משפרים את נגישות קוד ה- SQL על ידי חלוקת השאילתות לשלבים לוגיים ורצופים. כל שלב בתהליך השאילתה יכול להיות מיוצג על ידי CTE עצמאי, מה שהופך את כל השאילתה לנגישה יותר למעקב אחריה.
• ניתוב נתונים היררכי: CTEs יכולים לעזור בניווט ביחסים היררכיים, כגון מבנה ארגוני, יחסי הורה-ילד, או כל דגם נתונים שמעורבב ברמות מקוננות. CTEs רקורסיביים שימושיים לשאילתת נתונים היררכיים מאחר שהם מאפשרים לך לעבור בין רמות באופן רקורסיבי.
• כינונים מרובים ברמה מרובה: CTEs יכולים לעזור לבצע כינונים ברמות שונות, כגון חישוב תרשימי מכירות ברמות שונות (לדוגמה, לפי חודש, רבעון ושנה). שימוש ב-CTEs להפרדת שלבי הכינון מבטיח שכל רמה תחושב באופן עצמאי ולוגי.
• שילוב נתונים ממספר טבלאות: ניתן להשתמש ב-CTEs מרובים כדי לשלב נתונים מטבלאות שונות, עשוי להפוך את שלב השלבול הסופי למובנה יותר. גישה זו מפשטת חיבורים מורכבים ומבטיחה שהנתונים המקוריים מאורגנים לוגית לקריאות משופרת.

טכניקות CTE מתקדמות ב-SQL

CTEs תומכות בטכניקות SQL מתקדמות, ומהן רב תכליתיות ושימושיות למגוון מקרים שונים. הנה כמה מיישומי ה-CTEs המתקדמים.

CTEs מרובים בשאילתה אחת

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

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

WITH ProductSales AS (
    -- שלב 1: חשב את סך המכירות עבור כל מוצר
    SELECT ProductID, SUM(SalesAmount) AS TotalSales
    FROM Sales
    GROUP BY ProductID
),
AverageSales AS (
    -- שלב 2: חשב את ממוצע סך המכירות בכל המוצרים
    SELECT AVG(TotalSales) AS AverageTotalSales
    FROM ProductSales
),
HighSalesProducts AS (
    -- שלב 3: סנן מוצרים עם סך מכירות מעל הממוצע
    SELECT ProductID, TotalSales
    FROM ProductSales
    WHERE TotalSales > (SELECT AverageTotalSales FROM AverageSales)
)
-- שלב 4: דרג את המוצרים עם המכירות הגבוהות
SELECT ProductID, TotalSales, RANK() OVER (ORDER BY TotalSales DESC) AS SalesRank
FROM HighSalesProducts;

בדוגמה למעלה;

• ה-CTE הראשון (ProductSales) מחשב את סך המכירות לכל מוצר.

• ה-CTE השני (AverageSales) מחשב את הממוצע של סך המכירות הכולל של כל המוצרים.

• ה-CTE השלישי (HighSalesProducts) מסנן מוצרים שסך מכירותיהם עולה על הממוצע.

• השאילתה הסופית מדרגת את המוצרים הללו בהתבסס על סך המכירות הכולל שלהם.

CTEs בהצהרות UPDATE, DELETE, ו-MERGE

כאשר מוכנסות לפעולות UPDATE, DELETE, ו־MERGE, CTEs יכולות לפשט משימות של עיבוד נתונים, במיוחד כאשר מתמודדים עם סינונים מורכבים או נתונים היררכיים.

שימוש ב-CTE עם הצהרת UPDATE

נניח שיש לנו טבלת Employees עם עמודה של EmployeeSalary. אנו רוצים להעלות שכר של 10% לכל העובדים שעבדו בחברה למעלה מ-5 שנים.

-- הגדרת CTE למציאת עובדים שהועסקו לפני יותר מ-5 שנים
WITH LongTermEmployees AS (
    SELECT EmployeeID
    FROM Employees
    WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 5
)
-- עדכון שכרים ב-10% עבור העובדים הארוכי טווח המזוהים ב־CTE
UPDATE Employees
SET EmployeeSalary = EmployeeSalary * 1.1
WHERE EmployeeID IN (SELECT EmployeeID FROM LongTermEmployees);

ה-CTE LongTermEmployees מזהה עובדים שעבדו יותר מחמש שנים. ההצהרת UPDATE משתמשת ב-CTE זו כדי להעלות באופן בselective את השכרים.

שימוש ב-CTE עם הצהרת DELETE

כעת נניח שיש לנו טבלה בשם Products ורוצים למחוק את כל המוצרים שלא נמכרו ב-2 השנים האחרונות. נוכל להשתמש ב-CTE כדי לסנן את המוצרים:

-- הגדרת CTE כדי לזהות מוצרים שלא נמכרו ב-2 השנים האחרונות
WITH OldProducts AS (
    SELECT ProductID
    FROM Products
    -- שימוש ב-DATEADD כדי למצוא מוצרים שהתמכרו לפני יותר מ-2 שנים
    WHERE LastSoldDate < DATEADD(YEAR, -2, GETDATE())
)
-- מחיקת מוצרים שזוהו כישנים מהטבלה הראשית
DELETE FROM Products
WHERE ProductID IN (SELECT ProductID FROM OldProducts);

ה-CTE OldProducts מזהה מוצרים שלא נמכרו ב-שנתיים האחרונות, ולאחר מכן ההצהרה DELETE משתמשת ב-CTE זו כדי להסיר את המוצרים האלה.

שימוש ב-CTE עם הצהרת MERGE

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

-- CTE למיזוג נתוני מלאי חדשים וקיימים
WITH MergedInventory AS (
    SELECT ni.ProductID, ni.Quantity AS NewQuantity, i.Quantity AS CurrentQuantity
    FROM NewInventoryData ni
    -- השתמש ב-LEFT JOIN כדי לכלול את כל הנתונים החדשים, גם אם אינם במלאי הנוכחי
    LEFT JOIN Inventory i ON ni.ProductID = i.ProductID
)
-- מזג את הנתונים שהוכנסו לטבלת המלאי
MERGE INTO Inventory AS i
USING MergedInventory AS mi
ON i.ProductID = mi.ProductID
-- עדכן מוצרים קיימים עם כמויות חדשות
WHEN MATCHED THEN
    UPDATE SET i.Quantity = mi.NewQuantity
-- הכנס מוצרים חדשים אם הם אינם קיימים במלאי
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ProductID, Quantity) VALUES (mi.ProductID, mi.NewQuantity);

Common Table Expressions רקורסיביות (CTEs)

CTEs רקורסיבי מסייעות בביצוע פעולות מתקדמות וחוזרות.

מבוא ל-CTEs רקורסיביות

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

חבר עוגן וחבר רקורסיבי

CTE רקורסיבית מורכבת משני חלקים עיקריים:

• חבר עוגן: החלק שמגדיר את השאילתה הבסיסית שמתחילה את הרקורסיה.
• חבר רקורסיבי: החלק שמתייחס ל-CTE עצמו, מאפשר לו לבצע פעולות "רקורסיביות".

נניח שיש לנו טבלת Employees, כאשר כל שורה מכילה EmployeeID, EmployeeName, ו־ManagerID. אם רוצים למצוא את כל הדיווחים הישירים והעקיפים עבור מנהל מסוים, אנו מתחילים עם החבר העוגן שמזהה את המנהל ברמת הראש. החבר העוגן מתחיל עם העובד עם EmployeeID = 1.

החבר הרקורסיבי מוצא עובדים שמנהלם באמצעות ManagerID התואם ל־EmployeeID מהאיטרציה הקודמת. כל איטרציה מביאה את הרמה הבאה ביררכיה.

WITH EmployeeHierarchy AS (
    -- חבר עוגן: בחר במנהל רמת הראש
    SELECT EmployeeID, EmployeeName, ManagerID, 1 AS Level
    FROM Employees
    WHERE EmployeeID = 1  -- מתחילים עם המנהל ברמת הראש
    UNION ALL
    -- חבר רקורסיבי: מצא עובדים שדיווחו למנהלים הנוכחיים
    SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
    FROM Employees e
    INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, Level
FROM EmployeeHierarchy;

בעיות פוטנציאליות או הגבלות של CTEs ב־SQL

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

הגבלות ב־SQL Server וב־Azure

קיימות הגבלות ספציפיות לסביבת העבודה של CTEs בעבודה עם SQL Server או Azure Synapse Analytics. ביניהן:

• SQL Server: רמת הרקורסיה המקסימלית ברירת המחדל עבור CTEs רקורסיביות היא 100, וניתן לשנות אותה באמצעות הרמז OPTION (MAXRECURSION). אם המגבלה הזו נחרגת בלעדי תיקון, תתרחש שגיאה. CTEs לא יכולות להיות מקוננות ישירות זו בתוך זו או מוגדרות בתוך CTE אחרת.

• ניתוח סינאפס של אזור: CTE מקבלת תמיכה מוגבלת בפעולות SQL מסוימות כמו INSERT, UPDATE, DELETE ו-MERGE. בנוסף, CTE רקורסיביים לא נתמכים בסביבות הענן של Azure Synapse Analytics, מה שמגביל את היכולת לבצע פעולות מסוימות על נתונים היררכיים.

אם נתקלת בעצמך בעבודה עם SQL Server, ידוע ש־DataCamp מציעה מקורות רבים לעזור. להתחלה, אני ממליץ לקחת את הקורס Introduction to SQL Server של DataCamp כדי להתמקם ביסודות של SQL Server לצורך ניתוח נתונים. ניתן לנסות את המסלול המקצועי SQL Server Developer, שמכסה הכל מעסקאות וטיפול בשגיאות עד ניתוח סדרות זמניות. קורס ה־Hierarchical and Recursive Queries in SQL Server שלנו נכנס ישירות ללב של כיצד לכתוב שאילתות מתקדמות ב־SQL Server, כולל שיטות הכוללות CTEs.

בעיות אפשריות אחרות

אף על פי ש-CTEs שימושיים לפשט שאילתות מורכבות, ישנם כמה תחנות נפוצות שעליך להכיר. כוללות את הבאות:

• לולאות אינסופיות ב-CTEs רקורסיביות: אם תנאי הסיום ל-CTE רקורסיבית לא נעמד, יכול להתקבל לולאה אינסופית, שגורמת לשאילתה לרוץ ללא הפסקה. כדי למנוע מה-CTE הרקורסיבית לרוץ לאינסופיות, עליך להשתמש ברמז OPTION (MAXRECURSION N) כדי להגביל את מספר האיטרציות הרקורסיביות המרבי, כאשר N הוא המגבלה שצוינה.

• שיקולי ביצועים: CTE רקורסיביים עשויים להצטמצם מאוד אם עומק הרקורסיה גבוה או אם נתונים גדולים מעובדים. כדי למקסם את הביצועים, כדאי להגביל את הנתונים שנעבדים בכל איטרציה ולוודא סינון מתאים כדי למנוע רמות רקורסיה יתר.

מתי להשתמש ב-CTE נגד טכניקות אחרות

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

מסקנה

לשלוט ב-CTEs דורש אימון, כמו בכל דבר: אני ממליץ לנסות את קו הקריירה של DataCamp מנתח נתונים משווק ב-SQL כדי להפוך לניתן דרך בניתוח נתונים. הקורס דיווחים ב-SQL יסייע גם לך להפוך למיומן בבניית דוחות מורכבים ולוחות מחוונים להצגת נתונים יעילה. לבסוף, עליך לקבל אישור מומחה ב-SQL כדי להציג את המיומנות שלך בשימוש ב-SQL לפתור בעיות עסקיות ולהדגיש בין מקצוענים אחרים.