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

07/09/2011

מדדי מרכז: ממוצע (Average), חציון (Median), שכיח (Mode), אמצע טווח (Midrange)

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

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

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

Create Table #T(Shem Varchar(10),
                Taarih DateTime,
                Camut Int,
                Mehir Int,
                Total As Camut*Mehir);
Go

Insert
Into   #T
Select 'Sharon','20110801',10,15 Union All
Select 'Sharon','20110803',12,14 Union All
Select 'Sharon','20110805',16,16 Union All
Select 'Sharon','20110807',6,25 Union All
Select 'Reuven','20110802',5,16 Union All
Select 'Galit','20110802',3,25 Union All
Select 'Galit','20110805',10,14 Union All
Select 'Galit','20110810',5,15 Union All
Select 'Carmel','20110810',6,16 Union All
Select 'Carmel','20110812',9,14 Union All
Select 'Carmel','20110810',6,16;
Go

Select  *
From    #T T1
Order By Shem,
        Total;

clip_image002

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

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

Select  Shem,
        Avg(Total) Total
From    #T
Group By Shem
Order By Shem;

clip_image004

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

Select  Shem,
        Cast(Sum(Mehir*Camut) As Float)/Sum(Camut) Mehir
From    #T
Group By Shem
Order By Shem;

clip_image006

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

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

Select  Shem,
        Cast(Min(Total)+Max(Total) As Float)/2 Median
From    #T T1
Where   2*(Select Count(*)
        From    #T T2
        Where   T2.Shem=T1.Shem
                And T2.Total<=T1.Total)>=(Select Count(*) From #T T4 Where T4.Shem=T1.Shem)
        And 2*(Select Count(*)
        From    #T T3
        Where   T3.Shem=T1.Shem
                And T3.Total>=T1.Total)>=(Select Count(*) From #T T5 Where T5.Shem=T1.Shem)
Group By Shem
Order By Shem;

clip_image008

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

Select  *,
        (Select Count(*)
        From    #T T2
        Where   T2.Shem=T1.Shem
                And T2.Total<=T1.Total) [Asc],
        (Select Count(*)
        From    #T T3
        Where   T3.Shem=T1.Shem
                And T3.Total>=T1.Total) [Desc]
From    #T T1
Order By Shem,
        Total;

clip_image010

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

With T As
(Select *,
        Row_Number() Over(Partition By Shem Order By Total) Mispar,
        Count(*) Over(Partition By Shem) Mehirot
From    #T)
Select  Shem,
        Avg(Total) Median
From    T
Where   Abs(2*Mispar-Mehirot-1)<=1
Group By Shem
Order By Shem;

clip_image012

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

Select  *,
        Percentile_Cont(0.5) Within Group(Order By Total) Over (Partition By Shem) [Median1],
        Percentile_Disc(0.5) Within Group(Order By Total) Over (Partition By Shem) [Median2]
From    #T
Order By Shem,
        Total;

clip_image014

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

חציון משוקלל: כעת נחשב את החציון של המחירים- נבדוק כמה פריטים מכר כל אחד, ונחפש את המחיר של הפריט האמצעי כשהפריטים מסודרים בסדר מחיר עולה. למשל- כרמל מכרה 21=9+6+6 פריטים במחיר (4,6,6) בהתאמה, ומכאן שהחציון הוא 6 (מחיר) מכיוון שהפריט ה-11 (האמצעי מבין 21) נמכר במחיר 6.

עד לגרסת Denali לא הייתה דרך יעילה לחשב זאת, ואם היינו מתעקשים- זה יכול היה להיראות כך (*הצפייה מותרת לבני 18 ומעלה):

Select  Shem,
        Avg(Cast(Mehir As Float)) Median
From    #T T1
Where   2*(Select Sum(Camut)
        From     #T T2
        Where    T2.Shem=T1.Shem
                 And T2.Mehir<=T1.Mehir)>=(Select  Sum(Camut)
                                           From    #T T4
                                           Where   T4.Shem=T1.Shem)
        And 2*(Select Sum(Camut)
        From     #T T3
        Where    T3.Shem=T1.Shem
                 And T3.Mehir>=T1.Mehir)>=(Select Sum(Camut)
                                           From    #T T5
                                           Where   T5.Shem=T1.Shem)
Group By Shem
Order By Shem;

clip_image016

אכן- מראות קשים!

כדי להבין את הרעיון- נעביר את שליפות המשנה מה-Where ל-Select ונוותר על ה-Group By:

Select  *,
        (Select Sum(Camut)
        From    #T T2
        Where   T2.Shem=T1.Shem
                And T2.Mehir<=T1.Mehir) [Asc],
        (Select Sum(Camut)
        From    #T T2
        Where   T2.Shem=T1.Shem
                And T2.Mehir>=T1.Mehir) [Desc]
From    #T T1
Order By Shem,
        Mehir;

clip_image018

מגרסת Denali ואילך יש לנו היכולת לחשב סכום מצטבר, ובעזרתה נמצא את הרשומות בהן הסכום המצטבר שווה למחצית הסכום הכולל:

With T As
(Select *,
        Cast(Sum(Camut) Over(Partition By Shem) As Float)/2 [Sum/2],
        Sum(Camut) Over(Partition By Shem
                   Order By Mehir
                   Rows Unbounded Preceding) RunSum
From    #T)
Select  Shem,
        Avg(Cast(Mehir As Float)) Median
From    T
Where   [Sum/2] Between RunSum-Camut And RunSum
Group By Shem
Order By Shem;

clip_image020

כדי להבין את הדרך אציג את השליפה של ה-CTE:

Select  *,
        Cast(Sum(Camut) Over(Partition By Shem) As Float)/2 [Sum/2],
        Sum(Camut) Over(Partition By Shem
                   Order By Mehir
                   Rows Unbounded Preceding) RunSum
From    #T
Order By Shem,
        Mehir;

clip_image022

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

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

אצל שרון יש שתי רשומות כאלו, ולכן יש לחשב את המחיר הממוצע בהן.

שכיח לא משוקלל: בכלים שעמדו לרשותנו בגרסת 2000 ומטה היינו צריכים שאילתת משנה לחשב כמה פעמים מופיע כל Total אצל כל לקוח, לאחר מכן מחשבים לכל לקוח Max על מספר הפעמים בשאילתת המשנה, ולבסוף מבצעים Join בין המקסימום שקיבלנו לשאילתת המשנה כדי לקבל את ה-Total בו מדובר; כל זה ללא שימוש ב-CTE שהתווסף רק בגרסת 2005:

Select  T2.*
From    (Select Shem,
                Max([Count]) [Count]
        From    (Select Shem,
                        Count(*) [Count]
                From    #T
                Group By Shem,
                        Total) T
        Group By Shem) T1
Inner Join (Select  Shem,
                    Total,
                    Count(*) [Count]
            From    #T
            Group By Shem,
                    Total) T2
        On T1.Shem=T2.Shem
        And T1.[Count]=T2.[Count]
Order By T2.Shem;

clip_image024

מגרסת 2005, עת הגיעה בשורת פונקציות החלון וה-CTE, השליפה הפכה ליותר ידידותית לסביבה:

With T As
(Select Shem,
        Total,
        Count(*) [Count],
        Row_Number() Over(Partition By Shem Order By Count(*) Desc) Mispar
From    #T
Group By Shem,
        Total)
Select  *
From    T
Where   Mispar=1
Order By Shem;

clip_image026

שכיח משוקלל: מהו המחיר השכיח לכל מוכר? בגרסת 2000 וקודמותיה היינו נדונים להסתבך כך:

Select  T2.*
From    (Select Shem,
                Max(Camut) Camut
        From    (Select Shem,
                        Mehir,
                        Sum(Camut) Camut
                From #T
                Group By Shem,
                        Mehir) T
        Group By Shem) T1
Inner Join (Select  Shem,
                    Mehir,
                    Sum(Camut) Camut
           From     #T
           Group By Shem,
                    Mehir) T2
        On T1.Shem=T2.Shem
        And T1.Camut=T2.Camut
Order By T2.Shem;

clip_image028

בשאילתת משנה יש לחשב את סה"כ הכמות לכל צירוף של מוכר ומחיר, לאחר מכן למצוא את הכמות המקסימלית לכל מוכר, ולבצע Join עם שאילתת המשנה המקורית כדי לקבל את המחיר הרלוונטי.

מגרסת 2005 צפונה החישוב נעשה פשוט יותר בעזרת פונקציות החלון:

With T As
(Select Shem,
        Mehir,
        Sum(Camut) Camut,
        Row_Number() Over(Partition By Shem Order By Sum(Camut) Desc) Mispar
From    #T
Group By Shem,
        Mehir)
Select  *
From    T
Where   Mispar=1
Order By Shem;

clip_image030

אמצע טווח לא משוקלל: מדד נאיבי ולא מסובך שעושה שימוש רק בשני הערכים הקיצוניים, ובמקרה של אמצע טווח ה-Total-

Select  Shem,
        Cast(Min(Total)+Max(Total) As Float)/2 MR
From    #T
Group By Shem
Order By Shem;

clip_image032

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

Select  Shem,
        Cast(Min(Mehir)+Max(Mehir) As Float)/2 MR
From    #T
Group By Shem
Order By Shem;

clip_image034

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

להגיב »

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

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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