Pendahuluan
Tabel kalender bisa sangat berguna, khususnya untuk tujuan pelaporan, dan untuk menentukan hal-hal seperti hari kerja di antara dua tanggal. Saya sering melihat orang-orang kesulitan mengisi kalender atau tabel dimensi tanggal secara manual; biasanya ada banyak loop dan konstruksi kode iteratif yang digunakan. Dalam tip ini saya akan menunjukkan cara membuat dan menggunakan tabel kalender menggunakan solusi berbasis set yang kuat dan mudah disesuaikan.
Solusi
Saya selalu membuat tabel kalender, untuk berbagai aplikasi bisnis, dan menemukan beberapa cara untuk menangani detail tertentu. Membagikannya di sini diharapkan akan mencegah Anda menemukan kembali roda apa pun saat mengisi tabel Anda sendiri.
Salah satu keberatan terbesar yang saya dengar tentang tabel kalender adalah orang tidak ingin membuat tabel. Saya tidak bisa cukup menekankan betapa murahnya sebuah tabel dalam hal ukuran dan penggunaan memori, terutama karena penyimpanan yang mendasari terus menjadi lebih besar dan lebih cepat, dibandingkan dengan menggunakan semua jenis fungsi untuk menentukan informasi terkait tanggal di setiap kueri. Tanggal dua puluh atau tiga puluh tahun yang disimpan dalam tabel membutuhkan paling banyak beberapa MB, bahkan lebih sedikit dengan kompresi, dan jika Anda cukup sering menggunakannya, tanggal tersebut akan selalu tersimpan dalam memori.
Saya juga selalu secara eksplisit mengatur hal-hal seperti DATEFORMAT, DATEFIRST, dan LANGUAGE untuk menghindari ambiguitas, default ke Bahasa Inggris AS untuk awal minggu dan untuk nama bulan dan hari, dan berasumsi bahwa kuartal untuk tahun fiskal selaras dengan tahun kalender. Anda mungkin perlu mengubah beberapa spesifikasi ini bergantung pada bahasa tampilan, tahun fiskal, dan faktor lainnya.
Ini adalah populasi satu kali, jadi saya tidak khawatir tentang kecepatan, meskipun pendekatan CTE khusus ini tidak bungkuk. Saya suka mewujudkan semua kolom ke disk, daripada mengandalkan kolom yang dihitung, karena tabel menjadi hanya-baca setelah populasi awal. Jadi saya akan melakukan banyak perhitungan selama seri awal CTE. Untuk memulai, saya akan menunjukkan output dari setiap CTE satu per satu.
Anda dapat mengubah beberapa detail ini untuk bereksperimen sendiri. Dalam contoh ini, saya akan mengisi tabel dimensi tanggal dengan data yang mencakup 30 tahun, mulai dari 01-01-2010.
Pertama, kami memiliki CTE rekursif yang mengembalikan urutan yang mewakili jumlah hari antara tanggal mulai kami (2010-01-01) dan 30 tahun kemudian kurang sehari (2039-12-31):
-- prevent set or regional settings from interfering with
-- interpretation of dates / literals
SET DATEFIRST 7, -- 1 = Monday, 7 = Sunday
DATEFORMAT mdy,
LANGUAGE US_ENGLISH;
-- assume the above is here in all subsequent code blocks.
DECLARE @StartDate date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
;WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
)
SELECT n FROM seq
ORDER BY n
OPTION (MAXRECURSION 0);
Ini mengembalikan daftar nomor berikut:
Di SQL Server 2022 atau Azure SQL Database, CTE awal tersebut dapat sangat disederhanakan dengan menggunakan fungsi GENERATE_SERIES baru, yang juga akan meniadakan kebutuhan MAXRECURSION dalam kueri berikutnya:
DECLARE @StartDate date = '20100101', @years int = 30;
;WITH seq(n) AS
(
SELECT n = value FROM GENERATE_SERIES(0,
DATEDIFF(DAY, @StartDate, DATEADD(YEAR, @years, @StartDate))-1)
)
SELECT n FROM seq
ORDER BY n;
Selanjutnya, kita dapat menambahkan CTE kedua yang menerjemahkan angka tersebut ke dalam semua tanggal dalam rentang kita:
DECLARE @StartDate date = '20100101'; DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate)); ;WITH seq(n) AS ( SELECT 0 UNION ALL SELECT n + 1 FROM seq WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate) ), d(d) AS ( SELECT DATEADD(DAY, n, @StartDate) FROM seq ) SELECT d FROM d ORDER BY d OPTION (MAXRECURSION 0);
Yang mengembalikan rentang tanggal berikut:
Sekarang, kita dapat mulai memperpanjang tanggal tersebut dengan informasi yang umumnya penting untuk tabel kalender/dimensi tanggal. Banyak bit informasi yang dapat Anda ekstrak dari tanggal, tetapi akan lebih mudah untuk membuatnya tersedia dalam tampilan atau tabel daripada membuat setiap kueri menghitungnya sebaris. Saya bekerja sedikit mundur di sini, tetapi saya akan membuat CTE perantara untuk mengekstraksi tepat sekali beberapa perhitungan yang nantinya harus saya buat berkali-kali. berikut query-nya :
DECLARE @StartDate date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
;WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS
(
SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
SELECT
TheDate = CONVERT(date, d),
TheDay = DATEPART(DAY, d),
TheDayName = DATENAME(WEEKDAY, d),
TheWeek = DATEPART(WEEK, d),
TheISOWeek = DATEPART(ISO_WEEK, d),
TheDayOfWeek = DATEPART(WEEKDAY, d),
TheMonth = DATEPART(MONTH, d),
TheMonthName = DATENAME(MONTH, d),
TheQuarter = DATEPART(Quarter, d),
TheYear = DATEPART(YEAR, d),
TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
TheLastOfYear = DATEFROMPARTS(YEAR(d), 12, 31),
TheDayOfYear = DATEPART(DAYOFYEAR, d)
FROM d
)
SELECT * FROM src
ORDER BY TheDate
OPTION (MAXRECURSION 0);
Menghasilkan data sebagai berikut :
Jika Anda ingin tahun fiskal Anda diselaraskan secara berbeda, Anda dapat mengubah perhitungan tahun dan kuartal, atau menambahkan kolom tambahan. Katakanlah tahun fiskal Anda dimulai 1 Oktober, kemudian tergantung pada apakah itu terlambat 9 bulan atau 3 bulan lebih awal, Anda bisa mengganti d dengan ekspresi DATEADD:
;WITH q AS (SELECT d FROM
(
VALUES('20200101'),
('20200401'),
('20200701'),
('20201001')
) AS d(d))
SELECT
d,
StandardQuarter = DATEPART(QUARTER, d),
LateFiscalQuarter = DATEPART(QUARTER, DATEADD(MONTH, -9, d)),
LateFiscalQuarterYear = YEAR(DATEADD(MONTH, -9, d)),
EarlyFiscalQuarter = DATEPART(QUARTER, DATEADD(MONTH, 3, d)),
EarlyFiscalQuarterYear = YEAR(DATEADD(MONTH, 3, d))
FROM q;
Apa pun data sumber saya, saya dapat mengembangkan bagian-bagian itu dan mendapatkan lebih banyak detail tentang setiap tanggal:
DECLARE @StartDate date = '20100101';
DECLARE @CutoffDate date = DATEADD(DAY, -1, DATEADD(YEAR, 30, @StartDate));
;WITH seq(n) AS
(
SELECT 0 UNION ALL SELECT n + 1 FROM seq
WHERE n < DATEDIFF(DAY, @StartDate, @CutoffDate)
),
d(d) AS
(
SELECT DATEADD(DAY, n, @StartDate) FROM seq
),
src AS
(
SELECT
TheDate = CONVERT(date, d),
TheDay = DATEPART(DAY, d),
TheDayName = DATENAME(WEEKDAY, d),
TheWeek = DATEPART(WEEK, d),
TheISOWeek = DATEPART(ISO_WEEK, d),
TheDayOfWeek = DATEPART(WEEKDAY, d),
TheMonth = DATEPART(MONTH, d),
TheMonthName = DATENAME(MONTH, d),
TheQuarter = DATEPART(Quarter, d),
TheYear = DATEPART(YEAR, d),
TheFirstOfMonth = DATEFROMPARTS(YEAR(d), MONTH(d), 1),
TheLastOfYear = DATEFROMPARTS(YEAR(d), 12, 31),
TheDayOfYear = DATEPART(DAYOFYEAR, d)
FROM d
),
dim AS
(
SELECT
TheDate,
TheDay,
TheDaySuffix = CONVERT(char(2), CASE WHEN TheDay / 10 = 1 THEN 'th' ELSE
CASE RIGHT(TheDay, 1) WHEN '1' THEN 'st' WHEN '2' THEN 'nd'
WHEN '3' THEN 'rd' ELSE 'th' END END),
TheDayName,
TheDayOfWeek,
TheDayOfWeekInMonth = CONVERT(tinyint, ROW_NUMBER() OVER
(PARTITION BY TheFirstOfMonth, TheDayOfWeek ORDER BY TheDate)),
TheDayOfYear,
IsWeekend = CASE WHEN TheDayOfWeek IN (CASE @@DATEFIRST WHEN 1 THEN 6 WHEN 7 THEN 1 END,7)
THEN 1 ELSE 0 END,
TheWeek,
TheISOweek,
TheFirstOfWeek = DATEADD(DAY, 1 - TheDayOfWeek, TheDate),
TheLastOfWeek = DATEADD(DAY, 6, DATEADD(DAY, 1 - TheDayOfWeek, TheDate)),
TheWeekOfMonth = CONVERT(tinyint, DENSE_RANK() OVER
(PARTITION BY TheYear, TheMonth ORDER BY TheWeek)),
TheMonth,
TheMonthName,
TheFirstOfMonth,
TheLastOfMonth = MAX(TheDate) OVER (PARTITION BY TheYear, TheMonth),
TheFirstOfNextMonth = DATEADD(MONTH, 1, TheFirstOfMonth),
TheLastOfNextMonth = DATEADD(DAY, -1, DATEADD(MONTH, 2, TheFirstOfMonth)),
TheQuarter,
TheFirstOfQuarter = MIN(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
TheLastOfQuarter = MAX(TheDate) OVER (PARTITION BY TheYear, TheQuarter),
TheYear,
TheISOYear = TheYear - CASE WHEN TheMonth = 1 AND TheISOWeek > 51 THEN 1
WHEN TheMonth = 12 AND TheISOWeek = 1 THEN -1 ELSE 0 END,
TheFirstOfYear = DATEFROMPARTS(TheYear, 1, 1),
TheLastOfYear,
IsLeapYear = CONVERT(bit, CASE WHEN (TheYear % 400 = 0)
OR (TheYear % 4 = 0 AND TheYear % 100 <> 0)
THEN 1 ELSE 0 END),
Has53Weeks = CASE WHEN DATEPART(WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
Has53ISOWeeks = CASE WHEN DATEPART(ISO_WEEK, TheLastOfYear) = 53 THEN 1 ELSE 0 END,
MMYYYY = CONVERT(char(2), CONVERT(char(8), TheDate, 101))
+ CONVERT(char(4), TheYear),
Style101 = CONVERT(char(10), TheDate, 101),
Style103 = CONVERT(char(10), TheDate, 103),
Style112 = CONVERT(char(8), TheDate, 112),
Style120 = CONVERT(char(10), TheDate, 120)
FROM src
)
SELECT * FROM dim
ORDER BY TheDate
OPTION (MAXRECURSION 0);
Ini menambahkan informasi tambahan tentang tanggal tertentu, seperti periode pertama / periode terakhir tanggal tersebut, apakah itu tahun kabisat, beberapa format string populer, dan beberapa spesifikasi ISO 8601 tertentu (saya akan berbicara lebih banyak tentang yang ada di tip lain). Anda mungkin hanya menginginkan beberapa kolom ini, dan Anda mungkin menginginkan yang lain juga. Saat Anda puas dengan hasilnya, Anda dapat mengubah baris ini:
SELECT * FROM dim
Menjadi sebagai berikut :
SELECT * INTO dbo.DateDimension FROM dim
Kemudian Anda dapat menambahkan kunci primer yang bertumpuk dan menjadi 1 (dan indeks lain yang ingin Anda gunakan):
REATE UNIQUE CLUSTERED INDEX PK_DateDimension ON dbo.DateDimension(TheDate);
Untuk memberikan gambaran tentang berapa banyak ruang yang dibutuhkan tabel ini, bahkan dengan semua kolom yang mungkin tidak Anda perlukan, maksimumnya adalah sekitar 2MB dengan indeks berkerumun reguler yang ditentukan pada kolom TheDate, hingga 500KB untuk satu indeks columnstore berkerumun dikompresi dengan COLUMNSTORE_ARCHIVE (belum tentu sesuatu yang harus Anda lakukan, tergantung pada beban kerja yang akan bekerja terhadap tabel ini, tetapi karena hanya dapat dibaca secara efektif, overhead DML tidak benar-benar menjadi pertimbangan):
Selanjutnya, kita perlu membahas tentang hari libur, salah satu musim utama yang Anda perlukan untuk menggunakan tabel kalender alih-alih mengandalkan fungsi tanggal/waktu bawaan. Dalam versi asli tip ini, saya menambahkan kolom IsHoliday, tetapi seperti yang ditunjukkan dengan benar oleh komentar, set ini mungkin paling baik disimpan di tabel terpisah:
CREATE TABLE dbo.HolidayDimension ( TheDate date NOT NULL, HolidayText nvarchar(255) NOT NULL, CONSTRAINT FK_DateDimension FOREIGN KEY(TheDate) REFERENCES dbo.DateDimension(TheDate) ); CREATE CLUSTERED INDEX CIX_HolidayDimension ON dbo.HolidayDimension(TheDate); GO
Ini memungkinkan Anda untuk memiliki lebih dari satu hari libur untuk tanggal tertentu, dan bahkan memungkinkan untuk beberapa kalender lengkap masing-masing dengan rangkaian hari liburnya sendiri (bayangkan kolom tambahan yang menentukan ID Kalender).
Mengisi tabel dimensi hari libur bisa rumit. Karena saya berada di Amerika Serikat, saya akan berurusan dengan hari libur resmi di sini; tentu saja, jika Anda tinggal di negara lain, Anda harus menggunakan logika yang berbeda. Anda juga harus menambahkan hari libur perusahaan Anda sendiri secara manual, tetapi mudah-mudahan jika Anda memiliki hal-hal yang bersifat deterministik, seperti hari libur bank, Boxing Day, atau Senin ketiga bulan Juli adalah turnamen gulat tahunan di luar lokasi, Anda harus menambahkannya dapat melakukan sebagian besar dari itu tanpa banyak pekerjaan dengan mengikuti pola yang sama yang saya gunakan di bawah ini. Anda mungkin juga harus menambahkan beberapa logika jika perusahaan Anda mengamati liburan akhir pekan pada hari kerja sebelumnya atau berikutnya, yang menjadi lebih kompleks jika hal itu kebetulan bertabrakan dengan hari non-bisnis khusus perusahaan atau industri lainnya. Kami dapat menambahkan sebagian besar hari libur tradisional dengan satu tiket masuk dan kriteria yang agak sederhana:
;WITH x AS
(
SELECT
TheDate,
TheFirstOfYear,
TheDayOfWeekInMonth,
TheMonth,
TheDayName,
TheDay,
TheLastDayOfWeekInMonth = ROW_NUMBER() OVER
(
PARTITION BY TheFirstOfMonth, TheDayOfWeek
ORDER BY TheDate DESC
)
FROM dbo.DateDimension
),
s AS
(
SELECT TheDate, HolidayText = CASE
WHEN (TheDate = TheFirstOfYear)
THEN 'New Year''s Day'
WHEN (TheDayOfWeekInMonth = 3 AND TheMonth = 1 AND TheDayName = 'Monday')
THEN 'Martin Luther King Day' -- (3rd Monday in January)
WHEN (TheDayOfWeekInMonth = 3 AND TheMonth = 2 AND TheDayName = 'Monday')
THEN 'President''s Day' -- (3rd Monday in February)
WHEN (TheLastDayOfWeekInMonth = 1 AND TheMonth = 5 AND TheDayName = 'Monday')
THEN 'Memorial Day' -- (last Monday in May)
WHEN (TheMonth = 7 AND TheDay = 4)
THEN 'Independence Day' -- (July 4th)
WHEN (TheDayOfWeekInMonth = 1 AND TheMonth = 9 AND TheDayName = 'Monday')
THEN 'Labour Day' -- (first Monday in September)
WHEN (TheDayOfWeekInMonth = 2 AND TheMonth = 10 AND TheDayName = 'Monday')
THEN 'Columbus Day' -- Columbus Day (second Monday in October)
WHEN (TheMonth = 11 AND TheDay = 11)
THEN 'Veterans'' Day' -- (November 11th)
WHEN (TheDayOfWeekInMonth = 4 AND TheMonth = 11 AND TheDayName = 'Thursday')
THEN 'Thanksgiving Day' -- (Thanksgiving Day ()fourth Thursday in November)
WHEN (TheMonth = 12 AND TheDay = 25)
THEN 'Christmas Day'
END
FROM x
WHERE
(TheDate = TheFirstOfYear)
OR (TheDayOfWeekInMonth = 3 AND TheMonth = 1 AND TheDayName = 'Monday')
OR (TheDayOfWeekInMonth = 3 AND TheMonth = 2 AND TheDayName = 'Monday')
OR (TheLastDayOfWeekInMonth = 1 AND TheMonth = 5 AND TheDayName = 'Monday')
OR (TheMonth = 7 AND TheDay = 4)
OR (TheDayOfWeekInMonth = 1 AND TheMonth = 9 AND TheDayName = 'Monday')
OR (TheDayOfWeekInMonth = 2 AND TheMonth = 10 AND TheDayName = 'Monday')
OR (TheMonth = 11 AND TheDay = 11)
OR (TheDayOfWeekInMonth = 4 AND TheMonth = 11 AND TheDayName = 'Thursday')
OR (TheMonth = 12 AND TheDay = 25)
)
INSERT dbo.HolidayDimension(TheDate, HolidayText)
SELECT TheDate, HolidayText FROM s
UNION ALL
SELECT DATEADD(DAY, 1, TheDate), 'Black Friday'
FROM s WHERE HolidayText = 'Thanksgiving Day'
ORDER BY TheDate;
Black Friday sedikit lebih rumit, karena ini adalah hari Jumat setelah Kamis keempat di bulan November. Biasanya itu menjadikannya hari Jumat keempat, tetapi beberapa kali dalam satu abad itu sebenarnya adalah hari Jumat kelima, jadi UNION ALL di atas hanya mengambil hari setelah setiap Hari Thanksgiving.
Dan kemudian ada Paskah. Ini selalu menjadi masalah yang rumit; aturan untuk menghitung tanggal pastinya sangat berbelit-belit, saya menduga kebanyakan orang hanya dapat menandai tanggal tersebut di mana mereka memiliki kalender fisik yang dapat mereka lihat untuk mengonfirmasi. Jika perusahaan Anda tidak mengenali Paskah, Anda dapat melewatinya; jika ya, Anda dapat menggunakan fungsi berikut, yang akan mengembalikan tanggal liburan Paskah untuk tahun tertentu:
CREATE FUNCTION dbo.GetEasterHolidays(@TheYear INT)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
WITH x AS
(
SELECT TheDate = DATEFROMPARTS(@TheYear, [Month], [Day])
FROM (SELECT [Month], [Day] = DaysToSunday + 28 - (31 * ([Month] / 4))
FROM (SELECT [Month] = 3 + (DaysToSunday + 40) / 44, DaysToSunday
FROM (SELECT DaysToSunday = paschal - ((@TheYear + (@TheYear / 4) + paschal - 13) % 7)
FROM (SELECT paschal = epact - (epact / 28)
FROM (SELECT epact = (24 + 19 * (@TheYear % 19)) % 30)
AS epact) AS paschal) AS dts) AS m) AS d
)
SELECT TheDate, HolidayText = 'Easter Sunday' FROM x
UNION ALL SELECT DATEADD(DAY, -2, TheDate), 'Good Friday' FROM x
UNION ALL SELECT DATEADD(DAY, 1, TheDate), 'Easter Monday' FROM x
);
GO
(Anda dapat menyesuaikan fungsinya dengan mudah, tergantung pada apakah mereka hanya mengenali Minggu Paskah atau juga Jumat Agung dan/atau Senin Paskah. Ada juga tip lain di sini yang akan menunjukkan kepada Anda cara menentukan tanggal Mardi Gras, mengingat tanggal Paskah .)
Sekarang, untuk menggunakan fungsi itu untuk menambahkan liburan Paskah ke tabel HolidayDimension:
INSERT dbo.HolidayDimension(TheDate, HolidayText)
SELECT d.TheDate, h.HolidayText
FROM dbo.DateDimension AS d
CROSS APPLY dbo.GetEasterHolidays(d.TheYear) AS h
WHERE d.TheDate = h.TheDate;
Terakhir, Anda dapat membuat tampilan yang menjembatani kedua tabel ini (atau beberapa tampilan):
CREATE VIEW dbo.TheCalendar
AS
SELECT
d.TheDate,
d.TheDay,
d.TheDaySuffix,
d.TheDayName,
d.TheDayOfWeek,
d.TheDayOfWeekInMonth,
d.TheDayOfYear,
d.IsWeekend,
d.TheWeek,
d.TheISOweek,
d.TheFirstOfWeek,
d.TheLastOfWeek,
d.TheWeekOfMonth,
d.TheMonth,
d.TheMonthName,
d.TheFirstOfMonth,
d.TheLastOfMonth,
d.TheFirstOfNextMonth,
d.TheLastOfNextMonth,
d.TheQuarter,
d.TheFirstOfQuarter,
d.TheLastOfQuarter,
d.TheYear,
d.TheISOYear,
d.TheFirstOfYear,
d.TheLastOfYear,
d.IsLeapYear,
d.Has53Weeks,
d.Has53ISOWeeks,
d.MMYYYY,
d.Style101,
d.Style103,
d.Style112,
d.Style120,
IsHoliday = CASE WHEN h.TheDate IS NOT NULL THEN 1 ELSE 0 END,
h.HolidayText
FROM dbo.DateDimension AS d
LEFT OUTER JOIN dbo.HolidayDimension AS h
ON d.TheDate = h.TheDate;
And now you have a functional calendar view you can use for all of your reporting or business needs.
Kesimpulan
Membuat tabel dimensi atau kalender untuk tanggal bisnis dan periode fiskal mungkin tampak menakutkan pada awalnya, tetapi begitu Anda memiliki metodologi yang solid, itu bisa sangat bermanfaat. Ada banyak cara untuk melakukannya; beberapa akan menganut gagasan bahwa banyak dari fakta terkait tanggal ini dapat diturunkan pada waktu kueri, atau setidaknya menjadi kolom yang dihitung tanpa bertahan. Anda harus memutuskan apakah nilai dihitung cukup sering untuk membenarkan ruang tambahan pada disk dan di kumpulan buffer.
Untuk lebih membantu kinerja, Anda dapat menempatkan tabel kalender ke dalam grup filenya sendiri (atau basis datanya sendiri), dan menandainya sebagai hanya-baca setelah populasi awal. Ini tidak akan memaksa tabel untuk tetap berada di memori sepanjang waktu (ingat DBCC PINTABLE?), tetapi itu akan terjadi secara alami jika tabel tersebut cukup ditanyakan. Apa yang berpotensi membantu adalah mengurangi jenis pertengkaran lainnya.
Syafix Said
This Post Has 0 Comments