פונקציות RANK ו־DENSE_RANK

שיעור 9 מתוך 13 בקורס SQL למתקדמים של Coddy.

ROW_NUMBER() הוא סוג אחד של פונקציית דירוג, ויש עוד שתיים: RANK() ו-DENSE_RANK().

הפונקציה RANK() ממספרת שורות כמו ROW_NUMBER(), אבל מעניקה מספרים זהים לשורות זהות ומדלגת על מספרים. DENSE_RANK() דומה ל-RANK(), אבל אינה מדלגת על מספרים.

לדוגמה:

id	level
1	5
2	6
3	6
4	7
5	7
6	5

SELECT id, 
	ROW_NUMBER() OVER (ORDER BY level) as row_num,
	RANK() OVER (ORDER BY level) as row_rank,
	DENSE_RANK() OVER (ORDER BY level) as row_dense_rank,
FROM table1

התוצאה תהיה:

id	level	row_num	row_rank	row_dense_rank
1	5	1	1	1
2	6	2	2	2
3	6	3	2	2
4	7	4	4	3
5	7	5	4	3
6	5	6	6	4

ROW_NUMBER() מונה מ-1 עד 6 עם ערכים ייחודיים, RANK() מחזירה את אותם הערכים עבור אותן רמות, אבל מדלגת על 3 ועל 5, ו-DENSE_RANK() גם מחזירה את אותם הערכים עבור אותן רמות, אבל אינה מדלגת על אף ערך.

אתגר

בינוני

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

מדליית ונדיום מייצגת 5 נקודות

מדליית כסף מייצגת 3 נקודות

מדליית ארד מייצגת נקודה אחת

חשבו את הניקוד של כל אזור ואת המספר הכולל של המדליות שלו, ותנו לעמודות האלה את השמות total_score ו-total_medals בהתאמה. דרגו בצפיפות את העמודה total_medals ותנו לעמודה הזאת את השם final_rank.

לבסוף, סדרו את התוצאה לפי final_rank ו-region ו-

נסו בעצמכם

WITH region_total_medals AS (
    SELECT region, SUM(medal_count) as total_medals
    FROM medals
    GROUP BY region
), region_prepare_total_score AS (
    SELECT region, medal_color, medal_count, DENSE_RANK() OVER (ORDER BY medal_color) as medal_rank
    FROM medals
), region_total_score AS (
    SELECT region, SUM((medal_rank*2 - 1)*medal_count) as medal_rank
    FROM region_prepare_total_score
    GROUP BY region
)

SELECT region_total_medals.region, 
        total_medals, medal_rank,
        DENSE_RANK() OVER (ORDER BY total_medals) as final_rank
FROM region_total_score
JOIN region_total_medals ON region_total_medals.region = region_total_score.region

כל השיעורים ביחידה SQL למתקדמים

1מבוא

מבוא פישוט שאילתות באמצעות מילת המפתח WITH

4סיכום

אתגר מסכם מס׳ 1

2פונקציות חלון חלק 1

פונקציית ROW_NUMBER קריטריון ORDER BY קריטריון PARTITION BY פונקציות LEAD ו-LAG אתגר חזרה #1 אתגר חזרה #2

3פונקציות חלון – חלק 2

פונקציות RANK ו־DENSE_RANKפונקציית NTILE פונקציות צבירה קריטריון ROWS ו־RANGE

תרגלו בעצמכם: קומפיילר Python אונליין