Календарь стол для хранилища данных

Question

Календарь стол для хранилища данных

Для моего хранилища данных я создаю таблицу календаря следующим образом:

SET NOCOUNT ON

DROP Table dbo.Calendar
GO
Create Table dbo.Calendar
(
    CalendarId              Integer NOT NULL,
    DateValue               Date    NOT NULL,
    DayNumberOfWeek         Integer NOT NULL,
    NameOfDay               VarChar (10) NOT NULL,
    NameOfMonth             VarChar (10) NOT NULL,
    WeekOfYear              Integer NOT NULL,
    JulianDay               Integer NOT NULL,
    USAIsBankHoliday        Bit     NOT NULL,
    USADayName              VarChar (100) NULL,
)
ALTER TABLE dbo.Calendar ADD CONSTRAINT
    DF_Calendar_USAIsBankHoliday DEFAULT 0 FOR USAIsBankHoliday
GO
ALTER TABLE dbo.Calendar ADD CONSTRAINT
    DF_Calendar_USADayName DEFAULT '' FOR USADayName
GO

Declare @StartDate  DateTime = '01/01/2000'
Declare @EndDate    DateTime = '01/01/2020'

While @StartDate < @EndDate
Begin
    INSERT INTO dbo.Calendar 
    (
        CalendarId, 
        DateValue, 
        WeekOfYear,
        DayNumberOfWeek,
        NameOfDay,
        NameOfMonth,
        JulianDay
    )
    Values 
    (
        YEAR (@StartDate) * 10000 + MONTH (@StartDate) * 100 + Day (@StartDate), --CalendarId
        @StartDate,                 -- DateValue
        DATEPART (ww, @StartDate),  -- WeekOfYear
        DATEPART (dw, @StartDate),  -- DayNumberOfWeek
        DATENAME (dw, @StartDate),  -- NameOfDay
        DATENAME (M, @StartDate),   -- NameOfMonth
        DATEPART (dy, @StartDate)   -- JulianDay
    )

    Set @StartDate += 1
End

--=========================== Weekends
-- saturday and sunday
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Weekend, ' WHERE DayNumberOfWeek IN (1, 7) 


--=========================== Bank Holidays
-- new years day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'New Year''s Day, '  WHERE (CalendarId % 2000) IN (101)

-- memorial day (last Monday in May)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Memorial Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT MAX (CalendarId)
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 5 
        AND DATEPART (DW, DateValue)=2
        GROUP BY YEAR (datevalue)
    )

-- independence day
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Independence Day, '  WHERE (CalendarId % 2000) IN (704)

-- labor day (first Monday in September)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Labor Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT MIN (CalendarId)
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 9
        AND DATEPART (DW, DateValue)=2
        GROUP BY YEAR (datevalue)
    )

-- thanksgiving day (fourth Thursday in November)
UPDATE dbo.Calendar 
SET USAIsBankHoliday = 1, 
    USADayName += 'Thanksgiving Day, '  
WHERE 1=1
AND CalendarId IN 
    (
        SELECT Max (CalendarId)-2
        FROM dbo.Calendar 
        WHERE MONTH (DateValue) = 11
        AND DATEPART (DW, DateValue)=7
        GROUP BY YEAR (datevalue)
    )

-- christmas
UPDATE dbo.Calendar SET USAIsBankHoliday = 1, USADayName += 'Christmas Day, '  WHERE (CalendarId % 2000) IN (1225)

--=========================== Other named days
-- new years eve
UPDATE dbo.Calendar SET USADayName += 'New Year''s Eve, '  WHERE (CalendarId % 2000) IN (1231)

-- black friday (day after thanksgiving day)
UPDATE dbo.Calendar SET USADayName += 'Black Friday, '  WHERE CalendarId IN (SELECT CalendarId+1 From dbo.Calendar Where USADayName like '%Thanksgiving%')

-- christmas eve
UPDATE dbo.Calendar SET USADayName += 'Christmas Eve, '  WHERE (CalendarId % 2000) IN (1224)

-- boxing day
UPDATE dbo.Calendar SET USADayName += 'Boxing Day, '  WHERE (CalendarId % 2000) IN (1226)

--=========================== Remove trailing comma
UPDATE dbo.Calendar SET USADayName = SubString (USADayName, 1, LEN (USADayName) -1) WHERE LEN (USADayName) > 2

SELECT * FROM dbo.Calendar

Вот вывод этой команды

http://img35.imageshack.us/img35/3899/calendartable.png

Я видел подобные структуры, реализованные различными способами архитекторами данных.

Мой вопрос: какую еще полезную информацию о хранилище данных / стиле измерений я могу добавить к этой структуре таблицы?

13

sql-server data-warehouse

Источник

user109035 29 июл '09 в 17:36

4 ответа

Решение

Это мой список возможных столбцов в календарном измерении:

ключ
Дата
Вчера
Это сегодня
Завтра
День года
День полугодия
День квартала
День месяца
День недели
Краткое название дня недели
День недели сокращенное название CS
День недели длинное имя
День недели Длинное имя CS
Дни в неделе
Дней в месяце
Дни в квартале
Дни в полугодии
Дней в году
Обратный день недели
Обратный день месяца
Обратный день квартала
Обратный день полугодия
Обратный день года
Последние 7 дней
Последние 14 дней
Последние 30 дней
Последние 90 дней
Последние 180 дней
Последние 365 дней
Будний день
Выходные
Рабочий день недели
Рабочий день месяца
Рабочий день квартала
Рабочий день полугодия
Рабочий день года
Обратный рабочий день недели
Обратный рабочий день месяца
Обратный рабочий день квартала
Обратный рабочий день полугодия
Обратный рабочий день года
Рабочие дни в неделе
Рабочие дни в месяце
Рабочие дни в квартале
Рабочие дни в полугодии
Рабочие дни в году
Последний рабочий день в неделю
Последний рабочий день в месяце
Рабочий день
Это праздник
Это будущее
Прошлое
Предыдущий месяц
Текущий месяц
Следующий месяц
Месяц до даты
Начало месяца
Конец месяца
Прошлый месяц
Начало месяца
Конец месяца
Номер месяца
Название месяца Длинное
Название месяца Long CS
Название месяца короткое
Название месяца Короткая CS
Месяц Четверти
Полугодие
Предыдущая неделя
Текущая неделя
Следующая неделя
Неделя к дате
Начало недели
Конец недели
Прошлая неделя
Начало недели
Конец недели
Номер недели
Название недели длинное
Название недели короткое
Неделя месяца
Предыдущий квартал
Текущий квартал
Следующий квартал
Квартал на сегодняшний день
Начало квартала
Конец квартала
Прошлый квартал
Начало квартала
Конец квартала
Номер квартала
Название квартала Длинное
Название квартала Long CS
Название квартала Короткое
Предыдущий полугодие
Текущий полугодие
Следует за полугодием
Половина на сегодняшний день
Начало полугодия
Конец полугодия
Прошло полугодия
Начало полугодия
Конец полугодия
Полугодовой номер
Полугодовое имя длинное
Полугодовое имя Long CS
Полугодовое название короткое
Предыдущий год
Текущий год
Следующий год
Год до даты
Начало года
Конец года
Прошлый год
Начало года
Конец года
Номер года
Название года
Краткое название года
Год Квартальный Текст
Год месяц день
Год Полугодие
Год квартал
Год месяц
Год день года
Високосный год
Расстояние в днях от сегодняшнего дня
Расстояние в рабочих днях с сегодняшнего дня
Расстояние в календарных неделях от сегодняшнего дня
Расстояние в календарных месяцах от сегодняшнего дня
Расстояние в календарных кварталах от сегодняшнего дня
Расстояние в календаре полугодия с сегодняшнего дня
Расстояние в календарных годах от сегодняшнего дня
N-й день недели в месяце
Обратный N-й день недели в месяце

Я создал интерактивную таблицу, где вы можете создать собственное измерение времени для базы данных Postgre SQL.

7

Источник

user1442374 11 фев '14 в 13:37

Квартал года
год_квартал (2013-3)
месяц года
year_month (2013-08)
неделя года
неделя месяца
день года
день четверти
день месяца
день недели

1

Источник

user223478 09 авг '13 в 04:45

Хорошо, Радж, Это хороший пост и очень полезный скрипт для создания календаря. Другие поля, которые вы можете включить в одну и ту же таблицу: 1) QuateroftheYear 2) IsWeekend 3) IsWeekday

1

Источник

user2229466 31 мар '13 в 14:58

Другие вопросы по тегам sql-server data-warehouse

user129037 29 июл '09 в 18:43 2009-07-29 18:43 · Accepted Answer · 2009-07-29 18:43

четверть
Год
Финансовый / Бухгалтерский год
Финансовый / Бухгалтерский квартал
isWeekend
isWeekday
isWorkDay
WeekId (недели с начала года)
isLastDayofMonth
DaysSince (например, дни с 01.01.2000)

10

Источник

user129037 29 июл '09 в 18:43