Window Functions

פונקציות חלון מאפשרות חישובים מורכבים.

פונקציות חלון גם מאפשרות להשוות בין ערכי רשומות מאותה הטבלה ללא צורך ב-Self join.

Function(arg1,..., argn) OVER ( [PARTITION BY <...>] 
    [ORDER BY <....>] [<window_clause>] )

דוגמא: הפקודה הבאה מציגה את מס' וסכום הכולל של המוצרים לקטגוריות אולם אינה מציגה את הנתונים בכל קבוצה.

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

SELECT 
    COUNT(*) AS CAT_COUNT, 
    SUM(unitprice) AS CAT_SUM 
FROM products 
WHERE CategoryID IN (2, 3)

כדי לראות לאיזה קטגוריה שייך כל נתון נוכל להשתמש ב-group by כך. כאן נקבל שתי שורות, שורה אחת לכל קטגוריה עם הנתונים שמתאימים לה.

SELECT 
    categoryID , 
    COUNT(*) AS CAT_COUNT,
    SUM(unitprice) AS CAT_SUM 
FROM Products 
WHERE CategoryID IN (2, 3) 
GROUP BY CategoryID

אבל גם פה נראה את מספרי הקטגוריות, אבל לא את פירוט הנתונים בכל קבוצה.

OVER

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

<function> OVER ([PARTITION BY clause]
                 [ORDER BY clause]
                 [ROWS or RANGE clause])

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

במקרה שלנו נקבל בעמודת CAT_COUNT את המספר 25 שהוא מספר המוצרים בקטגוריות 2 ו-3. אבל בשונה מהשאילתות הקודמות נקבל פה פירוט של הנתונים. מספר השורות יהיה כמספר המוצרים שנכנסים לחישוב.

SELECT 
    ProductID , 
    CategoryID, 
    COUNT(*) OVER() AS CAT_COUNT
FROM Products 
WHERE CategoryID IN (2, 3)

PARTITION BY

אם נפרט PARTITION BY CategoryID נקבל בשורות של קטגוריה 2 את המספר 12 שהוא מספר המוצרים בקטגוריה ובשורות של קטגוריה 3 את המספר 13 שהוא מבפר המוצרים בקטגוריה 3.

SELECT 
    ProductID , 
    CategoryID, 
    COUNT(*) OVER (PARTITION BY CategoryID) as CAT_COUNT
FROM Products 
WHERE CategoryID IN (2, 3)

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

SELECT 
    ProductID , 
    CategoryID, 
    unitprice, 
    COUNT(*) OVER ( PARTITION BY categoryID ) CAT_COUNT, 
    SUM(unitprice) OVER(PARTITION BY categoryID ) sum_cat 
FROM Products 
WHERE CategoryID IN (2, 3);

ROWS

שורת ROWS מאפשרת להציג מידע מחושב מצטבר על פי סדר המיון.

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

החלוקה על פי שנים: PARTITION BY YEAR(orderdate).

הסדר על פי השנים: order by YEAR(orderdate).

טווח השורות נקבע על ידי: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

-- כל התוצאה של המקטע
BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-- מסמן 5 שורות. תתחיל 4 שורות לפני השורה הנוכחית ותחשב עד לשורה הנוכחית
BETWEEN 4 PRECEDING AND CURRENT ROW

-- להכניס לתוצאה את החישוב מהשורה הנוכחית ועד סוף המקטע
BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

-- כל השורות מתחילת המקטע עד לשורה הנוכחית
UNBOUNDED PRECEDING
--OR
BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SELECT 
    YEAR(o.orderdate) AS "Year", 
    unitprice, 
    sum(od.unitprice) OVER(PARTITION BY YEAR(orderdate) 
        order by YEAR(orderdate) ROWS BETWEEN UNBOUNDED 
            PRECEDING AND CURRENT ROW) 
        AS Running_sum 
FROM orders o JOIN [Order Details] od 
ON o.orderid = od.OrderID 
WHERE CUSTOMERID = 'ALFKI'

ROW_NUMBER

פונקציית ROW_NUMBER משמשת ליצירת רצף של מספרים לכל קבוצה בתוצאות שלנו.

בדוגמא יש לנו חלוקה לפי שנים, אז לכל שנה יהיה רצף של מספרי שורה משלה.

SELECT 
    YEAR(o.orderdate) AS "YEAR" , 
    unitprice, 
    row_number () OVER(PARTITION BY YEAR(orderdate) 
        order by orderdate) AS ROW_NUM 
FROM orders o JOIN [Order Details] od 
ON o.orderid = od.OrderID 
WHERE CUSTOMERID = 'ALFKI'

RANK

הפונקציה RANK מחזירה את הדירוג של הערך בקבוצה.

בדוגמא יש לנו נתונים של מחירים בסדר יורד. פונקציית RANK תתן להם מספר על פי הסדר שלהם במיון. במקרה הה המוצר עם המחיר הכי גבוה יקבל 1 והמוצר עם המחיר הכי נמוך יקבל את המספר הגבוה, שזה מספר המוצרים הקיים בשאילתא. מוצרים שיש להם את אותו המחיר יקבלו את אותו הדירוג. למשל, שני מוצרים במחיר 39 יקבלו דירוג 4, אבל יהיה דילוג על דירוג 5. המוצר אחר כך יקבל דירוג 6.

SELECT 
    productid, 
    productname, 
    unitprice, 
    RANK() OVER(ORDER BY unitprice DESC) AS pricerank 
FROM Products 
ORDER BY pricerank

DENSE_RANK

הפונקציה DENSE_RANK עובדת כמו RANK, אבל הדירוג שלה רציף. כלומר אם שני מוצרים קבלו את הדירוג 4, לא יהיה דילוג והמוצר הבא יקבל את הדירוג 5.

NTILE

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

בדוגמא תהיה חלוקה ל-4 קבוצות של מידע.

SELECT 
    productid, 
    productname, 
    unitprice, 
    NTILE (4) OVER(ORDER BY unitprice DESC) AS pricerank 
FROM Products 
where unitprice <10 
ORDER BY pricerank

Lag / Lead Functions

פונקציות שנותנות גישה בשורה הנוכחית לשדות אשר קיימות בשורות הבאות והקודמות.

בשאילתא שלפנינו נקבל את רשימת המוצרים מסודרים לפי מספר המוצר.

SELECT 
    productID, 
    ProductName, 
    UnitPrice 
FROM products 
ORDER BY productID

בשאילתא הבאה נקבל את אותם הנתונים עם 2 עמודות חדשות, אחת שמביאה את הערך הקודם ואחת שמביאה את הערך הבא.

SELECT 
    productID,
    ProductName,
    unitPrice,
    LAG(unitPrice, 1) OVER (ORDER BY unitPrice) as lag,
    LEAD(unitPrice,1) OVER (ORDER BY unitPrice) as lead 
FROM products 
WHERE categoryID = 1 
ORDER BY unitPrice

תרגול Window Functions – שאלות

  1. מתוך טבלת מוצרים הצג את שם המוצר, מחירו, מס' הקטגוריה שלו וסך כל המחירים עבור כל המוצרים בתוך טבלה זו.
  2. מתוך טבלת מוצרים הצג את שם המוצר, מחירו, מספר הקטגוריה שלו וסך כל המחירים עבור מס' הקטגוריה של אותו מוצר.
  3. מתוך טבלת מוצרים הצג את שם המוצר, מחירו, מספר הקטגוריה שלו וסך כל המחירים עבור מס' הקטגוריה של אותו מוצר. הצג את המוצרים בכל קטגוריה ממויינים לפי מחירם – מהגבוה לנמוך.
  4. מתוך טבלת מוצרים הצג את שם המוצר, מחירו, מספר הקטגוריה שלו וסך כל המחירים עבור מס' הקטגוריה של אותו מוצר. הצג את המוצרים בכל קטגוריה ממויינים לפי מחירם – מהגבוה לנמוך כאשר סכימת המחירים תתבצע מהשורה הראשונה של אותה קטגוריה עד השורה האחרונה.
  5. מתוך טבלת מוצרים הצג את שם המוצר, מחירו, מספר הקטגוריה שלו וסך כל המחירים עבור מס' הקטגוריה של אותו מוצר. הצג את המוצרים בכל קטגוריה ממויינים לפי מחירם – מהגבוה לנמוך כאשר סכימת המחירים תתבצע מהשורה הראשונה של אותה קטגוריה עד השורה הנוכחית.
  6. מתוך טבלת מוצרים הצג את שם המוצר, מחירו, מספר הקטגוריה שלו, דירוגו לפי מחיר באמצעות פונקציית Rank, דירוגו לפי מחירו באמצעות פונקציית Dense_Rank, מספר השורה, ומספר קבוצתו בהנחה שהשורות בטבלה חולקו ל 10 קבוצות.
  7. מתוך טבלת מוצרים הצג את שם המוצר, מחירו, מספר הקטגוריה שלו, דירוגו בתוך הקטגוריה בה הוא נמצא לפי מחירו באמצעות פונקציית Rank, דירוגו בקטגוריה בה הוא נמצא לפי מחירו באמצעות פונקציית Dense_Rank, מספר השורה, ומספר קבוצתו בהנחה שהשורות בטבלה חולקו ל 10 קבוצות.
  8. מתוך טבלת מוצרים הצג את מספר המוצר, שם המוצר, מחירו, מחיר המוצר הקודם לו, מיון לפי מחיר המוצר, מחיר המוצר הבא אחריו, מיון לפי מחיר המוצר( עבור המוצרים אשר מספר הקטגוריה שלהם היא 1. מיין את תוצאות השאילתה לפי מחיר המוצר.

תרגול Window Functions – תשובות

-- ex1
SELECT 
    ProductName,
    UnitPrice,
    CategoryID,
    SUM(unitprice) over() AS ORDERS_SUM
FROM products 

-- ex2
SELECT 
    ProductName,
    UnitPrice,
    CategoryID,
    SUM(unitprice) over(PARTITION BY CategoryID) AS CAT_SUM
FROM products 

-- ex3
SELECT 
    ProductName,
    UnitPrice,
    CategoryID,
    SUM(unitprice) over(PARTITION BY CategoryID Order by unitprice desc) 
        AS CAT_SUM
FROM products 

-- ex4
SELECT 
    ProductName,
    UnitPrice,
    CategoryID,
    SUM(unitprice) over(PARTITION BY CategoryID 
        Order by unitprice desc ROWS BETWEEN UNBOUNDED 
        PRECEDING AND UNBOUNDED FOLLOWING) AS CAT_SUM
FROM products 

-- ex5
SELECT 
    ProductName,
    UnitPrice,
    CategoryID,
    SUM(unitprice) over(PARTITION BY CategoryID Order by 
        unitprice desc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
        AS CAT_SUM
FROM products 

-- ex6
SELECT 
    ProductName,
    UnitPrice,
    CategoryID,
    RANK() OVER(ORDER BY unitprice) AS pricerank,
    DENSE_RANK() OVER(ORDER BY unitprice) AS price_denserank,
    row_number () OVER(ORDER BY unitprice) AS rownum,
    NTILE (10) OVER(ORDER BY unitprice) AS groupnum
FROM products 

-- ex7
SELECT 
    ProductName,
    UnitPrice,
    CategoryID,
    RANK() OVER(PARTITION BY CategoryID ORDER BY unitprice) AS pricerank,
    DENSE_RANK() OVER(PARTITION BY CategoryID ORDER BY unitprice) 
        AS price_denserank,
    row_number () OVER(ORDER BY CategoryID) AS rownum,
    NTILE (10) OVER(ORDER BY CategoryID) AS groupnum
FROM products 

-- ex8
SELECT 
    ProductID,
    ProductName,
    UnitPrice,
    LAG(unitPrice, 1) OVER (ORDER BY unitPrice) as lag,
    LEAD(unitPrice,1) OVER (ORDER BY unitPrice) as lead 
FROM products 
where CategoryID = 1
order by UnitPrice

ניווט במאמר

מאמרים אחרונים

Weekly Tutorial