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