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

22/08/2011

פונקציות החלון החדשות והמחודשות ב-SQL Server 2011 Denali

Filed under: Uncategorized — גרי רשף @ 20:50

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

הבשורה המשמחת ביותר מבחינתי היא הפונקציות Lag & Lead המאפשרות פניה לרשומות קודמות ועוקבות, כשלהן מתווספות First_Value & Last_ Value שמאפשרות להציג את הרשומה הראשונה והאחרונה בתחום.
פונקציות אלו יכולות להיות שימושיות כשמציגים שערי מט"ח תוך ציון שיעור השינוי היומי (המחושב על סמך הערך הנוכחי והערך הקודם) והשער בתחילת התקופה, למשל:

Use AdventureWorks;
Go

Select  ToCurrencyCode,
        AverageRate,
        CurrencyRateDate,
        Lag(AverageRate,1,Null) Over(Partition By ToCurrencyCode Order By CurrencyRateDate) PreviousRate,
        First_Value(AverageRate) Over(Partition By ToCurrencyCode,Year(CurrencyRateDate),Month(CurrencyRateDate) Order By CurrencyRateDate) FirstValue,
        Last_Value(AverageRate) Over(Partition By ToCurrencyCode,Year(CurrencyRateDate),Month(CurrencyRateDate) Order By CurrencyRateDate) LastValue
From    Sales.CurrencyRate
Order By ToCurrencyCode,
        CurrencyRateDate;
Go

clip_image002

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

1. שם העמודה שאת הערך הקודם שלה יש להציג.

2. כמה שלבים קודם יש לבדוק (בדרך כלל 1 אבל אפשר גם אחרת).

3. ערך חלופי אם אין ערך קודם (לרוב נבחר ב-Null או ב-0 אם ברצוננו לערוך חישובים).

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

פונקציית First_Value מקבלת את שם העמודה שאת הערך הראשון שלה יש להציג, וכן את אופן חלוקת הסט (Partition By) והמיון שלו (Order By) כדי להגדיר מי הראשון. בדוגמה זו שער החליפין הראשון של הדולר מול האירו הוא 1.1074 באפריל 2007, ו-1.0983 במאי 2007 (כי זה השער ב-01/05/2007).

פונקציות Lead (הערך הבא) ו-Last_Value פועלות באופן דומה לשתי הנ"ל, אם כי מסיבה לא ברורה (Feature? Bug?) פונקציית Last_Value מציגה את הערך השוטף (שהוא האחרון עד כה..) ולא את האחרון בתחום.

לפונקציות Lag ו-Lead יש שימושים מעניינים רבים ועוד אעסוק בכך בעתיד.

ארבע פונקציות חלון סטטיסטיות חדשות פחות מוצלחות לטעמי הן Percent_Rank, Cume_Dist, Percentile_Cont, Percentile_Disc.

ניצור טבלת מכירות להדגמה:

Use Tempdb;
Go

Create Table T_Mehirot(Moher Varchar(10),
                    Taarih DateTime,
                    Camut Int);
Go

Insert
Into T_Mehirot
Values  ('Hadas','20110801',15),
        ('Hadas','20110805',10),
        ('Yoram','20110802',10),
        ('Yoram','20110808',20),
        ('Yoram','20110812',10),
        ('Yoram','20110815',5),
        ('Yoram','20110819',15);
Go

Select  Percent_Rank() Over(Partition By Moher Order By Taarih) [Percent_Rank],
        Cume_Dist() Over(Partition By Moher Order By Taarih) [Cume_Dist],
        Percentile_Cont(0.5) Within Group(Order By Camut) Over (Partition By Moher) [Percentile_Cont],
        Percentile_Disc(0.5) Within Group(Order By Camut) Over (Partition By Moher) [Percentile_Disc],
        *
From    T_Mehirot
Order By Moher,
        Taarih;
Go

clip_image004

Percent_Rank– פונקציה זו ממספרת באחוזים מצטברים (המוצגים כשבר עשרוני) את השורות של כל Partition (במקרה זה – כל מוכר), כאשר המספור מתחיל מאפס (0). במה דברים אמורים? נניח ויש לנו פרוייקט בניית בית שיש בו חמש נקודות זמן משמעותיות: התחלה,

סיום הנחת היסודות,

סיום בניית השלד,

סיום יציקת הגג,

סיום גימור הבניין.

אין לי שמץ של מושג איך בונים בית אך דומני שזה הסדר הנכון..

בנקודת ההתחלה סיימנו 0% מהפרויקט, עם סיום הנחת היסודות סיימנו 25%, וכך הלאה.

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

clip_image006

אנחנו נציין ארבעה רבעונים (באדום) ולא את חמשת התאריכים שתוחמים אותם (בכחול). באופן דומה- התאריך 18/08/2011 מתייחס בדרך כלל ליממה שלמה ולא רק לנקודת הזמן בחצות הליל בו היום התחיל. מכאן שהחלוקה לנקודות זמן המתחילה מאפס אינה שימושית כל כך.

בנוסף- הפונקציה Percent_Rank אינה מאפשרת לציין משקל, וכך בעל כורחנו אנחנו מניחים שלכל השלבים בבניין הבית משקל יחסי זהה של 25%.

Cume_Dist– פונקציה זו יותר שימושית מהקודמת ומתאימה למניית תקופות, ולכן אינה מתחילה מאפס אלא מ-50% (במקרה של הדס לה שתי מכירות) או מ-25% (במקרה של יורם לו חמש מכירות). יחד עם זאת- גם כאן חסרה היכולת לציין משקלים.

Percentile_Cont– פונקציה זו מאפשרת להציג אחוזונים. השימוש הנפוץ באחוזונים הוא לחישוב חציון (Median מתקבל על ידי הפרמטר 0.5) שהוא הערך האמצעי מבין רשימת ערכים, ומהווה תחליף מקובל לממוצע כשרוצים לנטרל ערכים קיצוניים. למשל- אם במשק יש חמישה אנשים שמשכורותיהם 100,200,300,400,1000; המשכורת הממוצעת היא 400, וכך יוצא שאדם אחד מרוויח מעל הממוצע ושלושה מתחתיו (בישראל המשכורת הממוצעת היא כ-8000 ₪ שהרוב מרוויחים פחות ממנה בעוד שהמשכורת החציונית היא כ-5000 ₪). לחילופין אפשר להתייחס למשכורת החציונית – במקרה זה 300 – ואז שני אנשים ירוויחו יותר ממנה ושניים פחות ממנה.

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

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

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

Percentile_Disc– מחשבת אחוזונים כמו הקודמת, אך אינה מבצעת אינטרפולציה (כפי התבצעה במקרה של הדס בין 10 ל-15) ומחזירה ערך קונקרטי (כלומר- 10 שהוא הערך הסמוך לאמצע מלמטה ולא 12.5 שאינו קיים בטבלה). מי שרוצה לקבל את הערך הסמוך לאמצע מלמעלה- יכול למיין בסדר הפוך.

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

ולבסוף- תוספת משמעותית לפונקציות החלון הוותיקות (לא לפונקציות הסטטיסטיות החדשות!) היא היכולת להגביל אותן למסגרת חלקית (Frame) מהרשומות ב-Partition במקום לכולן כפי שהיה עד כה, וזה נעשה באמצעות הוספת הפרמטר Rows לאופרטור Over. נוכל למשל לחשב סכום מצטבר למוצר כך:

Select  *,
        Sum(Quantity) Over(Partition By ProductID
                    Order By TransactionDate, ReferenceOrderID
                    Rows Between Unbounded Preceding And Current Row) RunningAggregate
From    Production.TransactionHistory
Where   TransactionType='P'
Order By ProductID,
        TransactionDate,
        ReferenceOrderID;

clip_image008

הפניה של Rows יכולה להיות לכל הרשומות הקודמות (כבדוגמה הזו) או העוקבות, או למספר סופי של רשומות קודמות או עוקבות, ואפשרויות שונות מודגמות כאן:

clip_image010

סינטקס מקוצר לציבור החוסכים- במקום Rows Between Unbounded Preceding And Current Row ניתן להסתפק ב- Rows Unbounded Preceding, ובאופן דומה לגבי Preceding 1 וכו'; אם כי למרבה הפלא זה לא עובד ב-Following. Bug or Feature?

לצד האופרטור Rows קיים האופרטור החלופי Range שבניגוד ל-Rows מתייחס לכל הערכים הזהים לאלו שב-Frame. דוגמה:

Select  *,
        Count(Camut) Over(Partition By Moher
                    Order By Camut
                    Rows Between Unbounded Preceding And Current Row) [Rows],
        Count(Camut) Over(Partition By Moher
                    Order By Camut
                    Range Between Unbounded Preceding And Current Row) [Range]
From    T_Mehirot;

clip_image012

שימו לב לשורה 4 – האופרטור Rows מונה 2 כי זו השורה השניה של יורם, ואילו האופרטור Range מונה 3 גם כאן וגם בשורה הבאה מכיוון שבשתיהן הכמות זהה – 10 והוא סופר את כל העשיריות יחד.

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

הערה לסיום- הפוסט הזה מתבסס על גרסת CTP3, ולא מן הנמנע שבגרסה שתשוחרר בעתיד יהיו שינויים.

מודעות פרסומת

להגיב »

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

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

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