שימוש מתקדם בפונקציות אנליטיות של SQL Server 2012 - חלק 2 - ilDBA Portal

שימוש מתקדם בפונקציות אנליטיות של SQL Server 2012 – חלק 2

02/05/2013 | פורסם על ידי

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

פונקציות חלון מחשבות דברים כמו ממוצעים נעים וסיכומים נעים תחת תחום נתון של שורות, תחום ערכים או בפרק זמן מסויים. חלון (window) מתאר תת קבוצה של שורות מתוך התוצאה. תת קבוצה זו מעובדת ע"י פונקציות החלון, שמחזירה ערך וניתן להגדיר את ההתחלה והסוף של כל חלון. ניתן להשתמש בפונקציות הבאות בשילוב של חלון: SUM(), AVG(), MAX(), MIN(), COUNT(), VARIANCE() ו- STDDEV(). בנוסף, ניתן להשתמש בחלון בפונקציות FIRST_VALUE() ו- LAST_VALUE(), שמחזירות את הערך הראשון והאחרון בהתאמה בתוך חלון מסויים.

השאילתה הבאה מדגימה חישוב סיכום נע בשביל לחשב את סיכום נע של המכירות של שנת 2012 מינואר עד דצמבר. שימו לב שסך כל המכירות של כול חודש מתווסף לסיכום הנע של המכירות שגדל כול חודש.

SELECT
month, SUM(amount) AS month_amount,
SUM(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cumulative_amount
FROM total_sales
WHERE year = 2012
GROUP BY month
ORDER BY month;

הפלט הוא בשרטוט 10.

אז איך זה עובד?

השאילתה מחשבת את הסכום המצטבר של המכירות החל מחודש 1, אח"כ חודש 1 + חודש 2, אח"כ חודש 1 + חודש 2 + חודש 3 וכולי.. עד לחודש 12 כולל.

בוא נסביר את הביטוי:

SUM(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount
  • SUM(amount) מחשב את סכום המכירות של החודש.
  • ה-SUM() החיצוני מחשב את סכום המכירות המצטבר.
  • ORDER BY month מסדר את השורות ע"פ החודשים.
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW מגדיר את הסוף וההתחלה של החלון.
  • ההתחלה נתונה ע"י הביטוי UNBOUNDED PROCEEDING. ההתחלה של החלון היא השורה הראשונה בתוצאות הממוחזרת ע"י השאילתה.
  • סוף השורה נתון ב CURRENT ROW.
  • CURRENT ROW מייצג את השורה שמעובדת, וסוף החלון מחליק שורה אחת אחרי שה- SUM() החיצוני חישב והחזיר את הסכום המצטבר הנע הנוכחי.  זה ממשיך עד שהשורה האחרונה עובדה ע"י החלון והפונקציה SUM().

סוף החלון הוא בסוף התוצאה ע"פ הבא:

  • סוף החלון מחליק שורה אחת בתוצאה כל פעם ששורה מעובדת.
  • הסיכום הכולל של המכירות מתווסף לסכום המצטבר.

.

סוף החלון מתחיל בשורה הראשונה, סכום המכירות של אותו חודש מתווסף לסכום הכולל הנע, ואז סוף החלון זז שורה אחת לשורה השנייה. החלון עכשיו רואה 2 שורות. סכום המכירות לאותו חודש מתווסף לסכום הכולל הנע, וסוף החלון נע מטה שורה אחת לשורה השלישית. החלון רואה עכשיו 3 שורות. זה ממשיך עד שהשורה ה- 12 מעובדת. כעת החלון רואה 12 שורות.

הדוגמא הבאה משתמש בסכום מצטבר נע בין החודש השישי של 2012 לחודש 12 האחרון של 2012:

SELECT
month, SUM(amount) AS month_amount,
SUM(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS
cumulative_amount
FROM total_sales
WHERE year = 2012
AND month BETWEEN 6 AND 12
GROUP BY month
ORDER BY month;

הפלט הוא בשרטוט 11.

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

SELECT
month, SUM(amount) AS month_amount,
AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
AS moving_average
FROM total_sales
WHERE year = 2012
GROUP BY month
ORDER BY month;

הפלט בשרטוט 12.

12

אז איך זה עובד?

אם תסתכל בפלט נוכל לראות:

month 11 moving_average= (160221.98+264480.79+199609.68+212735.68)/4=209262.032500
month 2 moving_average= (116671.60+95525.55)/2=106098.575000

השאילתה משתמשת בביטוי הבא בשביל לחשב ממוצע נע:

AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_average

בוא נסביר את הביטוי:

  • SUM (amount) מחשב את סכום המכירות לחודש.
  • AVG () מחשב את הממוצע.
  • ORDER BY month מסדר את השורות ע"פ החודשים.
  • ROWS BETWEEN 3 PROCEEDING AND CURRENT ROW: מגדיר את ההתחלה של החלון כולל 3 השורות לפני החלון הנוכחי. סוף החלון הוא השורה הנוכחית המעובדת.
  • הביטוי כולו מחשב את הממוצע הנע של המכירות בין החודש הנוכחי לשלושת חודשים לפני החודש הנוכחי. עבור 2 החודשים הראשונים, שהם פחות מ 3 חודשים החלון הנע יהיה מבוסס על החודשים הזמינים.
    החלון מתחיל ונגמר בשורה 1 בשאילתה.

השאילתה הבאה מדגימה ממוצע נע מרכזי (Centered Average) ומחשבת את הממוצע הנע של סכום המכירות של החודש הקודם והחודש הבא מהחודש הנוכחי.

SELECT
month, SUM(amount) AS month_amount,
AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS moving_average
FROM total_sales
WHERE year = 2012
GROUP BY month
ORDER BY month;

הפלט הוא בשרטוט 13.

13

אז איך זה עובד?

בוא נסביר את הפלט. ניתן לראות בפלט למשל ש:

month 5 moving average=(175998.80+154349.44+124951.36)/3=151766.533333
month 1 moving average=(95525.55+116671.60)/2 =106098.575000

בוא נסביר את הביטוי הבא:

AVG(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS moving_average
  • SUM(amount) מחשב את סכום החודשים.
  • ה AVG() החיצוני מחשב את הממוצע.
  • ORDER BY month מסדר את השורות ע"פ חודשים.
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING מגדיר את תחילת החלון שכולל את השורה לפני השורה הנוכחית. סוף החלון הוא השורה מקדימה לשורה הנוכחית.

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

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

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

SELECT
month, SUM(amount) AS month_amount,
FIRST_VALUE(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS previous_month_amount,
LAST_VALUE(SUM(amount)) OVER
(ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
AS next_month_amount
FROM total_sales
WHERE year = 2012
GROUP BY month
ORDER BY month;

הפלט הוא בשרטוט 14.

14

ניתן להשתמש בפונקציות דווח (reporting functions) בשביל לבצע חישובים על קבוצות ו- partitions בתוך קבוצות. ניתן לבצע דוחות ע"י הפונקציות הבאות: SUM(), AVG(), MAX(), MIN(), COUNT(), VARIANCE(), and STDDEV()..

עבור 3 החודשים הראשונים של 2012 השתמש בשאילתות הדיווח הבאות:

  • סכום כולל של כל המכירות במשך 3 חדשים (total_month_amount).
  • סכום כולל של כל המכירות עבור סוגי המוצרים השונים (total_product_type_amount).
SELECT
month, prd_type_id,
SUM(SUM(amount)) OVER (PARTITION BY month)
AS total_month_amount,
SUM(SUM(amount)) OVER (PARTITION BY prd_type_id)
AS total_product_type_amount
total_sales FROMWHERE year = 2012
AND month <= 3
GROUP BY month, prd_type_id
ORDER BY month, prd_type_id;

הפלט הוא בשרטוט 15.

15

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

SELECT
month, SUM(amount) AS month_amount,
LAG(SUM(amount), 1) OVER (ORDER BY month) AS previous_month_amount,
LEAD(SUM(amount), 1) OVER (ORDER BY month) AS next_month_amount
FROM total_sales
WHERE year = 2012
GROUP BY month
ORDER BY month;

הפלט הוא בשרטוט 16.

16

אז כיצד זה עובד.

  • LAG(SUM(amount), 1) מביאה את סכום המכירה של השורה הקודמת.
  • LEAD(SUM(amount),1) מביאה את סכום המכירה של השורה הבאה.

מה שחסר לדעתי ב- SQL Server 2012 הוא השימוש בדירוג באופן היפותטי בפונקציות דרוג (rank) והסתברות לחישוב הדירוג והאחוזונים של שורה חדשה אם הייתה מוכנסת לטבלה. בצורה כזו ניתן לבצע חישובים היפותטיים עם הפונקציות: RANK (), DENSE_RANK (), PERCENT_RANK () ו- CUME_DIST ().

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

SELECT
prd_type_id, SUM(amount),
RANK() OVER (ORDER BY SUM(amount) DESC) AS rank,
PERCENT_RANK() OVER (ORDER BY SUM(amount) DESC) AS percent_rank
FROM all_sales
WHERE year = 2003
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;

פלט נדרש:

prd_type_id   (No column name)     rank   percent_rank
1      905081.84     1      0
2      186381.22     4      1
3      478270.91     2      0.333333333333333
4      402751.16     3      0.666666666666667

הפונקציה הבאה משתמשת בדירוג ודירוג אחוזונים היפותטי עבור הסכום $500,000.

SELECT
RANK(500000) WITHIN GROUP (ORDER BY SUM(amount) DESC)
AS rank,
PERCENT_RANK(500000) WITHIN GROUP (ORDER BY SUM(amount) DESC)
AS percent_rank
FROM all_sales
WHERE year = 2003
AND amount IS NOT NULL
GROUP BY prd_type_id
ORDER BY prd_type_id;

לסיכום:

במאמר זה הדגמתי שימוש מתקדם בפונקציות אנאליטיות ב- SQL Server 2012 מבוסס על נסיוני בבסיסי נתונים אחרים.

נתראה במאמר הבא!

ד. יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle, Sybase, Sybase Anywhere מזה 16 שנה .

מהנדס מערכות מידע ושני תארים שניים ב MBA ו- ME (הנדסת מערכת). מרצה בפורום משתמשי SQL Server , משתמשי Oracle (IOUG) , Oracle Open World 2010 (Active data Guard ו- .net) , Microsoft HPC והנדסת מערכת .

The following two tabs change content below.

דוד יצחק

דוד יצחק , DBA אפליקטיבי ותשתיתי של SQL Server, Oracle ,Sybase Anywhere מזה 17 שנה עם התמחות בשיפור ביצועים, שרידות, זמינות גבוהה, רפלקציה, תוספים גיאוגרפיים, אבטחת מידע ו-Business Intelligence. הוא מרכז את פורום DBA צפון של ilOUG ומרצה בפורום משתמשי SQL Server.

6 תגובות ל- “שימוש מתקדם בפונקציות אנליטיות של SQL Server 2012 – חלק 2”

commenter

חברים יקרים המאמר מבוסס על : פרטי המאמר ב- SQL Server מגאזין : August 21, 2012 09:38 AM Take Advantage of SQL Server 2012's Window Functionality Examples of how to use the window ranking, distribution, aggregate, and offset functions D. Yitzhak SQL Server Pro InstantDoc ID #143309 http://www.sqlmag.com/content1/topic/sql-server-2012-window-functionality-143309/catpath/sql-server-2012   המאמר מבוסס על הרצאה שניתנה ב- SQL Server פורום בפבואר 2012 (חודשיים לפני שהשתררה גרסה הסופית של SQL 2012) והועלתה לאתר ההשקה של צוות הפיתוח של SQL 2012 . http://blogs.technet.com/b/dataplatforminsider/archive/2012/02/06/recommended-sql-server-2012-virtual-launch-event-sessions-for-dbas-and-it-professionals.aspx?CommentPosted=true#commentmessage http://www.slideshare.net/…/fast-transition-to-sql-server-2012-from-mssql-2005-2008-for-developers-david-yitzhak     2.הרצאה שלי על שימוש מתקדם בפונקציות חלון ופונקציות אנליטיות והשוואה ל- MDX ו- DAX . הפעם בסביבת ORACLE בפרום ILOUG  

David Itshak | 28/09/2013 בשעה 22:49
commenter

David Yitzhak | Oracle ILOUG forum administrator

http://www.iloug.org.il/DBA_NorthForum.php

David Itshak | 28/09/2013 בשעה 22:51
commenter

חג שמח לכולם !

מוזמנים לכנס הבא של Oracle & ilOUG DBA North Forum .

כמו בכנסים הקודמים רוב החומר יחודי לפורום שלנו והכי חשוב מעשי .

אגב על הרצאה הקודמת : " ישום Golden Gate הגדול בעולם" Oracle כנראה יכתבו White Paper .

ניתן להירשם כאן :
http://www.iloug.org.il/Event_Page.php?EventID=175

David Yitzhak | Oracle ILOUG forum administrator

http://www.iloug.org.il/DBA_NorthForum.php

השאר תגובה:

שם (חובה):
אימייל (לא יפורסם) (חובה):
תגובה (חובה):

*



מאמרים קשורים

קהילת

קהילת ה- BI וה- BIG DATA מתכנסת ב- 28-10 !

שלום רב, למי שלא ידע 🙂 קהילת ה- BI וה- BIG DATA בעולמות Microsoft מתכנסת מידי חודש על מנת להפגש, להכיר ולשמוע הרצאות במגוון נושאים טכנולוגיים מרתקים בתחום. במפגש הקרוב (מספר 63) שיתקיים ב- 28-10-2015, יום רביעי [...]
הזמנה

הזמנה ל-SQL Saturday #481 – Israel 2016

שלום רב, בקרוב יתקיים בישראל כנס טכנולוגי מרכזי קהילת ה-DB וה-BI בתחום ה-SQL Server – ה-SQL Saturday! הכנס אשר מאורגן בהתנדבות על ידי אנשי הקהילה יכלול במהלכו מספר מסלולי לימוד בתחומים טכנולוגיים שונים. [...]

איך לבצע Sizing DB ב-Datacenter בארגונך בקלות (חלק א')

כחלק מהטמעות מוצרי IT תשתיתיים (כגון FWDB, מוצרי גיבוי, חוות Storage, מוצרי שו"ב ועוד…) בארגוני, אנו נדרשים המון פעמים לענות על שאלות לספקים כגון : מה גודל הכולל של ה –  Datacenter  ? מה חלוקת גודל ה [...]

תיעוד בסיס הנתונים – למה זה טוב?

ישנם מספר נושאים חשובים שיש לתת עליהם את הדעת בפיתוח ותכנון בסיס נתונים. כמובן שצריך לשים לב לנירמול נכון של הטבלאות, וכמובן שצריך להגדיר סטנדרט לכתיבה נכונה של הקוד, וכמובן שאי אפשר בלי [...]
Copyright 2019 ilDBA Portal. Brought to you by Brillix - Israel Leading DBA company. Sponsored by: DBSnaps - Database Video Tutorialss
Website Security Test
%d בלוגרים אהבו את זה: