במדריך זה, נלמד על פעולתו של פסקת PARTITION BY ב-SQL ונגלה כיצד אנו יכולים להשתמש בו כדי לחלק את הנתונים עבור תת-קבוצה מפורטת יותר.
תחביר:
נתחיל בתחביר של פסקת PARTITION BY. התחביר עשוי להיות תלוי בהקשר שבו אתה משתמש בו, אבל הנה התחביר הכללי:
בחר עמודה 1, עמודה 2, ...
OVER (PARTITION BY partition_column1, partition_column2, ...)
FROM table_name
התחביר הנתון מייצג את האלמנטים הבאים:
- עמודה 1, עמודה 2 - זה מתייחס לעמודות שברצוננו לכלול בערכת התוצאות.
- PARTITION BY עמודות - סעיף זה מגדיר כיצד אנו רוצים לחלק או לקבץ את הנתונים.
נתונים לדוגמה
תן לנו ליצור טבלה בסיסית עם נתונים לדוגמה כדי להדגים כיצד להשתמש בסעיף PARTITION BY. עבור דוגמה זו, בואו ניצור טבלה בסיסית המאחסנת את פרטי המוצר.
צור מוצרי TABLE (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR( 255 ),
קטגוריה VARCHAR( 255 ),
מחיר DECIMAL( 10 , 2 ),
כמות INT,
expiration_date DATE,
ברקוד BIGINT
);
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'כובע שף 25 ס'מ' ,
'מַאֲפִיָה' ,
24.67 ,
57 ,
'2023-09-09' ,
2854509564204 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'ביצי שליו - שימורים' ,
'מְזָוֶה' ,
17.99 ,
67 ,
'2023-09-29' ,
1708039594250 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'קפה - קפוצ'ינו ביצה נוג' ,
'מַאֲפִיָה' ,
92.53 ,
10 ,
'2023-09-22' ,
8704051853058 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'אגס - קוצני' ,
'מַאֲפִיָה' ,
65.29 ,
48 ,
'2023-08-23' ,
5174927442238 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'פסטה - שיער מלאך' ,
'מְזָוֶה' ,
48.38 ,
59 ,
'2023-08-05' ,
8008123704782 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'יין - Prosecco Valdobiaddene' ,
'ליצר' ,
44.18 ,
3 ,
'2023-03-13' ,
6470981735653 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'מאפה - מיני צרפתי מגוון' ,
'מְזָוֶה' ,
36.73 ,
52 ,
'2023-05-29' ,
5963886298051 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'כתום - שימורים, מנדרינית' ,
'ליצר' ,
65.0 ,
1 ,
'2023-04-20' ,
6131761721332 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'כתף חזיר' ,
'ליצר' ,
55.55 ,
73 ,
'2023-05-01' ,
9343592107125 );
לְהַכנִיס
לְתוֹך
מוצרים (שם_מוצר,
קטגוריה,
מחיר,
כַּמוּת,
תאריך תפוגה,
ברקוד)
ערכים ( 'Dc Hikiage Hira Huba' ,
'ליצר' ,
56.29 ,
53 ,
'2023-04-14' ,
3354910667072 );
ברגע שיש לנו את הגדרת הנתונים לדוגמה, נוכל להמשיך ולהשתמש בסעיף PARTITION BY.
שימוש בסיסי
נניח שאנו רוצים לחשב את סך הפריטים עבור כל קטגוריית מוצר בטבלה הקודמת. אנו יכולים להשתמש ב-PARTITION BY כדי לחלק את הפריטים לקטגוריות ייחודיות ולאחר מכן לקבוע את סך הכמות בכל קטגוריה.
דוגמה היא כדלקמן:
בחר
שם מוצר,
קטגוריה,
כַּמוּת,
SUM(quantity) OVER (PARTITION BY קטגוריה) AS total_items
מ
מוצרים;
שימו לב שבדוגמה הנתונה, אנו מחלקים את הנתונים באמצעות העמודה 'קטגוריה'. לאחר מכן אנו משתמשים בפונקציית הצבירה SUM() כדי לקבוע את סך הפריטים בכל קטגוריה בנפרד. התוצאה מציגה את סך הפריטים בכל קטגוריה.
שימוש בסעיף PARTITION BY
לסיכום, מקרה השימוש הנפוץ ביותר בסעיף PARTITION BY הוא בשילוב עם פונקציות החלון. פונקציית החלון מוחלת על כל מחיצה בנפרד.
חלק מפונקציות החלון הנפוצות לשימוש עם PARTITION BY כוללות את הדברים הבאים:
- SUM() - חשב את הסכום של עמודה בתוך כל מחיצה.
- AVG() - חשב את הממוצע של עמודה בתוך כל מחיצה.
- COUNT() - סופר את מספר השורות בתוך כל מחיצה.
- ROW_NUMBER() - הקצה מספר שורה ייחודי לכל שורה בתוך כל מחיצה.
- RANK() – הקצה דרגה לכל שורה בתוך כל מחיצה.
- DENSE_RANK() - הקצה דירוג צפוף לכל שורה בתוך כל מחיצה.
- NTILE() - חלקו את הנתונים לקוונטילים בתוך כל מחיצה.
זהו זה!
סיכום
במדריך זה, למדנו כיצד לעבוד עם פסקת PARTITION BY ב-SQL כדי לחלק את הנתונים למקטעים שונים ולאחר מכן להחיל פעולה ספציפית על כל אחת מהמחיצות שנוצרו בנפרד.