Giải phẫu các hàm cửa sổ SQL

Để hiểu dữ liệu doanh nghiệp; bạn phải truy vấn nó rất nhiều. Khi tôi nói 'Rất nhiều', tôi có ý đó. Làm việc với hàng đống dữ liệu không quen thuộc thường rất khó khăn và bạn nên dành thời gian để khám phá và hiểu chính dữ liệu đó. Thật tốt khi có các kỹ năng truy xuất dữ liệu cơ bản nhưng biết các chức năng phân tích để rút ra một số thông tin chi tiết hữu ích từ dữ liệu của bạn là điều tuyệt vời và nó cũng rất thú vị!

Tôi đến từ nền tảng trực quan hóa dữ liệu và điều quan trọng đối với tôi là không chỉ hiểu dữ liệu mà còn tìm ra bất kỳ phát hiện đáng chú ý nào để làm nổi bật dữ liệu đó cho các nhóm rộng hơn. Ngoài ra, việc xây dựng các bảng điều khiển phức tạp thường là một quy trình qua lại khi bạn quay lại nguồn dữ liệu của mình để kiểm đếm dữ liệu và các Hàm cửa sổ SQL luôn đồng hành cùng tôi trong hành trình phân tích dữ liệu của mình.

Mặc dù chúng rất hữu ích cho việc phân tích dữ liệu, nhưng vẫn có một số nhầm lẫn và mọi người thường sợ sử dụng chúng. Trong khi viết hướng dẫn chi tiết về Hàm cửa sổ SQL, tôi nhận ra rằng nó đã trở nên quá mô tả và tôi không muốn bỏ qua các chi tiết, đặc biệt là về cú pháp và các mệnh đề được sử dụng cùng với nó. Điều quan trọng là phải hiểu các khối xây dựng, phải không? Vì vậy, tôi sẽ cố gắng chia nhỏ các khối xây dựng của Chức năng cửa sổ trong bài viết này để việc xử lý và triển khai nó không quá khó khăn.

Như thường lệ, chúng tôi sẽ sử dụng cơ sở dữ liệu mẫu MySQL của classicmodels để trình diễn, cơ sở dữ liệu này chứa dữ liệu kinh doanh của một nhà bán lẻ ô tô. Dưới đây là Sơ đồ ER để tham khảo,

Điều đầu tiên trước tiên, Chức năng cửa sổ là gì?

Định nghĩa trong sách giáo khoa về Hàm cửa sổ là,

Hàm Cửa sổ thực hiện tính toán trên một tập hợp các hàng của bảng có liên quan nào đó đến hàng hiện tại.

Bạn nghĩ anh bạn nhỏ này nhìn thấy gì từ cửa sổ? Một góc nhìn từ khung cảnh bên ngoài cửa sổ của căn phòng hoặc tòa nhà này. Phải? Đó chính xác là chức năng của Cửa sổ. Nó cho phép bạn thực hiện tính toán đối với một tập hợp con dữ liệu mà không cần tổng hợp các hàng hiện tại.

nhu cầu là gì? Tại sao chức năng cửa sổ? Chức năng tổng hợp bị tụt lại phía sau ở đâu?

Đây là dữ liệu mẫu từ bảng PRODUCTS , vì mục đích minh họa, tôi đã giới hạn nó ở PRODUCTLINEs - Planes , Ships and Trains .

--sample data from table PRODUCTS.
SELECT
    *
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

--total quantity in stock for each productline
SELECT 
    PRODUCTLINE,
    SUM(QUANTITYINSTOCK) AS TOTAL_QUNATITY
FROM 
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
GROUP BY PRODUCTLINE;

      
                

                    
                    
                
            

Hãy cải tiến yêu cầu ngay bây giờ,

1. Hiển thị số lượng của từng sản phẩm trong PRODUCTLINE cùng với tổng số lượng trong kho cho PRODUCTLINE cụ thể đó .
2. Sắp xếp tập hợp kết quả được nhóm theo PRODUCTLINE.

--sum() as a window function
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Các loại chức năng cửa sổ

Thành thật mà nói, không có phân loại chính thức về Chức năng cửa sổ nhưng dựa trên cách sử dụng, chúng ta có thể phân loại ngắn gọn chúng theo 3 cách,

• Hàm tổng hợp - Hàm tổng hợp thông thường có thể được sử dụng làm Hàm cửa sổ để tính toán tổng hợp cho các cột số trong phân vùng cửa sổ, chẳng hạn như chạy tổng doanh số, giá trị tối thiểu hoặc tối đa trong phân vùng, v.v.
• Hàm xếp hạng - Các hàm này trả về giá trị xếp hạng cho mỗi hàng trong một phân vùng.
• Hàm giá trị - Các hàm này hữu ích để tạo số liệu thống kê đơn giản hoặc phân tích chuỗi thời gian.

Cú pháp phổ biến của Hàm cửa sổ là,

Trước khi chúng ta đi sâu hơn vào nó; trước tiên hãy hiểu ý nghĩa của từng mệnh đề trong đó,

OVER() Mệnh đề

OVER() chỉ định một hàm dưới dạng Hàm cửa sổ và do đó nó phải luôn được đưa vào câu lệnh. Nó xác định một tập hợp con (một cửa sổ) do người dùng chỉ định gồm các hàng mà Chức năng Cửa sổ sẽ được áp dụng trên đó. Nếu bạn không cung cấp bất kỳ thứ gì bên trong OVER() , Hàm Cửa sổ sẽ được áp dụng trên toàn bộ tập hợp kết quả.

Tiếp tục với ví dụ trên,

--empty OVER() clause
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER () AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

PHÂN VÙNG BẰNG ĐIỀU KHOẢN

PARTITION BY được dùng với mệnh đề OVER . Nó chia tập kết quả truy vấn thành các phân vùng hoặc nhóm dựa trên biểu thức do người dùng chỉ định và sau đó Hàm cửa sổ áp dụng cho từng phân vùng hoặc nhóm.

Đây là tùy chọn, vì vậy nếu bạn không chỉ định mệnh đề PARTITION BY thì hàm sẽ coi tất cả các hàng là một phân vùng duy nhất. Chính xác như những gì chúng tôi đã làm trong ví dụ trên, chúng tôi chỉ cần chỉ định một mệnh đề OVER() trống mà không có mệnh đề PARTITION BY và do đó tổng số lượng được tính cho tất cả các PRODUCTLINE s.

Điều gì xảy ra nếu chúng ta chỉ định một,

--OVER() with PARTITION BY
SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE) AS TOTAL_QUANTITY
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Bây giờ, thỏa thuận với mệnh đề PARTITION BY và GROUP BY là gì ? Chúng giống nhau hay khác nhau?

NHÓM THEO mệnh đề,

• Nó nhóm nhiều hàng thành các hàng tóm tắt dựa trên một hoặc nhiều cột/biểu thức (trả về 1 hàng cho mỗi nhóm). Nói một cách đơn giản hơn, nó làm giảm số lượng hàng trong tập hợp kết quả của bạn.
• Nó được sử dụng cùng với các Hàm tổng hợp như SUM(), AVG(), MAX(), v.v.
• Nó được đặt sau mệnh đề WHERE nhưng trước mệnh đề HAVING, ORDER BY . Cú pháp phổ biến là,

• Nó được sử dụng với mệnh đề OVER() trong Window Function. Nó chia tập kết quả truy vấn thành các phân vùng và sau đó Chức năng Cửa sổ áp dụng trên mỗi phân vùng.
• PARTITION BY tương tự như GROUP BY vì nó tổng hợp kết quả dựa trên biểu thức; tuy nhiên, sự khác biệt chính là, nó sẽ không làm giảm các hàng của tập hợp kết quả.
• Đây là tùy chọn, vì vậy nếu bạn không chỉ định mệnh đề PARTITION BY thì hàm sẽ coi tất cả các hàng là một phân vùng duy nhất.
• Cú pháp phổ biến là,

ĐẶT HÀNG THEO Mệnh đề

Nó được sử dụng để sắp xếp tập kết quả theo thứ tự tăng dần hoặc giảm dần trong mỗi phân vùng của tập kết quả. Theo mặc định, nó theo thứ tự tăng dần.

ROWS/RANGE Mệnh đề

Bây giờ chúng ta đã biết rằng tính năng chính của Hàm cửa sổ là tạo một cửa sổ hoặc một phân vùng của tập kết quả bằng cách sử dụng mệnh đề PARTITION BY và sau đó thực hiện các phép tính trên mỗi phân vùng. Điều gì sẽ xảy ra nếu chúng ta muốn tạo thêm các tập hợp con trong các phân vùng này? Ồ! phân vùng trong phân vùng? Vâng, đó là lý do tại sao chúng ta có mệnh đề FRAME .

Mệnh đề FRAME xác định thêm một tập hợp con của phân vùng hiện tại. Nó sử dụng ROW hoặc RANGE để xác định điểm bắt đầu và điểm kết thúc của tập hợp con này. Nó yêu cầu mệnh đề ORDER BY .

Các khung được xác định đối với hàng hiện tại, điều này đơn giản có nghĩa là bạn lấy vị trí của hàng hiện tại làm điểm cơ sở và với tham chiếu đó, bạn xác định khung của mình trong phân vùng.

• HÀNG — Điều này xác định phần đầu và phần cuối của khung bằng cách chỉ định số lượng hàng trước hoặc sau hàng hiện tại.
• RANGE — Trái ngược với ROWS , RANGE chỉ định phạm vi giá trị so với giá trị của hàng hiện tại để xác định khung trong phân vùng.

{ HÀNG | PHẠM VI} GIỮA <frame_starting> VÀ <frame_ending>

Trước khi tiếp tục, hãy hiểu một số thuật ngữ cơ bản xác định khung.

• TRƯỚC KHÔNG GIỚI HẠN - Điều này chỉ định tất cả các hàng (bắt đầu từ hàng đầu tiên) trước hàng hiện tại trong phân vùng.
• N TRƯỚC - Điều này chỉ định số lượng 'N' hàng trước hàng hiện tại của bạn trong phân vùng.
• SAU KHÔNG GIỚI HẠN - Điều này chỉ định tất cả các hàng sau hàng hiện tại của bạn (tất cả các hàng đến hàng cuối cùng) trong phân vùng.
• M SAU - Điều này chỉ định số hàng 'M' bên dưới hàng hiện tại của bạn trong phân vùng.

SELECT 
    PRODUCTNAME,
    PRODUCTLINE,
    QUANTITYINSTOCK,
    SUM(QUANTITYINSTOCK) OVER (PARTITION BY PRODUCTLINE ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS TOTAL
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

      
                

                    
                    
                
            

Dưới đây là một số ví dụ về mệnh đề FRAME ,

• ROWS GIỮA TRƯỚC KHÔNG GIỚI HẠN VÀ SAU KHÔNG GIỚI HẠN — Điều này có nghĩa là xem xét khung từ hàng đầu tiên của phân vùng đến hàng cuối cùng của phân vùng.
• HÀNG GIỮA KHÔNG GIỚI HẠN TRƯỚC VÀ 4 HÀNG SAU - Điều này có nghĩa là xem xét khung từ hàng đầu tiên của phân vùng đến hàng thứ 4 sau hàng hiện tại.
• HÀNG GIỮA 4 HÀNG TRƯỚC VÀ 1 HÀNG TRƯỚC - Khung sẽ là 4 hàng trước đó cho đến 1 hàng trước hàng hiện tại.

{ROWS/RANGE} GIỮA HÀNG TRƯỚC KHÔNG GIỚI HẠN VÀ HÀNG HIỆN TẠI

Điều này có nghĩa là coi khung là tất cả các hàng bắt đầu từ hàng số một đến hàng hiện tại trong phân vùng.

Không có mệnh đề ORDER BY , khung mặc định là,

{ROWS/RANGE} GIỮA TRƯỚC KHÔNG GIỚI HẠN VÀ SAU KHÔNG GIỚI HẠN

Điều này đơn giản có nghĩa là toàn bộ phân vùng.

Xác định bí danh cửa sổ,

Nếu có nhiều hàm cửa sổ trong truy vấn của bạn sử dụng cùng một cửa sổ, thì bạn có thể muốn sử dụng bí danh cửa sổ.

--finding out minimum and maximum MSRP for each productline
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MIN_MSRP,
    MAX(MSRP) OVER(PARTITION BY PRODUCTLINE) AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains');

--using window alias
SELECT
    PRODUCTNAME,
    PRODUCTLINE,
    MSRP,
    MIN(MSRP) OVER MSRP_WINDOW AS MIN_MSRP,
    MAX(MSRP) OVER MSRP_WINDOW AS MAX_MSRP
FROM
    CLASSICMODELS.PRODUCTS
WHERE PRODUCTLINE IN ('Planes','Ships','Trains')
WINDOW MSRP_WINDOW AS (PARTITION BY PRODUCTLINE);

Trong quá trình thực hiện truy vấn, các Hàm cửa sổ được thực hiện trên tập kết quả,

• Sau các mệnh đề JOIN , WHERE , GROUP BY và HAVING và
• Trước mệnh đề ORDER BY , LIMIT và SELECT DISTINCT .

Bạn có thể muốn khám phá dữ liệu từ 100 cách khác nhau và Chức năng Cửa sổ phù hợp với phân tích đó. Bài viết này chỉ là phần mở đầu để hiểu các mệnh đề và cú pháp cơ bản để nó không quá áp đảo với Window Function, nó chắc chắn sẽ tốt hơn khi thực hành.

• Bảng gian lận chức năng cửa sổ SQL
• Điều khoản khung
• HackerRank hoặc LeetCode để thực hành các bài toán SQL cơ bản/trung cấp/nâng cao.