[SQL] Tạo bảng lịch ảo bằng truy vấn (phần 1)
SQL
15
Male avatar

Hawkie viết ngày 23/05/2015

Lưu ý:

  • Người đọc cần có kiến thức cơ bản về CSDL quan hệ và ngôn ngữ truy vấn SQL.
  • Các truy vấn ví dụ sử dụng trong bài được viết bằng cú pháp T-SQL của SQL Server.
  • Câu truy vấn tốt hơn được cung cấp tại phần 2.

Vấn đề

Khi phát triển hệ thống nghiệp vụ cho công ty, tôi hay được yêu cầu lập các bản báo cáo dựa trên trục ngày tháng, ví dụ như báo cáo số đơn hàng mỗi ngày trong tháng. Trong một bài viết trước, tôi đã giới thiệu giải pháp để tạo một danh sách ngày tháng dưới dạng bản ghi để giải quyết vấn đề đó.

Tuy nhiên nhiều khi người dùng yêu cầu thêm chức năng như hiển thị xem mỗi ngày trên báo cáo có phải ngày nghỉ hay ko, và nếu ko thì là ngày làm việc thứ mấy trong tháng. Nếu chỉ là nghỉ thứ Bảy Chủ Nhật thì quá đơn giản, nhưng người dùng muốn hiển thị cả các ngày nghỉ lễ Tết, thậm chí là cả các ngày nghỉ do công ty tự quy định, và muốn có thể thiết lập thêm ngày nghỉ một cách dễ dàng. Nói tóm lại tôi cần phải làm một bộ lịch dưới dạng bản ghi dành riêng cho công ty.

Giải pháp sơ cấp

Cũng như vấn đề ở bài viết trước, giải pháp đơn giản nhất là lưu các dữ liệu cần thiết thành bảng trong CSDL. Nếu tôi đã có sẵn một bảng tất cả các ngày tháng, thì tôi sẽ thêm một cột bit/boolean chứa thông tin ngày đó có phải ngày nghỉ hay ko.

Ngày Nghỉ
2015/1/1 1
2015/1/2 0
... ...
2015/4/24 0
2015/4/25 1
2015/4/26 1
... ...

Khi người dùng muốn thêm bớt ngày nghỉ tôi sẽ để họ sửa cột đó cho từng ngày nghỉ mà họ muốn thiết lập. Đối với những ngày nghỉ định kỳ như thứ Bảy Chủ Nhật và lễ Tết, tôi có thể chạy một vài câu truy vấn UPDATE để thiết lập sẵn thông tin cho tất cả các ngày nghỉ có trong bảng.

UPDATE [Bảng ngày] SET [Nghỉ] = 1
WHERE DATEPART(weekday, [Ngày]) IN (1, 7) OR MONTH([Ngày]) = 1 AND DAY([Ngày]) = 1)

Giải pháp này đơn giản và cũng khá hiệu quả, cho phép tôi có thể chuyển giao việc bảo trì lịch nghỉ cho người dùng ko biết gì về CNTT. Tuy nhiên nó có một số vấn đề:

  • Độ tin cậy thấp: Khỏi phải giải thích.
  • Ko tận dụng được bảng ảo: Do giải pháp này gắn liền với việc lưu trữ một bảng thực chứa tất cả các ngày tháng, nên nó ko thể tận dụng được các giải pháp tạo bảng ngày tháng ảo dựa trên bảng số nguyên (ảo nốt) như ở bài viết trước.
  • Hiệu suất thấp: Hiệu suất ở đây là hiệu suất sử dụng dữ liệu (lượng thông tin trên lượng dữ liệu). Điều này là do có rất nhiều bản ghi chứa ngày làm việc trộn lẫn giữa các bản ghi chứa ngày nghỉ, trong khi thông tin tôi cần là ngày nào là ngày nghỉ.

Giải pháp trung cấp

Xuất phát từ mục đích ban đầu là tôi chỉ muốn biết một ngày nào đó có phải ngày nghỉ hay ko, tôi cũng có thể tạo một bảng riêng chỉ chứa các ngày nghỉ. Khi người dùng muốn thêm bớt ngày nghỉ tôi sẽ để họ thêm một bản ghi mới vào bảng hoặc xóa một bản ghi cũ đi.

Ngày nghỉ
2015/1/1
...
2015/4/25
2015/4/26
...

Các ngày nghỉ định kỳ có thể được tạo sẵn bởi một vài câu truy vấn INSERT dựa trên dữ liệu gốc từ bảng ngày tháng hay bảng số nguyên có sẵn (tham khảo bài viết trước).

INSERT INTO [Bảng ngày nghỉ] ([Ngày], [Nghỉ]) VALUES (DATEADD(week, [Số] - 5000, '2015-04-25'), 1)
FROM [Bảng số] WHERE [Số] BETWEEN 0 AND 10000

INSERT INTO [Bảng ngày nghỉ] ([Ngày], [Nghỉ]) VALUES (DATEADD(year, [Số] - 100, '2015-01-01'), 1)
FROM [Bảng số] WHERE [Số] BETWEEN 0 AND 200

Câu truy vấn đầu tiên thêm vào bảng 10.001 ngày thứ Bảy từ 5000 tuần trước đến 5000 tuần sau ngày thứ Bảy chuẩn là 2015/4/25. Câu truy vấn tiếp theo thêm 201 ngày 1/1 của các năm từ 100 năm trước đến 100 năm sau năm chuẩn là 2015.

Khi đã có bảng ngày nghỉ, tôi có thể kết hợp nó với bảng ngày tháng để tạo ra bảng lịch nghỉ ảo giống như bảng thực ở giải pháp sơ cấp nói trên.

SELECT [Ngày], IIF([Ngày nghỉ] IS NULL, 0, 1) AS [Nghỉ] 
FROM [Bảng ngày] LEFT OUTER JOIN [Bảng ngày nghỉ] ON [Ngày] = [Ngày nghỉ]

Câu truy vấn trên liệt kê lại tất cả các ngày trong bảng ngày tháng sẵn có, và thiết lập đó là ngày nghỉ nếu tồn tại bản ghi chứa ngày đó trong bảng ngày nghỉ. Việc còn lại chỉ là lưu nó thành một khung nhìn để có thể hiển thị ra cho người dùng.

Giải pháp này đã khá tốt, và nếu kết hợp với bảng ngày tháng và số nguyên ảo thì có thể nói là nó đã đủ "số má" để áp dụng vào hệ thống thực tiễn. Tuy nhiên khi đi vào thực tế nó vẫn có một số vấn đề khá rõ ràng như sau:

  • Hiệu suất vẫn ko cao: Tôi vẫn sẽ phải lưu hàng nghìn bản ghi chứa các ngày nghỉ định kỳ như thứ Bảy Chủ Nhật hay 1/1. Tôi cũng có thể xử lý thứ Bảy Chủ Nhật riêng khi tạo báo cáo cụ thể và chỉ lưu các ngày nghỉ khác, nhưng như vậy quá phức tạp và làm nảy sinh nhiều vấn đề hơn là hiệu quả mang lại.
  • Khó bảo trì: Việc thay đổi các ngày nghỉ định kỳ, ví dụ như chuyển sang nghỉ thứ Tư Chủ Nhật thay vì thứ Bảy Chủ Nhật, đòi hỏi tôi phải thực hiện một loạt các câu truy vấn tạm bợ (ad-hoc) khá phức tạp để thêm bớt bản ghi. Điều này dãn đến rủi ro nhầm lẫn khá cao và hạn chế khả năng chuyển giao công việc bảo trì lịch nghỉ cho người dùng bình thường.

Giải pháp cao cấp (sang chảnh)

Để ý rằng khi người dùng muốn thiết lập ngày 1/1 làm ngày nghỉ, thông tin mà tôi có được và cần lưu trữ ko phải là "nghỉ ngày 2015/1/1, nghỉ ngày 2016/1/1, nghỉ ngày ..." mà là "nghỉ ngày 1/1 của tất cả các năm". Như vậy thay vì lưu thông tin dưới dạng bảng ngày nghỉ, tôi có thể lưu thông tin dưới dạng bảng quy luật nghỉ như sau:

Ngày chuẩn Kiểu lặp Giải thích
2015/4/25 w Thứ Bảy
2015/4/26 w Chủ Nhật
2015/1/1 y Năm mới
2015/1/31 m Tổng kết cuối tháng (quán anh Béo)
2015/3/21 n Cưới con sếp tổng

Trong đó, kiểu lặp n, w, m, y lần lượt tương ứng với các ngày nghỉ ko lặp lại, lặp mỗi tuần, lặp mỗi tháng và lặp mỗi năm dựa trên một ngày được lấy làm chuẩn. Bằng cách này tôi có thể hạn chế lượng dữ liệu lưu trong CSDL đến rất gần với lượng thông tin thực tế cần lưu. Nhờ đó, tôi ko cần phải tạo sẵn dữ liệu ngày nghỉ cho vài trăm năm, hay thêm bớt hàng nghìn bản ghi khi cần chuyển ngày nghỉ từ thứ Bảy sang thứ Tư.

Vấn đề là tôi sẽ chuyển đổi quy luật nghỉ trên ra thành bảng (khung nhìn) lịch nghỉ như thế nào?

SELECT [Ngày], MAX(CASE [Kiểu lặp] 
WHEN 'n' THEN IIF([Ngày] = [Ngày chuẩn], 1, 0)
WHEN 'w' THEN IIF(DATEPART(weekday, [Ngày]) = DATEPART(weekday, [Ngày chuẩn]), 1, 0)
WHEN 'm' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]), 1, 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]) AND MONTH([Ngày]) = MONTH([Ngày chuẩn]), 1, 0)
ELSE 0 END) AS [Nghỉ]
FROM [Bảng ngày], [Bảng quy luật] GROUP BY [Ngày]

Các bước để đi đến câu truy vấn trên như sau:

Trước hết nếu đây ko phải là SQL mà là một ngôn ngữ lập trình khác thì tôi sẽ dùng hai vòng for lồng vào nhau (nested), vòng ngoài chạy tất cả các ngày, và vòng trọng chạy tất cả các quy luật để đánh giá từng quy luật đối với ngày đang xét, rồi sau đó đi đến kết luận ngày đang xét có phải ngày nghỉ hay ko. Tóm lại là tôi làm một phép nhân giữa dãy ngày và dãy quy luật. Quay trở lại SQL, phép nhân đó tương ứng với phép nhân Đề-các thuần túy bảng ngày và bảng quy luật, còn gọi là CROSS JOIN. (tham khảo)

SELECT ... FROM [Bảng ngày], [Bảng quy luật]

Tiếp theo, trong vòng for thứ hai ở trên tôi sẽ phải xét từng trường hợp cụ thể của kiểu lặp để tính ra một giá trị boolean thể hiện quy luật đang xét có áp dụng được với ngày đang xét hay ko, tức là tôi sẽ phải làm một câu lệnh case hay switch. Điều này trong SQL tương ứng với cú pháp CASE WHEN. (tham khảo)

CASE [Kiểu lặp] WHEN 'n' THEN ... WHEN 'w' THEN ... WHEN 'm' THEN ... WHEN 'y' THEN ... ELSE ... END

Đến phần đánh giá cụ thể cho từng kiểu lặp, nếu tôi hiểu đơn giản ngày nghỉ lặp theo tuần là các ngày có cùng số thứ tự trong tuần với ngày chuẩn, ngày nghỉ lặp theo tháng là các ngày có cùng số ngày với ngày chuẩn, và ngày nghỉ lặp theo năm là các ngày có cùng số ngày và số tháng với ngày chuẩn, thì tôi có thể dùng các hàm DATEPART, DAY, MONTH để đánh giá. (tham khảo

CASE [Kiểu lặp] 
WHEN 'n' THEN IIF([Ngày] = [Ngày chuẩn] , 1, 0)
WHEN 'w' THEN IIF(DATEPART(weekday, [Ngày]) = DATEPART(weekday, [Ngày chuẩn]), 1, 0)
WHEN 'm' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]), 1, 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = DAY([Ngày chuẩn]) AND MONTH([Ngày]) = MONTH([Ngày chuẩn]), 1, 0)
ELSE 0 END

Cuối cùng, tôi cần tổng hợp kết quả đánh giá của tất cả các quy luật đối với mỗi ngày để đưa ra kết luận ngày đó có phải ngày nghỉ hay ko. Với yêu cầu là chỉ cần có một quy luật áp dụng được thì ngày đó là ngày nghỉ, tôi sẽ phải sử dụng một hàm tổng hợp (aggregate) nào đó và GROUP theo ngày. Nếu là ngôn ngữ lập trình khác thì tôi sẽ dùng or cho kiểu boolean, nhưng đối với SQL thì tôi phải dùng các hàm MIN MAX.

SELECT [Ngày], MAX(CASE ... END) AS [Nghỉ] FROM [Bảng ngày], [Bảng quy luật] GROUP BY [Ngày]

Tổng hợp các bước lại tôi có được câu truy vấn hoàn chỉnh như đã trình bày ở trên.

Giải pháp này có rất nhiều ưu điểm so với các giải pháp lưu thông tin dưới dạng từng ngày nghỉ.

  • Độ tin cậy cao: Các ngày nghỉ định kỳ, chiếm trên 90% tổng số ngày nghỉ trong năm, đều được tính toán tự động dựa trên một thuật toán thống nhất nên hạn chế được tối đa lỗi con người. Các quy luật được thể hiện rõ ràng tách khỏi các ngày nghỉ đơn lẻ, với số bản ghi có thể kiểm soát được bằng mắt người. Tôi sẽ ko phải phân vân vì sao ngày 2015/12/12 lại là ngày nghỉ, hay tại sao bỗng dưng 2016/1/1 lại trở thành ngày đi làm.
  • Dễ bảo trì: Dù là thêm một ngày nghỉ, thêm một quy luật nghỉ, hay thay đổi một quy định nghỉ có sẵn, tôi đều chỉ cần thêm hoặc sửa đúng một bản ghi. Điều này cho phép một người dùng mù CNTT cũng có thể bảo trì lịch nhanh chóng và dễ dàng với một chút hướng dẫn hoặc một công cụ đơn giản.
  • Dễ mở rộng: Chỉ cần sửa chữa một chút bảng quy luật và câu truy vấn, tôi có thể thêm chức năng thiết lập ngày nghỉ theo quý, theo 5 năm, hay thậm chí là ngày thứ x của tuần thứ y trong tháng.

Kết

Trên đây là một số giải pháp để tạo ra một bảng lịch các ngày nghỉ có thể ứng dụng cho doanh nghiệp lẫn cá nhân, trong đó chú trọng vào việc hạn chế tối đa sự phụ thuộc vào dữ liệu thực cũng như lỗi con người, đồng thời tạo nền tảng cho việc mở rộng chức năng về sau.

Hai giải pháp sơ cấp và trung cấp do tôi cóp nhặt từ người đi trước kết hợp với kinh nghiệm cá nhân. Đối với giải pháp cao cấp, tôi lấy ý tưởng về bảng quy luật từ gói phần mềm quản lý tài nguyên cho doanh nghiệp (ERP) Dynamics NAV của Microsoft. Tất nhiên giải pháp cuối cùng mà tôi đưa ra chưa phải là hoàn hảo và vẫn còn nhiều điểm có thể sửa chữa, nâng cấp mà tôi sẽ đề cập đến trong phần 2.

Hy vọng bài viết sẽ có ích cho mọi người và chúc mọi người thành công.

Bình luận


White
{{ comment.user.name }}
Bỏ hay Hay
{{comment.like_count}}
Male avatar
{{ comment_error }}
Hủy
   

Hiển thị thử

Chỉnh sửa

Male avatar

Hawkie

3 bài viết.
3 người follow
Kipalog
{{userFollowed ? 'Following' : 'Follow'}}
Cùng một tác giả
Male avatar
3 0
Lưu ý: Người đọc cần có kiến thức cơ bản về CSDL quan hệ và ngôn ngữ truy vấn SQL. Các truy vấn ví dụ sử dụng trong bài được viết bằng cú pháp T...
Hawkie viết hơn 3 năm trước
3 0
Male avatar
3 3
Lưu ý: Người đọc cần có kiến thức cơ bản về CSDL quan hệ và ngôn ngữ truy vấn SQL. Các truy vấn ví dụ sử dụng trong bài được viết bằng cú pháp TS...
Hawkie viết hơn 3 năm trước
3 3
Bài viết liên quan
White
23 0
(Ảnh) Slow query là gì? Khi các câu query chậm hơn một thời gian nhất định tùy theo bạn định nghĩa, ví dụ chậm hơn 50ms, thì các câu query đó đư...
Triet Pham viết 1 năm trước
23 0
White
23 8
( Trong bài viết này mình chỉ nêu quan điểm và những thứ mình đã từng làm với việc phân quyền trên thực tế, chỉ mang tính tham khảo cho mọi người. ...
Phạm Anh Tuấn viết 3 tháng trước
23 8
White
24 4
Tạo dummy data với Faker và Mockaroo – Xa rồi những ngày nhập tay nhàm chán Cuộc đời một thằng developer có rất nhiều việc rất chán nhưng phải làm...
Huy Hoàng Phạm viết gần 3 năm trước
24 4
{{like_count}}

kipalog

{{ comment_count }}

bình luận

{{liked ? "Đã kipalog" : "Kipalog"}}


Male avatar
{{userFollowed ? 'Following' : 'Follow'}}
3 bài viết.
3 người follow

 Đầu mục bài viết

Vẫn còn nữa! x

Kipalog vẫn còn rất nhiều bài viết hay và chủ đề thú vị chờ bạn khám phá!