en-UShe-IL
You are here:   Blog
Register   |  Login

Blog Archive:

Maximize
* Can be used in order to search for older blogs Entries

Search in blogs


Blog Categories:

Maximize
* Can be used in order to search for blogs Entries by Categories

Blog Tags:

Maximize
* Can be used in order to search for blogs by keywords

TNWikiSummit


Awared MVP

 


Microsoft® Community Contributor 


Microsoft® Community Contributor


 Read first, before you you use the blog! Maximize

Recent Entries

Minimize
אפר5

Written by: ronen ariely
05/04/2012 14:23 RssIcon

עבודה עם לוג רישומים ופירוק הזמנים לפי חודשים

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

על מנת לדון בבעיה נבנה לנו מערכת טבלאות לוגי זמנים פשוטה לדוגמה

use QQ
go
 
CREATE TABLE [dbo].[tblcust](
    [custno] [int] NULL,
    [custname] [varchar](50) NULL
) ON [PRIMARY]
 
Insert Into tblcust (custno,custname)
Select 1,'A'
Union
Select 2,'B'
 
 
CREATE TABLE [dbo].[tblcustdate](
    [custno] [int] NULL,
    [DATESATART] [datetime] NULL,
    [DATEEND] [datetime] NULL
) ON [PRIMARY]
GO
 
Insert Into tblcustdate (custno,DATESATART,DATEEND)
Select 1,'2007-04-02 17:01:21.780','2007-05-02 17:01:21.780'
Union
Select 1,'2007-03-02 17:01:22.343','2007-03-05 17:01:22.343'
Union
Select 1,'2012-04-02 17:01:23.680','2012-04-02 17:01:23.680'
 
Insert Into tblcustdate (custno,DATESATART,DATEEND)
Select 1,'2011-04-02 17:01:21.780','2011-05-02 17:01:21.780'
GO

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

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

מספר לקוח

ינואר 2012

פברואר 2012

מרץ 2012

...

 

 

 

 

 

 

 

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

במדריך זה נגיע עד לשלב הצגה של הנתונים בהתאם לטבלה הבאה:

מספר לקוח

חודש

דקות גלישה

 

 

 

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

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

לעבודה...

נתחיל מהסוף להתחלה: אם הרשומות שלנו כבר היו מופרדות לחודשים הרי שהפתרון מאוד פשוט ומיידי. פשוט נבצע SELECT על כל הרשומות עם GROUP BY על מספר הלקוח ועל החודש וסיימנו את כל התרגיל.

אבל הבעיה הראשונה איתה עלינו להתמודד היא מה קורה כאשר לקוח גולש באופן רציף ובינתיים החודש השתנה. ז"א אם הלקוח התחיל לגלוש ב 29.03.2012 וסיים לגלוש ב 02.04.2012 ? במקרה זה עלינו לפרק את זמן הגלישה ברשומה שלנו בלוג, כך שעד לסיום החודש הזמן ירשם בטבלה הסופית כזמן של חודש 3 והחלק של הגלישה בחודש 4 ירשם בצורה נכונה בחודש 4.

פונקציית עזר Ariely_BringBackTimePerMonth

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

USE [_ArielyAccessoriesDB]
GO
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
CREATE FUNCTION [dbo].[Ariely_BringBackTimePerMonth] (@StartT AS DATETIME, @EndT AS DATETIME)
RETURNS @Out TABLE ([M] INT, [TIME] INT)
AS
BEGIN
    DECLARE @QQ AS DATETIME = @StartT
     
    WHILE DATEDIFF(MONTH,@QQ,@EndT)>0
    BEGIN
     
        DECLARE @NextMonthStartDate DATETIME
        set @NextMonthStartDate = DATEADD(mm, DATEDIFF(m,0,@QQ)+1,0)
         
        DECLARE @Minute2TheEndOfMonth AS INT = DATEDIFF(MINUTE,@QQ,@NextMonthStartDate)
         
        INSERT @Out ([M],[TIME]) VALUES (MONTH(@QQ),@Minute2TheEndOfMonth)
         
        SET @QQ = @NextMonthStartDate
    END
     
    INSERT @Out ([M],[TIME]) VALUES (MONTH(@QQ),DATEDIFF(MINUTE,@QQ,@EndT))
     
    RETURN
END

הערה: שמו  לב שאני מכניס את הפונקציה למסד נתונים כללי [_ArielyAccessoriesDB] בו אני מחזיק פונקציות עזר רבות ושונות לשימוש כללי. מאוד מומלץ להחזיק בכל שרת פונקציות עזר שונות בנפרד ולא להמציא את הגלגל בכל פעם מחדש.

עתה נוכל לפנות לפונקציה זו עבור כל רשומה בטבלה המקורית.

עבודה עם סמן (cursor)

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

הסבר הקוד הבא:

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

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

3. אני מכסני לטבלה הזמנית את הנתונים המפורקים

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

5. אני מחבר ב JOIN נתונים מטבלת הלקוחות שלנו אל הטבלה הזמנית שלנו על מנת לקלב את כל הנתונים שאני רוצה להציג בסוף

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

declare @TBL as table
(
    [custno] int
    , [M] int
    , [TIME] int
)
 
 declare @custno as int
 declare @DATESATART as datetime
 declare @DATEEND as datetime
 
 
DECLARE db_cursor CURSOR FOR
select [custno],[DATESATART],[DATEEND] from [tblcustdate]
 
OPEN db_cursor
  
FETCH NEXT FROM db_cursor INTO @custno,@DATESATART,@DATEEND
 
WHILE @@FETCH_STATUS = 0 
BEGIN 
       insert @TBL ([custno],[M],[TIME])
       select @custno,[M],[TIME]
       from _ArielyAccessoriesDB.dbo.Ariely_BringBackTimePerMonth(@DATESATART,@DATEEND)
 
       FETCH NEXT FROM db_cursor INTO @custno,@DATESATART,@DATEEND
END 
 
CLOSE db_cursor
DEALLOCATE db_cursor
 
--זמנים מחושבים לפי דקות ולפי פירוק לחודשים
select * from @TBL
 
-- נחבר נתונים מטבלת הלקוחות שלנו אל הנתונים מהטבלה הזמנית
select [tblcust].*,T.M,T.[TIME]
from [tblcust]
left join @TBL as T on T.custno=[tblcust].custno
 
--  ולכן מה שנשאר לנו זה רק לבצע סיכום לפי מסםר לקוח/חברה ולפי חודש
-- ואז לבצע פיבוט להצגה יפה של הנתונים עם אפסים במקום שחסר נתון
select [tblcust].*,T.M,sum( T.[TIME])
from [tblcust]
left join @TBL as T on T.custno=[tblcust].custno
group by [tblcust].custno,[tblcust].custname,T.M

מיטוב

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

* אם מירב הנתונים שלנו בלוג הזמנים לא כוללים זמן של יותר מחודש אחד, האם לא כדאי לנו לחלק את העבודה ל 2 קבוצות? במקום לעבור בלולאה על כל הרשומות תוך שימוש בסמן יקר + להריץ פונקציה יקרה עבור כל רשומה לפירוק הרשומה אשר כבר מפורקת מראש (מפני שכוללת נתון מחודש בודד) אז נוכל להריץ תחילה בחירה של כל הנתונים המסודרים כבר ואז להריץ בסמן ולהפנות לפונקצית הפירוק שלנו רק את הרשומות שיש לפרק. ביצוע Union פשוט על 2 השאילתות הנ"ל יכול למטב לנו את העבודה בסדרי גודל מאוד רציניים

* ומה אם נרצה לוותר על השמוש בטבלה זמנית? אין בעיה :-) ניתן לרשום פונקציה מעט שונה שבמקום להחזיר לנו טבלה בתוצאה פשוט תחזיר לנו שרשרת. נוכל למשל להחזיר שרשרת של שאילתה ואז להריץ אותה ישירות מחוץ ללולאה על הסמן בפעם אחת.

* רוצים להקשות יותר או למטב יותר?!? אין בעיה... עד שימאס לי ויגמר לי הזמן ניתן להציג גם כיצד נעבוד ללא סמן בכלל!

אז מה נעשה אם רוצים לוותר על השימוש בסמן? פשוט מאוד... נכין פונקציה מעט שונה שמחזירה לנו שרשרת XML. באובייקט זה תהיה שורה לכל חודש. עתה במקום להפעיל סמן ולעבור על הלולאות בלולאה ניתן להפעיל את הפונקציה ישירות על הטבלה שלנו ולהחזיר בעזרת SELECT פשוט את כל האלמנטים ב DOM של ה MXL בעזרת שימוש ב  FOR XML

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