New T-SQL Window Functions In SQL Server 2012 - ilDBA Portal

New T-SQL Window Functions In SQL Server 2012

25/07/2011 | פורסם על ידי

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

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI310

בעזרת WINDOW FUNCTIONS להלן "WF", סוגר SQL Server 2012 פערים בתחום היכולות של שאילתות המכונות שאילתות אנליטיות. אלה הן יכולות שכבר הופיעו ב-SQL 2005, למשל בפונקציות ()RANK() ,ROW NUMBER וכד', אך היו פערים בתחום זה, במיוחד בשל העובדה שיכולות אלו הוצגו ב-ORACLE כבר לפני זמן מה.
ב-ORACLE  יכולות אלה מכונות "ANALYTICAL FUNCTIONS" אבל תקן ANSI 99 מכנה אותן "WINDOW FUNCTIONS".

הדרך הטובה ביותר להסביר אותן תהיה באמצעות דוגמא, והדוגמא הראשונה שנבחן נקראת "RUNNING TOTALS".
הצורך שעונה עליו השאילתא היא הרצון להציג לכל שורה בתוצאה את הסה"כ לשורה זו החל מהשורה הראשונה ועד לשורה הנוכחית.

הערה: את בסיס הנתונים לדוגמא זו AdventureWorksDWDenali ניתן להוריד מכאן:
http://msftdbprodsamples.codeplex.com/releases/view/55330

קודם נראה את קוד ה-TSQL בפיתרון הקלאסי לפני SQL Server 2012  :

הקוד מחזיר את הסכום לכל ACCOUNTKEY ו- FINANCEKEY ,וגם את הסכום המצטבר לכל שורה.

SELECT S1.AccountKey, S1.FinanceKey, S1.Amount, SUM (S2.Amount) AS Balance
FROM dbo.FactFinance AS S1
JOIN dbo.FactFinance AS S2
   ON S2.AccountKey = S1.AccountKey
   AND S2.FinanceKey <= S1.FinanceKey
GROUP BY S1.AccountKey, S1.FinanceKey, S1.Amount

פרט לכך שהכתיבה איננה טריויאלית לכל אחד, הצורך להוסיף את FactFinance פעמיים בכדי לחשב את הסה"כ, נותנת אותותיה גם ב-QUERY PLAN :

למרות שב'היגיון הפשוט' נראה לנו שניתן לחשב את תוצאות השאילתא רק במעבר אחד על הטבלה ה-QUERY OPTIMIZER (להלן ה-QO) אינו מסוגל לפעול לפי ההגיון שלנו אלה לפי החוקים המאפיינים את המנוע שמבוססים על תורת הקבוצות (SET BASED), אי לכך למעשה הטבלה שבה 39409 שורות מבצעת NL JOIN  שתוצאת הביניים שלו היא 16433427 שורות! וה-COST של השאילתא הוא 112.263 .

ב- SQL Server 2012 לעומת זאת השאילתא שלמעלה תיכתב כך בעזרת WF:

SELECT AccountKey, FinanceKey, Amount,
SUM (Amount) OVER (PARTITION BY AccountKey
                   ORDER BY FinanceKey
                   ROWS BETWEEN UNBOUNDED PRECEDING
                   AND CURRENT ROW) AS balance
FROM dbo.FactFinance;

דבר ראשון שאנו שמים לב עליו שיש פה פשוט… פחות קוד.
זה משהו משמח שמאפיין את השימוש ב-WF.
דבר שני – שימו לב שהטבלה מופיעה רק פעם אחת בשאילתא.
מה שקודם אמר לנו 'ההגיון הפשוט', הוא עכשיו גם אפשרי על ידי כתיבת שאילתות עם WF.
בואו נראה גם מה קורה ל–QUERY PLAN:


שימו לב ב- QP  ל-'WINDOW SPOOL' שזהו למעשה החלק שמימש את ה-WF שהגדרנו – הפלט שלו הוא 78818 שורות בלבד, וה-COST של השאילתא למתענינים הוא 0.358 בלבד – סדר גודל שלם פחות מהשאילתא ה'קלאסית'. הרבה יותר יעיל.

בואו נבחן את חלקי ה- WF :

FUNCTION (COLUMN)  OVER ( PARTITION BY .......
ORDER BY .....
ROWS | RANGE ...... )
השימוש במילה OVER מגדיר את תחילתה של הפונקציה.
השימוש ב-PARTITION, מאפשר לנו להגדיר את העמודה לפיה החלוקה או ההקבצה  עליה תתבצע הפונקציה (אין לה שום קשר ל-GROUP BY של השאילתא עצמה).
השימוש ב-ORDER BY, קובע את הסדר לפיו יתבצע המיון של ה-WF.
במקרה של השאילתה לעיל, המיון לפי FINANCEKEY, וההקבצה לפי ACCOUNTKEY.
יש לציין שניתן לציין יותר מעמודה אחת ב-ORDER BY (גם כאן אין קשר ל–ORDER BY של השאילתא עצמה).
השימוש ב-ROWS מאפשר לנו להגדיר את טווחי השורות בתוך ה- PARTITION  שעליה תופעל הפונקציה. במקרה שלנו, הגדרנו את הפונקציה לפעול על כל ה-PARTITION, ע"י השימוש UNBOUNDED PRECEDING ו-CURRENT ROW
למעשה במקרה זה מכיוון שברצוני להפעיל את הפונקציה על החלון כולו אין צורך להשתמש ב-ROWS כלל מכיוון שזו ברירת המחדל ולכן השאילתא תראה סופית כך :
SELECT AccountKey, FinanceKey, Amount,
SUM(Amount) OVER (PARTITION BY AccountKey
                  ORDER BY FinanceKey) AS balance
FROM dbo.FactFinance;

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

SELECT SalesOrderID, ProductID, LineTotal ,
       LineTotal / (SELECT SUM (O2.LineTotal)
                    FROM Sales.SalesOrderDetail AS O2
                    WHERE O1.ProductID = O2.ProductID
                    AND   ModifiedDate >= '20010101'
                    AND   ModifiedDate < '20020101') AS PctOfTotalPerProduct,
       LineTotal / (SELECT SUM (O2.LineTotal)
                    FROM Sales.SalesOrderDetail AS O2
                    WHERE ModifiedDate >= '20010101'
                   AND ModifiedDate < '20020101') AS PctOfAllOrders
FROM Sales.SalesOrderDetail AS O1
WHERE ModifiedDate >= '20010101'
AND ModifiedDate < '20020101';

.הבה נראה כיצד נכתוב את אותה שאילתא עם WF.

SELECT SalesOrderID, ProductID, LineTotal,
       LineTotal / SUM (LineTotal) OVER(PARTITION BY ProductID) AS PctOfTotalPerProductWF,
       LineTotal / SUM (LineTotal) OVER () AS PctOfAllOrdersWF
FROM Sales.SalesOrderDetail AS O1
WHERE ModifiedDate >= '20010101'
AND ModifiedDate < '20020101';

כמו שאתם רואים  – קצר מהיר ואלגנטי, ביחוד השימוש  ב-PARTITION פעם אחת להגדרת החלון של פונקצית ה-SUM על קבוצות PRODUCT ופעם אחת ללא שימוש ב-PARTITION להפעלת פונקצית ה-SUM על כל התוצאות.

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

SELECT CustomerID, orderdate, SalesOrderID,
       DATEDIFF(day,(SELECT TOP (1) I.orderdate
                     FROM Sales.SalesOrderHeader AS I
                     WHERE I.CustomerID = O.CustomerID
                     AND I.orderdate <= O.orderdate
                     AND (I.orderdate < O.orderdate
                          OR I. SalesOrderID < O.SalesOrderID)
                     ORDER BY orderdate DESC, SalesOrderID DESC),
               Orderdate) AS diff
FROM Sales.SalesOrderHeader AS O

מורכב למדי כפי שאתם רואים.
הנה הדוגמא ע"י שימוש בפונקצית LAG  למימוש אותה שאילתא :

SELECT  CustomerID, orderdate, SalesOrderID,
        DATEDIFF (day,LAG (orderdate) OVER (PARTITION BY CustomerID ORDER BY orderdate),
                  Orderdate) AS diff
FROM Sales.SalesOrderHeader

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

SELECT  CustomerID, orderdate, SalesOrderID,
        DATEDIFF (day, LAG (orderdate, 1) OVER (PARTITION BY CustomerID ORDER BY orderdate),
                  Orderdate) AS diff
FROM Sales.SalesOrderHeader

לסיכום,
ברשימה זו הראיתי חלק מיכולות ה-WF של TSQL ב-SQL Server 2012.
הרבה מתכנתי TSQL ישמחו על השיפורים, ביחוד בסביבות של מחסני נתונים גדולים.
הדוגמאות לעיל ירוצו על CTP3 של DENALI שניתן להוריד מפה :

https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/

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

דני רביד.

9 תגובות ל- “New T-SQL Window Functions In SQL Server 2012”

commenter

Hi Dani,
interesting article – I should find some time to play around with this beta!
I think LEED is actually spelled LEAD (at least in Oracle).
Just a minor thing – Oracle calls a function a "Window function" only when you use the syntax of … OVER(… ROWS BETWEEN … AND…). This defines a window of rows for each row in the resultset  – for example, moving average.
In Oracle terminology, there are other groups of analytic functions like ranking (RANK, DENSE_RANK…), LEAD/LAG etc.

commenter

אהבתי את הביטוי
"במיוחד בשל העובדה שיכולות אלו הוצגו ב-ORACLE כבר לפני זמן מה."
נכון
בשנת 1999
ממש לא מזמן

David Itshak | 08/02/2012 בשעה 21:54
commenter

נכון הנה דוגמאות לפונקציות אנליטיות שממושו באורקל ורק עכשיו 
נכון הנה דוגמאות לפונקציות אנליטיות שממושו באורקל ורק עכשיו קיימים ב- SQL server 2012
Performing a Cumulative Sum
•          Example :  a cumulative sum to compute the cumulative sales amount for 2012, from  January to  December;
•          Notice :  each monthly sales amount is added to the cumulative amount that grows after each month:
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
דוד

david Itshak | 08/02/2012 בשעה 21:56
commenter

 
 
Hi
http://www.sqlserver.co.il/?p=3628
My lost lecture in SQL Server forum today deal with :
• Using the old & new Analytic Functions in SQL 2012
I have read from SQL Server Magazine that you are writing book on windows function .
The examples in present and script are unique taken from ORACLE, which have implemented Analytic Functions long ago :
· Using ROLLUP with Analytic Function
· Using CUBE and RANK() with Analytic Function
· Using GROUPING SETS and RANK() with Analytic Function
· Using the CUME_DIST() and PERCENT_RANK() Functions
What I wish in Next Version:
Use the inverse percentile functions -Next version maybe
Performing a Cumulative Sum
Performing a Moving Average
Performing a Centered Average
Using the Hypothetical Rank and Distribution Functions- Next version maybe ?
Enjoy !!!

David Itshak | 08/02/2012 בשעה 22:08
commenter

יש פונקציות שקיימות ב- Oracle ועדיין לא מומשו ב- SQL 2012  למשל : Using the Hypothetical Rank and Distribution Functions-  Next version maybe ? •          Use  the hypothetical rank and distribution functions to calculate the rank and percentile that a new row would have if you inserted it into a table. •          Perform hypothetical calculations with the following functions: RANK(), DENSE_RANK(), PERCENT_RANK(), and CUME_DIST() to get the rank and percent rank of sales by product type for 2012:  

david Itshak | 13/02/2012 בשעה 20:56
commenter

Hi
See my l last lecture under (I am also ORACLE DBA , so  I am using windows function for 6 years ).

Fast
transition to sql server 2012 from mssql 2005 2008 for developers – David
Yitzhak

http://www.sqlserver.co.il/?p=3668

In lecture
Look for windows function like you never know with new implementations and I whish
for SQL server 2014 :
Using the RANK() and DENSE_RANK() Functions -1

Controlling
Ranking of Null Values – Next version maybe

Using
the PARTITION BY Clause with Analytic Functions

Using
ROLLUP with Analytic Function

Using  CUBE and RANK() with Analytic Function

Using   GROUPING SETS and RANK()  with Analytic Function

Using
the CUME_DIST() and PERCENT_RANK() Functions

Use the
inverse percentile functions -Next version maybe

Use the
inverse percentile functions -Next version maybe

Performing
a Cumulative Sum – CONT

Performing
a Moving Average

Performing
a Centered Average

Getting
the First and Last Rows Using FIRST_VALUE() and LAST_VALUE()

Using
the Reporting Function

Using
the LAG() and LEAD() Functions

David Itshak

 

david itshak | 17/03/2012 בשעה 12:24
commenter

שלום רב
הרצאה אחרונה שלי מ 2.2012 בפורום SQL Server הועלתה לאתר הרשמי של מיקרוסופט לבלוג של צוות הפיתוח בסוף הדף – לדף ההשקה של SQL 2012 .
בהרצאה מדובר בעיקר על שימוש בפונקציות אנליטיות , פונקציות חלון בצורה מתקדמת כולל מה חסר עדיין בגרסה החדשה .
בהרצאה אני מפנה לנושאים של T&Eבתהליכי DRP ושימוש ב SOS (System of Systems) .
אז הנה הנדסת מערכת בטכניון נחשפה למיליונים וגם פורום sql server הישראלי !
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

David Itshak

11 Mar 2012 3:41 AM
For some time, other DBMSs such as Oracle, Sybase and DB2 have had support for window functions. SQL Server has had only a partial implementation up to now. SQL Server 2012 enhances support for windows aggregate function by introducing windows order and frame clause, support for offset functions, and support for distribution function. In this article I will show you advanced use of those function. The interesting thing is that the examples were taken from other RDBMS (ORACLE, SYBASE). Also I will demonstrate what is still missing and needed in SQL SERVER 2012 and how I would accept Microsoft to implement it, in next version Maybe.
This article is accompanied by the script fast_transition_to_mssql_2012.sql which demonstrates Programmability Enhancements (Database Engine) SQL 2012 as well as the material covered in this article.
http://www.slideshare.net/…/fast-transition-to-sql-server-2012-from-mssql-2005-2008-for-developers-david-yitzhak
להשתמע עוד.
ד יצחק

commenter

שלום לכולם
בספטמבר יפורסם ב- SQL Server Magazine מאמר  שמבוסס על הרצאה שהעברתי בפברואר השנה ב- SQL Server Forum והועלה ל- launch Event של SQL Server Magazine . מאמר זה מתבסס על ניסיון למעלה משנה עם גרסת בטא של SQL 2012  ומבוסס על ניסיוני כ- DBA של Oracle (שם יכולת זו קיימת למעלה מעשור ) .
הנה פרטי המאמר :
 
Article Title: Using SQL Server 2012’s Window Functionality
Using Window Ranking Functions
Using the ROLLUP, CUBE, and GROUPING SETS Operators
Using Window Distribution Functions
Using Window Aggregate Functions
Using Window Offset Functions
 
Date: 07/18/12
Publication: SQL Server Pro
Month & Year: September 2012
Database Number: R2R2043
InstantDoc ID: 143309
 
שימו לב שבמסגרת ILOUG – DBA Forum – North אני ארחיב את היריעה ואדבר על שימוש של BI ב- SQL עבור DAX ו Microsoft BISM והקשר ל- MDX . אני אדגים כיצד השתמשנו ביכולת אנליטיות אלה בחוות עיבוד מקבילי של Microsoft HPC . חומר ייחודי וחדשני .
כולכם מוזמנים !
ד יצחק

commenter

שלום רב
מוזמנים להרצאה השלישית שלי במספר במסגרת ILOUG – DBA Forum – North. הנושא מרתק וכולל חשיפה של ה- DBA לשימוש מתקדם בפונקציות חלון של Oracle  , מבוא ל BI  :  MDX ו- DAX ומה הקשר לעזאזל עם פונקציות חלון .

רקע
שימוש מתקדם בפונקציות חלון ופונקציות אנליטיות ב- Oracle עבור DW , BI ו- Data Mining
מתי להשתמש בפונקציות האלה ומתי להשתמש ב- MDX (כמו בהיפריון) או בהרחבה של מיקרוסופט ל- Excel :   Power Pivot  ו- DAX
החומר מבוסס על מערכת סימולציה ותחקור שעובדת ע"ג Oracle 11GR2 ע"ג Win 2008 R2 בחוות עיבוד מקבילי מבוססת HPC של מיקרוסופט . ה-Instance הוא אחד הגדולים בעולם ע"ג WIN : 4 טרה של data .
 
ד יצחק

השאר תגובה:

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

*



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

קהילת

קהילת ה- 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 בלוגרים אהבו את זה: