מילוי ערכים בטבלת Dimension
Dimensions.Date בסקריפט, עם פונקציות SQL לכל עמודה, ופתרון לשתי עמודות שאין להן פונקציה מובנית: היום ברבעון והשבוע בחודש.OrderDate מהטבלה Orders במסד התפעולי Northwind.GETDATE().while כל עוד תאריך ההתחלה קטן או שווה לתאריך הסיום. בכל סיבוב מכניסים שורה ואז מקדמים יום עם DATEADD ברזולוציה dd.identity לא מוזן.CONVERT ל-char(10) עם סגנון 103 לפורמט UK, ועם 101 לפורמט האמריקאי.DATENAME, יום בחודש עם DAY, שנה עם YEAR, ויום בשנה, יום בשבוע ורבעון עם DATEPART (עם dy, dw ו-qq). שם היום בשבוע נלקח עם DATENAME ו-dw.DATEDIFF, מוסיפים אותם לאפס עם DATEADD כדי לקבל את היום הראשון של הרבעון, ומחשבים את ההפרש בימים בינו לבין התאריך. מוסיפים 1, כי החישוב מתחיל מאפס.CONCAT של חודש, יום 01 ושנה, ועם CAST ל-datetime. הבדיקה כיסתה קצה בחודש פברואר: 7 מתחיל שבוע שני, 14 שלישי, 28 בחמישי.0 בתחילתו, ואז RIGHT של שני תווים כדי לקבל שתי ספרות, ועוד שנה בארבע ספרות. הגרסה הראשונה שלקחה LEFT נתנה תוצאה שגויה בחודש 10, ולכן עברו ל-RIGHT.@d, כך שהחישוב עובד בשורה אחת של insert.פרקי ההרצאה: מילוי ערכים בטבלת Dimension
תמלול ההרצאה: מילוי ערכים בטבלת Dimension
הטקסט המלא של הדקות הראשונות בהרצאה «מילוי ערכים בטבלת Dimension» בקורס הווידאו של PracticU – כתוביות בעברית עם זמן התחלה לכל פסקה. כך תוכלו לקרוא ולחפש את הנושאים לפני שמתחילים ללמוד.
בשיעור הבא אנחנו ניקח את טבלת dimensions.date טבלה המייצגת לנו את צרור התאריכים הרלוונטיים בתוך מערכת ה-BI שלנו, המכילה כל שורה שמייצגת תאריך מסוים ונמלא אותה בערכים. נכתוב סקריפט על מנת למלא אותה בערכים.
כדי למלא אותה בערכים אז אנחנו צריכים לדעת מהם גבולות הגזרה שלנו, מהו התאריך התחלה ומהו התאריך סיום. תאריך ההתחלה הוא התאריך הקטן ביותר שבמנו אנחנו רוצים להתחיל, אנחנו רוצים לשלוף את התאריך הנמוך ביותר של ההזמנה orderDate מתוך טבלת orders לשלוף אותו מתוך הדאטאביס והוא בעצם יהיה המינימום שלנו על מנת להכניס את זה לתוך השדה שהוא יהיה נקודת ההתחלה.
מינימום orderDate from orders אבל אנחנו רוצים לשלוף את זה מתוך הדאטאביס ה-Operational מתוך Northwind אז נכתוב זאת על ידי ציון מפורש שאנחנו שולפים את orderDate From לונפווינג נקודה נקודה orders. עד כאן קבענו מהי נקודת ההתחלה.
נקודת סיום שלנו יכולה להיות getDate. מאחר ואנחנו כבר מפעילים פונקציה אז נכתוב את זה על ידי סלקט. getDate התאריך האחרון הוא ה-endDate שלנו וכל מה שאנחנו צריכים לעשות בעצם זה לקחת ולרוץ כל עוד התאריך ההתחלה שלנו דהיינו ה-startDate קטן או שווה כי אנחנו רוצים גם את התאריך של היום האחרון
מהתאריך האחרון שלנו שהוא ה-end date. כל עוד הוא קטן או שווה אנחנו נבצע כאן איזשהו insert, איזושהי מניפולציה, ונקדם את התאריך שלנו, נקדם אותו ביום אחד קדימה ל-date add שאנחנו מוסיפים לו רזולוציה של יום אחד אז זה dd.1 מוסיפים יום לתאריך עצמו ואת זה אנחנו מכניסים בחזרה לתוך המשתנה וכך יש
לנו קידום של התאריך עצמו. מה אנחנו עושים בין לבין? אנחנו פשוט ממלאים אחר כבוד את כל התאריכים שיש לנו כאן בתוך הטבלה. בואו נפתח את הסקריפט המתאים על מנת לקבל את שמות העמודות אם נרפרש כאן. נראה שיש לנו כבר בתוך טייבלס אחת סכמה של דיימנשיינס את הטבלה עצמה, דייטס.
נפתח את העמודות הרלוונטיות ונתחיל לכתוב את השמות שלהם. אז בעצם את ה-dead s k אותו אין לנו מה למלא והוא יתמלא בצורה אוטומטית מאחר והוא העמודה של ה-identity אז אותו אנחנו לא ממלאים. בואו נכתוב כאן את כל שאר העמודות.
full k, months name שלנו, יום של החודש, day of months, year num שאותו יצרנו, day of year, sick, day of week num, כך גם את השדה עצמו כאן, name quarter, נעבור
ל-quarter num ו-quarter day of quarter שלנו, לאחר מכן ל-יום בחודש, ופורמטים שונים, בחרנו לעסוק בפורמט הזה, אבל כמובן שאפשר להוסיף כל פורמט אפשרי.
יכול להיות שיאמר השואל, מדוע אנחנו יוצרים וריאציות שונות של התאריך? הרי תמיד אפשר על ידי מניפולציה לקבל את כל הווריאציות הללו.
התשובה היא, זה נכון, אבל אנחנו לא רוצים לבצע מניפולציה בעת שליפה, אלא אנחנו רוצים שהנתונים יהיו לנו כבר זמינים על מנת למזער כמה שפחות המרות בעת שליפת הנתונים ועל מנת לקבל שורה קאש של דאטה בהתאם לפורמט שאותו אנחנו שולפים מבלי צורך לבוא ולעשות עליו איזושהי מניפולציה כדי לחפש ערך כזה
או אחר. מה שאנחנו נצטרך עכשיו לעשות, נמלא את השדות הללו בתאריכים בהתאם לסטארט דייט. אז קודם כל סטארט דייט שלנו כבר מכיל תאריך והוא זה שמתאים לעמודת דייט ובואו נתקדם.
אם אנחנו רוצים עכשיו את הפורמט הזה אז אנחנו נעשה לו convert של התאריך שאותו אנחנו רוצים. לאן אנחנו רוצים להמיר את זה? אז מדובר כאן על car10 ולכן נרשום כאן car10 שאותו אנחנו רוצים להמיר את השטרודל סטארט דייט.
הפורמט שאיתו אנחנו רוצים לעבוד מאחר ואנחנו כאן מדברים על העמודה עצמה של uk אז אנחנו נכתוב את זה בהמרה של שדה convert מעניין לראות אם נפתח כאן את הביטוי של convert נראה שיש לה מספר פורמטים ואם אנחנו רוצים לדעת מה קורה ב uk מה קורה בפורמט שהוא d months וyear אנחנו צריכים לקחת את 103 זה בדיוק מה שאנחנו
רוצים לעשות כאן דיי, מאנס ואייר, אז אנחנו צריכים כאן את 103. ובסוף כשנרצה לקחת את הקונברט של ארצות הברית כשדה אחרון שלנו, אז אם נסתכל כאן שוב פעם בפורמט עצמו, נראה ש-US השונה הוא שהוא 101. אז כל מה שצריך כאן לעשות זה רק לכתוב 101 על מנת לקבל את הפורמט של התאריך.
אם נגיע לכך, כרגע אנחנו נמצאים בשדה של ה-Mנס-נאם, איזה חודש אנחנו מדברים אז זה מאוד פשוט אנחנו רוצים את הסטאר דייט שלנו לקבל את החודש בעצמו נתקדם לשורה הבאה אם אני רוצה את ה-month name לקבל את ה-month name יש לנו פונקציה שנקראת date name שיודעת לקבל לנו את המשתנה עצמו מה אנחנו רוצים לקבל אז אנחנו רוצים לקבל בעצם את החודש זה החלק שלנו
ועל מי אנחנו רוצים לקבל את זה אנחנו רוצים לדלות את זה מתוך הסטאר דייט כך אנחנו מקבלים כאן את החודש אבל מבחינה מילולית ואני כעת מתייחס בסקריפט שלי לעמודה הזאת מונס ניין שזה המתאים הלאה זה השדה המתאים לו אנחנו נתקדם וכרגע אנחנו עובדים נעבור לעמודה הבאה דיי אוף מונס נמחק כאן את הקשקושים אבל תעקבו אחר העמודות שאני מציין דיי אוף מונס זה בעצם היום בחודש היום בחודש ניתן לנו על
ידי פונקציה דיי שי היא רק מקבלת את התאריך עצמו ויודעת להגיד לנו מהו היום בחודש. עברנו לשנה, אז השנה עצמה הפונקציה היא מאוד פשוטה, לקבל את השנה.
והיום בשנה, איך נציין את היום בשנה? אז גם כאן אנחנו נוכל לקחת את ה-Date Part. אם נסתכל בגוגל על Date Part ונרצה לבחון מהן היכולות שאנחנו יכולים להביא כאן, נראה שיש לנו מגוון רחב של דוגמאות שאותן ניתן לקבל.
אז כמו שיש לנו את החודש, את היום, day of year, היום בחודש, אז אותו אנחנו מייצגים על ידי האותיות dy וזה בדיוק מה שאנחנו נעשה כאן.
Day of year, היום בשנה שלנו, אנחנו רוצים day of week, day of week מאוד דומה לפורמט הזה, גם כאן אנחנו רוצים date part, אנחנו רוצים אותו במספר, ובמקום לקבל dy אנחנו לוקחים dw.
Day of Week. אבל אם אנחנו רוצים את ה-Day of Week Name, אז זה כבר לא Date Part, אלא זה Date Name. וכאן גם אנחנו רוצים את ה-DW ולאחר מכן את ה-Start Date.
Day of Week Name, היום בשבוע. Sunday, Monday, Tuesday, Friday וכו' וכו'. Quarter, אם אני רוצה לקבל את ה-Quarter, זה ממש לקבל פארט מתוך התאריך עצמו, ולכן אני משתמש בפארט, אבל תחת בקשה של day year אני אבקש כאן qq מהו הday of quarter אם נסתכל כאן בתוך הבקשה שלנו ונראה שיש לנו כאן את הquarter בעצמו עבור שליפת
היום בתוך הרבעון אם נחפש את ההמרה שלו בתוך גוגל בפונקציה שבה אנחנו יכולים לתת לה איזה שהיא אינטרוול אין לנו את האינטרוול שבו אנחנו יכולים לדעת מהו היום באותו רבעון מבחינת היום רמת הרבעון עצמו.
כדי לעשות זאת, אז אנחנו חייבים לבצע את המניפולציה בעצמנו איך למצוא את היום הזה. נזוז רגע הצידה וננסה להבין מה קורה כאן. כשאנחנו בעצם רוצים להציג מהו היום ברבעון, משמעות הדבר שאם השנה שלנו מתחלקת לארבעה רבעים, יש לנו רבעון ראשון, רבעון שני, רבעון שלישי ורבעון רביעי.
כשאנחנו נופלים בתאריך מסוים, נופל כאן בתאריך הזה ואני רוצה לדעת מהו היום ברבעון שבו אני נמצא, כלומר כמה ימים עברו מתחילת הרבעון הנוכחי ועד עכשיו.
אין לי דרך לדעת זאת, אין לנו איזושהי פונקציה שיודעת להביא לנו את המידע הזה ולהציג לנו אותו בצורה מסודרת, פונקציה כ-datepart או כאיזה משהו חלקי.
מה יש לנו? יש לנו פונקציה שיודעת להגיד לנו באיזה רבעון אנחנו נמצאים. כלומר, כל מה שאני צריך לדעת כדי למצוא זאת, אז לבצע איזשהי חיסור של מניפולציה בין כמה שדות שאיתם אנחנו נעבוד.
בדוגמה שלנו ניתן לקחת את היום עצמו ולדעת בעצם כמה רבעונים אנחנו עברנו, באיזה רבעון אנחנו נמצאים, למצוא כאן את ה-QQ, באיזה רבעון אנחנו נמצאים, ואת אותו רבעון לדעת להתחיל אותו מההתחלה ולמצוא בעצם את היום הראשון שלו.
ברגע שאני אדע שאני נמצא ברבעון שלוש אני אוכל למצוא את היום הראשון שלו. אחרי שמצאתי את היום הראשון כל מה שאני אצטרך לדעת זה לחסר בין היום הראשון ובין היום הנוכחי.
כלומר לבצע כאן איזשהו חיסור, החיסור הזה זה דייט דיף, חיסור בין שני תאריכים, איסור ביניהם על מנת לדעת כמה ימים עברו לי ביניהם.
כדי למצוא את שני התאריכים הללו, אז תאריך אחד יש לי, זה ה-X שבו אני נמצא. תאריך שני זה בעצם תחילת הרבעון. איך אני אמצא מהו תחילת הרבעון?
אני אקח את הרבעון עצמו, את מספר הרבעונים, ואני אקדם אותם החל מתחילת שנה ואני אקבל כאן את היום עצמו. בואו נעשה זאת.
כדי למצוא באיזה רבעון אני נמצא, כמה רבעונים עברתי, אז זה בעצם דייט דיף. אני רוצה לדעת מה ההפרש מבחינת רבעונים בין 0 ובין הרבעון הנוכחי שלי, שזה ה-Start Date.
זה בעצם ההפרש ביניהם. את ההפרש הזה אני רוצה להוסיף לנקודת ההתחלה שלנו, ל-0. Date Add, QQ, כמה אני רוצה להוסיף את כל זה, למי אני רוצה להוסיף לתחילת שנה בעצם ל-0 הזה, ל-0 שמבחינתנו שזה תחילת שנה.
עד כאן בעצם הגעתי ליום הראשון, אבל עכשיו כל מה שאני צריך לעשות זה בעצם רק למצוא את ההפרשים בין שני התאריכים, דייט דיף ברמה של ימים בין מי למי, בין התאריך הזה שמצאנו שהוא התחלת הרבעון שבו אנחנו נמצאים ובין התאריך הנוכחי שבו אנחנו כרגע נמצאים שזה הסטארט דייט.
עד כאן ביצענו בעצם את הפקודה שבה אנחנו רוצים לדעת באיזה יום בחודש אנחנו נמצאים. מאחר ועשינו זאת וקיבלנו חיסור החל מהאפס, אז אנחנו נקבל כאן יום אחד פחות, ולכן כדי לקדם אותו אז אנחנו נוסיף עוד פלוס, וזה מה שייתן לנו את ה-day of quarter.
זה כמובן כהערה, נכניס אותו בעצם לירוקת ונתקדם לשדה הבא. ובכן, בבואנו לפתור את week of month, מספר השבוע בתוך החודש הנוכחי. אנחנו עומדים בפני אתגר, מאחר ופונקציות מערכת שקיימות בתוך SQL Server לא נותנות לנו תמיכה במידע הזה.
אנחנו יכולים לקבל כאן את מספר השנה, את מספר החודש, את מספר השבוע, את מספר הרבעון. אני יכול לקבל את מספר השבוע בתוך השנה כולה.
אני יכול לקבל את מספר היום באותו שבוע, אבל אני לא יכול לקבל את מה שכרגע אני מבקש לדעת, Week of Month, את מספר השבוע בתוך החודש הנוכחי.
בואו נפתח רגע מערכת זמנים ונסתכל על הלוח שנה שלנו. בלוח שנה, כשאני מסתכל על חודש פברואר 2021 לצורך הדוגמה, חודש פברואר מתפרס לי על פני 1, 2, 3, 4 שבועות.
לא שבועות מלאים, לא שבועות קלנדרים מלאים, אבל מאחר והוא התחיל ביום שלי ויש לי כאן זליגה לשבוע החמישי, 28 בחודש אמור לתת לי את השבוע מספר 5. לעומת זאת תאריכים 1 עד 6 כולל השישי לחודש שנופל בשבת הם נופלים בשבוע הראשון של החודש.
השביעי לחודש הוא כבר בשבוע השני לחודש. בואו נסתכל על תאריך נוסף, על חודש נוסף.