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

14/07/2010

קיבוץ נתונים

Filed under: Uncategorized — תגיות: , , , , , , , , , , , — גרי רשף @ 12:28

לא, לא מדובר בהתיישבות העובדת, אלא באופציות כדוגמת Group By והשימושים השונים בהן.
השימוש ב-Group By אמור להיות מוכר לכל מי שמכיר SQL גם ברמה בסיסית:

Use AdventureWorks;

Go

Select    uid,

        type,

        parent_obj,

        sysstat,

        Max(crdate) Mx_crdate,

        Count(name) Cnt_name

From    sys.sysobjects

--Where    crdate>='20100101'

Group By uid,

        type,

        parent_obj,

        sysstat

--Having    Count(name)<10;

בדוגמה הנ"ל הערתי (to comment) את שתי שורות התנאי שצירפתי כדי להמחיש את ההבדל בין תנאים על הנתונים עצמם לפי ביצוע הקיבוץ (Where) ותנאים על הסיכומים לאחר ביצוע הקיבוץ (Having).

אני משתמש לעיתים קרובות ב-Having לחיפוש חריגים בטבלאות שהנתונים בהן אמורים להיות יחודיים (Unique), בערך כך:

Select    ..,

        ..,

        Count(..)

From    ..

Group By ..,

        ..

Having Count(..)>1

Order By Count(..) Desc

לעיתים קרובות אני נתקל בשאלות של מתחילים ש"אינם מצליחים", ודבר ראשון אני בודק אם יש התאמה בין ה-Select וה-Group By: כל השדות שמופיעים ב-Select ללא פונקציית צבירה (כדוגמת Sum או Count או Max וכו') – צריכים להופיע גם ב-Group By (ההיפך אינו הכרחי).

בדוגמה העליונה יש ארבעה שדות כאלה (uid,type,parent_obj,sysstat) והם מופיעים גם ב-Select וגם ב-Group By.

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

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

With Rollup

קודם כל דוגמה:

Select    uid,

        type,

        parent_obj,

        sysstat,

        Max(crdate) Mx_crdate,

        Count(name) Cnt_name

From    sys.sysobjects

Group By uid,

        type,

        parent_obj,

        sysstat

With Rollup

Order By 4,3,2,1;

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

תוצאת השליפה מחזירה את ה-Group By של uid,type,parent_obj,sysstat,

מוסיפה לו את ה-Group By של uid,type,parent_obj,

מוסיפה לו את ה-Group By של uid,type,

מוסיפה לו את ה-Group By של uid,

ולבסוף מוסיפה לו את הסיכום הכללי של כל הטבלה.

למה ולמתי זה טוב? מי שכותב דוחות ב-SSRS או בכלים אחרים נתקל בוודאי בדוחות מורכבים שאמורים לסכם מכירות תקופתיות לפי אזורים, ישובים, חנויות ומוצרים.

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

הדוח כולל אם כך את הפונקציונליות של ה-RollUp, ואם מישהו רוצה לבנות דוח בעצמו ולא בעזרת מחולל דוחות- ה-Rollup מספר לו את כל המדיע והסיכומים הנדרשים, ולא יהיה צורך בכל ביצוע Drill Down לשלוף מחדש מידע מהטבלאות.

הערה: מכיוון שבכל סוג של Group By יש מספר שונה של עמודות, אלו שאינן רלוונטיות מופיעות כעמודות Null, ולפי זה ניתן לדעת מי ומה (ולבחור במקרה הצורך).

With Cube

דוגמה:

Select    uid,

        type,

        parent_obj,

        sysstat,

        Max(crdate) Mx_crdate,

        Count(name) Cnt_name

From    sys.sysobjects

Group By uid,

        type,

        parent_obj,

        sysstat

With Cube

Order By 4,3,2,1;

כאן יוחזרו בדרך כלל הרבה יותר רשומות מאשר בסעיף הקודם, מכיוון שהשליפה מכילה את כל אופציות ה-Group By האפשריות של ארבע העמודות. מי שזוכר גרסא דינקותא שלו ממבוא לסטטיסטיקה והסתברות ודאי יודע שלקבוצה בת 4 איברים יש 16=4^2 (שתיים בחזקת 4) תתי קבוצות, החל מהקבוצה עצמה וכלה בקבוצה הריקה; וכל 16 אופציות ה-Group By האלו (שכל אחת כוללת מספר רשומות) כלולות ב-Cube.

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

Grouping Sets

דוגמה:

Select    uid,

        type,

        parent_obj,

        sysstat,

        Max(crdate) Mx_crdate,

        Count(name) Cnt_name

From    sys.sysobjects

Group By Grouping Sets((type,uid),

                    (sysstat),

                    (uid,type,parent_obj,sysstat),

                    ())

Order By 4,3,2,1;

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

למעשה ניתן לקבל בעזרת ה-Grouping Sets את כל אחת מהאופציות הקודמות שהוצגו כאן, על ידי ציון האופציות הרלוונטיות.

למה ולמתי זה טוב? לכלי דיווח כמו הנ"ל, אבל כשאופציות הבחירה מוגבלות..

פונקציות חלון – Window Functions

דוגמה עם שלל אפשרויות בתוכה:

Select    name,

        uid,

        type,

        parent_obj,

        sysstat,

        Row_Number() Over(Order By uid) Mone_rn,

        Rank() Over(Order By uid) Mone_r,

        Dense_Rank() Over(Order By uid) Mone_dr,

        Row_Number() Over(Partition By uid Order By name) Mone_rn2,

        Count(uid) Over() Cnt,

        Count(uid) Over(Partition By uid) Cnt_uid,

        Max(sysstat) Over(Partition By uid) Mx_uid

From    sys.sysobjects

Order By Mone_rn;

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

Mone_rn – מספור Row_Number של השורות (..1,2,3,4)

Mone_r – מספור Rank של של השורות (..1,1,1,4,4,6)

Mone_dr – מספור Dense_Rank של השורות (..1,1,1,2,2,3)

Mone_rn2 – מספור Row_Number של כל קבוצת uid בנפרד (למעשה מספור משנה של Dense_Rank מהשורה הקודמת)

Cnt – חישוב Count כללי (כלומר- מספר השורות בטבלה)

Cnt_uid – חישוב Count לכל קבוצה בנפרד

Mx_uid – חישוב ערך sysstat מקסימלי בכל קבוצת uid.

החישובים הנ"ל מעט מלאכותיים בחלקם (למי משנה מה ה-sysstat המקסימלי?..) ונועדו להמחיש שימושים שונים בפונקציות החלון.

בשל היעדר פונקציות Lead ו-Lag ב-SQL Server שמאפשרות פניה לרשומה הקודמת או הבאה – ניתן לבצע זאת בעזרת Row_Number שממספר את השורות, וביצוע Join עצמי עם תנאי כדוגמת Tbl1.Mispar=Tbl2.Mispar+1.

ניתן להשתמש בפונקציות חלון בשליפות Group By השונות, כמובן בכפוף להוראות השימוש המצורפות: למשל- לא ניתן למספר לפי עמודה שאינה נכללת בשליפה..

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

להגיב »

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

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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