הבלוג של גרי רשף

08/01/2012

יעול שאילתת Union עצמי ודוגמה ליישום בבדיקת טבלת Slowly Changing Dimension

Filed under: Uncategorized — גרי רשף @ 20:18

Union עצמי הוא Union של טבלה עם עצמה – כשרוצים למשל לאחד נתונים ממספר עמודות,
וכדי שזה לא ישאר ברמה הטכנית – אני אדגים כיצד לבדוק תקינות טבלת Slowly Changing (להלן- SCD) בעזרתה.

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

Use TempDB;
Go

If Object_Id('T_SCD','U') Is Not Null Drop Table T_SCD;
Go

Create Table T_SCD(ID Int, StartDate DateTime, EndDate DateTime);
Go

--1 תקין
Insert Into T_SCD Select 1 ID, '20090101' StartDate, '20090731' EndDate;
Insert Into T_SCD Select 1 ID, '20090801' StartDate, '20090815' EndDate;
Insert Into T_SCD Select 1 ID, '20090816' StartDate, '20091031' EndDate;
Insert Into T_SCD Select 1 ID, '20091101' StartDate, '20091231' EndDate;
--2 רשומה אחת "חותכת" את האחרת
Insert Into T_SCD Select 2 ID, '20090101' StartDate, '20090731' EndDate;
Insert Into T_SCD Select 2 ID, '20090801' StartDate, '20090815' EndDate;
Insert Into T_SCD Select 2 ID, '20090810' StartDate, '20091031' EndDate;
Insert Into T_SCD Select 2 ID, '20091101' StartDate, '20091231' EndDate;
--3 רשומה אחת "בולעת" את האחרת
Insert Into T_SCD Select 3 ID, '20090101' StartDate, '20090815' EndDate;
Insert Into T_SCD Select 3 ID, '20090801' StartDate, '20090810' EndDate;
Insert Into T_SCD Select 3 ID, '20090816' StartDate, '20091031' EndDate;
Insert Into T_SCD Select 3 ID, '20091101' StartDate, '20091231' EndDate;
--4 רשומות חופפות
Insert Into T_SCD Select 4 ID, '20090101' StartDate, '20090815' EndDate;
Insert Into T_SCD Select 4 ID, '20090101' StartDate, '20090815' EndDate;
Insert Into T_SCD Select 4 ID, '20090816' StartDate, '20091031' EndDate;
Insert Into T_SCD Select 4 ID, '20091101' StartDate, '20091231' EndDate;
--5 יש "חור" באמצע
Insert Into T_SCD Select 5 ID, '20090101' StartDate, '20090731' EndDate;
Insert Into T_SCD Select 5 ID, '20090801' StartDate, '20090815' EndDate;
Insert Into T_SCD Select 5 ID, '20090816' StartDate, '20091031' EndDate;
Insert Into T_SCD Select 5 ID, '20091201' StartDate, '20091231' EndDate;
--6 תאריך ההתחלה אחרי תאריך הסיום
Insert Into T_SCD Select 6 ID, '20090131' StartDate, '20090121' EndDate;
--7 עוד אחד תקין
Insert Into T_SCD Select 7 ID, '20120101' StartDate, '20120103' EndDate;
Insert Into T_SCD Select 7 ID, '20120104' StartDate, '20120105' EndDate;
Insert Into T_SCD Select 7 ID, '20120106' StartDate, '20120108' EndDate;
Insert Into T_SCD Select 7 ID, '20120109' StartDate, '20120110' EndDate;
--8 שתי תקופות חופפות מיותרות באמצע
Insert Into T_SCD Select 8 ID, '20120101' StartDate, '20120103' EndDate;
Insert Into T_SCD Select 8 ID, '20120104' StartDate, '20120110' EndDate;
Insert Into T_SCD Select 8 ID, '20120105' StartDate, '20120106' EndDate;
Insert Into T_SCD Select 8 ID, '20120105' StartDate, '20120106' EndDate;
--9 שתי תקופות חופפות בתוך חור באמצע
Insert Into T_SCD Select 9 ID, '20120101' StartDate, '20120103' EndDate;
Insert Into T_SCD Select 9 ID, '20120108' StartDate, '20120110' EndDate;
Insert Into T_SCD Select 9 ID, '20120105' StartDate, '20120106' EndDate;
Insert Into T_SCD Select 9 ID, '20120105' StartDate, '20120106' EndDate;
--10 שני זוגות של תקופות חופפות
Insert Into T_SCD Select 10 ID, '20120101' StartDate, '20120102' EndDate;
Insert Into T_SCD Select 10 ID, '20120101' StartDate, '20120102' EndDate;
Insert Into T_SCD Select 10 ID, '20120105' StartDate, '20120106' EndDate;
Insert Into T_SCD Select 10 ID, '20120109' StartDate, '20120110' EndDate;
Insert Into T_SCD Select 10 ID, '20120109' StartDate, '20120110' EndDate;
--11 עוד אחד תקין
Insert Into T_SCD Select 11 ID, '20120101' StartDate, '20120110' EndDate;
Go

Select  *
From    T_SCD
Order By ID,
        StartDate;

clip_image002

הפתרון המקובל הוא לבצע Union כך:

Select  ID,
        StartDate [Date]
From    T_SCD
Union All
Select  ID,
        EndDate [Date]
From    T_SCD
Order By ID,
        [Date];

clip_image004

פתרון חלופי הוא להשתמש באופרטור Values:

Select  ID,
        T1.[Date]
From    T_SCD T
Cross Apply (Select * From (Values(T.StartDate),(T.EndDate)) AS D([Date])) T1
Order By ID,
        [Date];

clip_image006

מה יותר יעיל?

clip_image008

מכיוון שבשאילתה השניה יש רק Table Scan אחד (ולא שניים כמו בראשונה) – היא יותר יעילה, ולא אתמקח על המחיר המופקע שדפקו לי ב-Sort..

כיצד ניתן לייעל בעזרת Union עצמי שכזה בדיקה של SCD? הבדיקה שהצעתי לפני שנתיים- כבודה במקומה מונח, והיא מובילה אותנו ישר לשורות השגויות – אם יש. דרך חלופית ויעילה יותר היא לבצע Group By לעיין בשני הסיכומים הבאים:

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

2. מכיוון שיכול להיות שיש גם חפיפה וגם חור שמקזזים זה את זה וכו' – נבדוק גם שכל תאריך מופיע פעמיים – פעם בסוף תקופה ופעם בתחילת התקופה שאחריו, חוץ מתאריך ההתחלה ותאריך הסיום שמופיעים פעם אחת כל אחד (למען הדיוק- לתאריך הסיום יש להוסיף יום אחד כדי שיהיה שווה לתאריך הפתיחה העוקב).

קצת מורכב, ולכן נבנה זאת שלב אחר שלב- נתחיל בGroup By לפי ID ותאריך:

Select  ID,
        [Date],
        Sum(DateDiff(Day,StartDate,EndDate+1)) SDD,
        Min(StartDate) Mn,
        Max(EndDate)+1 Mx,
        Min(Min(StartDate)) Over(Partition By ID) MnMn,
        Max(Max(EndDate)) Over(Partition By ID)+1 MxMx,
        Count(*) Cnt
From    T_SCD T
Cross Apply (Select * From (Values(T.StartDate),(T.EndDate+1)) AS D([Date])) T1
Group By ID,
        [Date]
Order By ID,
        [Date];

clip_image010

ID – עמודת Group By.

Date – עמודת Group By.

SDD – (כלומר ה-Sum של DateDiff) מסכם את אורכי התקופות. בהמשך נחלק את זה ב-2 כי כל תקופה מסוכמת פעמיים- פעם בתאריך ההתחלה ופעם בתאריך הסיום.

Mn – התאריך המינימלי. בהמשך נשתמש בו למציאת התקופה הראשונה בכל ID.

Mx – התאריך המקסימלי. בהמשך נשתמש בו למציאת התקופה האחרונה בכל ID.

MnMn – התאריך המינימלי של ה-ID, כלומר- תאריך ההתחלה שלו.

MxMx – התאריך המקסימלי של ה-ID, כלומר- תאריך הסיום שלו.

Cnt – ה-Count. ישמש לבדוק האם תאריכי ההתחלה והסיום של כל ID הופיעו פעם אחת, וכל השאר פעמיים.

כעת נבצע Group By לפי ID כשהשליםה הנ"ל היא ה-T1 ב-CTE (כמובן- ללא ה-Order By):

With T1 As
(Select ID,
        [Date],
        Sum(DateDiff(Day,StartDate,EndDate+1)) SDD,
        Min(StartDate) Mn,
        Max(EndDate)+1 Mx,
        Min(Min(StartDate)) Over(Partition By ID) MnMn,
        Max(Max(EndDate)) Over(Partition By ID)+1 MxMx,
        Count(*) Cnt
From    T_SCD T
Cross Apply (Select * From (Values(T.StartDate),(T.EndDate+1)) AS D([Date])) T1
Group By ID,
        [Date])
Select  ID,
        Sum(SDD)/2 SDD,
        DateDiff(Day,MIN(Mn),MAX(Mx)) DDs,
        Sum(Case When [Date]=MnMn Then Cnt End) SmMn,
        Sum(Case When [Date]=MxMx Then Cnt End) SmMx,
        Count(Case When Mn<>MnMn And Mx<>MxMx And Cnt<>2 Then 1 End) CntEls
From    T1
Group By ID
Order By ID;

clip_image012

ID – העמודה לפיה מתבצע Group By.

SDD – סיכום של סיכום אורכי התקופות (מחולק ב-2 כי כל תקופה סוכמה פעמיים).

DDS – (כלומר ה-DateDiff של הסיכומים) ההפרש בימים בין תאריך ההתחלה ותאריך הסיום של ה-ID כולו (אמור להיות שווה ל-SDD).

SmMn – כמה פעמים הופיע תאריך ההתחלה של ה-ID (אמור להיות שווה 1).

SmMx – כמה פעמים הופיע תאריך הסיום של ה-ID (אמור להיות שווה 1).

CntEls – כמה Count יש שאינם תאריך התחלה או סיום של ה-ID וגם אינם שווים 2 (אמור להיות שווה 0).

נבדוק מי אינו מקיים את התנאים (=החריגים), ולשם כך נגדיר את השליפה מ-T1 בתור T2:

With T1 As
(Select ID,
        [Date],
        Sum(DateDiff(Day,StartDate,EndDate+1)) SDD,
        Min(StartDate) Mn,
        Max(EndDate)+1 Mx,
        Min(Min(StartDate)) Over(Partition By ID) MnMn,
        Max(Max(EndDate)) Over(Partition By ID)+1 MxMx,
        Count(*) Cnt
From    T_SCD T
Cross Apply (Select * From (Values(T.StartDate),(T.EndDate+1)) AS D([Date])) T1
Group By ID,
        [Date]),
T2 As
(Select ID,
        Sum(SDD)/2 SDD,
        DateDiff(Day,MIN(Mn),MAX(Mx)) DDs,
        Sum(Case When [Date]=MnMn Then Cnt End) SmMn,
        Sum(Case When [Date]=MxMx Then Cnt End) SmMx,
        Count(Case When [Date]<>MnMn And [Date]<>MxMx And Cnt<>2 Then 1 End) CntEls
From    T1
Group By ID)
Select  *
From    T2
Where SDD<>DDS
      Or (SmMn<>1
         Or SmMx<>1
         Or CntEls>0)
Order By ID;

clip_image014

כלומר: 2,3,4,5,8,9,10 הם השגויים (גם 6 שגוי ומי שרוצה גם את השגיאות "הפנימיות" יכול להוסיף תנאי SDD שלילי).

התסבוכת הזו שווה לנו? נשווה אותה לבדיקה מהפוסט הקודם:

clip_image016

השאילתה הישנה כבדה פי שלושה מהחדשה, שוב- בגלל החסכון ב-Table Scan (על מחיר ה-Sort כבר התבכיינתי?).

שתי הסתייגויות לפני שרצים לשנות את ה-QC בייצור:

1. השאילתה הישנה לא רק מצאה באילו ID יש שגיאות, אלא גם את השורות השגויות; והחדשה רק באילו ID יש שגיאות, ובאלו שכן- יש לחפש נקודתית את השורות השגויות.

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

מודעות פרסומת

תגובה אחת »

  1. יפה מאוד!

    תגובה של גילה — 12/01/2012 @ 10:01


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

יצירה של אתר חינמי או בלוג ב־WordPress.com.

%d בלוגרים אהבו את זה: