הבלוג של גרי רשף

16/11/2011

כיצד מוצאים מי עשה לנו סיפתח

Filed under: Uncategorized — גרי רשף @ 21:12

סיפתח לפי המילון העולמי לעברית מדוברת (דב"א ובן יהודה, ואין הכוונה לא ל-DBA ולא לאליעזר בן-יהודה) הוא המכירה הראשונה בבסטה בשוק, שמשולה ליורה הפותח את עונת הגשמים.
אנחנו רוצים למצוא מי הלקוח הראשון שקנה אצלנו בכל יום, ומשימה כדוגמת זו גורמת לרבים להסתבך: ברור שיש כאן שימוש ב-Group By לפי יום, וגם Min על משהו שכן מדובר במכירה הראשונה באותו היום. אלא מה- אם נעשה Min על השעה או המונה או מספר ההזמנה, אז היכן הלקוח? ואם נעשה Min על הלקוח – לא נקבל את הלקוח הראשון שנכנס לחנות אלא את הראשון לפי סדר הא"ב..

בימי גרסת 2000 העליזים היינו פותרים זאת כך:

Use AdventureWorksDWDenali;
Go

Select  F.OrderDate,
        F.CustomerKey
From    FactInternetSales F
Inner Join (Select OrderDate,
                Min(SalesOrderNumber) SalesOrderNumber
        From    FactInternetSales
        Where   SalesOrderLineNumber=1
        Group By OrderDate) T
        On F.OrderDate=T.OrderDate
        And F.SalesOrderNumber=T.SalesOrderNumber
Where   SalesOrderLineNumber=1
Order By F.OrderDate;

(מתנצל- בדיעבד גיליתי שיש הבדלים בין גרסאות ה-AdventureWorksDW השונות ובזו של Denali מופיעים התאריכים במפורש ולא הקודים שמפנים לטבלת מימד הזמן).

בהתחלה מבצעים Group By לפי תאריך ומוצאים את מספר ההזמנה הראשון לכל יום,

ולאחר מכן על ידי Join מוצאים את השורה כולה עם קוד הצרכן של אותה הזמנה.

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

מגרסת 2005 ניתן להשתמש ב-Row_Number – ממספרים את השורות של כל יום בסדר עולה של מספרי הזמנה, ושולפים את כל הראשונות (מספר שורה = 1):

Select  OrderDate,
        CustomerKey
From    (Select Row_Number() Over(Partition By OrderDate Order By SalesOrderNumber) N,
                *
        From    FactInternetSales
        Where   SalesOrderLineNumber=1) F
Where   N=1
Order By OrderDate;

מגרסת 2012 מתווספת אופציה חדשה- ניתן להיעזר בפונקציית החלון החדשה First_Value שעושה בדיוק את מה שבלבל אותנו בהתחלה עם ה-Min ויכולה למצוא את הערך בעמודה אחת (לקוח) כשהערך בעמודה השניה (מספר הזמנה) מינימלי:

Select  Distinct OrderDate,
        First_Value(CustomerKey) Over(Partition By OrderDate Order By SalesOrderNumber) SalesOrderNumber
From    FactInternetSales
Where   SalesOrderLineNumber=1
Order By OrderDate;

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

מה לגבי ביצועים? ה-Execution Plan ממליץ בשלושת המקרים על יצירת אינדקס, ואנחנו מצייתים:

Create  NonClustered Index Idx_FactInternetSale
        On FactInternetSales (SalesOrderLineNumber)
        Include (CustomerKey,SalesOrderNumber,OrderDate);
Go

clip_image002

(הקלקה על צילום המסך לקבלת קובץ המקור)

הפתעה: מתברר שדווקא הדרך המסורבלת של 2000 היא היעילה ביותר, ואילו זו Denali (2012) היא הפחות יעילה..

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

בנוסף- שיטת 2012 אינה מאפשרת לקבל את כל השורה, ולשם כך נצטרך להשתמש ב-First_Value לכל אחת מהעמודות או לבצע Join כמו בגרסת 2000.

טוב לזכור זאת, וגם למחוק את האינדקס אם לא נזדקק לו יותר:

Drop Index Idx_FactInternetSale On FactInternetSales;
Go
מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: