Làm việc với bảng lịch

Phân tích chuỗi thời gian trong SQL Server

Maham Faisal Khan

Senior Data Science Content Developer

Bảng lịch là gì?

SELECT *
FROM dbo.Calendar;

DateKey	Date	Day	DayOfWeek	DayName	...
20000101	2000-01-01	1	7	Saturday	...
20000102	2000-01-02	2	1	Sunday	...
20000103	2000-01-03	3	2	Monday	...

Nội dung của bảng lịch

Cột chung

Date
Day Name
Is Weekend

Năm dương lịch

Tháng dương lịch
Quý dương lịch
Năm dương lịch

Năm tài chính

Tuần tài chính trong năm
Quý tài chính
Ngày đầu năm tài chính

Cột chuyên biệt

Tên ngày lễ
Chi tiết âm lịch
Tuần ISO trong năm

Xây dựng bảng lịch

CREATE TABLE dbo.Calendar
(
    DateKey INT NOT NULL,
    [Date] DATE NOT NULL,
    [Day] TINYINT NOT NULL,
    DayOfWeek TINYINT NOT NULL,
    DayName VARCHAR(10) NOT NULL,
    ...
)

SELECT
    CAST(D.DateKey AS INT) AS DateKey,
    D.[DATE] AS [Date],
    CAST(D.[day] AS TINYINT) AS [day],
    CAST(d.[dayofweek] AS TINYINT) AS [DayOfWeek],
    CAST(DATENAME(WEEKDAY, d.[Date]) AS VARCHAR(10)) AS [DayName],
    ...

Sử dụng bảng lịch

SELECT
    c.Date
FROM dbo.Calendar c
WHERE
    c.MonthName = 'April'
    AND c.DayName = 'Saturday'
    AND c.CalendarYear = 2020
ORDER BY
    c.Date;

Date
2020-04-04
2020-04-11
2020-04-18
2020-04-25

Sử dụng bảng lịch

SELECT
    c.Date
FROM dbo.Calendar c
WHERE
    c.MonthName = 'April'
    AND c.DayName = 'Saturday'
    AND c.CalendarYear = 2020
ORDER BY
    c.Date;

Date
2020-04-04
2020-04-11
2020-04-18
2020-04-25

Ghi chú nhanh về APPLY()

SELECT
    FYStart =
        DATEADD(MONTH, -6,
            DATEADD(YEAR,
                DATEDIFF(YEAR, 0,
                    DATEADD(MONTH, 6, d.[date])), 0)),
    FiscalDayOfYear =
        DATEDIFF(DAY,
            DATEADD(MONTH, -6,
                DATEADD(YEAR,
                    DATEDIFF(YEAR, 0,
                        DATEADD(MONTH, 6, d.[date])), 0)), d.[Date]) + 1,
    FiscalWeekOfYear =
        DATEDIFF(WEEK,
            DATEADD(MONTH, -6,
                DATEADD(YEAR,
                    DATEDIFF(YEAR, 0,
                        DATEADD(MONTH, 6, d.[date])), 0)), d.[Date]) + 1
FROM dbo.Calendar d;

Ghi chú nhanh về APPLY()

SELECT
    fy.FYStart,
    FiscalDayOfYear = DATEDIFF(DAY, fy.FYStart, d.[Date]) + 1,
    FiscalWeekOfYear = DATEDIFF(WEEK, fy.FYStart, d.[Date]) + 1
FROM dbo.Calendar d
    CROSS APPLY
    (
        SELECT FYStart =
            DATEADD(MONTH, -6,
                DATEADD(YEAR,
                    DATEDIFF(YEAR, 0,
                        DATEADD(MONTH, 6, d.[date])), 0))
    ) fy;

Ayo berlatih!

Phân tích chuỗi thời gian trong SQL Server