מדד מרכז הוא מדד המייצג את כלל האוכלוסיה (בניסוח לא מדוייק), ומחזיר ערך "מרכזי" שאמור לתאר במקורב את כל האוכלוסיה או חלק משמעותי ממנה.
הממוצע הוא המוכר והשימושי מבין מדדי המרכז שאותו אין צורך להציג, ובנוסף לו מקובלים עוד מספר מדדים.
החציון הוא מדד סטטיסטי תחליפי לממוצע המציג את הערך האמצעי מבין הערכים הממויינים לפי ערכם, ויש לו שני שימושים עיקריים:
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;
במספר מקרים ניתן לבצע את החישובים ביעילות בעזרת פונקציות CLR, אבל אני אשתמש להלן רק בפקודות TSQL קיימות.
ממוצע לא משוקלל: החישוב במקרה זה פשוט ומתבסס על הפונקציה האגרגטיבית Avg. למשל- המכירה הממוצעת של כל מוכר:
Select Shem,
Avg(Total) Total
From #T
Group By Shem
Order By Shem;
ממוצע משוקלל: נחשב את המחיר הממוצע לפריט עבור כל מוכר, כאשר בכל שורה מופיע המחיר והמשקל (=הכמות שנמכרה). לשם כך יש צורך לכפול כל מחיר בכמות (כאן זה נעשה ממילא בעמודת Total אבל בשביל הסדר הטוב נבצע זאת שוב) ולחלק בסה"כ הכמות (אני ממיר את המונה ל-Float כאשר התוצאה צפויה להיות לא שלמה):
Select Shem,
Cast(Sum(Mehir*Camut) As Float)/Sum(Camut) Mehir
From #T
Group By Shem
Order By Shem;
חציון לא משוקלל: נתחיל מהמקרה הפשוט יחסית בו החציון אינו משוקלל, ולפיכך יש לנו טבלה שבה לכל מוכר צריך למצוא את הרשומה האמצעית לפי סדר הסה"כ (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;
השליפה מחפשת את הרשומות שמספרן הסידורי (מחושב על ידי 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;
מגרסת 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;
מגרסת 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;
יש שתי פונקציות לחישוב חציון- 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;
אכן- מראות קשים!
כדי להבין את הרעיון- נעביר את שליפות המשנה מה-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;
מגרסת 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;
כדי להבין את הדרך אציג את השליפה של ה-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;
את מחצית הסכום הכולל חישבנו בעזרת פונקציית החלון 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;
מגרסת 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;
שכיח משוקלל: מהו המחיר השכיח לכל מוכר? בגרסת 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;
בשאילתת משנה יש לחשב את סה"כ הכמות לכל צירוף של מוכר ומחיר, לאחר מכן למצוא את הכמות המקסימלית לכל מוכר, ולבצע 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;
אמצע טווח לא משוקלל: מדד נאיבי ולא מסובך שעושה שימוש רק בשני הערכים הקיצוניים, ובמקרה של אמצע טווח ה-Total-
Select Shem,
Cast(Min(Total)+Max(Total) As Float)/2 MR
From #T
Group By Shem
Order By Shem;
אמצע טווח משוקלל: אין הבדל בין חישוב אמצע טווח לא משוקלל ומשוקלל, מכיוון שהחישוב אינו מתייחס למשקלים אלא רק לשתי התוצאות הקיצוניות, ולכן חישוב אמצע טווח המחירים יעשה באותה שיטה כמו חישוב אמצע טווח ה-Total-
Select Shem,
Cast(Min(Mehir)+Max(Mehir) As Float)/2 MR
From #T
Group By Shem
Order By Shem;
