יחסיים לאחסן כמויות עצומות של נתונים בצורה של טבלאות. טבלאות אלה יכולות לכלול מספר שורות ועמודות. אבל, מה אם היית צריך לשנות את הנתונים ברמת השורה לנתונים עמודיים? ובכן, במאמר זה על SQL Pivot, אני אראה לך כיצד תוכל להמיר שורות לעמודה בשרת SQL.
הנושאים הבאים יוסרו במאמר זה:
מה זה PIVOT ב- SQL?
PIVOT משמש לסיבוב ערך הטבלה על ידי המרת הערכים הייחודיים של עמודה אחת למספר עמודות. הוא משמש לסיבוב השורות לערכי עמודות ומריץ צבירות כנדרש בערכי העמודות הנותרים.
לעומת זאת, UNPIVOT משמש לביצוע הפעולות ההפוכות. לכן, הוא משמש להמרת העמודות של טבלה ספציפית לערכי עמודות.
בהמשך למאמר זה, הבה נבין את התחביר של SQL Pivot.
תחביר:
בחר שם עמודות שאינו מופעל, [שם עמוד המסתובב ראשון] כעמודה שם, [שם עמוד שני מסתובב] כעמודה שם, [שם עמודה שלישי המסתובב] כעמודה שם, ... [שם עמודה אחרונה המסתובב] כעמודה שם FROM (שאילתת בחר המייצרת את הנתונים) כ [כינוי לשאילתה הראשונית] PIVOT ([AggregationFunction] (ColumName) עבור [ColumnName של העמודה שערכיה יהפכו לכותרות עמודה] IN ([First ColumnName מופנה], [Second ColumnName המופנה], [שם העמודה השלישי המופנה] ... [אחרון עמודה ציר])) AS [כינוי לטבלת הצירים]
כאן,אתה יכול גם להשתמש ב- סעיף לפי סדר כדי למיין את הערכים בסדר עולה או יורד. עכשיו שאתה יודע מה זה PIVOT ב- SQL והתחביר הבסיסי שלו, הבה נתקדם ונראה כיצד להשתמש בו.
דוגמאות
להבנתך הטובה יותר, אשקול את הטבלה הבאה כדי להסביר לך את כל הדוגמאות.
טבלת ספקים:
ספק מזהה | DaysofManufacture | עֲלוּת | מספר לקוח | רכישת מזהה |
אחד | 12 | 1230 | אחת עשרה | P1 |
2 | עשרים ואחת | 1543 | 22 | P2 |
3 | 32 | 2. 3. 4. 5 | אחת עשרה | P3 |
4 | 14 | 8765 | 22 | P1 |
5 | 42 | 3452 | 33 | P3 |
6 | 31 | 5431 | 33 | P1 |
7 | 41 | 2342 | אחת עשרה | P2 |
8 | 54 | 3654 | 22 | P2 |
9 | 33 | 1234 | אחת עשרה | P3 |
10 | 56 | 6832 | 33 | P2 |
תן לנו לכתוב שאילתה פשוטה כדי לאחזר את העלות הממוצעת שהוצא על ידי כל לקוח.
בחר לקוח מזהה, ממוצע (עלות) כממוצע מחיר לקוח ממספקי קבוצה לפי לקוח מזהה
תְפוּקָה:
מספר לקוח | ממוצע לקוחות |
אחת עשרה | 1787.75 |
22 | 4654 |
33 | 5238.33 |
עכשיו, נניח שאנחנו רוצים לסובב את הטבלה שלעיל. כאן, ערכי העמודות של CustomerID יהפכו לכותרות העמודות.
- צור טבלת ציר עם שורה אחת ושלוש עמודות בחר 'ממוצע של לקוחות' כעלות מחיר בהתאם ללקוחות, [11], [22], [33] FROM (בחר לקוח מזהה, עלות מספקים) כטבלת מקור PIVOT (ממוצע (עלות) עבור לקוח ID ב [11], [22], [33])) AS PivotTable
תְפוּקָה:
עלות_לפי_לקוחות | אחת עשרה | 22 | 33 |
ממוצע לקוחות | 1787.75 | 4654 | 5238.33 |
הערה: כשאתה משתמש פונקציות מצטברות עם PIVOT, ערכי null אינם נחשבים בעת חישוב צבירה.
ובכן, זו הייתה דוגמה בסיסית, אך בואו נבין כעת כיצד פעל סעיף PIVOT.
עבודה בסעיף PIVOT
כפי שאתה יכול להתייחס לעיל, כדי ליצור טבלת PIVOT, עליך לבצע את השלבים הבאים:
- בחר עמודות לציר
- לאחר מכן בחר טבלת מקור.
- החל את מפעיל ה- PIVOT ולאחר מכן השתמש בפונקציות המצטברות.
- הזכר ערכי ציר.
בחר עמודות לציר
בתחילה עלינו לציין את השדות שייכללו בתוצאות שלנו. בדוגמה שלנו שקלתי את העמודה AverageCostofCustomer בטבלת ה- Pivot. ואז יצרנו שלוש עמודות נוספות עם כותרות העמודות 11, 22 ו- 33. דוגמא-
בחר 'ממוצע עלויות לקוח' כעלות_לפי_לקוחות, [11], [22], [33]
בחר טבלת מקור
לאחר מכן, עליך לציין את משפט SELECT שיחזיר את נתוני המקור לטבלת הציר. בדוגמה שלנו, אנו מחזירים את ה- CustomerID והעלות מטבלת הספקים.
(בחר לקוח מזהה, עלות מספקים) כטבלת מקור
החל את מפעיל ה- PIVOT ולאחר מכן השתמש בפונקציות המצטברות
לאחר מכן, עליך לציין את הפונקציה המצרפית שתשתמש בה בעת יצירת טבלת הציר. בדוגמה שלנו השתמשתי בפונקציית AVG כדי לחשב את העלות הממוצעת.
PIVOT (ממוצע (עלות)
הזכר ערכי ציר
לבסוף, עליך להזכיר את הערכים שיש לכלול בטבלת הציר שהתקבלה. ערכים אלה ישמשו ככותרות העמודות בטבלת הציר.
מה מסדרת בג'אווה
עבור לקוח ב- ([11], [22], [33])) כטבלת ציר
כך עובדים מפעילי PIVOT. בהמשך במאמר זה על SQL PIVOT, הבה נבין עד כמה הוא שונה מ- SQL UNPIVOT.
SQL UNPIVOT
אופרטור SQL UNPIVOT משמש לביצוע הפעולה ההפוכה מזו של PIVOT. הוא משמש לסיבוב נתוני העמודות לנתונים ברמת השורה. התחביר, של UNPIVOT, דומה לזה של PIVOT. ההבדל היחיד הוא שאתה צריך להשתמש ב- ' UNPIVOT ” .
דוגמא:
בואו ניצור טבלה עם העמודות SupplierID, AAA, BBB ו- CCC. כמו כן, הכנס מעט ערכים.
צור לוח מדגם (ספק ID int, AAA int, BBB int, CCC int) עבור הכנס לערכים מדגמים (1,3,5,6) הכנס לערכים לדוגמא (2,9,2,8) הכנס לערכים לדוגמא (3, 8,1,7) GO
תְפוּקָה:
ספק מזהה | AAA | BBB | CCC |
אחד | 3 | 5 | 6 |
2 | 9 | 2 | 8 |
3 | 8 | אחד | 7 |
עכשיו, בואו נגיד, אנחנו רוצים לבטל את ציר השולחן. לשם כך, אתה יכול להתייחס לקוד הבא:
בחר ספק ID, לקוחות, מוצרים FROM (בחר ספק D, AAA, BBB, CCC FROM טבלה) p UNPIVOT (מוצרים ללקוחות ב (AAA, BBB, CCC)) כדוגמה GO
ספק מזהה | לקוחות | מוצרים |
אחד | AAA | 3 |
אחד | BBB | 5 |
אחד | CCC | 6 |
2 | AAA | 9 |
2 | BBB | 2 |
2 | CCC | 8 |
3 | AAA | 8 |
3 | BBB | אחד להמיר בינארי ל- int Java |
3 | CCC | 7 |
כך תוכלו להשתמש ב- SQL PIVOT וב- UNPIVOT. בכך אנו מגיעים לסוף מאמר זה. אני מקווה שהבנת, איך להשתמש ב- SQL. אם ברצונך ללמוד עוד על MySQL ולמד להכיר את בסיס הנתונים היחסי של קוד פתוח, ואז לבדוק את שלנו שמגיע עם הדרכה חיה בהנחיית מדריך וניסיון פרויקט אמיתי. הכשרה זו תעזור לך להבין את MySQL לעומק ותעזור לך להשיג שליטה בנושא.
יש לך שאלה עבורנו? אנא הזכיר זאת בפרק ההערות במאמר זה בנושא SQL Pivot ואחזור אליך.