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

לדוגמא טבלת שערי המטבע הבאה מכילה את תאריך המטבע וכן את שער הדולר באותו יום:

article08 01

וכן טבלת מכירות המכילה פרטי מכירה הנקובים בדולרים:

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

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

נציג את הפתרון באמצעות Sql Server, למרות שאותה שיטה תעבוד גם בDB אחרים.

הטבלה המקורית:

שלב 1:

לכל רשומה נחבר את כל הרשומות לפניו (באמצעות alias). נבצע זאת באמצעות join אך עם אופרטור >.

בסופו של התהליך הAlias יתאר את תאריך מ , בעוד שהטבלה המקורית תתאר את תאריך עד.

המוביל הוא תאריך מ, לכן נציג את שאר החליפין של הAlias וכן נמיין לפי התאריך של הAlias.

הSQL שנקבל:

select t2.rate_date from_date , t1.rate_date to_date,t2.exchange_rate
from dbo.exchange_rate t1 inner join dbo.exchange_rate t2
on t2.rate_date<t1.rate_date
order by t2.rate_date

 

הרצה של Sql זה תחזיר לנו את הRecordset הבא:

שלב 2:

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

נמצא זאת באמצעות שימוש בmin עם group by.

השאילתא:

select t2.rate_date from_date , min(t1.rate_date) to_date,t2.exchange_rate
from dbo.exchange_rate t1 inner join dbo.exchange_rate t2
on t2.rate_date<t1.rate_date
group by t2.rate_date,t2.exchange_rate
order by t2.rate_date

אשר תחזיר :

 

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

שלב 3:

אם שמם לב, תאריך העד של רשומה זהה לתאריך ה מ של הרשומה הבאה בסדר. דבר זה יגרום בעיות כאשר תאריך המכירה (בטבלת המכירות) יהיה זהה לתאריך זה. דבר שיגרום להכפלת רשומות. לדוגמא, אם תאריך המכירה הוא 29/4/2011 המכירות יוכפלו ב2 . הכפלה זאת נובעת מכך שהאופרטור Between לוכד גם את הקצוות של הערכים המופיעים בו. לצורך זה, יש להוריד שניה מתאריך העד.

כלומר, הSQL יהיה:

 

select t2.rate_date from_date , dateadd(s,-1,min(t1.rate_date)) to_date,t2.exchange_rate
from dbo.exchange_rate t1 inner join dbo.exchange_rate t2
on t2.rate_date<t1.rate_date
group by t2.rate_date,t2.exchange_rate
order by t2.rate_date

 

 

שיחזיר את הRecordset הבא:

שלב 4:

בשלב האחרון, עלינו לטפל במקרה קצה: מה קורה שאנחנו צריכים להצליב מכירה הנמצאת בתאריך שלא נמצא בטווח התאריכים בטבלת נתוני המטבע. לדוגמא, מכירה שהופיע לפני 31/12/2010 או אחרי 29/6/2012.

אנחנו לא יודעים מה היה לפני 31/12/2010, לכן נקבע באופן שרירותי את הערך 3.5 ואת כל שערי החליפין אחרי 29/6/2012, נקבע כאחרון הידוע (בתאריך 29/6/2012).

כדי ללכוד את התאריכים שלא נמצאים בטווח, נשנה את סוג הjoin ל full outer.

כלומר, הSQL יהיה:

select t2.rate_date from_date , dateadd(s,-1,min(t1.rate_date)) to_date,t2.exchange_rate
from dbo.exchange_rate t1 full outer join dbo.exchange_rate t2
on t2.rate_date<t1.rate_date
group by t2.rate_date,t2.exchange_rate
order by t2.rate_date

 

נקבל את הRecordset הבא:

נלכוד את הnull של הExchange_rate ןנשתול את הערך השרירותי 3.5, את ערכי התאריכים הNull, נחליף בתאריכים מוגזמים, כדי לכסות את כל האפשרויות. נניח, 1/1/1911 ו1/1/2311.

הSQL הסופי, יהיה:

select isnull(t2.rate_date,cast('1/1/1911' as datetime)) from_date , isnull(dateadd(s,-1,min(t1.rate_date)),cast('1/1/2311' as datetime))to_date, isnull(t2.exchange_rate,3.5) exchange_rate
from dbo.exchange_rate t1 full outer join dbo.exchange_rate t2
on t2.rate_date<t1.rate_date
group by t2.rate_date,t2.exchange_rate
order by t2.rate_date

הSql יחזיר את הRecordset הבא:

 

אחרי שסיימנו, נחבר את הSQL לטבלת הSales המקורית, כדי לראות את סכום המכירה בשקלים:

select sales_date,exchange_rate, sales_in_dollar,sales_in_dollar*exchange_rate sales_in_shekel from dbo.sales inner join (select isnull(t2.rate_date,cast('1/1/1911' as datetime)) from_date , isnull(dateadd(s,-1,min(t1.rate_date)),cast('1/1/2311' as datetime))to_date, isnull(t2.exchange_rate,3.5) exchange_rate from dbo.exchange_rate t1 full outer join dbo.exchange_rate t2 on t2.rate_date<t1.rate_date group by t2.rate_date,t2.exchange_rate ) xchng on sales_date between from_date and to_date

 

אשר יחזיר את הRecordset הבא:

ניתן ורצוי להשתמש באותה שיטה לא רק כדי לחבר טבלאות, אלא גם כדי ליצור טבלה חדשה בתהליך הETL, לדוגמא.

הערה: השימוש שעשינו בOrder By הוא למטרת המחשה בלבד.

 

 

 

חדשות dwh.co.il

BI&BigData

07 אפריל 2020

bi\dwh in israel

רשימת קורסים

קורס ה Bo Designer Master הוא קורס ייחודי שפותח בא ...
קורס ה SQL Wiz הוא קורס מיוחד המלמד חסר נסיון בSQL ...
בקורס זה אנו נלמד כיצד לפתח דוחות באמצעות הWebinte ...
בקורס זה אנו נלמד כיצד לפתח דוחות באמצעות הWebinte ...
הSDK של הקליינט של הBusiness Objects מרחיב את האפש ...
קורס ה Deski\Client-Business Objects, נועד לאפשר פ ...
בסדנא זאת, אנו נלמד על כלים מיוחדים עבור הDesigner ...
תנו לנו לייק לקבלת עדכונים שוטפים