Tài liệu học SQL: Sự khác biệt giữa Window Functions và GROUP BY

Trong tài liệu học SQL lần này, Datapot xin gửi tới bạn chủ đề Window Functions và GROUP BY là hai khái niệm dễ gây nhầm lẫn trong SQL. Điểm chung giữa chúng là đều cho phép người dùng thực hiện các tính toán (MIN, MAX, COUNT, SUM, …) theo các nhóm. Tuy nhiên, hai khái niệm này có nhiều điểm khác biệt rõ rệt.

Định nghĩa Window Functions và GROUP BY

GROUP BY

Mệnh đề GROUP BY cho phép nhóm một tập hợp các hàng dựa trên một số tiêu chí và áp dụng một hàm tổng hợp (aggregate function) (ví dụ: MIN, AVG, …) cho mỗi nhóm, thu được một kết quả cho mỗi nhóm bản ghi.

• Cú pháp

SELECT column1, Aggregate Functions(column2)

FROM table_name

WHERE [condition]

GROUP BY column1

Window Function

Window Function (hay Analytics Function) là hàm mà tính toán các giá trị trên một nhóm hàng và trả về một kết quả cho mỗi hàng.

• Cú pháp

Cú pháp đầy đủ:

analytic_function_name ( [ argument_list ] ) OVER over_clause

Trong đó:

over_clause:

{ named_window | ( [ window_specification ] ) }

window_specification:

[ named_window ]

[ PARTITION BY partition_expression [, ...] ]

[ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]

[ window_frame_clause ]

window_frame_clause:

{ rows_range } { frame_start | frame_between }

rows_range:

{ ROWS | RANGE }

Cú pháp thông thường:

analytic_function_name ( [ argument_list ] ) over ([ PARTITION BY partition_expression [, ...] ] [ ORDER BY expression [ { ASC | DESC }  ] [, ...] ])

Sự khác biệt giữa Window Functions và GROUP BY

Để so sánh sự khác nhau giữa Window Functions và GROUP BY, ta có ví dụ minh hoạ như sau

Bài toán: Từ bảng FactInternetSales và DimSalesTerritory,

–     Tính tổng doanh thu bán qua Internet của từng nhóm lãnh thổ (SalesTerritoryGroup)

–     Tính tỷ lệ đóng góp của từng khu vực (SalesTerritoryRegion) trong mỗi nhóm lãnh thổ đó.

Với yêu cầu đầu tiên, ta có thể thực hiện bằng aggregate function và GROUP BY như sau:

SELECT dst.SalesTerritoryGroup

, SUM(fis.SalesAmount) AS SalesByGroup

FROM FactInternetSales AS fis

LEFT JOIN DimSalesTerritory AS dst ON fis.SalesTerritoryKey = dst.SalesTerritoryKey

GROUP BY dst.SalesTerritoryGroup

Kết quả trả về gồm 3 dòng:

Với truy vấn này, ta không thể thêm các trường khác, chẳng hạn như SalesTerritoryRegion, vì như vậy doanh thu sẽ được tính theo Region chứ không còn là Group nữa.

Nếu thực hiện bằng Window Function:

SELECT dst.SalesTerritoryGroup

, SUM(fis.SalesAmount) OVER (PARTITION BY dst.SalesTerritoryGroup) AS SalesByGroup

FROM FactInternetSales AS fis

LEFT JOIN DimSalesTerritory AS dst ON fis.SalesTerritoryKey = dst.SalesTerritoryKey

Kết quả trả về 60398 dòng như sau:

Với truy vấn này, ta có thể thêm các trường khác như SalesTerritoryRegion, SalesTerritoryCountry, cho phép dễ dàng tham chiếu đến những thông tin đó mà không làm ảnh hưởng đến các tính toán vừa thực hiện.

Dễ dàng nhận thấy giữa Window Functions và GROUP BY thì GROUP BY làm giảm số dòng xuống đúng bằng số nhóm được nhóm lại, trong khi Window Function giữ nguyên số dòng ban đầu.

Với yêu cầu thứ hai, để thực hiện bằng GROUP BY, trước hết ta phải viết subquery hoặc CTE để tạo ra hai bảng tính doanh thu theo từng khu vực và từng nhóm lãnh thổ rồi join với nhau.

SELECT ct2.*

, ct1.SalesByGroup

, FORMAT(ct2.SalesByRegion/ct1.SalesByGroup, 'p') AS Contribution

FROM

     (

     SELECT dst.SalesTerritoryGroup

     , SUM(fis.SalesAmount) AS SalesByGroup

     FROM FactInternetSales AS fis

     LEFT JOIN DimSalesTerritory AS dst ON fis.SalesTerritoryKey = dst.SalesTerritoryKey

     GROUP BY dst.SalesTerritoryGroup

     ) AS ct1

JOIN

     (

     SELECT dst.SalesTerritoryRegion

     , dst.SalesTerritoryGroup

     , SUM(fis.SalesAmount) AS SalesByRegion

     FROM FactInternetSales AS fis JOIN DimSalesTerritory AS dst ON fis.SalesTerritoryKey = dst.SalesTerritoryKey

     GROUP BY dst.SalesTerritoryRegion

     , dst.SalesTerritoryGroup

     ) AS ct2

    ON ct1.SalesTerritoryGroup = ct2.SalesTerritoryGroup

Với Window Function, đầu tiên ta viết một subquery hoặc CTE để tạo ra bảng tính doanh thu theo khu vực, mục đích để tính toán cuối cùng thực hiện trên bảng đó.

WITH cte AS

     (

     SELECT dst.SalesTerritoryRegion

     , dst.SalesTerritoryGroup

     , SUM(fis.SalesAmount) AS SalesByRegion

     FROM FactInternetSales AS fis join DimSalesTerritory AS dst ON fis.SalesTerritoryKey = dst.SalesTerritoryKey

     GROUP BY dst.SalesTerritoryRegion

     , dst.SalesTerritoryGroup

     )

SELECT SalesTerritoryRegion

, SalesTerritoryGroup

, SalesByRegion

,SUM(cte.SalesByRegion) OVER (PARTITION BY SalesTerritoryGroup) AS SalesByGroup

, FORMAT(SalesByRegion/SUM(cte.SalesByRegion) OVER (PARTITION BY SalesTerritoryGroup), 'p') AS Contribution

FROM cte

Cả Window Functions và GROUP BY cho ra cùng một kết quả gồm 10 dòng như sau:

Ở truy vấn dùng Window Function, nếu không dùng CTE mà tính toán trực tiếp trên các bảng ban đầu thì kết quả sẽ gồm 60398 dòng như trên do tính chất của Window Function là giữ nguyên số dòng.

Kết luận

Window Functions và GROUP BY có các điểm khác biệt như sau:

• Window Function tính toán giá trị cho từng dòng, trong khi GROUP BY thì không.
• GROUP BY làm giảm số dòng ban đầu xuống bằng đúng số nhóm, Window Function thì giữ nguyên số dòng.
• Vì tính chất trên, có thể dùng Window Function thể thêm tính toán phân theo nhóm vào bảng ban đầu trong khi nếu dùng GROUP BY thì cần các cách xử lý khác, chẳng hạn như JOIN.

Trên đây là tài liệu học SQL về WINDOW FUNCTIONS mà chúng mình muốn gửi đến các bạn. Hãy truy cập vào phần tài liệu của chúng mình để biết thêm những kiến thức mới về Power BI, SQL,… Ngoài ra Datapot còn có các khóa học chuyên về SQL bạn có thể xem thêm tại đây.

https://datapot.vn/khoa-hoc/data-analytics-foundation/

DP-080: Querying Data with Microsoft T-SQL