CTE רקורסיבי ופונקציות החלון

22/07/2013

אין תגובות

CTE רקורסיבי הוא הכלי החביב עלי ביותר מתוך כל מה ש-SQL Server וחבורתו מציעים לנו: לא High Availability, לא File Stream, לא Variable Tables, ואפילו לא Service Broker. יתכן שאם תופיע מודעת דרושים בה נדרש מומחה ל-CTE רקורסיבי שיכתוב רקורסיות מהבוקר עד הערב – אני אתקשר ואשאל בנימוס כמה הם רוצים שאשלם להם כל חודש תמורת הזכות..
למרבה הצער השימוש בהם אינו נפוץ, הם מתאימים לבעיות מאוד ספציפיות, ועיקר שמושן הוא באתגרים טכניים שאני מאוד מחבב; אך כמו כל מיני חידות הגיון בסגנון של איך להעביר זאב, כבשה ואבטיח בסירה מבלי שהכבשה תתנגוס באבטיח ומבלי שהזאב יכרסם מעט את הכבשה; לא מדובר בבעיות מעשיות מהחיים.

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

With N As

(Select 1 Nm

Union All

Select  Nm+1

From    N

Where   Nm<100)

Select  *

From    N;

clip_image002_thumb

נוסיף לזה פונקציית חלון שתחשב את סכום כל המספרים, אבל בתוך ה-CTE; רק לפני שנריץ את זה ננסה לחשוב מה יקרה:

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

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

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

4. לא תחזור הודעת שגיאה, והמערכת תעשה בדיוק מה שצריך- תחזיר את הסכום של כל המספרים מ-1 ועד 100.

לא נחזיק את הקוראים במתח:

With    N As

(Select 1 Nm,

        Cast(1 As Int) N1

Union   All

Select  Nm+1,

        Cast(Sum(Nm+1) Over() As Int) N1

From    N

Where   Nm<100)

Select  *

From    N;

clip_image004_thumb

כפי שאפשר לראות – אפשרות 3 היא הנכונה: המערכת לכאורה מחזירה תשובה ומחשבת Sum של החלון (שבמקרה זה הוא כולל את כל הסט), אלא שהיא רואה רק את השורה השוטפת ואותה ניא מחזירה, וכך הערך שווה לסכום.

אם היינו ממקמים את פונקציית החלון מחוץ ל-CTE, כשהסט כבר מוכן ואינו "Under Construction" – אזי היא הייתה פועלת ללא דופי:

With N  As

(Select 1 Nm

Union   All

Select  Nm+1

From    N

Where   Nm<100)

Select  *,

        Cast(Sum(Nm+1) Over() As Int) N1

From    N;

clip_image006_thumb

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

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

מתי זה לא כך? הנה דוגמה עם עץ מוצר:

If Object_ID('T_BOM','U') Is Not Null Drop Table T_BOM;

 

Create Table T_BOM(ID Int Primary Key,

                   Shem Varchar(20) Not Null,

                   Muzar Int,

                   Constraint FK_BOM Foreign Key(Muzar) References T_BOM(ID));

 

Insert Into T_BOM Values(1,'כסא',Null);

Insert Into T_BOM Values(2,'רגל לכסא',1);

Insert Into T_BOM Values(3,'רגל גולמית לכסא',2);

Insert Into T_BOM Values(4,'גומי לרגל לכסא',2);

Insert Into T_BOM Values(5,'משענת לכסא',1);

Insert Into T_BOM Values(6,'מושב לכסא',1);

 

Insert Into T_BOM Values(7,'שולחן',Null);

Insert Into T_BOM Values(8,'רגל לשולחן',7);

Insert Into T_BOM Values(9,'רגל גולמית לשולחן',8);

Insert Into T_BOM Values(10,'גומי לרגל לשולחן',8);

Insert Into T_BOM Values(11,'פלטה לשולחן',7);

 

Insert Into T_BOM Values(12,'שרפרף',Null);

Insert Into T_BOM Values(13,'רגל לשרפרף',12);

Insert Into T_BOM Values(14,'רגל גולמית לשרפרף',13);

Insert Into T_BOM Values(15,'גומי לרגל לשרפרף',13);

Insert Into T_BOM Values(16,'מושב לשרפרף',12);

 

Select * From T_BOM;

clip_image008_thumb

הכסא מורכב מרגל של כסא, משענת של כסא, ומושב של כסא;

הרגל של הכסא מורכבת מרגל גולמית וגומי;

וכך הלאה לגבי שולחן ושרפרף.

אנחנו רוצים לשלוף רקורסיבית את הרכיבים של כל מוצר, תוך שהם ממוספרים,

כלומר- שלושת הרכיבים הישירים של הכסא ממוספרים 1,2,3;

ושלושת הרכיבים שלהרגל של הכסא ממוספרים 1,2;

וכך הלאה לגבי שולחן ושרפרף:

With T  As

(Select *,

        1 Lvl,

        Cast(1 As BigInt) Nm

From    T_BOM

Where   Muzar Is Null

Union   All

Select  T_BOM.*,

        T.Lvl+1,

        Row_Number() Over(Partition By T.ID Order By T_BOM.Muzar) Nm

From    T

Inner Join T_BOM

        On T.ID=T_BOM.Muzar)

Select  *

From    T;

clip_image010_thumb

עובד: בשורות 12-14 רואים את ששלושת הרכיבים הישירים של הכסא ממוספרים 1,2,3;

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

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *