Aggregate SQL là gì
|
Câu đố vui 2 được post trên group Quản trị CSDL SQL Server, bài viết này nhằm cung cấp đáp án và một vài cách truy vấn giúp xác định một tập chứa các số tăng dần có bị gián đoạn hay không, bằng cách sử dụng các hàm thống kê SQL. Bảng T1 có cấu trúc và được insert 10k dòng dữ liệu như hình ở phần comment. Nếu chúng ta được yêu cầu kiểm tra xem giá trị cột data1 có liên tiếp, không bị gián đoạn hoặc lặp lại thì sử dụng câu truy vấn nào dưới đây vừa đủ dữ kiện để kết luận? CREATE DATABASE labdb01
GO
CREATE TABLE T1(id INT IDENTITY PRIMARY KEY CLUSTERED, data1 BIGINT, data2 DATETIME, data3 CHAR(64))
BEGIN TRANSACTION
DECLARE @i INT = 1
WHILE @i <= 10000
BEGIN
INSERT INTO T1(data1,data2,data3)
SELECT @i, GETDATE(), 'T1-' + CAST(@i AS CHAR(8))
SET @i += 1
END
COMMIT
Nếu chúng ta được yêu cầu kiểm tra cột data1 có chạy từ 1 đến 10000 không hơn không kém và không sót giá trị nào thì chúng ta nên viết câu truy vấn như thế nào để khẳng định được điều này? Ok, trước tiên chúng ta hãy liệt kê một vài tình huống dữ liệu không như yêu cầu để lát nữa kiểm thử câu truy vấn. Dùng các hàm thống kê SQLCó thể phân tích sơ bộ dữ liệu của chúng ta có thể có các tình huống như sau:
Chúng ta sẽ tạo dữ liệu mẫu để kiểm chứng những điều trên
Đoạn code SQL dưới đây tạo bảng và dữ liệu như mô tả trên USE labdb01
GO
---Bảng có 10K dòng, từ 0-10000, khuyết gia trị 1
SELECT * INTO T1_LowX
FROM T1
UPDATE T1_LowX SET data1 = 0 WHERE data1 = 1
--Bảng có 10K dòng, từ 1-10001, khuyết giá trị 10000
SELECT * INTO T1_HighX
FROM T1
UPDATE T1_HighX SET data1 = 10001 WHERE data1 = 10000
--Bảng có 9999 dòng, từ 1-10000, khuyết giá trị 2
SELECT * INTO T1_LessX
FROM T1
DELETE FROM T1_LessX WHERE data1 = 2
--Bảng có 10K dòng + 1, 1-10000, hai dòng trùng giá trị 10000
SELECT * INTO T1_MoreX
FROM T1
INSERT INTO T1_MoreX(data1,data2,data3)
SELECT 10000, GETDATE(), 'T1_MoreX-10000'
--Bảng có 10k dòng, và giá trị cột data1 đủ từ 1 tới 10000
-- SELECT * FROM T1
--Bảng có 10k dòng, giá trị cột data1 có 2 dòng bằng 5, không có dòng nào bằng 4
SELECT * INTO T1_Double5
FROM T1
UPDATE T1_Double5 SET data1 = 5 WHERE data1 = 4
--Bảng có 10k dòng, giá trị cột data1 có 3 dòng bằng 5, không có dòng nào bằng 4, 6
SELECT * INTO T1_Triple5
FROM T1
UPDATE T1_Triple5 SET data1 = 5 WHERE data1 IN ( 4,6)
Sau đó chúng ta dùng các hàm thống kê SQL như chúng ta đã phân tích USE labdb01
GO
SELECT MIN(data1) NhoNhat, MAX(data1) LonNhat, COUNT(data1) TongSoDong, COUNT(DISTINCT data1) TongSoDongDuyNhat FROM T1
SELECT MIN(data1) NhoNhat, MAX(data1) LonNhat, COUNT(data1) TongSoDong, COUNT(DISTINCT data1) TongSoDongDuyNhat FROM T1_LowX
SELECT MIN(data1) NhoNhat, MAX(data1) LonNhat, COUNT(data1) TongSoDong, COUNT(DISTINCT data1) TongSoDongDuyNhat FROM T1_HighX
SELECT MIN(data1) NhoNhat, MAX(data1) LonNhat, COUNT(data1) TongSoDong, COUNT(DISTINCT data1) TongSoDongDuyNhat FROM T1_LessX
SELECT MIN(data1) NhoNhat, MAX(data1) LonNhat, COUNT(data1) TongSoDong, COUNT(DISTINCT data1) TongSoDongDuyNhat FROM T1_MoreX
SELECT MIN(data1) NhoNhat, MAX(data1) LonNhat, COUNT(data1) TongSoDong, COUNT(DISTINCT data1) TongSoDongDuyNhat FROM T1_Double5
SELECT MIN(data1) NhoNhat, MAX(data1) LonNhat, COUNT(data1) TongSoDong, COUNT(DISTINCT data1) TongSoDongDuyNhat FROM T1_Triple5
Kết quả chúng ta thu được
Chúng ta có tổng cộng 10K dòng, chạy từ 1 đến 10000 nên mỗi giá trị trong khoảng này phải là duy nhất. Bảng T1_LowX có giá trị nhỏ nhất là 0 -> loại. Bảng T1_HighX có giá trị lớn nhất là 10001 -> loại. Bảng T1_LessX có tổng số dòng không đủ 10K -> loại. Bảng T1_MoreX có tổng số dòng hơn 10K -> loại. Bảng T1_Double5 với T1_Tripple5 mặc dù có đủ 10K dòng và giá trị đầu với cuối đều giống mong đợi, nhưng các giá trị nằm trong khoảng giữa lại nhảy loạn xạ không đủ số lượng giá trị duy nhất -> loại. Chỉ có bảng T1 là thỏa mãn điều kiện của bài toán. Sử dụng hàm ranking trong SQLChúng ta có thể sử dụng hàm ranking windows function để đạt được mục đích tương tự như vậy. Với ý tưởng sắp xếp cột data1 theo thứ tự tăng dần và đánh số từ 1 tới 10000, kết quả thu được sẽ là cột thứ tự với giá trị bằng giá trị cột data1. USE labdb01
GO
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1
)
SELECT *
FROM ttmp
Kết quả sẽ như hình dưới đây Vậy chúng ta chỉ cần xét điều kiện tồn tại dòng dữ liệu mà cột data1 khác với cột Num thì bảng đó không thỏa điều kiện. USE labdb01
GO
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1
)
SELECT TOP 1 'T1',* FROM ttmp WHERE data1 <> Num
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1_LowX
)
SELECT TOP 1 'T1_LowX',* FROM ttmp WHERE data1 <> Num
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1_HighX
)
SELECT TOP 1 'T1_HighX',* FROM ttmp WHERE data1 <> Num
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1_LessX
)
SELECT TOP 1 'T1_LessX',* FROM ttmp WHERE data1 <> Num
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1_MoreX
)
SELECT TOP 1 'T1_MoreX',* FROM ttmp WHERE data1 <> Num
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1_Double5
)
SELECT TOP 1 'T1_Double5',* FROM ttmp WHERE data1 <> Num
;WITH ttmp AS
(
SELECT *, ROW_NUMBER() OVER(ORDER BY data1 ASC) AS Num
FROM T1_Triple5
)
SELECT TOP 1 'T1_Triple5',* FROM ttmp WHERE data1 <> Num
Những bảng nào xuất hiện bất kì dòng dữ liệu có cột data1 không khớp với Num chứng tỏ không thỏa mãn điều kiện bài toán. Chúng ta thấy kết quả câu truy vấn ứng với bảng T1 không có dòng nào -> đây là bảng thỏa điều kiện dữ liệu cột data1 chạy từ 1 đến 10000 và không bị gián đoạn, giống với kết quả ở trên khi sử dụng các hàm thống kê. Share this:Like this:Like Loading... Related |
