השוואה לשורה הקודמת

ב-SQL Server בניגוד ל-Oracle לא קיימות הפונקציות Lag ו-Lead שמאפשרות להשוות שורה לקודמת לה, ולכן מי שמעוניין בהשוואה שכזו נאלץ ללכלך את ידיו ולכתוב SQL-ים מסובכים.
האתגר אופייני בעיקר לניתוח של נתונים לאורך זמן, למשל- השוואה בין שער המניה היום לשער המניה ביום המסחר הקודם, השוואה בין המכירות החודש למכירות בחודש הקודם (או בחודש המקביל אשתקד) וכו'.

פניתי ל-AdventureWorks לטבלה HumanResources.EmployeePayHistory ובדקתי ארבע שליפות שונות:
1. מספור השורות בעזרת CTE, ושליפת Left Join בין הטבלה לעצמה כך שלכל שורה תותאם הקודמת.
2. CTE רקורסיבי: מספור השורות, ולאחר מכן שליפה רקורסיבית בסדר עולה של הרשומות.
3. מספור השורות, ושליפה כך שהערך הקודם נשלף בנפרד בתוך ה-Select.
4. שליפה נוסטלגית בסגנון SQL 2000 ללא מספור השורות וללא CTE.

Use AdventureWorks;

Go

/*1*******************************************************************************************/

With EH As

(Select    Row_Number() Over(Partition By EmployeeId Order By RateChangeDate) Mispar,

        *

From    HumanResources.EmployeePayHistory)

Select    EH1.EmployeeID,

        EH1.RateChangeDate,

        EH2.Rate Rate_Previous,

        EH1.Rate Rate_Current

From    EH EH1

Left Join EH EH2

        On EH1.EmployeeID=EH2.EmployeeID

        And EH1.Mispar-1=EH2.Mispar

Order By EH1.EmployeeID,

        EH1.RateChangeDate;

/*2*******************************************************************************************/

With EH As

(Select    Row_Number() Over(Partition By EmployeeId Order By RateChangeDate) Mispar,

        *

From    HumanResources.EmployeePayHistory),

Eh1 As

(Select    EH.Mispar,

        EH.EmployeeID,

        EH.RateChangeDate,

        CAST(Null As Money) Rate_Previous,

        EH.Rate Rate_Current

From    EH

Where    Mispar=1

Union All

Select    EH.Mispar,

        EH.EmployeeID,

        EH.RateChangeDate,

        EH1.Rate_Current Rate_Previous,

        EH.Rate Rate_Current

From    EH1

Inner Join EH

        On EH1.EmployeeID=EH.EmployeeID

        And EH1.Mispar+1=EH.Mispar)

Select    EmployeeID,

        RateChangeDate,

        Rate_Previous,

        Rate_Current

From    EH1

Order By EmployeeID,

        RateChangeDate;

/*3*******************************************************************************************/

With EH As

(Select    Row_Number() Over(Partition By EmployeeId Order By RateChangeDate) Mispar,

        *

From    HumanResources.EmployeePayHistory)

Select    EmployeeID,

        RateChangeDate,

        (Select    Rate

        From    EH EH1

        Where    EH1.EmployeeId=EH.EmployeeId

                And EH1.Mispar+1=EH.Mispar) Rate_Previous,

        Rate Rate_Current

From    EH

Order By EmployeeID,

        RateChangeDate;

/*4*******************************************************************************************/

Select    EmployeeID,

        RateChangeDate,

        (Select    Rate

        From    HumanResources.EmployeePayHistory EH1

        Where    EH1.EmployeeId=EH.EmployeeId

                And EH1.RateChangeDate=(Select    Max(RateChangeDate) RateChangeDate

                                        From    HumanResources.EmployeePayHistory EH2

                                        Where    EH2.EmployeeId=EH1.EmployeeId

                                                And EH2.RateChangeDate<EH.RateChangeDate)) Rate_Previous,

        Rate Rate_Current

From    HumanResources.EmployeePayHistory EH

Order By EmployeeID,

        RateChangeDate;

לפי ה-Execution Plan שמתמחר כל שליפה באחוזים בהתאם להערכת העלות שלה בסך הכל -

1 היא הטובה ביותר,

4 היא השניה בטיבה,

2 היא השלישת,

3 הכי גרועה.

בדקתי את זמני ה-CPU (בשרת לא התבצע שום דבר משמעותי במקביל) בעזרת Set Statistics Time On תוך שאני מריץ מספר פעמים כדי לוודא שהתוצאות אינן מקריות, ואז השליפה הראשונה והשליפה הרביעית נמצאות יחד במקום הראשון, והשניה והשלישית מחליפות מקומות בדירוג..

מסקנות:

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

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

3. ה-Estimated Subtree Cost סובל מבעיות אמינות. יש הרבה מה ללמוד מה-Execution Plan, אבל ראוי לא לבנות יותר מדי על ה-Estimated Subtree Cost.