*** לפודקאסט של הבלוג לחצו כאן ****
הסבר על פונקציות חלון
פונקציות חלון או windows function (לעיתים מכונות גם פונקציות אנליטיות) הן הרחבה של שפת ה- SQL הסטנדרטית והן יכולות לבצע חישובים על מספר רב של שורות (החישוב עצמו מבוצע ע"י שאילתה פנימית ונסתרת של מנוע הדאטה-בייס) מהפונקציות האלה חוזר ערך אחד שמתאים לכל שורה בשאילתה שכתבנו.
הפונקציות נקראות 'חלון' כי החלון מגדיר את טווח השורות בחישוב. החלון של הפונקציה מוגדר ע"י פקודת Over.
על השורות בתוך החלון אפשר להגדיר קבוצות ע"י הוראת Partition BY ואף לקבוע את הסדר שלהם ע"י הוראת Order By. ניתן גם לצמצם את טווח החלון ע"י ההוראות:
rows, preceding, following.
דוגמה
select
orders.*,
sum(sum_order) over () as sum_of_all_orders,
sum(sum_order) over (partition by customer_id) as sum_of_customer_orders,
max(sum_order) over (partition by country) as max_order_at_country,
avg(sum_order) over (partition by country) as avg_order_at_country,
case when sum_order > avg(sum_order) over (partition by country) then 1 else 0 end as order_is_above_country_avg
from
orders
הסבר
sum(sum_order) over () as sum_of_all_orders
בפונקציה הזאת אין ערכים בתוך הוראת ה- over ולכן גודל החלון לא הוגבל. מכיוון שאין הגבלה לגודל החלון, החישוב של פונקציית ה- sum יתייחס לכל השורות בטבלה והפלט שיתקבל יהיה זהה לכל השורות בשאילתה. כלומר, סכום הרכישה יופיע בכל השורות.
sum(sum_order) over (partition by customer_id) as sum_of_customer_orders
הפונקציה הנ"ל דומה לפונקציה הקודמת אך חילקנו בה את החלון לקבוצות של לקוחות לפי Customer_id (למעשה ביצענו פעולה דומה לאגרגציה לפי Customer_id) ולכן בכל שורה נקבל את סכום הרכישות של הלקוח.
סוגים של פונקציות חלון
פונקציות אגרגטיביות (ראינו דוגמאות בחלק הקודם)
Count, Avg, Sum, Min, Max וכו' – פונקציות אלו משמשות להוספת חישובים אגרגטיבים על מספרים.
ערכים
Lag\Lead – מחזירות את הערך של השורה הקודמת או הבאה בפלט של הטבלה.
First_value\Last_value – מחזירות את הערך הראשון או האחרון של החלון.
פונקציות דירוג
Row_Number\Rank – משמשות לדרג את המספרים לפי סדר מסוים.
Percentile – משמשת לערך של אחוזונים.
Qualify
יש דאטה-בייסים המאפשרים להשתמש בסינון של פונקציות חלון. הוראה זאת מכונה Qualify.
בדוגמה הזאת, יתבצע סינון של כל הזמנות של הלקוחות שסך הרכישות שלהם הוא מעל 1,000 דולר.
select
orders.*,
sum(sum_order) over (partition by client_id) as sum_of_all_orders
from
orders
qualify
sum(sum_order) over (partition by client_id)>1000
תרגול שפת SQL
באתר strata scratch תמצאו תרגילים רבים עם שאלות עסקיות מורכבות, וממשק המאפשר כתיבה והרצה של שאילתות כך שתוכלו לבדוק האם הפתרון שלכם נכון. באתר יש גם אפשרות לתרגל תרגילים ב- Python.
התרגול באתר הוא ללא עלות, אך אפשר להירשם כדי לקבל גישה לתרגילים חדשים ופתרונות לכל התרגילים (גילוי נאות – רישום לאתר דרך הקישור מתגמל אותי בעמלה קטנה, וגולשים המגיעים דרך הקישור יכולים להשתמש בקוד קופון yuval20 על מנת לקבל 20% הנחה).
נכתב על ידי יובל מרנין,
לשרותי ניתוח נתונים ויצירת דוחות BI ניתן לפנות אליי ב- לינקדאין, פייסבוק או במייל: [email protected]
*** לפודקאסט של הבלוג לחצו כאן ****
English version of the article –Simple explanation on window functions in SQL
פרק קודם – פרק 11: סדר הוראות בשפת SQL
פרק הבא – פרק 13: CTE ושאילתות רקורסיביות בשפת SQL
