ב-SQL, סכום מצטבר מתייחס לשיטה המאפשרת לנו לחשב את הסכום הרצוי של עמודה מספרית בתוך טבלת מסד נתונים. סכום מצטבר נכנס לפעולה במגוון רחב של תרחישים כמו מעקב אחר ההתקדמות או חישוב הממוצעים המתגלגלים של ערך נתון.
במדריך זה נלמד כיצד ליישם ולנצל את הסכום המצטבר ב-SQL באמצעות שיטות וטכניקות שונות.
נתונים לדוגמה
כדי להבין טוב יותר כיצד אנו יכולים ליישם ולהשתמש בסכום המצטבר, הבה נתחיל בהגדרת טבלה בסיסית עם נתונים לדוגמה. זה מאפשר לנו להדגים את פעולתן של כל השיטות במהירות וביעילות בפוסט זה.
התחל ביצירת טבלה בסיסית המאחסנת את פרטי המוצר.
CREATE מוצרי TABLE (
product_id INT PRIMARY KEY,
שם המוצר VARCHAR ( 255 ) ,
מחיר דצימלי ( 10 , 2 )
) ;
זה אמור ליצור טבלה חדשה בשם 'מוצרים' המאחסנת את מזהה המוצר, שם המוצר והמחיר של כל מוצר.
לאחר מכן נוכל להמשיך ולהוסיף נתונים לדוגמה כפי שמוצג בהצהרות ה-insert לדוגמה הבאות:
INSERT INTO מוצרים ( מזהה_מוצר, שם_מוצר, מחיר )
ערכים
( 1 , 'קוד סטודיו ויזואלי' , 10.00 ) ,
( 2 , 'טקסט נשגב' , 80.00 ) ,
( 3 , 'PyCharm Professional' , 199.00 ) ,
( 4 , 'Eclipse IDE' , 30.00 ) ,
( 5 , 'IntelliJ IDEA Ultimate' , 699.00 ) ,
( 6 , 'GitHub Desktop' , 20.00 ) ,
( 7 , 'תוכנת המתן' , 10.00 ) ,
( 8 , 'Xcode' , 660.00 ) ,
( 9 , 'NetBeans' , 0.00 ) ,
( 10 , 'אָטוֹם' , 60.00 ) ;
הערה: הנתונים הנתונים הם בדיוניים לחלוטין. הוא אינו מייצג את התמחור בפועל של אף אחד מהפריטים הרשומים.
הטבלה המתקבלת היא כדלקמן:
סכום מצטבר של SQL (הצטרפות עצמית)
אחת הטכניקות שבהן אנו יכולים להשתמש כדי לבצע סכום מצטבר בעמודה נתונה היא שימוש בשיטת החיבור העצמי. יתרון אחד של שיטה זו הוא שהיא פועלת כמעט בכל מסדי הנתונים של SQL, גם אלה שאינם תומכים בפונקציות ה-Window.
קחו למשל את טבלת ה'מוצרים' הקודמת. אנו יכולים ליצור את הסכום המצטבר של עמודת המחיר כפי שמוצג בשאילתה הבאה:
בחרp1.product_id,
p1.product_name,
p1.price,
סְכוּם ( p2.price ) AS מצטבר_sum
מ
מוצרים p1
לְהִצְטַרֵף
מוצרים p2
עַל
p1.product_id > = p2.product_id
GROUP BY
p1.product_id,
p1.product_name,
p1.price
מיין לפי
p1.product_id;
שמתם לב לפעולה של השאילתה? אם לא, הישאר איתנו כשאנחנו מסבירים את זה צעד אחר צעד.
בשאילתת הדוגמה הנתונה, אנו מתחילים ביצירת שני כינויים - 'p1' ו- 'p2' - לטבלת 'מוצרים' המאפשרת לנו לבצע הצטרפות עצמית.
לאחר מכן נמשיך לצרף את 'p1' ו- 'p2' בתנאי ש-'product_id' של 'p1' גדול או שווה ל-'product_id' של 'p2'.
בשלב הבא, אנו קוראים לפונקציה sum() שבעצם מחשבת את הסכום המצטבר של המחירים עבור כל שורה.
לבסוף, אנו מקבצים את התוצאות באמצעות 'מזהה_מוצר', 'שם_מוצר' ו'מחיר' ומסדרים את התוצאות.
לאחר פעולה זו, אמור להיות לנו סכום מצטבר עבור כל רשומה כפי שמוצג בטבלה המתקבלת כפי שמוצג להלן:
כפי שאתה יכול לראות, אנו מקבלים את הסכום הכולל של כל השורות הקודמות. השורה האחרונה צריכה להכיל את הסכום הכולל של כל השורות הקודמות.
סכום מצטבר של SQL (פונקציות חלון)
דרך יעילה ופרקטית יותר לחישוב הסכום המצטבר ב-SQL היא ניצול פונקציות החלון בכל פעם שהיא נתמכת.
אם יש לך מסד נתונים כגון SQL Server, PostgreSQL או MySQL גרסה 8.0 ומעלה, זוהי השיטה השימושית והמומלצת ביותר לקביעת הסכום המצטבר של עמודה נתונה.
תסתכל על הדוגמה כפי שהודגם בקטע הבא:
בחרמזהה מוצר,
שם מוצר,
מחיר,
סְכוּם ( מחיר ) על ( ORDER BY product_id ) AS מצטבר_sum
מ
מוצרים;
במקרה זה, אנו מתחילים בבחירת העמודות 'מזהה_מוצר', 'שם_מוצר' ו'מחיר' מטבלת 'מוצרים'.
לאחר מכן אנו משתמשים בפונקציה SUM() כפונקציית חלון באמצעות פסקת OVER.
בסעיף OVER, אנו מציינים את פסקת ORDER BY המגדירה את הסדר שבו מחושב הסכום המצטבר.
זה אמור להחזיר פלט דומה כפי שמוצג להלן:
תבחין שהשימוש בפונקציות החלון הוא קוהרנטי, יעיל וקריא יותר בהשוואה לשימוש בחיבורים עצמיים.
סיכום
במדריך זה, למדנו הכל על סכומים מצטברים ב-SQL. סקרנו גם כיצד להשתמש בפונקציות החיבורים העצמיים והחלון כדי לבצע את הסכום המצטבר ב-SQL.