Những ngày gần đây tôi cảm thấy đầu óc và suy nghĩ của bản thân không còn được minh mẫn và bình thường như trước đây. Tôi cảm thấy đau đầu và như bị ức chế thần kinh vậy, mọi thứ xung quanh tôi khiến tôi cảm thấy ồn ào và khó chịu vô cùng. Chỉ một tiếng click chuột của thằng bạn cùng phòng cũng làm tôi cảm thấy giật mình, gần như tôi không kiểm soát được đầu óc của mình nữa. Tôi nằm xuống và suy nghĩ, suy nghĩ về mọi thứ quanh tôi. Những hoạt động quanh tôi, con người, sự vật, sự kiện, tất cả những gì tôi trải qua từ trước tới này: Về quá khứ, hiện tại và tương lai. Tôi thấy bất lực với việc điều khiển suy nghĩ, tôi cố gắng nhắm mắt lại và cố gắng chỉ suy nghĩ về 1 điều: Tại sao mình lại thế này, tại sao?? Tôi bật điện thoại và tìm hiểu về nguyên nhân dẫn đến bệnh thần kinh, trước đây tôi chưa từng làm việc này. Tôi đang cảm thấy sợ, sợ mình mất đi khả năng điều khiển hành vi. Tôi tưởng tượng ra 1 ngày nào đó tôi gặp phải hoàn cảnh này, chắc chắn gia đình tôi sẽ rất khổ và tôi sẽ trở thành 1 gánh nặng nếu tôi không thể điều khiển được hành vi, cảm xúc của mình như hiện tại.
Tôi tự nói với bản thân rằng: Chắc chắn mình sẽ không bao giờ gặp phải chuyện này, nhưng vài phút sau tôi lại có suy nghĩ khác. Mình sẽ không thể biết được khi nào mình đang bị bệnh thần kinh hoặc liên quan đến việc điều khiển hành vi cảm xúc. Tôi dần cảm thấy sợ và cố gắng ngủ.
Như mọi ngày, đa phần thời gian của tôi là ngồi máy tính, đọc sách, lướt web và ngủ. Tôi không tham gia bất kỳ trò chơi, hoạt động mang tính giải trí nào hết. Tôi sợ những gì xảy ra với tôi ngày hôm nay lại tiếp tục lặp lại. Tôi bật facebook lên và gửi tin nhắn cho một người bạn của tôi và hẹn một buổi đi chơi, tôi nghĩ điều này có thể khiến tôi cảm thấy không còn căng thẳng nữa.
Người bạn đó của tôi đồng ý đi chơi vào tối hôm sau, đến giờ hẹn tôi gọi cho cô bạn đó nhưng không có ai bắt máy.Tôi không hiểu lý do, tôi tiếp tục gọi thêm 4 cuộc nhưng cũng không có ai bắt máy, tôi cảm thấy bứt dứt khó hiểu. Sau đó tôi lấy xe và đi lượn quanh khu tôi ở 1 vài vòng, tôi đi và suy nghĩ tại sao lại không nghe máy nhỉ? Tôi đãng trí và không tập trung lái xe, chỉ chút nữa là tôi gây ra tai nạn rồi. Sau đó tôi đi về phòng và nhắn tin facebook cho cô ấy, khoảng 30 phút sau thì cô ấy đọc tin nhắn tôi gửi. Tuy nhiên tôi không nhận được bất kỳ câu trả lời nào.
Chuyện này khiến tôi khó hiểu, và tôi lại cảm thấy bất an trong người, mọi thứ quanh tôi thật khó mà hiểu được.
Tôi sợ một ngày nào đó mình không còn nhận thức được bản thân nữa nên tôi bắt đầu tìm hiểu và tự nghiên cứu về hành vi của tôi thường ngày và ghi chép chúng lại trên blog này.
Tôi nhận ra rằng, mình phải hoàn thành mọi công việc của mình trước khi bệnh thần kinh ập đến với bản thân. Trước khi đầu óc của tôi không còn được bình thường thì tôi sẽ đưa thông tin về blog này cho người trong gia đình tôi biết. Những hành vi sau này của tôi nếu không được kiểm soát thì đó hoàn toàn là do bản thân tôi và không ai có ảnh hưởng khiến tôi trở nên như vậy cả, vì tôi cảm thấy chính bản thân tôi hiện tại đang còn ý thức được việc mình có thể bị rối loạn thần kinh trong tương lai. Ngay cả bản thân tôi còn không kiểm soát được nó thì không ai có thể khiến tôi khác đi được.
5/09/2015
5/05/2015
Lucene - A Full-Featured Text Search Engine
1. Building a Search Engine
Lê Đình Duy
PS: Bài mới hơn về Lucene có thể xem tại đây: Lucene -Thư viện mã nguồn mở hỗ trợ phát triển máy tìm kiếm
Nếu bạn có ý định xây dựng một search engine, vấn đề đặt ra đầu tiên là: Xây dựng một search engine phức tạp như thế nào? Bài viết " Why Writing Your Own Search Engine Is Hard", có thể giúp bạn hình dung phần nào. Nói một cách tóm tắt:
i. Bạn phải cần có một crawler, một con robot chuyên đi thu thập các trang web/tài liệu.
ii. Bạn phải cần có một chương trình đánh chỉ mục. Việc đánh chỉ mục sẽ giúp cho việc tìm kiếm các tài liệu liên quan đến một hay nhiều từ khóa cho trước sau này. Một ví dụ điển hình là inverted list, trong đó với mỗi từ khóa, lưu danh sách các tài liệu liên quan đến nó.
iii. Bạn cần phải có thuật toán xếp hạng kết quả trả về. Với việc có rất nhiều tài liệu liên quan đến các từ khóa mà người dùng nhập vào, vấn đề là làm thế nào để trả về kết quả gần với mong đợi của người dùng nhất. Một ví dụ điển hình là Page rank, thuật toán gắn với sự thống trị của Google trong lĩnh vực tìm kiếm hiện nay.
iv. Bạn cần phải có tài nguyên. Đây là chuyện những người làm academic có vẻ ít quan tâm nhất nhưng với các ứng dụng thực tế, đây là một trong những vấn đề mấu chốt. Bạn lưu trữ 100,000 trang web thì có thể sẽ chẳng có vấn đề gì với một máy PC, nhưng nếu bạn muốn lưu trữ hàng chục tỉ trang web (Google lưu khoảng 25 billions, số liệu năm 2006), thì đó lại là chuyện khác. Lúc này các vấn đề như phân tán dữ liệu thế nào, xử lí việc nhất quán dữ liệu thế nào (ví dụ lưu dữ liệu nhiều máy, nhưng một trong số đó bị hư), việc phân bổ các câu query thế nào, etc sẽ không đơn giản. Ngoài ra, băng thông cũng là vấn đề quan trọng không kém. Nếu muốn nhiều người có thể sử dụng dịch vụ của mình thì server phải mạnh, băng thông phải đủ lớn để có thể cho phép nhiều người cùng tải dữ liệu về một cách nhanh chóng, etc.
Như vậy, có thể thấy rằng, việc xây dựng một search engine from scratch là chuyện cực kì phức tạp. Đó cũng chính là lí do tại sao chúng ta nên tìm những open sources liên quan đến ứng dụng này. Nổi bật nhất trong số này là các ứng dụng/phần mềm dựa trên thư việnLucene.
2. Lucene
Lucene là một thư viện mã nguồn mở, được phát triển bởi Dough Cutting (hiện đang làm việc cho Yahoo). Thư viện này cung cấp các hàm cơ bản hỗ trợ cho việc đánh chỉ mục và tìm kiếm. Từ thư viện Lucene này, có nhiều kịch bản sử dụng sau:
i. Dùng Lucene tích hợp vào ứng dụng hiện có. Ví dụ tôi đang muốn phát triển một semanticvideo search engine, trong đó tôi có dữ liệu văn bản là các transcript và tôi muốn có một công cụ hỗ trợ cho việc tìm kiếm dựa trên văn bản. Bằng cách này, người dùng có thể gõ vào từ khóa President Bush để tìm các video transcript có nói về President Bush. Sử dụng các hàm trong thư viện Lucene liên quan đến việc đánh chỉ mục và tìm kiếm, tôi có thể thực hiện thao tác này khá dễ dàng.
ii. Xây dựng một search engine cho riêng bạn. Lúc này bạn cần phải có một web crawler chuyên đi thu thập các trang web trên Internet, để đem về đánh chỉ mục và cho phép tìm kiếm. Ứng dụng kiểu này có thể thấy tương tự ở trang www.baomoi.com. Trong ứng dụng này, web crawler sẽ được dùng để đi thu thập các tin từ các website (chủ yếu là tin tức, ví dụ vnexpress, tuoitre.com.vn, nld.com.vn, etc), sau đó tiến hành phân loại, lập chỉ mục để hỗ trợ tìm kiếm. Trong trường hợp cần web crawler, Nutch là một phần mềm mã nguồn mở, cũng do chính tác giả của Lucene là Dough Cutting phát triển, có thể giúp bạn việc này. Để có thể tiến hành thu thập và lưu trữ hàng triệu trang web một cách có hiệu quả ở nhiều máy khác nhau, Hadoop sử dụng công nghệ của GoogleFS có thể được tích hợp cùng. Bên cạnh đó Solr, một phần mềm mã nguồn mở dùng cho xây dựng các search server, cung cấp giao diện bằng web với người sử dụng cũng được xây dựng dựa trên thư viện Lucene.
Lucene ban đầu được viết hoàn toàn bằng Java. Sau đó được port qua các ngôn ngữ khác ví dụ như C, C++ ( CLucene), .NET (Lucene.NET ), Perl (Plucene), Ruby ( Ferret) và đặc biệt là PHP (Zend Framework ).
3. Lucene and MySQL
Nếu bạn đã từng làm về các ứng dụng của hệ thống thông tin, trong đó có sử dụng chức năng tìm kiếm của các hệ quản trị cơ sở dữ liệu, ví dụ như MySQL. Câu hỏi có thể đặt ra là: Dùng chức năng tìm kiếm của Lucene và của hệ quản trị CSDL như MySQL có gì khác nhau? Có thể chỉ ra một số ý như sau:
i. Khác với MySQL, dữ liệu được index phải được lưu trữ trong database, trong khi đó Lucene chỉ tạo chỉ mục trên dữ liệu hiện có. Bằng cách này, Lucene có thể tạo chỉ mục cho dữ liệu lưu trữ trong database, trong các thư mục của hệ thống tập tin. Hơn thế nữa, với việc dùng các plug-in về parsing, Lucene có thể đánh chỉ mục cho các tập tin pdf, html, MS Word, etc.
ii. Câu truy vấn của MySQL bị giới hạn bởi cú pháp của SQL query, trong khi câu truy vấn của Lucene gần với các hệ thống information retrieval hơn. Với Lucene, bạn có thể dùng proximity search, fuzzy search, wildcard search và quan trọng nhất là term boosting có thể giúp rank các kết quả trả về theo mức độ liên quan (relevancy).
iii. Tốc độ của Lucene tốt hơn so với MySQL trong trường hợp dữ liệu lớn.
Nhân tiện cũng nói thêm, để có thể tăng khả năng tìm kiếm của các hệ quản trị CSDL,Sphinx là một ứng dụng như vậy.
4. A Case Study
Một trong những lí do tôi phải tìm hiểu về Lucene đó là tôi đang làm về Person X. Ứng dụng này có thể tóm tắt như sau: Giả sử tôi có một cơ sở dữ liệu các ảnh cùng captions của nó (ví dụ như trang này http://www.cs.berkeley.edu/
Bước đầu tiên là thu thập dữ liệu. Một chương trình như Teleport có thể giúp tải hết các trang của site ở trên ( http://www.cs.berkeley.edu/
Bước tiếp theo là dùng Lucene để tạo chỉ mục cho tập các files trên và tiến hành tìm kiếm. Để làm điều này, trước tiên là download Lucene, phiên bản cho Java về từ địa chỉ này. Tôi dùng Windows và ko cần build lại nên chọn download bản đã build sẵn của Lucene, ví dụ như http://ftp.kddilabs.jp/infosyst
Sau khi unzip, có thể dùng Eclipse để load chương trình Demo trong đó có hỗ trợ các thao tác tạo chỉ mục và tìm kiếm. Lưu ý rằng Eclipse ko thể dùng file build.xml để tạo project nên tôi phải tự tạo một project mới trong Eclipse, trong đó src trỏ về thư mục src/demo. Sau khi khai báo trong mục Java Build Path/Libraries để sử dụng thư viện Lucene build sẵn lucene-core-2.2.0.jar, tôi có thể chạy các ứng dụng tạo chỉ mục và tìm kiếm một cách dễ dàng.
Một lưu ý là một khi đã tạo được chỉ mục, chúng ta có thể sử dụng ứng dụng Luke để có thể thực hiện các câu truy vấn với giao diện khá trực quan.
5. Future Plan
Từ việc tìm hiểu về Lucene, tôi có ý định làm một ứng dụng kiểu như site www.baomoi.com . Bước đầu tiên là thu thập dữ liệu thông qua dùng web crawler. Với web crawler, chúng ta có thể customize Nutch. Một khi đã có được dữ liệu, các ứng dụng tương tự như baomoi.comcó thể được phát triển tiếp.
Lê Đình Duy
PS: Bài mới hơn về Lucene có thể xem tại đây: Lucene -Thư viện mã nguồn mở hỗ trợ phát triển máy tìm kiếm
Video Processing with OpenCV
OpenCV có hỗ trợ xử lí video. Có 2 dạng hỗ trợ, thứ nhất là video thu nhận từ webcam và thứ hai là video đọc từ tập tin video.
Tôi chưa bao giờ dùng OpenCV để đọc file video cho xử lí của mình nên không rành lắm. Tuần rồi, tôi với Phong dùng code STIP của Ivan Laptev để extract space time interest points mới đụng chuyện này. Code STIP của Laptev (chạy trên Windows) viết bằng OpenCV, do đó việc đọc dữ liệu video được giao cho OpenCV xử lí. Rắc rối ở đây là có những tập tin video đưa vào, chương trình nó chỉ báo một câu lỗi duy nhất rồi thoát ra luôn.
Để có thể hiểu rõ hơn về hỗ trợ của OpenCV trong xử lí các tập tin video, link này là quan trọng nhất: http://opencv.willowgarage.
Có thể nói tóm tắt như thế này: Với mỗi tập tin video, có 2 khái niệm cần được phân biệt, đó là container và codec. Ví dụ AVI là container, còn DivX là codec. Container liên quan đến định dạng của tập tin video, ví dụ qui định video, audio, subtitle lưu như thế nào để chương trình đọc nó có thể đọc lên và synchronize; trong khi đó codec liên quan đến thuật toán nén dữ liệu. Sự kết hợp của container và codec sẽ cho ra nhiều output khác nhau. Chính vì sự phức tạp như vậy nên nhiều khi cùng là một tập tin có phần mở rộng là avi, nhưng có máy lại đọc được có máy lại đọc không được. Lí do chính nằm ở chỗ, codec có thể khác nhau, và nếu máy không cài phần mềm hỗ trợ codec đó thì nó không đọc được. Sự nhầm lẫn còn là do đôi lúc người ta gộp 2 khái niệm lại làm một, ví dụ khi nói đến file .mp4 cho iPhone, thực ra là người ta đang nói đến container mp4, và codec là H.264/AVC.
Trên Windows, OpenCV chỉ hỗ trợ các tập tin video được lưu theo container dạng AVI (điều này cũng dễ hiểu vì AVI là định dạng do Microsoft đề xuất), còn codec là loại dữ liệu không nén. Dữ liệu không nén tất nhiên sẽ cho kích thước rất lớn, gấp cả trăm lần so với dữ liệu được nén (ví dụ dùng H.264/AVC). Cứ tưởng tượng phải xử lí 100G video dạng nén với OpenCV, thì sẽ thấy dữ liệu dạng không nén lớn khủng khiếp như thế nào. Việc OpenCV chỉ hỗ trợ video dạng không nén, có lẽ là do vấn đề bản quyền.
Có một thực tế là dù trong danh sách này http://opencv.willowgarage.
Ngoài Virtual Dubb khá thông dụng cho việc chuyển đổi các định dạng video, phần mềm sau cũng cực kì hữu ích SUPER: http://www.erightsoft.com/
Nguồn: Blog Lê Đình Duy
Face Representation by SIFT Descriptor
.jpg)
Mặc dù đã có khá nhiều nghiên cứu về face representation cho face recognition, nhưng cách biểu diễn mà nhiều người dùng nhất vẫn là PCA. Cách biểu diễn này dùng một tập trainining faces (thường là đã được normalized to a canonical pose), mỗi face được biểu diễn trong một không gian đa chiều, mỗi chiều là một vị trí trong face. Ví dụ, face có kích thước 80x80, thì sẽ được biểu diễn trong không gian 6,400 chiều (=80x80), chiều thứ nhất tương ứng với vị trí (0, 0), chiều thứ hai (0, 1), v.v ... Trong không gian này, mỗi face sẽ tương ứng với một point. Feature vector tương ứng với point đó hình thành bằng cách lấy pixel intensity tại các điểm trong ảnh. Cách biểu diễn này cho số chiều quá lớn, sẽ dễ dẫn đến các hậu quả như over-fitting hoặc chi phí tính toán cao. PCA là phương pháp dùng để rút gọn số chiều lại. Thông thường, nếu có K training faces (K nhỏ hơn rất nhiều so với kích thước ảnh NxN), thì sau khi dùng PCA, số chiều tối đa là K.
PCA có thể tính bằng cách dùng matlab. Code bằng C++ có thể xem tại đây: csuEvalFaceRec - http://www.cs.colostate.edu/evalfacerec/algorithms5.html
Gần đây, có một cách biểu diễn khác đó là detect các feature points ở các vị trí như mắt, mũi, miệng, sau đó extract descriptors (ví dụ SIFT) tại các feature points, rồi nối lại thành feature vector, hoặc dùng theo kiểu BoW. Cách biểu diễn này được dùng khá nhiều cho các work về face identification hay face matching. Điển hình là bài của Everingham tại BMVC'06: Hello my name is Buffy - Automatic Naming of Characters in TV Video. Có cả source chạy bằng matlab cho phần feature point detection và descriptor extraction. Để xem các bài dùng code này có thể dùng Google Scholar tìm các bài cite tới bài này!
Với các face có kích thước đủ lớn (khoảng trên 100x100 pixel), chất lượng ảnh đầu vào nét, code trên chạy khá tốt. Tuy nhiên khi chạy trên dữ liệu face từ TRECVID data, phần eye corner detection không được tốt (các corner khác như mouth, nose thì vẫn tốt).
Lê Đình Duy
5/04/2015
[SQL] Tạo bảng số nguyên ảo bằng truy vấn
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.
Vấn đề
Khi sử dụng CSDL SQL để phát triển hệ thống, đôi lúc tôi cần đến một dãy số nguyên hay một dãy ngày tháng dưới dạng các bản ghi để dùng vào mục đích hiển thị như một số trường hợp sau:
- Tạo một điều khiển cho phép người dùng chọn giá trị trong khoảng nhất định tùy vào thông số. VD: chọn năm từ 2 năm trước đến 2 năm sau hiện tại (2015 thì là từ 2013 đến 2017), hay chọn một ngày từ 1 tuần trước đến 1 tuần sau hiện tại.
- Làm trục ngày tháng để tạo bảng báo cáo kết quả kinh doanh trong một khoảng thời gian nhất định. VD: tạo bảng báo cáo tổng số đơn hàng mỗi ngày từ ngày 21 tháng trước đến ngày 20 tháng này (kể cả những ngày ko có đơn nào).
| Ngày | Số đơn |
|---|---|
| 2015/5/21 | 4 |
| 2015/5/22 | 0 |
| 2015/5/23 | 5 |
| ... | ... |
| 2015/6/19 | 20 |
| 2015/6/20 | 12 |
Giải pháp sơ cấp
Giải pháp đơn giản nhất là lưu các dữ liệu này thành bảng trong CSDL. Tôi sẽ tạo một bảng gồm một cột số nguyên chứa các giá trị từ 0 cho đến một số lớn nào đó (vd. 2^16), một bảng gồm một cột ngày chứa các ngày từ thời điểm nào đó trong quá khứ (vd. 1900/1/1) đến thời điểm nào đó trong tương lai (vd. 2100/1/1). Tôi cũng có thể gộp hai bảng này lại thành một bảng gồm hai cột.
Như vậy khi cần dùng đến một dãy số nguyên hay một dãy ngày tháng tôi có thể viết một câu truy vấn đơn giản:
SELECT [Số] FROM [Bảng số] WHERE [Số] BETWEEN [@Năm nay] - 2 AND [@Năm nay] + 2
SELECT [Ngày] FROM [Bảng ngày] WHERE [Ngày] BETWEEN DATEADD(week, -1, [@Hôm nay]) AND DATEADD(week, 1, [@Hôm nay])
Người nào có kinh nghiệp với CSDL có thể nhận ra ngay giải pháp này có hai vấn đề to đoành:
- Độ tin cậy thấp: Ko có gì đảm bảo được bảng của tôi ko thiếu một vài bản ghi ở đâu đó do một cơ số những lý do nào đó.
- Tạo kiểu gì: Nếu phải ngồi gõ từng bản ghi thì thà nghỉ việc còn hơn. Ngoài ra có thể tạo bằng Excel (bằng cắt dán công thức hoặc VBA) rồi nhập nguyên bảng vào CSDL, nhưng như thế khá thô thiển. Còn nếu viết được một câu truy vấn SQL để tự tạo ra bảng thì cũng chả cần quái gì đến bảng nữa.
Giải pháp trung cấp
Để giảm sự phụ thuộc vào dữ liệu đến từ bảng, trước hết tôi có thể viết câu truy vấn để sinh ra ngày tháng từ bảng số nguyên rồi lưu lại thành khung nhìn (view) dùng thay cho bảng ngày tháng:
SELECT DATEADD(day,[Số],'1900-01-01') AS [Ngày] FROM [Bảng số]
Còn đối với bảng số nguyên, nhận thấy mọi số nguyên đều có thể viết thành tổng của các số trong khoảng từ 0 đến 9 nhân với lũy thừa của 10, tôi có thể tạo một bảng gồm 10 bản ghi chứa giá trị từ 0 đến 9 và sử dụng câu truy vấn sau (lưu thành khung nhìn) để sinh ra tất cả các số nguyên cần dùng.
SELECT [Bảng 4].[Số] * 10 * 10 * 10 + [Bảng 3].[Số] * 10 * 10 + [Bảng 2].[Số] * 10 + [Bảng 1].[Số] FROM
[Bảng số] AS [Bảng 1], [Bảng số] AS [Bảng 2], [Bảng số] AS [Bảng 3], [Bảng số] AS [Bảng 4]
Câu truy vấn trên sẽ sinh ra 10.000 bản ghi chứa các số nguyên từ 0 cho đến 9.999 bằng cách nhân Đề-các 4 bảng 10 bản ghi lại với nhau. Tôi có thể tăng số bảng sử dụng lên cho đến khi đạt đến một mức số đủ dùng. Đơn giản hơn tôi có thể tăng hệ số và số bản ghi gốc lên. VD: nếu sử dụng hệ số 20 thay vì 10 thì sử dụng bảng gồm các số từ 0 đến 19 và khi tính thì nhân với các lũy thừa của 20.
Hiển nhiên khi tôi đã có một khung nhìn để sinh các số nguyên thì tôi cũng có thể sinh ngày tháng bằng khung nhìn đó thay vì dùng một bảng số nguyên đầy đủ làm gốc. Như vậy tôi đã có thể sinh ra hai bảng số nguyên và ngày tháng với số bản ghi lên đến hàng triệu và hơn nữa chỉ từ một bảng gồm chục bản ghi.
Tuy nhiên giải pháp này vẫn tồn tại hai vấn đề mặc dù ko đáng kể so với giải pháp trước:
- Vẫn phụ thuộc dữ liệu: Tuy tôi đã giảm sự phụ thuộc xuống một mức hoàn toàn nằm trong tầm kiểm soát của con người, giải pháp này xét cho cùng vẫn phải dựa vào dữ liệu thực và do đó dù nhỏ vẫn có rủi ro.
- Câu truy vấn kềnh càng: Tôi cần phải lựa chọn cân bằng giữa một bên là hệ số và số bản ghi gốc, với một bên là độ dài của câu truy vấn khi cần phải tạo ra một lượng bản ghi rất lớn (ví dụ hàng tỷ bản ghi).
Giải pháp cao cấp (sang chảnh)
Giải pháp nói trên có thể dùng cho hầu hết các biến thể phổ biến của SQL, từ T-SQL (SQL Server), PL/SQL (Oracle) cho đến Microsoft Access, và nó cũng đủ "số má" để có thể sử dụng cho các hệ thống thực tiễn. Tuy vậy tôi vẫn thích một giải pháp có "đẳng cấp" hơn như trình bày ở dưới đây,
Lưu ý: Giải pháp dưới đây dành cho T-SQL. Các biến thể SQL khác có thể cũng có cách áp dụng nhưng tôi sẽ ko đề cập ở đây,
WITH
[Bảng số cấp 1] AS (SELECT 1 AS [Số 1] UNION ALL SELECT 1 AS [Số 1]),
[Bảng số cấp 2] AS (SELECT [Bảng 1].[Số 1] FROM [Bảng số cấp 1] AS [Bảng 1], [Bảng số cấp 1] AS [Bảng 2]),
[Bảng số cấp 3] AS (SELECT [Bảng 1].[Số 1] FROM [Bảng số cấp 2] AS [Bảng 1], [Bảng số cấp 2] AS [Bảng 2]),
[Bảng số cấp 4] AS (SELECT [Bảng 1].[Số 1] FROM [Bảng số cấp 3] AS [Bảng 1], [Bảng số cấp 3] AS [Bảng 2]),
[Bảng số cấp 5] AS (SELECT [Bảng 1].[Số 1] FROM [Bảng số cấp 4] AS [Bảng 1], [Bảng số cấp 4] AS [Bảng 2]),
[Bảng số cấp 6] AS (SELECT [Bảng 1].[Số 1] FROM [Bảng số cấp 5] AS [Bảng 1], [Bảng số cấp 5] AS [Bảng 2])
SELECT (ROW_NUMBER() OVER (ORDER BY [Số 1])) -1 AS [Số] FROM [Bảng số cấp 6]
Câu truy vấn trên sẽ sinh ra 2^2^2^2^2^2 = 4.294.967.296 bản ghi chứa các số nguyên từ 0 đến 4.294.967.295 mà ko cần bất kỳ dữ liệu có sẵn nào, và chỉ cần thêm 1 cấp nữa thì giá trị số nguyên lớn nhất trên lý thuyết sẽ vượt ra ngoài khả năng lưu trữ của kiểu bigint.
Câu truy vấn trên nếu phân tích ra thì gồm các yếu tố như sau:
WITH: Cú phápWITHtrong T-SQL cho phép tôi định nghĩa tạm thời một bảng bên trong một câu truy vấn bình thường khác. Điều này cho phép tôi lần lượt tạo ra các bảng số cấp từ 1 đến 6 trong cùng một câu truy vấn thay vì phải chia ra làm 6 câu truy vấn (khung hình). (https://msdn.microsoft.com/en-us/library/ms175972.aspx)SELECT 1 AS [Số]: Câu lệnh đơn giản này trả về đúng 1 bản ghi chứa số 1, nhưng nó lại là một điểm mấu chốt của giải pháp, vì nó cho phép tôi tạo ra dữ liệu từ chỗ ko có gì. Đây là bước đi từ 0 lên 1 bản ghi, loại bỏ sự phụ thuộc vào dữ liệu có sẵn.UNION ALL: Cú phápUNIONgộp các bản ghi từ hai bảng hoặc câu truy vấn lại với nhau theo chiều dọc thay vì chiều ngang nhưJOIN. Có thể nói nếuJOINlà phép nhân thìUNIONlà phép cộng. Chú ý ở đây tôi dùngUNION ALLthay vìUNIONđể chỉ thị cho SQL Server giữ lại cả hai bản ghi cùng giá trị bằng 1 thay vì gộp lại thành một bản ghi. Đây là bước đi từ 1 lên 2 bản ghi, tạo ra bảng số cấp 1 và cho phép tôi bắt đầu tăng số bản ghi theo cấp số mũ. (http://www.w3schools.com/sql/sql_union.asp)[Bảng số cấp 2~6]: Các bảng số cấp từ 2 trở đi đơn thuần là phép nhân Đề-các các bảng số cấp dưới với chính nó. Cứ mỗi cấp thì số bản ghi lại được lũy thừa 2 lên.SELECT ROW_NUMBER() OVER (ORDER BY [Số]): Do các bản ghi trong các bảng số tôi tạo ra ở các bước trên đều chỉ chứa cùng giá trị là 1, nên tôi cần một cách để chuyển đổi số lượng bản ghi ra thành giá trị chứa trong bản ghi. Cách đơn giản nhất là dùngROW_NUMBER()để trả về số thứ tự dòng (bản ghi) hiện tại. (https://msdn.microsoft.com/en-us/library/ms186734.aspx). Ngoài ra tôi cũng có thể dùngSUM([Số 1]) OVER (PARTITION BY [Số 1] ORDER BY [Số 1] ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))để trả về tổng của các giá trị các bản ghi từ dòng đầu tiên cho đến dòng hiện tại để được kết quả tương tự, với điều kiện tất cả các bản ghi phải có cùng giá trị là 1.
Hiển nhiên một câu truy vấn như trên nếu muốn chạy trọn vẹn thì cũng phải mất thời gian tính bằng phút kể cả trên một máy chủ chuyên dụng. Do vậy khi sử dụng trên thực tế tôi sẽ điều chỉnh cấp cho phù hợp (thường chỉ cần đến cấp 5).
Kết
Trên đây là một số giải pháp để tạo ra một bảng các số nguyên mà ko dựa vào các dữ liệu có ý nghĩa thực tế khác trong hệ thống, trong đó chú trọng vào việc tạo ra một bảng số nguyên ảo bằng câu truy vấn lưu dưới dạng khung nhìn thay vì sử dụng một bảng số nguyên thực 100%.
Những giải pháp trên do tôi cóp nhặt từ hệ thống của người đi trước và từ mạng internet, kết hợp với kinh nghiệm cá nhân, nhằm giải quyết một số nhu cầu thực tế bản thân thường gặp. Hy vọng nó có thể có ích cho mọi người và chúc mọi người thành công.
Nguồn: Hawkie
[SQL] Tạo bảng lịch ảo bằng truy vấn (phần 2)
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.
- Người đọc cần đọc phần 1 để hiểu rõ nội dung bài viết này.
Vấn đề
Trong phần 1 tôi đã giới thiệu một số giải pháp để tạo một bảng lịch ngày tháng có chứa thêm thông tin ngày nào là ngày nghỉ. Trong đó, giải pháp cao cấp mà tôi đưa ra về phương diện lý thuyết cốt lõi có thể nói là đã khá hoàn hảo và tôi cảm thấy ko cần thiết phải đào sâu cải tiến thêm nữa. Tuy nhiên việc ứng dụng cái lý thuyết cốt lõi ấy vào giải quyết vấn đề thực tế lại là một vấn đề khác và đòi hỏi rất nhiều sự sửa chữa, cải tiến ở mức độ chi tiết, cũng như mở rộng thêm chức năng để đáp ứng nhu cầu người dùng.
Cụ thể tôi có thể thấy được một số vấn đề đối với giải pháp cao cấp ở bài viết trước như sau:
- Chênh lệch số ngày trong tháng: Nếu người dùng thiết lập quy luật nghỉ lặp hàng tháng lấy ngày 2015/1/31 làm ngày chuẩn, thì tháng 4 chỉ có 30 ngày sẽ ko có ngày nào ứng với quy luật này. Vậy người dùng nếu muốn thiết lập ngày cuối mỗi tháng là ngày nghỉ thì sẽ phải làm thế nào?
- Ngày nghỉ trong quá khứ: Nếu sang năm nay quy luật nghỉ thứ Bảy Chủ Nhật được chuyển thành thứ Tư Chủ Nhật, thì tất cả các ngày thứ Bảy cho đến năm ngoái sẽ đột nhiên được hiển thị là ngày làm việc. Vậy người dùng nếu muốn đưa ra hai bản báo cáo thành tích từng ngày (có thông tin ngày nghỉ) của tháng 1 năm ngoài và tháng 1 năm nay để so sánh thì sẽ phải làm thế nào?
- Thiết lập ngoại lệ: Nếu người dùng muốn thiết lập ngày cuối mỗi tháng là ngày nghỉ, nhưng vì 1/1 đã là ngày nghỉ lễ rồi nên ngày 12/31 là ngày đi làm, thì sẽ phải làm thế nào?
Chênh lệch số ngày trong tháng
Đây là vấn đề dễ thấy nhất (bị lộ sớm nhất), và cách sửa cũng đơn giản nhất. Vấn đề nảy sinh do khi xác định phương pháp đánh giá quy luật nghỉ, tôi đã dựa trên định nghĩa "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". Định nghĩa này đứng từ góc nhìn của người làm hệ thống đang tìm cách giải quyết vấn đề. Trong khi đó suy nghĩ của người dùng thường sẽ là "mỗi tháng lấy một ngày nhất định theo tiêu chí nào đó làm ngày nghỉ".
Vậy thì để có giải pháp toàn diện và "đẹp" nhất tôi cần chuyển suy nghĩ theo góc nhìn của người dùng: một quy luật lặp theo tháng nếu áp dụng vào tháng của ngày cần đánh giá thì sẽ rơi vào ngày nào? Từ hướng này, tôi có thể thấy: nếu số ngày của ngày chuẩn vượt quá số ngày của tháng đó thì ngày nghỉ sẽ rơi vào ngày cuối của tháng. Như vậy nếu ngày cần đánh giá có cùng số ngày với số lớn hơn trong hai số là 1.số ngày của ngày chuẩn và 2.số ngày của tháng chứa ngày cần đánh giá, thì ngày đó sẽ là ngày nghỉ.
WHEN 'm' THEN IIF(DAY([Ngày]) = IIF(DAY([Ngày chuẩn]) < ..., DAY([Ngày chuẩn]), ...), 1, 0)
Vấn đề còn lại là tìm ra số ngày của tháng chứa ngày cần đánh giá. Cách đơn giản nhất là lấy số ngày của ngày cuối cùng của tháng đó. Điều này mỗi ngôn ngữ SQL có sự khác nhau, đối với T-SQL thì đó sẽ là
DAY(EOMONTH([Ngày])). Sau khi đã sửa được phương pháp đánh giá cho kiểu lặp theo tháng, tôi sẽ áp dụng tương tự cho kiểu lặp theo năm và đi đến được câu truy vấn như sau.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]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))), 1, 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))) 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]
Ngày nghỉ trong quá khứ
Đây là một vấn đề hơi khó nhận ra nếu ko có nhu cầu người dùng thực tế. Giải pháp nảy ra sớm nhất trong đầu tôi là lưu một bảng lịch quá khứ. Hàng ngày tôi sẽ lưu ngày hôm đó vào cột ngày, và thông tin có phải ngày nghỉ hay ko vào một cột nữa của bảng đó. Tác vụ này có thể được tự động hóa bằng SQL Agent hoặc Task Scheduler. Khi lập bảng lịch, tôi sẽ chỉ thực hiện tính toán tự động dựa trên quy luật nghỉ đối với các ngày từ thời điểm hiện tại trở đi, sau đó kết hợp với bảng lịch quá khứ bằng cú pháp
UNION.SELECT [Ngày], MAX(...) AS [Nghỉ] FROM [Bảng ngày], [Bảng quy luật] WHERE [Ngày] >= GETDATE() GROUP BY [Ngày]
UNION
SELECT [Ngày], [Nghỉ] FROM [Bảng lịch quá khứ]
Hoặc tôi cũng có thể chỉ lưu lại các ngày nghỉ trong quá khứ thay vì toàn bộ tất cả các ngày, sau đó trong câu truy vấn tôi sẽ tham vấn đến bảng này nếu ngày cần đánh giá nằm trước thời điểm hiện tại.
SELECT [Bảng ngày].[Ngày], IIF([Ngày] >= GETDATE(), MAX(...), [Bảng lịch quá khứ].[Nghỉ]) AS [Nghỉ]
FROM [Bảng ngày] LEFT OUTER JOIN [Bảng lịch quá khứ] ON [Bảng ngày].[Ngày] = [Bảng lịch quá khứ].[Ngày], [Bảng quy luật]
GROUP BY [Ngày]
Hai giải pháp trên đến từ suy nghĩ rằng vấn đề này là do bản thân việc tính toán tự động khiến cho dữ liệu quá khứ đáng lẽ phải được cố định thì lại bị thay đổi. Tuy nhiên như giải pháp sơ cấp và trung cấp ở phần 1, hai giải pháp trên đều phụ thuộc nhiều vào dữ liệu thực dẫn đến độ tin cậy kém. Đặc biệt giải pháp thứ nhất có thể dẫn đến xuất hiện hai bản ghi của cùng một ngày nếu một bản ghi chứa một ngày sau hiện tại vì một lý do nào đó lọt vào bảng lịch quá khứ.
Để khắc phục điều đó, tôi chuyển suy nghĩ sang việc lưu lại dữ liệu về quy luật nghỉ trong quá khứ thay vì ngày nghỉ. Tôi có thể tạo một bảng quy luật nghỉ quá khứ rồi tham vấn bảng này đối với các ngày trước thời điểm hiện tại. Tuy nhiên việc lưu quy luật thay vì ngày dẫn đến một vấn đề mới: làm sao tôi có thể biết được một quy luật trong quá khứ bắt đầu được áp dụng từ khi nào và hết hiệu lực từ khi nào?
Vậy là tôi cần thêm một cột ngày bắt đầu và một cột ngày kết thúc vào bảng quy luật quá khứ để biết quy luật nào có thể dùng được tại một thời điểm nhất định trong quá khứ. Đến đây tôi lại nhận ra rằng quy luật hiện hành đơn giản chỉ là các quy luật có ngày bắt đầu ở trong quá khứ và ngày kết thúc ở một thời điểm trong tương lai. Như vậy tôi có thể lưu cả các quy luật quá khứ trong bảng quy luật hiện hành bằng việc thêm ngày bắt đầu và kết thúc như sau:
| Ngày chuẩn | Kiểu lặp | Giải thích | Ngày bắt đầu | Ngày kết thúc |
|---|---|---|---|---|
| 2014/1/4 | w | Thứ Bảy | 2014/1/1 | 2014/12/31 |
| 2015/1/7 | w | Thứ Tư (từ 2015) | 2015/1/1 | 2100/1/1 |
Và khi đánh giá ngày và quy luật nghỉ, tôi sẽ chỉ xét đến các quy luật có ngày bắt đầu đứng trước và ngày kết thúc đứng sau ngày cần đánh giá như sau:
MAX(IIF([Ngày] NOT BETWEEN [Ngày bắt đầu] AND [Ngày kết thúc], 0, CASE [Kiểu lặp] ... END)) AS [Nghỉ]
Chú ý ở đây tôi ko đưa điều kiện vào cú pháp
WHERE vì như vậy sẽ loại mất các ngày ko nằm giữa ngày bắt đầu và kết thúc của bất cứ một quy luật nào. Như vậy tôi sẽ được câu truy vấn như sau:SELECT [Ngày], MAX(IIF([Ngày] NOT BETWEEN [Ngày bắt đầu] AND [Ngày kết thúc], 0, 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]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))), 1, 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))) 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]
Thiết lập ngoại lệ
Vấn đề này cũng là một vấn đề khá thú vị. Nhận thấy yêu cầu của người dùng ko phải là "thiết lập ngoại lệ cho một quy luật nghỉ" mà là "thiết lập một ngày làm việc theo quy luật lặp nhất định", tôi có thể thêm quy luật ngày đi làm vào bảng quy luật, và khi có một ngày ứng với cả quy luật nghỉ lẫn quy luật đi làm thì sẽ ưu tiên quy luật đi làm. Bảng quy luật nghỉ của tôi như vậy sẽ phải thêm cột thể hiện quy luật đó là quy luật nghỉ hay đi làm như sau:
| Ngày chuẩn | Kiểu lặp | Nghỉ | Giải thích | Ngày bắt đầu | Ngày kết thúc |
|---|---|---|---|---|---|
| 2015/1/31 | m | 1 | Nghỉ cuối tháng | 1900/1/1 | 2100/1/1 |
| 2015/12/31 | y | 0 | Mai nghỉ rồi còn gì | 1900/1/1 | 2100/1/1 |
Về phía câu truy vấn, nếu như trước đây kết quả đánh giá mà tôi cần tổng hợp từ một cặp ngày và quy luật chỉ gồm hai ứng viên là 1.ngày ko ứng với quy luật và 2.ngày ứng với quy luật, thì bây giờ tôi có ba ứng viên là 1.ngày ko ứng với quy luật, 2.ngày ứng với quy luật và quy luật là nghỉ và 3.ngày ứng với quy luật và quy luật là đi làm, trong đó ứng viên 3. luôn được ưu tiên. Do vốn sẵn tôi đã dùng hàm
MAX với khả năng 1. và 2. lần lượt mang giá trị 0 và 1, nên tôi có thể cho ứng viên 3 mang giá trị 2 như sau:WHEN 'n' THEN IIF([Ngày] = [Ngày chuẩn], 2 - [Nghỉ], 0)
Và khi tổng hợp lại tôi cần quyết định ngày đó có phải ngày nghỉ hay ko theo bảng sau:
| MAX(...) | Nghỉ |
|---|---|
| 0 | 0 |
| 1 | 1 |
| 2 | 0 |
Nên mặc dù chẳng liên quan gì nhưng tôi có thể chuyển đổi giá trị trả về của hàm
MAX thành giá trị cuối cùng như sau:SELECT [Ngày], MAX(...) % 2 AS [Nghỉ] FROM ...
Và tôi sẽ được câu truy vấn hoàn chỉnh như sau:
SELECT [Ngày], MAX(IIF([Ngày] NOT BETWEEN [Ngày bắt đầu] AND [Ngày kết thúc], 0, CASE [Kiểu lặp]
WHEN 'n' THEN IIF([Ngày] = [Ngày chuẩn], 2 - [Nghỉ], 0)
WHEN 'w' THEN IIF(DATEPART(weekday, [Ngày]) = DATEPART(weekday, [Ngày chuẩn]), 2 - [Nghỉ], 0)
WHEN 'm' THEN IIF(DAY([Ngày]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))), 2 - [Nghỉ], 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))) AND
MONTH([Ngày]) = MONTH([Ngày chuẩn]), 2 - [Nghỉ], 0)
ELSE 0 END)) % 2 AS [Nghỉ]
FROM [Bảng ngày], [Bảng quy luật] GROUP BY [Ngày]
Đến đây tôi đã có thể ngồi lại và tận hưởng hương vị chiến thắng, cho đến khi người dùng đưa ra thêm yêu cầu như sau: thiết lập các ngày cuối tháng làm ngày nghỉ trừ ngày 12/31, nhưng mà nếu 12/31 rơi vào ngày nghỉ hàng tuần thì cho nghỉ nốt. Giải pháp tôi đưa ra ở trên như vậy là đổ bể hoàn toàn, do tôi đã dựa trên quá nhiều giả định có thể dễ dàng bị phá bỏ bới yêu cầu người dùng.
Để ý thấy rằng trong yêu cầu nói trên có sự xuất hiện của 3 quy luật lặp khác nhau là 1.tất cả các ngày cuối tháng, 2.ngày 12/31 hàng năm và 3.ngày nghỉ theo tuần, tôi nhận thấy cần phải tìm cách so sánh mức độ ưu tiên của nhiều hơn hai quy luật, và để làm được điều đó thì tôi cần thêm một cột thể hiện độ ưu tiên vào bảng quy luật như sau
| Ngày chuẩn | Kiểu lặp | Nghỉ | Giải thích | Độ ưu tiên | Ngày bắt đầu | Ngày kết thúc |
|---|---|---|---|---|---|---|
| 2015/1/31 | m | 1 | Nghỉ cuối tháng | 10 | 1900/1/1 | 2100/1/1 |
| 2015/12/31 | y | 0 | Mai nghỉ rồi còn gì | 20 | 1900/1/1 | 2100/1/1 |
| 2015/4/25 | w | 1 | Thứ Bảy | 50 | 1900/1/1 | 2100/1/1 |
| 2015/4/26 | w | 1 | Chủ Nhật | 50 | 1900/1/1 | 2100/1/1 |
Trong đó quy luật có giá trị độ ưu tiên càng lớn thì càng được ưu tiên cao hơn khi tổng hợp kết quả đánh giá các quy luật cho mỗi ngày. Vấn đề còn lại là làm thế nào để tổng hợp kết quả?
SELECT [Ngày], ISNULL((
SELECT TOP 1 [Nghỉ] FROM (
SELECT IIF([Ngày] NOT BETWEEN [Ngày bắt đầu] AND [Ngày kết thúc], 0, 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]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))), 1, 0)
WHEN 'y' THEN IIF(DAY([Ngày]) = IIF(DAY([Ngày chuẩn]) < DAY(EOMONTH([Ngày])),
DAY([Ngày chuẩn]), DAY(EOMONTH([Ngày]))) AND MONTH([Ngày]) = MONTH([Ngày chuẩn]), 1, 0)
ELSE 0 END) AS [Kết quả đánh giá], [Nghỉ], [Độ ưu tiên]
FROM [Bảng quy luật]
) AS [Bảng đánh giá quy luật]
WHERE [Kết quả đánh giá] = 1 ORDER BY [Độ ưu tiên] DESC
), 0) AS [Nghỉ]
FROM [Bảng ngày]
Để đi đến được câu truy vấn khủng long trên, tôi cần phải hình dung lại lần nữa cách tôi sẽ giải quyết vấn đề nếu sử dụng ngôn ngữ lập trình thông thường. Như đã trình bày ở phần 1, tôi sẽ sử dụng hai vòng
for lồng vào nhau, vòng ngoài chạy tất cả các ngày, vòng trong chạy tất cả các quy luật. Điểm khác biệt ở đây là thay vì chỉ cần tìm được một quy luật ứng với ngày cần đánh giá là có thể kết luận đó là ngày nghỉ, thì bây giờ tôi cần phải tìm tất cả các quy luật ứng với ngày cần đánh giá, tìm ra quy luật có độ ưu tiên cao nhất, và lấy giá trị nghỉ của quy luật đó làm kết quả đánh giá cho ngày đó.
Để dễ hình dùng hơn, thay vì suy nghĩ từ ngoài vào thì tôi suy nghĩ từ trong ra. Trước hết tôi cần đánh giá tất cả các quy luật hiện có đối với ngày cần đánh giá, với phần đánh giá lấy lại từ câu truy vấn ở phần trên. Do tôi cần biết độ ưu tiên và giá trị nghỉ của từng quy luật để dùng cho các xử lý về sau nên tôi cũng phải thêm các dữ liệu đó vào câu truy vấn.
SELECT ... AS [Kết quả đánh giá], [Độ ưu tiên], [Nghỉ] FROM [Bảng quy luật]
Tiếp theo tôi cần loại bỏ tất cả các quy luật ko áp dụng được với ngày cần đánh giá. Do tôi ko thể sử dụng một giá trị vừa được tính toán trong phần
SELECT để làm điều kiện trong phần WHERE, nên tôi sẽ phải chuyển câu truy vấn trên thành một câu truy vấn con (subquery) như một bảng tạm để dùng trong câu truy vấn loại bỏ như sau:SELECT ... FROM (
SELECT ... AS [Kết quả đánh giá], [Độ ưu tiên], [Nghỉ] FROM [Bảng quy luật]
) AS [Bảng đánh giá quy luật]
WHERE [Kết quả đánh giá] = 1
Sau khi đã loại bỏ các quy luật ko cần thiết, tôi cần tìm ra quy luật có độ ưu tiên cao nhất và lấy ra giá trị nghỉ của nó. Điều này có thể được thực hiện đơn giản bằng cách sắp xếp các bản ghi theo thứ tự giảm dần của độ ưu tiên và lấy bản ghi ở trên cùng bằng cú pháp SELECT TOP 1 (tham khảo). Để ý ở đây tôi cần tìm ra một quy luật, tức một bản ghi, chứ ko phải tìm ra một giá trị là độ ưu tiên cao nhất, nên các hàm aggregate đều ko sử dụng được.
SELECT TOP 1 [Nghỉ] FROM (
SELECT ... AS [Kết quả đánh giá], [Độ ưu tiên], [Nghỉ] FROM [Bảng quy luật]
) AS [Bảng đánh giá quy luật]
WHERE [Kết quả đánh giá] = 1 ORDER BY [Độ ưu tiên] DESC
Việc còn lại chỉ là thực hiện câu truy vấn trên đối với tất cả các ngày trong bảng ngày. Do câu truy vấn trên chỉ trả về đúng một giá trị nên tôi có thể chuyển nó thành một câu truy vấn con như một giá trị trả về trong phần
SELECT như sau:SELECT [Ngày], (
SELECT TOP 1 [Nghỉ] FROM (
SELECT ... AS [Kết quả đánh giá], [Độ ưu tiên], [Nghỉ] FROM [Bảng quy luật]
) AS [Bảng đánh giá quy luật]
WHERE [Kết quả đánh giá] = 1 ORDER BY [Độ ưu tiên] DESC
) AS [Nghỉ] FROM [Bảng ngày]
Trên lý thuyết thì câu truy vấn đề đây đã hoàn thành, nhưng khi chạy thử thì tôi phát hiện ra còn một cái bẫy cuối cùng. Đó là đối với những ngày ko ứng với một quy luật nào thì
SELECT TOP 1 sẽ ko trả về bản ghi nào và do vậy giá trị nghỉ ở câu truy vấn ngoài cùng sẽ trở thành NULL. Do nếu ko có quy luật nào áp dung được thì ngày cần đánh giá tự động trở thành ngày làm việc, nên tôi có thể khắc phục vấn đề này bằng hàm ISNULL (tham khảo).SELECT [Ngày], ISNULL((
SELECT TOP 1 [Nghỉ] FROM (
SELECT ... AS [Kết quả đánh giá], [Độ ưu tiên], [Nghỉ] FROM [Bảng quy luật]
) AS [Bảng đánh giá quy luật]
WHERE [Kết quả đánh giá] = 1 ORDER BY [Độ ưu tiên] DESC
), 0) AS [Nghỉ] FROM [Bảng ngày]
Cuối cùng tôi sẽ được câu truy vấn hoàn chỉnh như đã đưa ra ở trên.
Kết
Đến đây thì có thể nói là bảng ngày tháng nhỏ nhắn ban đầu của tôi đã tiến hóa lên thành một ứng dụng lịch làm việc tương đối mạnh (so với lượng công sức bỏ ra). Từ nền tảng này tôi có thể thêm thắt nhiều chức năng khác như tạo lịch cho riêng từng bộ phận, thậm chí từng người trong công ty, hay quản lý thêm sự kiện và công việc bên cạnh ngày nghỉ tùy vào nhu cầu của người dùng.
Những chức năng trên, hay bản thân chức năng tạo lịch từ quy luật, đều có thể được thực hiện dễ dàng (có thể là dễ hơn) bởi một ứng dụng lập trình bằng ngôn ngữ khác ngoài SQL. Cũng có thể có người sẽ cho rằng những chức năng này thuộc về logic nghiệp vụ và ko nên để trong tầng dữ liệu. Tuy nhiên ở đây tôi muốn thể hiện rằng một phần ko nhỏ những việc ngôn ngữ lập trình khác làm được thì SQL cũng làm được với một nét đẹp của riêng SQL. Đó là chưa kể đến lý do mang tính thực dụng là một khung nhìn SQL sẽ có thể được ứng dụng rộng rãi và đơn giản hơn rất nhiều so với một thư viện hay một dịch vụ tương đương.
Tất cả vấn đề trong bài viết này đều do tôi tự nghĩ ra, nhưng là dựa trên những nhu cầu rất thật trong thực tiễn công việc. Các giải pháp là do tôi tự nghĩ ra dựa trên kinh nghiệm cá nhân kết hợp với các thủ thuật cóp nhặt từ trên internet. 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.
Nguồn: Hawkie
Subscribe to:
Posts (Atom)