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

11/08/2011

זיהוי תחומים רציפים בטבלאות לוג

Filed under: Uncategorized — גרי רשף @ 16:17

הפוסט הזה נכתב בעקבות שאלה שנשאלה בפורום בסיסי נתונים בתפוז, והתמודדתי איתה בעזרת טריק יפה שלמדתי מאב"ג לפני שנים רבות באחד ממפגשי ISUG (כשעדיין התכבדנו בנוכחותו).
נתונה טבלת נוכחות של סטודנטים, ואנחנו מעוניינים לבדוק כמה ימים ברציפות הם נכחו או נעדרו בכל פעם;
או אולי- נתונה טבלת שערי מניות ואנחנו מעוניינים לחלק אותה לתקופות של עליות ותקופות של ירידות (תוך שאנחנו נעזרים באופציות ה-Lead וה-Lag שהתווספו לגרסת Denali שמאפשרות להשוות כל רשומה לקודמת);

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

Use tempdb;
Go

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

Create Table T_Nohehut(Shem Varchar(20),
                       Taarih DateTime,
                       Nohehut TinyInt);
Go

Insert Into T_Nohehut(Shem,Taarih,Nohehut)
Select 'Anat','20110701',1 Union All
Select 'Anat','20110702',1 Union All
Select 'Anat','20110703',0 Union All
Select 'Anat','20110705',0 Union All
Select 'Anat','20110707',1 Union All
Select 'Anat','20110711',0 Union All
Select 'Anat','20110713',0 Union All
Select 'Anat','20110717',0 Union All
Select 'Anat','20110719',1 Union All
Select 'Anat','20110723',0 Union All
Select 'Beni','20110701',1 Union All
Select 'Beni','20110702',1 Union All
Select 'Beni','20110703',1 Union All
Select 'Carmel','20110711',0;
Go

Select *
From   T_Nohehut
Order By Shem,
       Taarih;
Go

clip_image002

כעת נמספר את השורות בשני אופנים:

1. כל שם לפי סדר עולה של תאריכים.

2. כל צירוף של שם ונוכחות לפי סדר עולה של תאריכים.

והתוצאה יחד עם ההפרש בין שני המספורים:

Select  Row_Number() Over(Partition By Shem Order By Taarih) Mispar1,
        Row_Number() Over(Partition By Shem, Nohehut Order By Taarih) Mispar2,
        Row_Number() Over(Partition By Shem Order By Taarih)-Row_Number() Over(Partition By Shem, Nohehut Order By Taarih) Mispar,
        *
From    T_Nohehut
Order By Shem,
        Taarih;
Go

clip_image004

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

מה שנעשה איפוא זה לבצע Group By לפי שם, נוכחות ומספר לשליפה הנ"ל,

ולהוסיף לה תאריכי התחלה וסיום בעזרת Min ו-Max על עמודת תאריך,

ומספר ימים בתחום בעזרת Count:

With T As
(Select Row_Number() Over(Partition By Shem Order By Taarih)-Row_Number() Over(Partition By Shem, Nohehut Order By Taarih) Mispar,
        *
From    T_Nohehut)
Select  Shem,
        Nohehut,
        Min(Taarih) MiTaarih,
        Max(Taarih) AdTaarih,
        Count(Mispar) Yamim
From    T
Group By Shem,
        Nohehut,
        Mispar
Order By Shem,
        Min(Taarih);
Go

clip_image006

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

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

כתיבת תגובה

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

הלוגו של WordPress.com

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

תמונת Twitter

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

תמונת Facebook

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

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

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

מתחבר ל-%s

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

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