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

רגע לפני שמתחילים 🙋

השתמשנו בדאטהסט מהמם מאתר Kaggle- תוכלו לצפות בו בקישור הזה.
לאחר שהורדנו את הדאטהסט השמטנו ממנו רשומות שהיו לא רלוונטיות (כמו תחביבים, מקום לידה, שם האבא וכו’) - בכדי ליעל את גודל המסד ולדאוג ששליפות יהיו מהירות יותר.
כמו כן נרמלנו את שלושת הטבלאות כך שלא יהיו נתונים שחוזרים על עצמם פרט לקוד הספורטאי שאותו בחרנו בתור Primary Key.

על מנת להכניס את הדאטהסט הזה לתוך מסד הנתונים שלנו נעזרנו בכלי Convert CSV to SQL שפשוט הציל אותנו משעות של עבודה מאתגרת.

בכדי לאתחל את העבודה יש להוריד את הסקריפט ליצירת מסד הנתונים הכולל בתוכו את כלל המידע (העדכני ביותר נכון לכתיבת שורות אלו) דרך הקישור הזה

לפני שניתן יהיה להריץ את הסקריפט, ומכיוון שהוא נחשב כבד ומבצע לא מעט איטרציות על מסד הנתונים, יש לאפשר ל-MySQL Workbench לבצע Queries באורך של יותר מ-30 שניות.
על מנת לעשות זאת יש להיכנס ל:

Edit –> Preferences –> SQL Editor

ושם לשנות בשדה DBMS Connection Read Timeout Interval את הערך ל- 600 שניות ולא 30 כפי שמופיע כברירת מחדל.

לפני שנצלול לשאילתות ולמחקר שאנחנו רוצים לבצע על מסד הנתונים, ניגש תחילה לתרשים ה-ERD:
ERD (Entity-Relationship Diagram) הוא כלי חיוני לא רק בתכנון מסדי נתונים אלא גם בחקירתם. כאשר אנו בוחנים מסד נתונים קיים, ERD מספק לנו תצוגה גרפית וברורה של המבנה שלו, הקשרים בין הישויות השונות והאופן שבו הנתונים מאורגנים.
תרשים ה-ERD של המסד שלנו נראה כך (לחצו על התמונה להגדלה):

חקירה, שאילתות ומה שבניהם 🔬

על מנת להעשיר את הפלט בחרנו להשתמש בסגנונות כתיבת שאילתות רבים.

1. שאילתה מסוג JOIN

שאלת המחקר: מהו דירוג המדינות לפי יחס המדליסטים לחלק מיליון נפש? מדוע היא מעניינת: ישנן מדינות שאחוז המדליסטים בהם יחסית לאוכלוסיה הוא גבוה בהרבה ממדינות אחרות. הדבר יכול להצביע על ההשקעה של הממשלות והחברה האזרחית בענפי הספורט השונים.

SELECT 
    a.country AS Country,
    cp.Population2024 AS Population,
    COUNT(DISTINCT m.code) AS Num_Medallists,
    (COUNT(DISTINCT m.code) / cp.Population2024) * 1000000 AS Medallists_Per_Million
FROM  medallists m
JOIN  athletes a ON m.code = a.code
JOIN  country_population cp ON a.country = cp.Country
GROUP BY  a.country, cp.Population2024
ORDER BY  Medallists_Per_Million DESC;

תוצאותיה:

התובנות והמענה על שאלת המחקר: התוצאות מאוד מפתיעות. בעוד שהיינו מצפים שארה"ב תהיה אלופת המדד- דווקא מדינות שפחות ציפינו מהם מככבות בראש הרשימה.
דבר שיכול להוות כר פורה למחקר עתידי.

2. שאילתה מסוג Subquery

שאלת המחקר: מהו הפער בגילאים בין זוכי מדליות אישיות לבין קבוצתיות (באופן ממוצע) בחתך לפי ענפי הספורט השונים. מדוע היא מעניינת: אנחנו רוצים לאושש טענה לפיה ספורטאים צעירים יותר בעלי סיכוי רב יותר לזכות במדליה על פני חבריהם לקבוצה.

SELECT 
    m.discipline AS Discipline,
    AVG(TIMESTAMPDIFF(YEAR, a.birth_date, m.medal_date)) AS Avg_Age_Medallists,
    (
        SELECT 
            AVG(TIMESTAMPDIFF(YEAR, a2.birth_date, CURDATE()))
        FROM 
            teams t2
        JOIN 
            team_athletes ta ON t2.code = ta.team_code
        JOIN 
            athletes a2 ON ta.athlete_code = a2.code
        WHERE 
            t2.discipline = m.discipline
    ) AS Avg_Age_Team_Members
FROM 
    medallists m
JOIN 
    athletes a ON a.code = m.code
WHERE 
    INSTR(a.disciplines, m.discipline) > 0
GROUP BY 
    m.discipline
ORDER BY 
    Avg_Age_Medallists DESC;

תוצאותיה:

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

3. שאילתה מסוג CTE

שאלת המחקר: אילו מאמנים השיגו הכי הרבה מדליות? מדוע היא מעניינת: אנחנו רוצים לדעת אילו מאמנים השיגו הכי הרבה מדליות לרבות סוגי המדליות וענפי ספורט השונים.

WITH CoachMedalDetails AS (
    SELECT 
        a.coach,
        a.disciplines,
        SUM(CASE WHEN m.medal_type = 'Gold Medal' THEN 1 ELSE 0 END) AS gold_medals,
        SUM(CASE WHEN m.medal_type = 'Silver Medal' THEN 1 ELSE 0 END) AS silver_medals,
        SUM(CASE WHEN m.medal_type = 'Bronze Medal' THEN 1 ELSE 0 END) AS bronze_medals,
        COUNT(m.medal_type) AS total_medals
    FROM  Medallists m
    JOIN  Athletes a ON m.code = a.code
    WHERE a.coach IS NOT NULL AND a.coach != ''
    GROUP BY a.coach, a.disciplines
)
SELECT 
    coach,
    disciplines,
    SUM(gold_medals) AS gold_medals,
    SUM(silver_medals) AS silver_medals,
    SUM(bronze_medals) AS bronze_medals,
    SUM(total_medals) AS total_medals
FROM CoachMedalDetails
GROUP BY coach, disciplines
ORDER BY total_medals DESC, 
    gold_medals DESC, 
    silver_medals DESC, 
    bronze_medals DESC
limit 10

תוצאותיה:

התובנות והמענה על שאלת המחקר: ישנם מאמנים להם ידי קסם. מאמנים שיודעים לייצר ספורטאים מוצלחים אשר מצליחים בפועל להביא הישגים גדולים. ברגע שאנחנו כחוקרים יודעים לסמן את אותם המאמנים , נוכל לנסות ולחקור את התכונות והמאפיינים שלהם ולנסות לשכפלם.

הסבר השאילתה

מה עושה השאילתה הזו?

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

איך היא עושה את זה?

1. יצירת טבלה זמנית: השאילתה מתחילה ביצירת טבלה זמנית בשם CoachMedalDetails. הטבלה הזו משמשת כבסיס לחישובים הבאים.
2. חיבור טבלאות: היא מחברת שתי טבלאות: Medallists (רשימת הזוכים במדליות) ו-Athletes (רשימת הספורטאים). החיבור נעשה על סמך הקוד של הספורטאי, כדי לזהות איזה מאמן מאמן כל ספורטאי.
3. ספירת מדליות: עבור כל מאמן וענף ספורט, השאילתה סופרת את מספר המדליות מכל סוג (זהב, כסף, ארד). היא עושה זאת באמצעות פונקציית SUM וביטויים מותנים (CASE WHEN).
4. חישוב מדליות כולל: היא מחשבת את מספר המדליות הכולל של כל מאמן וענף.
5. מיון התוצאות: השאילתה מדרגת את התוצאות לפי מספר המדליות הכולל, ולאחר מכן לפי מספר מדליות הזהב, הכסף והארד.
6. הצגת התוצאות: לבסוף, השאילתה מציגה את 10 המאמנים המצליחים ביותר, כלומר אלו שזכו במספר הגדול ביותר של מדליות.

4. שאילתה עם UNION

שאלת המחקר: מה הגיל הממוצע של זוכי מדליות זהב בענפי הספורט שונים?
מדוע היא מעניינת: הבנת הגיל הממוצע לזכייה במדליות יאפשר לנו למטב תוכניות אימונים והשקעה בספורטאים על גבי ציר הזמן.

SELECT  discipline, AVG(age) AS avg_age
FROM (
    SELECT m.medal_type, m.discipline, TIMESTAMPDIFF(YEAR, a.birth_date, m.medal_date) AS age
    FROM medallists m
    JOIN athletes a ON m.code = a.code
    WHERE m.medal_type = 'Gold Medal'
) AS sub
GROUP BY medal_type, discipline

UNION

SELECT 'All Sports' AS sport, AVG(age) AS avg_age
FROM (
    SELECT m.medal_type, TIMESTAMPDIFF(YEAR, a.birth_date, m.medal_date) AS age
    FROM medallists m
    JOIN athletes a ON m.code = a.code
    WHERE m.medal_type = 'Gold Medal'
) AS sub
GROUP BY medal_type;

תוצאותיה:

התובנות והמענה על שאלת המחקר: הגיל הממוצע לזכיה במדליה עומד על 26.5 שנים. ישנם ענפי ספורט בהם אנחנו רואים שהממוצע נמוך (כמו טקוואנדו) , ז"א שאלה ענפי ספורט אשר דורשים יכולות של מתאמן צעיר על פני מתאמן בוגר יותר (כמו כדוריד לדוגמא).

5. שאילתת חלון (Window Function)

שאלת המחקר: מי הם עשרת המדינות המצטיינות בענפים ספורטיביים מסוימים, על סמך אחוז המדליות שהן זוכות בהן בכל ענף?
מדוע היא מעניינת? שאלת המחקר הזו מעניינת כי היא מאפשרת להבין אילו מדינות מצטיינות בתחומים ספורטיביים ספציפיים ואילו תחומים הם החזקים ביותר עבור כל מדינה. ההבנה של חלוקת המדליות לפי מדינה וענף יכולה לעזור בזיהוי תחומים שבהם מדינות מצטיינות באופן יחסי, המאפשר בדיקה מעמיקה יותר לגבי הסיבות להצטיינות, החל מהשקעה ממשלתית, מסורת לאומית בספורט זה או תנאים אחרים.

SELECT 
    m.country,
    m.discipline,
    COUNT(m.medal_code) AS total_medals,
    SUM(COUNT(m.medal_code)) OVER (PARTITION BY m.discipline) AS total_medals_in_discipline,
    COUNT(m.medal_code) * 100.0 / SUM(COUNT(m.medal_code)) OVER (PARTITION BY m.discipline) AS percentage_of_medals
FROM 
    medallists m
GROUP BY 
    m.country, m.discipline
ORDER BY 
    percentage_of_medals DESC;

תוצאה:

איך עובדת השאילתה: השאילתה משתמשת בפונקציית חלון כדי לחשב את אחוז המדליות שכל מדינה זכתה בהן בכל ענף ספורט מתוך סך המדליות באותו ענף. להלן פירוט התהליך:

1. השאילתה מתחילה בספירת סך המדליות שכל מדינה זכתה בהן בכל ענף ספורט (באמצעות COUNT(m.medal_code)).
2. באמצעות פונקציית החלון SUM(COUNT(m.medal_code)) OVER (PARTITION BY m.discipline), השאילתה מחשבת את סך כל המדליות שחולקו בענף הספורט הרלוונטי בכל המדינות יחדיו.
3. השאילתה מחשבת את אחוז המדליות של כל מדינה בענף הספורט מתוך סך המדליות שחולקו באותו ענף, על ידי חלוקת סך המדליות של המדינה בסך המדליות בענף והכפלה ב-100.
4. השאילתה מקבצת את הנתונים לפי מדינה וענף ספורט, ולאחר מכן מסדרת את התוצאות לפי אחוז המדליות בסדר יורד, כדי להציג אילו מדינות מצטיינות ביותר בענפים השונים.

סיכום: השאילתה מספקת מבט מקיף על ההתפלגות היחסית של המדליות בין המדינות בענפי ספורט שונים, ומדגישה את הענפים שבהם מדינה מסוימת מצטיינת יחסית לאחרות. זהו כלי חשוב להבנת ההצלחה של מדינות באולימפיאדה בענפים מסויימים ולניתוח הביצועים שלהן בהקשרים בינלאומיים.

View’s & Stored Procedure 🫣

יצירת View

CREATE VIEW athlete_medals AS
SELECT 
    a.code,
    a.name,
    a.country,
    m.medal_type,
    m.event
FROM 
    athletes a
JOIN 
    medallists m ON a.code = m.code;

כמה מילים על הצורך: View athlete_medals מספק מבט משולב על ספורטאים ומדליות שהם זכו בהן, כולל פרטים על סוג המדליה והאירוע.
השימוש ב-View זה מאפשר גישה נוחה למידע מקיף על הישגי הספורטאים ומסייע לניתוחי מידע שונים, כגון בחינת ביצועים לפי סוג המדליה.

Stored Procedure

ה-Stored Procedure בשם GetMedalCountByCountry נועד לספק דרך מהירה ונוחה לקבלת מספר המדליות הכולל של מדינה מסוימת.
פונקציה זו מאפשרת להפעיל שאילתה מותאמת אישית לפי המדינה המבוקשת, ומאפשרת קבלת תוצאות מהירה ואפקטיבית. היא שימושית לצורך ניתוח והשוואת ביצועי מדינות שונות באולימפיאדה.

DELIMITER //

CREATE PROCEDURE GetMedalCountByCountry(IN p_country VARCHAR(20))
BEGIN
  SELECT 
      m.country, 
      COUNT(*) AS total_medals
  FROM 
      medallists m
  WHERE 
      m.country = p_country
  GROUP BY 
      m.country;
END //

DELIMITER ;

סיכום 💡

הפרוייקט סייע לנו לעבוד עם כלים רבים של שפת SQL, לנתח נתונים רבים ולייצר גם תובנות מרחיקות לכת בנוגע לתפיסת מקסום האימונים של ספורטאים. מכיוון שאולימפיאדת פריז עודנה מתקיימת, הנתונים איתם השתמשנו השתנו מרגע לרגע, וכך גם בקריאת שורות אלה היקף ותוקף הנתונים ישתנה.

אתגרים וקשיים

מבחינת קשיים אשר נתקלנו בהם, היו לא מעט:
בעיות ביצועים וקריסת ה-Workbench:
כאשר עבדנו עם כמות גדולה של נתונים שכללה את כל האתלטים והקבוצות, נתקלנו בבעיות ביצועים משמעותיות שהובילו לקריסת סביבת ה-SQL Workbench. בשל כך, נאלצנו לצמצם את כמות הנתונים בהם אנו מטפלים ולמקד אותם רק במדליסטים, כלומר אתלטים וקבוצות שזכו במדליות בלבד. זאת במטרה לשפר את ביצועי המערכת ולהימנע מקריסות נוספות.

אתגרי שמירה על שלמות המידע לאחר המחיקה:
צמצום הנתונים למדליסטים בלבד הביא עימו אתגר לשמור על שלמות המידע. היה צורך לוודא כי במחיקת אתלטים או קבוצות שאינם מדליסטים, לא יימחקו בטעות גם אתלטים שהשתתפו בקבוצות שזכו במדליות. לצורך כך, השתמשנו בשאילתות מחיקה מותאמות שדואגות לכך שכל אתלט או קבוצה שזכו במדליות ישמרו.

נרמול עמודת האתלטים בצוות:
אחת הבעיות המרכזיות בה נתקלנו הייתה עמודת ה-athletes_codes בטבלת הקבוצות (Teams), אשר הכילה רשימה משרשרת של קודי אתלטים. מבנה זה לא אפשר ביצוע שאילתות יעיל והקשה על ניתוח המידע. כדי לפתור בעיה זו, החלטנו לנרמל את הנתונים באמצעות יצירת טבלה מקשרת חדשה בשם team_athletes. טבלה זו מאפשרת לשמור על קשרים ברורים בין אתלטים לקבוצות.

סקריפט לטיפול בנתונים:
להלן הסקריפט שבוצע כדי למחוק נתונים של אתלטים וקבצים שלא זכו במדליה (אבל אם אתלט לא זכה במדליה אבל השתתף בצוות שזכה עליו להשאר), וכן נרמול רשימת האתלטים בצוות על ידי טבלה מקשרת ולבסוף מחיקת רשימת האתלטים בצוות כדי למנוע כפילות ולנרמל:

-- שלב 1: מחיקת קבוצות שלא זכו במדליות
DELETE FROM Teams
WHERE code NOT IN (
    SELECT DISTINCT team FROM Medallists
);

-- שלב 2: מחיקת אתלטים שלא זכו במדליות (באופן אישי או כחלק מקבוצה)
DELETE FROM Athletes
WHERE code NOT IN (
    SELECT DISTINCT code 
    FROM Medallists  -- אתלטים שזכו במדליות אישיות
)
AND code NOT IN (
    SELECT code FROM (
        SELECT a.code 
        FROM Athletes a
        JOIN Teams t ON INSTR(t.athletes_codes, a.code) > 0
    ) AS temp
);

-- שלב 3: אוכלוסיית הטבלה המקשרת team_athletes
INSERT INTO team_athletes (team_code, athlete_code)
SELECT 
    t.code, a.code 
FROM teams t
JOIN athletes a 
ON INSTR(t.athletes_codes, a.code) > 0;

-- מחיקת העמודה athletes_code והחלפתה בטבלת team_atheletes
ALTER TABLE Teams DROP COLUMN athletes_codes;

לאחר שצמצמנו את הנתונים למדליסטים בלבד כדי לשפר את ביצועי המערכת ולהימנע מקריסות, נותרה בפנינו השאיפה להשתמש במלוא הנתונים המקוריים, כלומר לכלול גם את כל האתלטים והקבוצות שהשתתפו באולימפיאדה, ולא רק את אלו שזכו במדליות. באמצעות אופטימיזציה של הסקריפט לאכלוס הטבלה המקשרת team_athletes וטעינת המידע בשלבים, הצלחנו לבסוף להשתמש במידע כולו, ובכך לשמור על ניתוח כוללני ומקיף של הנתונים.

תובנות

• זאת לא התוצאה אלא הדרך - התמודדנו עם קשיים שגרמו לנו לחשוב מחוץ לקופסא ולייצר לעצמנו פתרונות מורכבים.
• היכולת שלנו להמיר נתונים טבלאיים למסד רלציוני השתפרה באופן מדהים.
• השתפרה גם היכולת לשאול שאלות ולהסיק מסקנות מתוך הנתונים.

ועכשיו מילות סיכום

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

אנחנו מקווים שנהנתם לקרוא את הפרוייקט. לכל שאלה ניתן לכתוב לנו במייל: Dolev[@]Ravid.email