מציאת שתי שורות: לפני ואחרי נקודה נתונה

04/04/2015

אין תגובות

נתונה טבלה, נקודה מסויימת (תאריך, ערך כספי..), וצריך למצוא את השורה שלפניה ואת השורה שאחריה.
למשל- יש טבלה ובה עמודת תאריכים, וצריך למצוא את התאריך שלפני 01/04/2015 והתאריך שאחרי 01/04/2015.

image

במקרה זה- 25/03/2015, 02/04/2015.
כיצד נעשה זאת באופן כללי? מכיוון שמדובר בשתי השורות הראשונות בכפוף לתנאי כלשהו, האפשרות הראשונה העולה בדעתנו היא שימוש באופרטור Top, אלא שלא ברור מהיכן להתחיל לחפש: אין מדובר בשני הראשונים החל מ-01/04/2015, אלא החל מהתאריך שלפני.. אם כך, שתי הצעות ישומיות:

  1. נמצא את הראשון אחרי (בסדר עולה) וניעזר ב-Lag כדי למצוא את הקודם.
  2. נמצא את התאריך הראשון לפני (בסדר יורד), ובעזרתו את שני הראשונים (כלומר- Top 2 החל ממנו).

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

Select    *

Into    #T

From    sys.messages;

 

Alter Table #T Add Constraint PK_#T Primary Key Clustered(language_id, message_id);

ונבדוק את שתי האפשרויות:

Set Statistics IO On;

Set Statistics Time On;

Select    Top 1 *

From    (Select    *,

                Lag(message_id) Over(Partition By language_id Order By message_id) message_id_Lag

        From    #T

        Where    language_id=1028) T

Where    message_id>=6000

Order By message_id;

 

Select    Top 2 *

From    #T

Where    language_id=1028

        And message_id>=(Select    Top 1 message_id

                        From    #T

                        Where    language_id=1028

                                And message_id<6000

                        Order By message_id Desc)

Order By message_id;

Set Statistics Time Off;

Set Statistics IO Off;

image מבחינת התוצאות- הן לא חוזרות באותו מבנה: בראשונה הם בשתי עמודות שונות באותה רשומה,
ובשניה- בשתי רשומות שונות.
בהמשך אראה כיצד להגיע לאותו מבנה (אם זו הבעייה..).
הכתיבה של הראשונה עם הקינון של השאילתה נראית מסורבלת שלא לצורך, אולם אם לא מקננים- ה-Lag מחזיר Null כי הוא מתייחס רק לקטע החל מ-message_id=6000 ואינו “רואה” את מה שמחוצה לו, ולכן אין ברירה אלא לגרום לו לבצע Scan על כל language_id=1028, ולפלטר את message_id>=6000.
מה עושים אם כך כדי לגרום לו “ללכת ברוורס” צעד אחד מ-message_id=6000 ומשם להמשיך קדימה? זה בדיוק מה שעושה האופציה השניה..

image image מבחינת ה-Execution Plan – האפשרות הראשונה נראית טובה פי שניים מהשנייה, אולם המערכת כאן טועה, וכשבודקים את זמני הריצה ומספר ה-pages הנקראים – השנייה טובה בהרבה. אם מריצים כל אחת 100 פעם בעזרת Go 100 – זה מאשר שהשנייה מהירה יותר.
במקרה זה האפשרות הראשונה נאלצת לבצע Scan על כמה רבבות שורות (כתוב Seek אבל הכוונה היא שבעזרת Seek מגיעים לנקודת ההתחלה של ה-Scan), והשנייה – שני Seek-ים ממוקדם.

מה קורה אם התנאי הפוך- בתוך message_id נחפש את שני ה-language_id סביב נקודה נתונה? הרי מדובר בקבוצות קטנות יותר (10-20 language_id לכל message_id)..
ניצור מחדש את הטבלה אך עם Clustered Index בסדר הפוך מהקודם:

Drop Table #T;

Select    *

Into    #T

From    sys.messages;

 

Alter Table #T Add Constraint PK_#T Primary Key Clustered(message_id,language_id);

ונשווה את:

Set Statistics IO On;

Set Statistics Time On;

Select    Top 1 *

From    (Select    *,

                Lag(language_id) Over(Partition By message_id Order By language_id) language_id_Lag

        From    #T

        Where    message_id=6001) T

Where    language_id>=1034

Order By language_id;

 

Select    Top 2 *

From    #T

Where    message_id=6001

        And language_id>=(Select    Top 1 language_id

                        From    #T

                        Where    message_id=6001

                                And language_id<1034

                        Order By language_id Desc)

Order By language_id;

Set Statistics Time Off;

Set Statistics IO Off;

image
image

image

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

להשלמת התמונה: כל אחת מהשיטות מחזירה את התוצאות בפורמט אחר, לאו דווקא זה המועדף עלינו.
אם נרצה לקבל 2 שורות (כמו באפשרות השניה), נוכל בראשונה להשתמש באופרטור Values:

Select    OA.language_id

From    (Select    Top 1 *

        From    (Select    language_id,

                        Lag(language_id) Over(Partition By message_id Order By language_id) language_id_Lag

                From    #T

                Where    message_id=6001) T

        Where    language_id>=1034

        Order By language_id) T

Outer Apply (Select * From (Values(language_id),(language_id_Lag)) V(language_id)) OA;

image

ואם נרצה “לשטח” את השנייה – נשתמש ב-Group By:

Select    Min(language_id) language_id_Lag,

        Max(language_id) language_id

From    (Select    Top 2 *

        From    #T

        Where    message_id=6001

                And language_id>=(Select    Top 1 language_id

                                From    #T

                                Where    message_id=6001

                                        And language_id<1034

                                Order By language_id Desc)

        Order By language_id) T;

image

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

כתיבת תגובה

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