Đã đăng vào thg 5 13, 9:16 SA 24 phút đọc

133

Unlock bộ đồ nghề của anh em lên +8 với SQL Window Functions (#1)

MayFest2024

Trong làn gió của mùa hè năm nay, chúng ta lại một lần nữa được chứng kiến sức mạnh của sự đoàn kết và sự phát triển của cộng đồng VIBLO. Với lòng nhiệt huyết và sự hỗ trợ không ngừng từ nhà tài trợ uy tín CMC GLOBAL, chúng ta đã có cơ hội tạo nên một sân chơi chia sẻ kiến thức đầy bổ ích - MAY FEST 2024 trên nền tảng VIBLO. Mỗi năm, MAY FEST không chỉ đánh dấu một ngày lễ của cộng đồng lập trình viên mà còn là một dịp quý báu để chúng ta cùng nhau tận hưởng và chia sẻ kiến thức, kinh nghiệm. Với sự tài trợ của CMC GLOBAL, chúng ta đã có thêm cơ hội mở rộng tầm nhìn, đẩy mạnh sự phát triển cá nhân và chung của mọi thành viên. Chúng ta không chỉ tạo ra một sự kiện, mà chúng ta còn tạo ra một cộng đồng - nơi mà sự sáng tạo và kiến thức được lan tỏa, nơi mà mọi người đều có cơ hội để phát triển và thành công. Cùng nhau, chúng ta hãy chào đón một MAY FEST 2024 ấn tượng, đong đầy tinh thần và kỷ niệm không thể nào quên nhé! ♥️

Hí anh em, hôm nay anh em hãy Unlock hết khả năng của SQL Window Functions cùng tôi nhé. Bài viết này tôi sẽ đi từ cơ bản đến nâng cao nhằm để anh em có được hướng tiếp cận dễ dàng nhất.

Created by DALL-E3

Khái quát về SQL Window Functions

Window Functions là gì?

Trong lĩnh vực quản lý cơ sở dữ liệu (Database Management) và phân tích dữ liệu (Data Analysis), việc tiếp cận với những kiến thức phức tạp đôi khi giống như một nhà thám hiểm đang đi vào vùng đất chưa được khám phá. Sẽ có một sự khó khăn nhất định trong việc khai thác các insight và rút ra những điều có ý nghĩa từ dữ liệu thô. SQL, với bộ công cụ mạnh mẽ của nó, đóng vai trò như la bàn định hướng trong hành trình khám phá này.

Trên đường đi, chúng ta thường gặp phải những rào cản hoặc thách thức lớn dường như khó giải quyết bằng các công cụ hoặc kỹ thuật hiện có trong "thùng đồ nghề" của mình. Một trường hợp như vậy mà nhiều người trong chúng ta gặp phải là hạn chế của Aggregation Functions được sử dụng với các phép toán GROUP BY.

Nghe qua thì SQL Window Functions có vẻ chỉ là một tập lệnh khác trong kho lệnh đồ sộ của SQL. Tuy nhiên, chúng ẩn chứa sức mạnh tiềm ẩn, giống như một cây cọ vẽ vô hình, biến những hàng dữ liệu thành một bức tranh với vô vàn khả năng. Hôm nay, chúng ta sẽ cùng nhau khám phá những hàm mạnh mẽ này, từng bước gỡ bỏ các lớp lang và nhìn thấy được hiệu quả mà chúng mang lại cho việc phân tích dữ liệu.

Giải mã Window Functions

Hãy tưởng tượng bạn đang ngồi trên một chiếc xe buýt tham quan, nhìn ra ngoài cửa sổ. Bạn nhìn thấy mọi thứ lần lượt, phải không? SQL Window Functions hoạt động hơi giống như vậy. Chúng xem xét dữ liệu của bạn từng dòng một, nhưng chúng nhớ những gì chúng đã thấy trước đó và những gì sắp tới. Nó giống trí nhớ ngắn hạn (Short-term memory) nhưng siêu phàm hơn của con người vậy.

Created by DALL-E3

Function: Đây có thể là hàm SUM, AVG, MAX hoặc bất kỳ hàm nào khác bạn cần. Thường thì đây là trung tâm của phép toán mà bạn muốn thực hiện! Chúng tương tự như các hàm tổng hợp thông thường nhưng không làm giảm số lượng hàng được trả về.
OVER: là nền tảng của SQL Window Functions. Câu lệnh này cho phép chúng ta chỉ định một Window hoặc một tập dữ liệu con mà hàm sẽ xử lý.
PARTITION BY: Nếu bạn muốn thực hiện các tính toán trên các khối dữ liệu cụ thể, PARTITION BY là cách bạn báo cho SQL phân chia dữ liệu. Nếu không chỉ định PARTITION BY, hàm sẽ xử lý tất cả các dòng của tập kết quả truy vấn thành một phân vùng duy nhất. Nó hoạt động tương tự như mệnh đề GROUP BY, nhưng trong khi GROUP BY tổng hợp dữ liệu, PARTITION BY thì không, nó chỉ đơn giản là nhóm dữ liệu cho mục đích của Window Functions.
ORDER BY: Sắp xếp các dòng trong mỗi phân vùng. Nếu không chỉ định ORDER BY, hàm sẽ xử lý tất cả các dòng của phân vùng thành một nhóm duy nhất.

Dưới đây là một đoạn lệnh tổng quát của Window Functions trong SQL:

SELECT column_name, 
       WINDOW_FUNCTION(column_name) OVER (
           PARTITION BY column_name 
           ORDER BY column_name 
           RANGE/ROWS BETWEEN ... AND ...
       ) 
FROM table_name;

Tuyệt vời! Bây giờ bạn đã có cái nhìn tổng quan từ trên xuống dưới về cách hoạt động của Window Functions. Tất nhiên, chúng ta sẽ cần xem một vài ví dụ cơ bản để hiểu rõ hơn về nó.

Ví dụ ở đây

Giả sử chúng ta có một bảng dữ liệu bán hàng đơn giản và các chi tiết dòng cho dữ liệu bán hàng này.

Running Totals

SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  SaleDate, 
  SUM(SaleAmount) OVER (ORDER BY SaleDate) AS RunningTotal
FROM Sales;

Kết quả thu được:

Thuật toán này thường được gọi là tính tổng tích lũy (cumulative sum) và được sử dụng để tính toán giá trị tổng cộng theo thứ tự của một cột dữ liệu. Nó hữu ích để theo dõi các giá trị như doanh số bán hàng theo thời gian, số lượng click chuột trong nhật ký truy cập web, hoặc lợi nhuận ròng trong bảng tính tài chính. Ví dụ trong bảng kết quả, cột mới được thêm vào là RunningTotal. Cột này hiển thị tổng doanh số tính đến ngày hiện tại (theo SaleDate).

Cumulative Totals

Bây giờ, giả sử chúng ta muốn xem hiệu suất của từng thành viên trong nhóm bán hàng thay đổi như thế nào theo thời gian? Theo dõi các con số (chẳng hạn như Chỉ tiêu) trong nhóm bán hàng là rất quan trọng, vì vậy chúng ta có thể có một yêu cầu khác là thực sự tính toán một thứ gì đó giống như Cumulative Totals cho từng người trong nhóm chứ không phải toàn bộ tập dữ liệu. Làm thế nào để chúng ta có thể tiếp cận vấn đề này?

Hãy xem xét mã và kết quả trước tiên, mọi thứ sẽ trở nên rõ ràng hơn. Nhưng trước tiên, hãy xem bạn có thể phát hiện ra những thay đổi nào trong đoạn mã này so với ví dụ trước đó không.

SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  SaleDate, 
  SUM(SaleAmount) OVER (PARTITION BY Salesperson ORDER BY SaleDate) AS CumulativeSalePerPerson
FROM Sales;

Kết quả thu được:

Nếu chúng ta tập trung vào trường dữ liệu mới “CumulativeSalePerPerson” (Tổng doanh số tích lũy theo nhân viên), chúng ta sẽ thấy mẫu này hơi khó phân biệt, nhưng đến dòng thứ ba thì mọi thứ trở nên rõ ràng hơn nhiều. "Alice" có doanh số đầu tiên trong "Hàng 1" là "300" sau đó cô ấy có thêm một doanh số khác là "200" trong "Hàng 3", vì vậy tổng doanh số tích lũy của cô ấy tại thời điểm đó là "500". Tương tự, Bob có doanh số được hiển thị ở "Hàng 2" và "Hàng 5", đó là lý do tại sao anh ấy không đạt được "450" cho đến "Hàng 5", nơi anh ấy đạt được doanh số "300" để cộng vào "150" trước đó. Thật đơn giản! Hãy tưởng tượng việc bạn phải vắt óc suy nghĩ về cách chúng ta có thể làm điều này với một truy vấn SQL thông thường, điều đó sẽ là IMPOSSIBLE!

Moving Average of SalesAmount

Đối với một team Sales, việc theo dõi các xu hướng chung mà cả team đang hướng tới để đạt được chỉ tiêu bán hàng là rất quan trọng. Nếu bạn đang tìm kiếm xu hướng thay vì tổng số, đường Moving Average sẽ làm giảm thiểu các biến động hàng ngày và làm nổi bật hướng đi chung của doanh số (này tôi thường dùng để vẽ biểu đồ line cho smooth hơn nè). Giống như việc bạn lùi lại khỏi một bức tranh để nhìn toàn cảnh, thay vì tập trung vào từng nét vẽ riêng lẻ.

SELECT SaleID, SaleDate, Salesperson, SaleAmount, 
       AVG(SaleAmount) OVER (
                    ORDER BY SaleDate 
                    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
                            ) AS MovingAverage
FROM Sales;

Kết quả thu được:

Tôi đã sử dụng ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING để xem xét ngày hôm trước và ngày hôm sau của mỗi dòng. Bây giờ chúng ta đã thực hiện một vài vòng lặp với các hàm cửa sổ: câu hỏi quan trọng mà bạn có thể tự hỏi mình là ...

'Why does it matter? 🤔'

Sự khác biệt chính giữa Window Functions và Aggregation Functions theo GROUP BY là trong khi Aggregation Functions trả về một kết quả duy nhất cho mỗi nhóm (giống như SUM hoặc AVG của một nhóm), thì Window Functions sẽ trả về kết quả cho từng hàng (như tổng cộng dồn accumulating total tại mỗi hàng).
Làm chủ SQL Window Functions giống như bổ sung một công cụ mạnh mẽ vào bộ công cụ thao tác dữ liệu của bạn. Chúng cung cấp các khả năng nâng cao để phân tích và báo cáo dữ liệu phức tạp, cho phép bạn rút ra thông tin chi tiết và đưa ra quyết định sáng suốt. Cho dù đó là tính toán tổng cộng dồn accumulating total), xếp hạng kết quả, hay so sánh các hàng riêng lẻ với số liệu tổng hợp của tập dữ liệu, SQL Window Functions là KHÔNG THỂ THIẾU. Hãy sử dụng chúng trong quá trình làm việc với SQL của bạn và bạn sẽ thấy các truy vấn của mình đạt được hiệu quả và rõ ràng hơn chỉ trong thời gian ngắn!
Thực tế, đôi khi bạn có thể sử dụng SQL Window Functions HOẶC truy vấn phụ để hoàn thành cùng một tác vụ. Để làm chủ SQL thực sự, bạn cần phải thành thạo nhiều phương tiện để đi đến câu trả lời và chọn con đường tốt nhất để tiến về phía trước, điều này cũng liên quan đến việc xem xét phương thức nào hiệu quả nhất về mặt Query Optimization (vấn đề này thì mình sẽ đề cập chi tiết hơn trong một bài khác).

Bây giờ chúng ta đã có một hiểu biết khái quát về Window Functions, chúng ta nên dành một chút thời gian để kiểm tra các loại Window Functions mà chúng ta có sẵn để mở rộng kho lưu trữ của mình.

Chức năng của Window Function

Aggregate Window Functions

Những hàm này tương tự như Aggregation Functions thông thường nhưng không làm giảm số lượng hàng được trả về. Các ví dụ bao gồm SUM(), AVG(), MIN(), MAX(), COUNT().

SUM(): Hàm này trả về tổng của một cột số.
AVG(): Hàm này trả về giá trị trung bình của một cột số.
COUNT(): Hàm này trả về số lượng hàng khớp với một tiêu chí nhất định.
MIN(): Hàm này trả về giá trị nhỏ nhất của cột được chọn.
MAX(): Hàm này trả về giá trị lớn nhất của cột được chọn.

Ranking Window Functions

Các hàm này gán một thứ hạng duy nhất cho mỗi hàng trong một phân vùng của tập kết quả (hoặc toàn bộ tập dữ liệu). Các ví dụ là ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE().

RANK(): Hàm này gán một thứ hạng duy nhất cho mỗi hàng riêng biệt trong phân vùng của tập kết quả. Thứ hạng được gán theo thứ tự được chỉ định trong mệnh đề ORDER BY của OVER() . Nếu hai hoặc nhiều hàng có thứ hạng bằng nhau, thì mỗi hàng bằng nhau sẽ nhận được cùng thứ hạng và các thứ hạng tiếp theo sẽ bị bỏ qua.
DENSE_RANK(): Hàm này hoạt động tương tự như RANK(), nhưng khi hai hoặc nhiều hàng có thứ hạng bằng nhau, thì thứ hạng tiếp theo không bị bỏ qua. Vì vậy, nếu bạn có ba mục ở hạng 2, thì hạng tiếp theo được liệt kê sẽ là 3.
ROW_NUMBER(): Hàm này gán một số thứ tự hàng duy nhất cho mỗi hàng trong phân vùng, bất kể các bản sao. Nếu có các giá trị trùng lặp trong tập được sắp xếp, nó vẫn sẽ gán các số thứ tự hàng khác nhau cho mỗi hàng.
NTILE(): Hàm này được sử dụng để chia một phân vùng được sắp xếp thành một số lượng nhóm nhất định, hoặc "ô", và gán một số nhóm cho mỗi hàng trong phân vùng. Điều này có thể hữu ích cho một số việc như chia một tập dữ liệu thành các tứ phân vị, thập phân vị hoặc bất kỳ tập hợp các nhóm có kích thước bằng nhau khác.

Bây giờ chúng ta hãy cùng nhìn qua các hàm xếp hạng này trong code như thế nào nhé:

-- RANK() Example
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  RANK() OVER (ORDER BY SaleAmount DESC) AS RankByAmount
FROM Sales;

-- DENSE_RANK() Example
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  DENSE_RANK() OVER (ORDER BY SaleAmount DESC) AS DenseRankByAmount
FROM Sales;

-- ROW_NUMBER() Example
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  ROW_NUMBER() OVER (ORDER BY SaleAmount DESC) AS RowNumByAmount
FROM Sales;

-- NTILE() Example
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  NTILE(4) OVER (ORDER BY SaleAmount DESC) AS Quartile
FROM Sales;

Value Window Functions

Các hàm này trả về các giá trị cụ thể từ mỗi phân vùng. Các hàm này cung cấp một cách để truy cập dữ liệu cụ thể từ một phân vùng, cho phép bạn so sánh hoặc tính toán các khác biệt giữa các giá trị trong tập kết quả.

Các ví dụ là FIRST_VALUE(), LAST_VALUE(), LEAD(), LAG().

FIRST_VALUE(): Hàm này trả về giá trị đầu tiên trong một tập hợp các giá trị được sắp xếp theo thứ tự từ một phân vùng. Ví dụ, bạn có thể sử dụng hàm này để tìm ra doanh số bán hàng đầu tiên của nhân viên bán hàng.
LAST_VALUE(): Hàm này trả về giá trị cuối cùng trong một tập hợp các giá trị được sắp xếp theo thứ tự từ một phân vùng. Nó có thể được sử dụng để tìm ra số tiền bán hàng gần đây nhất cho một sản phẩm cụ thể.
LEAD(): Hàm này cho phép bạn truy cập dữ liệu từ các hàng tiếp theo trong cùng một tập kết quả, cung cấp một cách để so sánh giá trị hiện tại với các giá trị từ các hàng sau. Nó hữu ích cho việc tính toán sự khác biệt về số tiền bán hàng giữa hai ngày liên tiếp.
LAG(): Tương tự như LEAD(), hàm LAG() cho phép bạn truy cập dữ liệu từ các hàng trước đó trong tập kết quả, mà không cần phải tự tham gia (self-join). Điều này có thể hữu ích cho việc so sánh dữ liệu hiện tại với dữ liệu lịch sử.

Những hàm này là những công cụ mạnh mẽ để phân tích dữ liệu, cho phép bạn điều hướng qua dữ liệu của mình và thu thập thông tin chi tiết từ các điểm dữ liệu cụ thể liên quan đến các điểm khác.

-- FIRST_VALUE() and LAST_VALUE() Example
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  FIRST_VALUE(SaleAmount) OVER (ORDER BY SaleDate) AS FirstSaleAmount,
  LAST_VALUE(SaleAmount)  OVER (ORDER BY SaleDate 
                               RANGE BETWEEN UNBOUNDED PRECEDING AND 
                               UNBOUNDED FOLLOWING
                               ) AS LastSaleAmount
FROM Sales;

-- LEAD() and LAG() Example
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  LAG(SaleAmount) OVER (ORDER BY SaleDate) AS PreviousSaleAmount,
  LEAD(SaleAmount) OVER (ORDER BY SaleDate) AS NextSaleAmount
FROM Sales;

Window Frame Specification

Khái niệm này đề cập đến tập hợp con của các hàng được sử dụng để thực hiện các tính toán cho một hàng cụ thể. Window Frame có thể được xác định bằng mệnh đề ROWS hoặc RANGE, và nó có thể không giới hạn (xem xét tất cả các hàng) hoặc giới hạn trong một phạm vi cụ thể.

ROWS: Định nghĩa Window Frame theo các hàng. Bạn có thể chỉ định một số lượng hàng cố định hoặc sử dụng UNBOUNDED PRECEDING và UNBOUNDED FOLLOWING để bao gồm tất cả các hàng.
RANGE: Định nghĩa Window Frame dựa trên một phạm vi hàng. Tương tự như ROWS, bạn có thể chỉ định một phạm vi hoặc sử dụng các tùy chọn UNBOUNDED.

-- ROWS Window Frame Specification
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  AVG(SaleAmount) OVER (ORDER BY SaleDate ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MovingAvg
FROM Sales;

-- RANGE Window Frame Specification
SELECT 
  SaleID, 
  Salesperson, 
  SaleAmount, 
  SUM(SaleAmount) OVER (ORDER BY SaleAmount RANGE BETWEEN 50 PRECEDING AND 50 FOLLOWING) AS CumulativeSum
FROM Sales;

Window frame specification đóng vai trò then chốt khi bạn muốn thực hiện các tính toán trên một tập hợp các hàng cụ thể liên quan đến hàng hiện tại, thay vì toàn bộ phân vùng.

Troubleshooting Window Functions

Nếu Window Functions của bạn không hoạt động như mong đợi, hãy cân nhắc những điều sau:

Kiểm tra mệnh đề OVER của bạn: Mệnh đề OVER xác định cách thức hoạt động của Window Functions. Hãy đảm bảo rằng bạn đã chỉ định các mệnh đề PARTITION BY và ORDER BY chính xác.
Kiểm tra cú pháp của hàm: Mỗi hàm cửa sổ có cú pháp riêng. Hãy đảm bảo xem lại cú pháp của hàm bạn đang sử dụng để đảm bảo nó chính xác.
Kiểm tra các kiểu dữ liệu: Hãy đảm bảo các kiểu dữ liệu bạn đang sử dụng trong hàm là tương thích. Ví dụ: bạn không thể thực hiện phép tính SUM trên một trường văn bản (hoặc một cột có giá trị chuỗi ẩn).

Optimizing Window Functions

Window Functions thường có thể dẫn đến các truy vấn chậm do thực tế chúng thực hiện các tính toán trên nhiều hàng. Dưới đây là một số mẹo để tối ưu hóa các Window Functionscủa bạn:

Giảm số lượng hàng: Nếu có thể, hãy lọc dữ liệu của bạn trước khi áp dụng hàm cửa sổ. Càng ít hàng hàm phải xử lý, truy vấn của bạn sẽ chạy càng nhanh. Đây là cách tốt nhất để đảm bảo bạn có thể làm việc hiệu quả hơn để gỡ lỗi và chạy mã của mình.
Sử dụng index phù hợp: Nếu bạn đang phân vùng hoặc sắp xếp dữ liệu của mình, hãy đảm bảo có các index phù hợp cho các cột đó. Điều này có thể tăng tốc đáng kể hiệu suất của Window Functions.
Tránh sắp xếp phức tạp: Nếu có thể, hãy tránh sử dụng nhiều cột trong mệnh đề ORDER BY của bạn trong hàm cửa sổ. Mỗi cột bổ sung có thể làm tăng thời gian tính toán.
Giới hạn Window frame: Theo mặc định, Window Functions xem xét tất cả các hàng trong phân vùng. Nếu bạn không cần xem xét tất cả các hàng, hãy sử dụng mệnh đề ROWS hoặc RANGE để giới hạn Window frame.

Summary

Với Window Functions, bạn có thể thực hiện các phép chuyển đổi và tính toán phức tạp trên dữ liệu của mình, giúp các truy vấn SQL của bạn mạnh mẽ và có ý nghĩa sâu hơn. Cho dù bạn đang xếp hạng kết quả, tính toán tổng cộng dồn accumulating total), hay truy cập các giá trị cụ thể trong một phân vùng, Window Function cung cấp sự linh hoạt và chức năng cần thiết để phân tích dữ liệu nâng cao.

Heyyy, vậy là anh em mình đã vượt qua một vùng đất khá phức tạp của Window Functions SQL, khám phá khả năng biến đổi các phân tích dữ liệu phức tạp thành các tác vụ dễ quản lý hơn. Các hàm nâng cao này không chỉ giúp đơn giản hóa các truy vấn của chúng ta mà còn mở ra vô vàn khả năng cho việc khám phá và báo cáo dữ liệu. Khi bạn tiếp tục kết hợp Window Functions vào kho lưu trữ SQL của mình, hãy nhớ rằng chìa khóa để thành thạo là thực hành và thử nghiệm. Vì vậy, hãy bắt đầu thử nghiệm khám phá để có thể hiểu rõ hơn nhé.

@Database sharpAggregation

Khái quát về SQL Window Functions

Window Functions là gì?

Giải mã Window Functions

Ví dụ ở đây

Running Totals

Cumulative Totals

Moving Average of SalesAmount

Chức năng của Window Function

Aggregate Window Functions

Ranking Window Functions

Value Window Functions

Window Frame Specification

Troubleshooting Window Functions

Optimizing Window Functions

Summary

Mục lục