ביצוע Lag (או Lead) תוך פניה אחת לטבלה

17/07/2011

אין תגובות

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

פעולה די טריוויאלית בסביבה עסקית, אולם בכלים הקיימים כיום ב-TSQL – יש לבצע שליפות מסובכות יחסית ולא כל כך יעילות הכוללות מספר פניות לטבלה (למשל- Join של הטבלה עם עצמה כך שלכל רשומה מהטבלה האחת מותאמת הרשומה הקודמת מהטבלה השנייה).

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

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

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

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

Use AdventureWorks;

With CR As

(Select Row_Number() Over(Partition By ToCurrencyCode Order By CurrencyRateDate) Mispar,

        *

From    Sales.CurrencyRate)

Select  CR1.ToCurrencyCode,

        CR1.AverageRate [Current],

        CR2.AverageRate Previous

From    CR CR1

Left Join CR CR2

        On CR1.ToCurrencyCode=CR2.ToCurrencyCode

        And CR1.Mispar=CR2.Mispar+1

Order By CR1.ToCurrencyCode,

        CR1.Mispar;

clip_image002

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

וכעת ל"תרגיל" (כשהייתי ילד היו אומרים "קונץ"):

נמספר את הרשומות כמו בדוגמה הקודמת,

ונוסיף שתי עמודות – באחת נחלק את המספר ב-2,

ובשניה נחלק את (המספר ועוד 1) ב-2:

clip_image004

שני טורי החלוקה מאפשרים לחלק את הרשומות לצמדים-

הראשונה של מספר זוגי והאי זוגי שאחריו,

והשנייה של מספר אי זוגי והזוגי שאחריו.

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

ואם בעמודת המספר יש מספר אי זוגי – נבחר את שער החליפין לפי העמודה הראשונה:

With CR As

(Select Row_Number() Over(Partition By ToCurrencyCode Order By CurrencyRateDate) Mispar,

        *

From    Sales.CurrencyRate),

T As

(Select Mispar/2 Mispar1,

        (Mispar+1)/2 Mispar2,

        *

From    CR)

Select  ToCurrencyCode,

        AverageRate [Current],

        Case When Mispar1<>Mispar2 Then Max(Case When Mispar2=Mispar1 Then 

                AverageRate End) Over(Partition By ToCurrencyCode,Mispar1)

            Else 

                Max(Case When Mispar2>Mispar1 Then AverageRate End) Over(Partition By ToCurrencyCode,Mispar2) 

            End Previous

From T

Order By ToCurrencyCode,

        Mispar;

clip_image006

מה לגבי היעילות?

נתייעץ עם האופטימייזר ונבקש Execution Plan:

clip_image008

למרבה ההפתעה גם הצלחנו לפנות פעם אחת לטבלה (שימו לב שבשליפה הראשונה יש שני Clustered Index Scan ובשניה רק אחד),

והתוצאות טובות יותר (אם כי השליפה הראשונה לא התיימרה להיות היעילה ביותר האפשרית).

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

כתיבת תגובה

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