You are currently viewing Đố vui SQL 2: Sử dụng các hàm thống kê SQL (statistical aggregate functions)

Đố vui SQL 2: Sử dụng các hàm thống kê SQL (statistical aggregate functions)

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ê SQL

Có 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:

  • Bảng có giá trị data1 nhỏ hơn 1 hoặc lớn hơn 10000, chúng ta có thể sử dụng hàm MIN(), MAX() để xác định tình huống này không thỏa điều kiện
  • Bảng có nhiều hơn hoặc ít hơn 10K dòng, chúng ta có thể sử dụng hàm COUNT() để loại bỏ.
  • Bảng có đủ 10k dòng, tất cả dữ liệu cột data1 đều nằm trong khoảng 1-10000, tình huống này các hàm MIN(), MAX(), COUNT() chưa đủ để xác định thỏa điều kiện hay không.
    • Nếu cột data1 thiếu một giá trị nào đó, thì sẽ có một giá trị khác xuất hiện hai lần, chúng ta có thể sử dụng COUNT DISTINCT để loại bỏ tình huống này.

Chúng ta sẽ tạo dữ liệu mẫu để kiểm chứng những điều trên

  • Bảng có 10K dòng, từ 0-10000, khuyết gia trị 1
  • Bảng có 10K dòng, từ 1-10001, khuyết giá trị 10000
  • Bảng có 9999 dòng, từ 1-10000, khuyết giá trị 2
  • Bảng có 10K dòng + 1, 1-10000, hai dòng trùng giá trị 10000
  • Bảng có 10k dòng, và giá trị cột data1 đủ từ 1 tới 10000
  • 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
  • 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

Đ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

BảngNhỏ NhấtLớn NhấtTổng Số DòngTổng Số Dòng
Khác Biệt
T11100001000010000
T1_LowX0100001000010000
T1_HighX1100011000010000
T1_LessX11000099999999
T1_MoreX1100001000110000
T1_Double5110000100009999
T1_Triple5110000100009998
Kết quả áp dụng các hàm thống kê SQL trên bảng tương ứng.

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 SQL

Chú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

Kết quả hàm ROW_NUMBER() trên bảng T1

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
 

hàm ranking vs các hàm thống kê SQL
Những bảng có cột data1 không khớp với 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ê.

Gửi phản hồi