content top
קיבוץ נתונים

קיבוץ נתונים

לא, לא מדובר בהתיישבות העובדת, אלא באופציות כדוגמת 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;

השליפה הזו מחזירה את כל השורות מהטבלה, ומוסיפה לכל אחת חישובים וסיכומים המתייחסים לשורות האחרות.

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 השונות, כמובן בכפוף להוראות השימוש המצורפות: למשל- לא ניתן למספר לפי עמודה שאינה נכללת בשליפה..


18 תגובות על “קיבוץ נתונים”

  1. שלום,

    תודה על הטיפים.

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

  2. מאת Saggi Neumann:

    @רני – ממוצע משוקלל באמצעות הפונקציות הפשוטות שקיימות ב-SQL Server דורש אגרגציה על אגרגציה – בשאילתה הפנימית, אגרגציה שמחזירה עבור כל קבוצה sum ו-count, ובשאילתה החיצונית אגרגציה נוספת שמחזירה את סכום המכפלות של ה-sum וה-count חלקי סכום ה-count.

    ב-SQL 2008 אתה יכול לכתוב sqlclr aggregate function שמקבלת כמה פרמטרים ולבצע את זה באגרגציה אחת אולי, אבל בלי לבדוק, לא נראה שזה שווה את המאמץ מבחינת ביצועים שתוכל להוציא מזה.

    שגיא

  3. תודה.

    חשבתי שיש משהו דומה ל SUMPRODUCT של אקסל.

    *וסליחה על טעות הכתיב.

  4. מאת גרי רשף:

    רני ויסמן- אם הבנתי אותך נכון אז כך:
    Select ID,
    Sum(Ziun*Nekudot)/Sum(Nekudot) MemuzaMeshuklal
    From MyTbl
    Group By ID
    מקווה שהתצוגה של עברית ואנגלית ביחד אינה משתבשת ושהבנת את רוח הדברים.

  5. כן.

    לפעמים לבעיות מסובכות, יש תשובות פשוטות.

    תודה רבה.

  6. מאת עידו:

    כדי לעשות ממוצע משוקלל
    אם הנתון בעמודה A
    והמשקל בעמודה B
    תעשה
    SUM(A*B)/COUNT(A*B)

  7. מאת גרי רשף:

    עידו- מה שכתבת במונה (למעלה) – נכון
    ומה שבמכנה – שגוי: צריך להופיע שם (Sum(B,
    כלומר- סכום המשקלים.

  8. מאת עידו:

    צודק, כמובן.

    זה השתרבב ממשהו אחר שעשיתי בו-זמנית..

  9. עידו וגרי צודקים כמובן אם הנתון שלפיו משקללים קיים בתוך השורה…

  10. מאת יוסי כהן:

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

  11. מאת גרי רשף:

    יוסי כהן: זה קצת מסובך, אכתוב תיאור כללי של מה שהייתי מנסה:

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

    2. לאחר מכן להפוך את הנ"ל לשאילתת משנה בשאילתה ראשית שתשלוף את כל השורות בהן המכירה המקסימלית שלו (עד כה) *גדולה* מהחודשית.

    3. לבסוף- לשלוף מטבלת המוכרים את כל מי שאינו בקודמת (2).
    כלומר- כל מי שאין לו מכירה מקסימלית עד כה גדולה מהחודשית.

  12. מאת יוסי כהן:

    גרי קודם כל תודה על ההתיחסות.
    לא כל כך הבנתי את סעיף 2. בהבנה שלי בקריאת המשפט לא יתכן שמכירה חודשית כשלהיא תהיה גדולה מהמכירה המקסימלית של המוכרן. נניח לצורך הפשטות טבלת מכירות לפי תז וחודש:
    אני מעוניין שהשאילתא תוציא את מוכרן 2.
    חודש תז1 תז2 תז3
    1 100 50 50
    2 200 60 60
    3 90 70 70
    4 120 80 60
    5 100 90 80

  13. מאת גרי רשף:

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

    Create Table #Sellers(ID Int);

    Insert
    Into #Sellers
    Values (1),
    (2),
    (3);

    Create Table #Sales(ID Int,Month Int, Quantity Int);

    Insert
    Into #Sales
    Values (1,1,100),
    (1,2,200),
    (1,3,90 ),
    (1,4,120),
    (1,5,100),
    (2,1,50 ),
    (2,2,60 ),
    (2,3,70 ),
    (2,4,80 ),
    (2,5,90 ),
    (3,1,50 ),
    (3,2,60 ),
    (3,3,70 ),
    (3,4,60 ),
    (3,5,80 );

    Select *
    From #Sellers Slr
    Where Not Exists (Select *
    From (Select *,
    Max(Quantity) Over(Partition By ID Order By Month) MxQuantity
    From #Sales Sl) T
    Where Quantity<MxQuantity
    And T.ID=Slr.ID);

  14. מאת יוסי כהן:

    כיוון שהדוגמה לטבלת מכירות יצאה מבובלבת מנסה לשלוח שוב:
    חודש תז 1 תז2 תז3
    —– —— —– ——
    1 100 50 50
    2 200 60 60
    3 90 70 70
    4 120 80 60
    5 100 90 80

  15. מאת גרי רשף:

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

  16. מאת יוסי כהן:

    מלך , לאחר מאמץ הבנתי והצלחתי. תודה גדולה.
    חשבתי שהשיטה תהיה אחרת למשל יש דרך כשנמצאים על רשומה לבדוק ערך שדה ברשומה קודמת ?

  17. מאת גרי רשף:

    אפשר גם כך בעזרת הפונקציה Lag במקום Max
    ולזכור שבשורה הראשונה ה-Lag הוא Null

  18. מאת יוסי כהן:

    אפשר עוד שאלה? נניח שיש רשומת הזמנה עם 10 שדות להערות, שם השדות …. HAARA1, HAARA2, HAARA3 , האם ניתן בפקודת LOOP בשאילתת SQL לבדוק אם אחת ההערות מכילה ערך כלשהו, במקום לרשום 10 שורות של IF או CASE שבודק כל שדה בנפרד.

כתיבת תגובה

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

7 + 2 =