Главная

Дополнительные таблицы. Календарь

Измерение даты и времени -- ключевая и обязательная сущность любого хранилища данных или аналитической системы. В данной статье будет приведен пример создания типовой таблицы календаря с гранулярностью до уровня дня. Для создания данной таблицы  нам понадобится таблица dbo.Numbers из предыдущей статьи. Полный код и результаты работы можно посмотреть здесь.

Создадим таблицу dbo.DimDate. В качестве кластерного ключа таблицы будем использовать суррогатный целочисленный ключ, представленный в формате YYYYMMDD. Ниже представлен скрипт создания таблицы календаря, а также скрипт создания дополнительной таблицы dbo.Numbers.


if object_id('dbo.DimDate', 'U') is not null
 drop table dbo.DimDate
go

create table [dbo].[DimDate](
 [DateKey]      [int]           NOT NULL,
 [DateValue]    [date]          NOT NULL,
 [DateName]     [nvarchar](10)  NOT NULL,
 [Year]         [int]           NOT NULL,
 [Quarter]      [tinyint]       NOT NULL,
 [QuarterName]  [nchar](15)     NOT NULL,
 [MonthNum]     [tinyint]       NOT NULL,
 [MonthName]    [nvarchar](15)  NOT NULL,
 [DayOfWeek]    [tinyint]       NOT NULL,
 [DayOfMonth]   [tinyint]       NOT NULL,
 [DayOfYeear]   [smallint]      NOT NULL,
 [DayName]      [nvarchar](15)  NOT NULL,
 [DayNameShort] [nchar](2)      NOT NULL,
 [WeekOfYear]   [tinyint]       NOT NULL,
 [WeekOfMonth]  [tinyint]       NOT NULL,
 [WeekName]     [nchar](23)     NOT NULL,
 [IsWeekend]    [tinyint]       NOT NULL,
 [IsPreWeekend] [tinyint]       NOT NULL
)
go

alter table [dbo].[DimDate] add constraint [PK_DimDate] primary key([DateKey])
alter table [dbo].[DimDate] add constraint [DF_DimDate_Weekend]  default ((0)) for [IsWeekend]
alter table [dbo].[DimDate] add constraint [DF_DimDate_PreWeekend]  default ((0)) for [IsPreWeekend]
go

if object_id('dbo.Numbers', 'U') is not null
 drop table dbo.Numbers
go

select top(1000000) n = row_number() over(order by s1.number)
into dbo.Numbers
from master..spt_values s1, master..spt_values s2
option (maxdop 1);
go

create unique clustered index ix_numbers_n on dbo.Numbers(n)
Ниже представлен алгоритм заполнения таблицы календаря. Результаты выполнения запроса можно посмотреть здесь
/* Установка параметров сессии, влияющих на выполнение запроса */
set language russian
set dateformat 'ymd'
set datefirst 1

/* Определяем первый день года и последний */
declare @s date = '2014-01-01'; 
declare @e date = dateadd(day, -1, dateadd(year, 2, @s));

/* Выходные по производственному календарю 2015 и 2014 годов */
;with Holiday(DateKey) as
(
 select 20150101 union all
 select 20150102 union all
 select 20150105 union all
 select 20150106 union all
 select 20150107 union all
 select 20150108 union all
 select 20150109 union all
 select 20150223 union all
 select 20150309 union all
 select 20150501 union all
 select 20150504 union all
 select 20150511 union all
 select 20150612 union all
 select 20151104 union all
 
 select 20140101 union all
 select 20140102 union all
 select 20140103 union all
 select 20140106 union all
 select 20140107 union all
 select 20140108 union all
 select 20140310 union all
 select 20140501 union all
 select 20140502 union all
 select 20140509 union all
 select 20140612 union all
 select 20140613 union all
 select 20141103 union all 
 select 20141104
),
/* Сокращенные дни по производственному календарю на 2014 и 2015 года */
PreHoliday(DateKey) as
(
 select 20150430 union all
 select 20150508 union all
 select 20150611 union all
 select 20151103 union all
 select 20151231 union all
 
 select 20141231 union all
 select 20140307 union all
 select 20140430 union all
 select 20140611 union all
 select 20140805
)
insert into dbo.DimDate
select  
 DateKey        = d.DateKey, -- 20140101
 DateValue      = DateValue,
 DateName       = Convert(nvarchar(10), DateValue, 104), -- 01.01.2014
 Year           = Year(DateValue),
 Quarter        = Datepart(QUARTER, DateValue),
 QuarterName    = N'Квартал ' + Convert(nvarchar(1), Datepart(QUARTER, DateValue)) + ', ' + Convert(nvarchar(4),Year(DateValue)), -- Квартал 1, 2014
 MonthNum       = Month(DateValue),
 MonthName      = Datename(MONTH, DateValue)+ ', ' + Convert(nvarchar(4),Year(DateValue)), -- Январь, 2014
 DayOfWeek      = DatePart(DW, DateValue),
 DayOfMonth     = Day(DateValue),
 DayOfYear      = DatePart(DY, DateValue),
 DayName        = Upper(Left(DateName(DW, DateValue),1)) + Substring(DateName(DW, DateValue), 2, Len(DateName(DW, DateValue)) ),
 DayNameShort   = Case When DateName(DW, DateValue) = N'понедельник' Then N'Пн'
        When DateName(DW, DateValue) = N'вторник'     Then N'Вт'
        When DateName(DW, DateValue) = N'среда'       Then N'Ср'
        When DateName(DW, DateValue) = N'четверг'     Then N'Чт'
        When DateName(DW, DateValue) = N'пятница'     Then N'Пт'
        When DateName(DW, DateValue) = N'суббота'     Then N'Сб'
        When DateName(DW, DateValue) = N'воскресенье' Then N'Вс'
      End,
 WeekOfYear     = DatePart(WK, DateValue),
 WeekOfMonth    = dense_rank() over(partition by Year(DateValue), Month(DateValue) order by DatePart(WK, DateValue)),
 WeekName       = Convert(nvarchar(10), Min(DateValue) over(partition by Year(DateValue), Datepart(WK, DateValue)), 104) 
      + ' - ' + Convert(nvarchar(10), Max(DateValue) over(partition by Year(DateValue), Datepart(WK, DateValue)), 104), -- 01.01.2014 - 05.01.2014
 IsWeekend      = Case When Holiday.DateKey is not null or DatePart(DW, DateValue) in (6,7) Then 1 Else 0 End,
 IsPreWeekend   = Case When PreHoliday.DateKey is not null Then 1 Else 0 End
 
from
(
 select top (datediff(day, @s, @e) + 1) 
  DateKey = Convert(int, Convert(nvarchar(8), Convert(date, DateAdd(Day, n-1, @s)), 112)), -- Ключ даты формата YYYYMMDD
  DateValue = Convert(date, DateAdd(day, n-1, @s))
 from dbo.Numbers order by n
) d
left join Holiday
 on Holiday.DateKey = d.DateKey
left join PreHoliday
 on PreHoliday.DateKey = d.DateKey

Ссылки

15 мая 2015 г.