איך לפשט שאילתות SQL באמצעות Case ו - Views

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

אחד הפתרונות לפישוט המסד והשאילתות הוא להשתמש ב - Views. בפוסט זה אתייחס למילה CASE והשימוש בה בשאילתות וב - Views.

נתון המסד הבא:

1. המסד מכיל סרטים, קטגוריות ומשתמשים.
2. כל סרט משויך לקטגוריה. (CategoryID)
3. כל משתמש יכול לבחור מספר קטגוריות של סרטים שהוא אוהב (טבלת UsersLikesCategories).
4. לכל סרט יש תאריך שבו הוא יוצא לאקרנים (StartDate), והתאריך האחרון שבו הוא משודר בקולנוע (EndDate). תאריך ה - EndDate הוא Nullble, מכיוון שלא תמיד ידוע מתי הסרט יורד מהמסך.

הבעיה:

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

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

פתרון לבעית התאריכים

ניצור View חדש בשם vwMovies. באמצעות CASE ניצור בקלות שדה בוליאני (BIT) שיחזיר True במידה ויש להציג את ההערה, לשדה זה נקרא Notice.

המבנה של משפט Case הוא כדלהלן:

1
2
3
4
 

CASE
  WHEN condition THEN trueresult
  [ELSE elseresult]
END

נתחיל מלבדוק אם הערך EndDate הוא Null או לא. בשלב זה השדה יחזיר 1 אם EndDate הוא לא Null ו - 0 אם הוא Null, הנה הקוד, לאחר מכן ההסבר:

1
2
3
4
5
6
7
8
9
10
 

CREATE VIEW vwMovies AS
  SELECT
       MovieID, CategoryID, Title, Description, StartDate, EndDate,
       CONVERT(BIT, 
         (CASE WHEN (EndDate IS NULL)
           THEN 0 ELSE 1 END)
       ) AS Notice
    FROM
       Movies
GO

הסבר לקוד, ואני אתחיל מהקטע החשוב - שורות 5 ו - 6:

שורה 5 מתחילה במילה CASE, ולאחריה WHEN - שבודק את התנאי EndDate IS NULL. המשפט CASE מתחיל בשורה חמש וממשיך בשורה 6: THEN - יקרה במידה והתנאי שב - WHEN מתקיים. ELSE, יקרה במידה והתנאי לא התקיים. במקרה שלנו אם EndDate הוא NULL, יוחזר 0 (אל תתריע), אחרת יוחזר 1 (יש תאריך סיום).

והסבר לשאר השורות:

1. יצירת View חדש בשם  vwMovies.
2. משפט Select
3. רשימת השדות שיש לבחור
4. הפונקציה CONVERT, שמקבלת טיפוס וערך, ומבצעת הסבה. במקרה שלנו, הערך הוא מה שיוחזר ממשפט ה - CASE שיצרנו (0 או 1), והטיפוס יהיה BIT.
5. בשורה זו אנחנו נותנים לשדה את שמו - Notice, בשורה אחת זה נראה כך:
   

   CONVERT(BIT, [Case Clause]) AS Notice
6. בשורות 8-9 אנחנו בוחרים את הטבלה ממנה נשלוף את הנתונים.

עכשיו נותר לטפל בתאריך במידה והוא קיים. לצורך כך, נשתמש בפונקציה DatePart. הפונקציה מקבלת  את החלק של התאריך שאנחנו רוצים להחזיר, ואת התאריך, ומחזירה INT. דוגמה פשוטה לשימוש בה (בהנחה שהיום 29 למאי, שעה 21:10):

1
2
 

SELECT DatePart(month, GetDate()); -- Returns 5 
SELECT DatePart(hour, GetDate()); -- Returns 21

במקרה שלנו צריך לבדוק את החודש והשנה, לכן התנאי שלנו יהיה:

1
2
3
4
5
6
 

DatePart(year, EndDate) = DatePart(year, GetDate())
AND
(
  DatePart(month, EndDate) = DatePart(month, GetDate())
  OR DatePart(month, EndDate) = (DatePart(month, GetDate()) + 1)
)

יש עוד מיליון דרכים לבצע את הבדיקה הזו, אבל לצורך העניין התנאי הוא:

אם השנה ב - EndDate זהה לשנה הנוכחית (GetDate) וגם (החודש ב - End זהה לחודש הנוכחי או לחודש הבא)

את כל זה נעטוף ב - CASE שיחזיר 1 אם התנאי מתבצע (כלומר יש להתריע), אחרת יחזיר 0. ונשים אותו בתוך ה - CASE הקודם שבנינו, במידה וה - EndPart הוא לא NULL.

מה שיצא זה:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 

CREATE VIEW vwMovies AS
SELECT
  MovieID, CategoryID, Title, Description, StartDate, EndDate,
  CONVERT(BIT, 
    (CASE WHEN (EndDate IS NULL) THEN 0
      ELSE 

        (CASE WHEN
          (
           DatePart(year, EndDate) = DatePart(year, GetDate())
            AND

            (
              DatePart(month, EndDate) = DatePart(month, GetDate()) OR 
              DatePart(month, EndDate) = (DatePart(month, GetDate()) + 1)
            )
          ) THEN 1 ELSE 0
        END)
    END)
  ) AS Notice
  FROM
    Movies
GO

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

ה - View הזו כמובן היא מבחינתו טבלה שאפשר לשלב ב - SP's שלנו ועוד, ולשנות גם את הכללים לסרט שיש להתריע עליו בקלות יחסית. מכאן הדרך לשאר השאילתה היא הרבה יותר פשוטה. אני אשאיר לכם לעשות את שיעורי הבית ;)

עד כאן. נתראה בפוסט הבא...