“ Bảng T1 có cấu trúc và được insert 10 k dòng tài liệu như hình ở phần comment. Nếu tất cả chúng ta được nhu yếu kiểm tra xem giá trị cột data1 có liên tục, 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 tất cả chúng ta được nhu yế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ì tất cả chúng ta nên viết câu truy vấn như thế nào để chứng minh và khẳng định được điều này ? Ok, thứ nhất tất cả chúng ta hãy liệt kê một vài trường hợp tài liệu không như nhu yế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 tài 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à tài liệu như diễn đạ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 đó tất cả chúng ta dùng các hàm thống kê SQL như tất cả chúng ta đã nghiên cứu và 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ả tất cả chúng ta thu được
BảngNhỏ NhấtLớn NhấtTổng Số DòngTổng Số Dòng
Khác BiệtT11100001000010000T1_LowX0100001000010000T1_HighX1100011000010000T1_LessX11000099999999T1_MoreX1100001000110000T1_Double5110000100009999T1_Triple5110000100009998Kế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 hoàn toàn có thể sử dụng hàm ranking windows function để đạt được mục tiêu tựa như như vậy. Với sáng tạo độc đáo sắp xếp cột data1 theo thứ tự tăng dần và đánh số từ 1 tới 10000, hiệu 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 T1Vậy tất cả chúng ta chỉ cần xét điều kiện kèm theo sống sót dòng tài liệu mà cột data1 khác với cột Num thì bảng đó không thỏa điều kiện kèm theo .
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 có cột data1 không khớp với Num.
Những bảng nào Open bất kỳ dòng tài liệu có cột data1 không khớp với Num chứng tỏ không thỏa mãn nhu cầu điều kiện kèm theo bài toán. Chúng ta thấy tác dụng 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 kèm theo tài liệu cột data1 chạy từ 1 đến 10000 và không bị gián đoạn, giống với tác dụng ở trên khi sử dụng các hàm thống kê .
Share this:
Like this:
Like
Loading ...
Related
Source: kubet
Category: Tải Phầm Mềm
Leave a Reply