五餅二魚工作室

James Fu 的技術學習之路

2013-04-23

[SQL]建立時間資料表

[SQL]建立時間資料表

之前在剛接觸 BI 的時候，時常會有個納悶，為什麼要產生個時間資料表，看書上一些 Sample 都只是看起來要把時間分出年、季、月，因此也就沒有太多的去了解了。

 

最近在測試一些 Power Pivot 的時候才發覺，如果當我們的資料來源來自許多不同的表格的時候，透過時間資料表來將這些資料給串再一起，就可以免去要去把這些表格要在事前先把它給 Union 在一起，但要怎麼去產生這樣的時間資料表，直覺上就是要去把所有的資料表的時間放到一個 Table 內，感覺上似乎就有點麻煩。

 

昨天在聽一場課程中，老師介紹到一個方式，原來利用 CTE 遞迴的方式來做處理，就可以解決了，老師參考書上的做法列出一個範例。

-- 取自 Microsoft SQL Server 2012 Analysis Services: The BISM Tabular Model    P. 294
DECLARE @StartYear AS INT = 2000 ; 
DECLARE @EndYear AS INT = 2006 ;

WITH Years 
    AS ( SELECT YYYY = @StartYear 
    UNION ALL 
    SELECT YYYY + 1 
    FROM Years 
    WHERE YYYY < @EndYear 
    ), 
Months 
    AS ( SELECT MM = 1 
    UNION ALL 
    SELECT MM + 1 
    FROM Months 
    WHERE MM < 12 
    ), 
Days 
    AS ( SELECT DD = 1 
    UNION ALL 
    SELECT DD + 1 
    FROM Days 
    WHERE DD < 31 
    ), 
DatesRaw 
    AS ( SELECT YYYY = YYYY, 
    MM = MM, 
    DD = DD, 
    ID_Date = YYYY * 10000 + MM * 100 + DD, 
    Date = CASE WHEN ISDATE(YYYY * 10000 + MM * 100 + DD) = 1 
    THEN CAST(CAST(YYYY * 10000 + MM * 100 
    + DD AS VARCHAR) AS DATE) 
    ELSE NULL 
    END 
    FROM Years 
    CROSS JOIN Months 
    CROSS JOIN Days 
    WHERE ISDATE(YYYY * 10000 + MM * 100 + DD) = 1 
    ) 
SELECT d.ID_Date, 
    d.Date, 
    [Year] = YEAR(d.Date), 
    MonthNumber = MONTH(d.Date), 
    [Month] = DATENAME(MONTH, d.Date), 
    DayOfMonth = DAY(d.Date), 
    DayOfWeekNumber = DATEPART(dw, d.Date), 
    [DayOfWeek] = DATENAME(dw, d.Date), 
    WorkingDay = CAST(CASE DATEPART(dw, d.Date) 
    WHEN 1 THEN 0 -- Sunday 
    WHEN 7 THEN 0 -- Saturday 
    ELSE 1 -- Might lookup for a holidays table here 
    END AS BIT) 
FROM DatesRaw d 
ORDER BY d.Date

 

上述這個範例在 CTE 內先利用三個子 Table，將年、月、日分開產生出集合，接著在用 Cross Join 把這些合併起來，因此我依樣畫葫蘆，做一個自己的版本來使用

DECLARE @StartYear AS INT = 2000 ; 
DECLARE @EndYear AS INT = 2006 ;

WITH AllDays 
    AS ( 
        SELECT [Date] = CAST( CAST(@StartYear as varchar ) + '0101' AS DATE )
        UNION ALL 
        SELECT [Date] = DATEADD( day, 1 , [Date] ) FROM AllDays where [Date] < CAST( CAST(@EndYear as varchar ) + '1231' AS DATE ) 
        ) 
SELECT 
    CONVERT(varchar,d.[Date],112) ID_Date,
    d.Date,
    [Year] = YEAR(d.Date), 
    MonthNumber = MONTH(d.Date), 
    [Month] = DATENAME(MONTH, d.Date), 
    DayOfMonth = DAY(d.Date), 
    DayOfWeekNumber = DATEPART(dw, d.Date), 
    [DayOfWeek] = DATENAME(dw, d.Date), 
    WorkingDay = CAST(CASE DATEPART(dw, d.Date) 
    WHEN 1 THEN 0 -- Sunday 
    WHEN 7 THEN 0 -- Saturday 
    ELSE 1 -- Might lookup for a holidays table here 
    END AS BIT) 
FROM AllDays d OPTION( MaxRecursion 10000 )

 

由於 CTE 預設的最大地迴是 100 ，因此在原範例中最大的遞迴數量是 31，因此不用加上查詢提示，而我這個做法則是用加法的方式讓他一天一天去跑，因此只要超過一年就會超過預設值的限制，因此我在最後加上一個 MAXRECURSION 的查詢提示，並且設定為 10000，如果有需要更多的可以在調整那個數字。

系列文章

標籤雲