Tiếp tục seri về các Window Functions trong SQL, trong bài viết này SmartData sẽ chia sẻ về các hàm trong nhóm Ranking Function, như tên gọi của nó đã nói lên chức năng chính của các hàm sẽ xoay quanh việc sắp xếp thứ tự của các dòng dữ liệu. Các hàm thường được sử dụng trong nhóm này là rank(), dense_rank(), row_number(), cume_cust(),..Hãy cùng SmartData đi vào chi tiết chức năng và cách dùng của từng hàm.
Hàm row_number trong Window Function MySQL
Vẫn với dataset sử dụng ở bài trước, ta sẽ dùng với các hàm sau đây
Hàm row_number sẽ gắn một số (số dòng) cho dòng dữ liệu tuỳ theo cột được chọn để chia nhóm, số khởi đầu luôn là 1, row_number được gắn một giá trị tương ứng cho từng dòng trong nhóm đến khi kết thúc.
Ví dụ: Đánh dấu số thứ tự cho từng nhóm dữ liệu chia nhóm bởi cột category
SQL> SELECT category, color, sale_price, row_number() OVER (PARTITION by category order by category) AS STT FROM sampleKết quả:
Ta có thể thấy với mỗi nhóm dữ liệu phân biệt bởi trường category thì mỗi bản ghi để được đánh STT tương ứng cho đến khi hết dòng dữ liệu thuộc nhóm đó.
Hàm rank trong Window Function MySQL
Hàm Rank trong window function khá giống với row_number, đó là sẽ thực hiện sắp xếp dữ liệu theo trường được chỉ định chia nhóm (cột chỉ định trong order by), sau đó gắn thêm một giá trị gọi là xếp hạng dòng dữ liệu đó theo nhóm được chia (partition by), nếu bản ghi đồng hạng thì chúng sẽ có cùng 1 giá trị rank, bản ghi ở rank tiếp theo sẽ bằng số lượng bản ghi xếp trên nó.
Ta cùng đi vào ví dụ sau để dễ hiểu hơn
SQL> SELECT category, color, rank() OVER (order by color) AS rank_by_color FROM sample;Kết quả:
Giải thích:
Hàm order by phân loại cột color, còn hàm rank xếp hạng theo từng color. Tuy nhiên, tất cả giá trị màu giống nhau đều có chung xếp hạng, còn màu khác có xếp hạng riêng. Màu đen Black xuất hiện 3 lần trong dataset; thay vì gắn một giá trị xếp hạng 1, 2, 3, các mục màu đen được xếp hạng 1.
Tuy nhiên, màu nâu Brown sẽ là 4, không phải 2. Rank function bỏ qua các giá trị và gắn giá trị theo trình tự thời gian cho các mục khác nhau
Hàm dense_rank trong Window Function MySQL
Hàm dense_rank cũng có chức năng tương tự hàm rank, chỉ khác ở chỗ không bỏ qua bất kỳ giá trị xếp hạng trong hàm order by
Ví dụ:
SQL> SELECT category, color, dense_rank() OVER (order by color) AS dense_rank_by_color FROM sample;Kết quả:
Giải thích:
3 dòng đầu tiên sẽ có xếp hạng 1. Tuy nhiên, màu (Brown) tiếp theo sẽ không có rank 4, mà là rank 2, là thứ tự thời gian tiếp theo trong danh mục đánh số. Hàm dense_rank là một window function thực tế hơn bởi nó gắn một giá trị có ý nghĩa cho tất cả danh sách các mục.
Hàm cume_cust trong Window Function MySQL
Hàm cume_cust trong window function có chức năng Tính tỷ lệ các giá trị nhỏ hơn hoặc bằng giá trị lớn nhất trong một nhóm dữ liệu (được chia bởi partition). Điều nãy có nghĩa giá trị cume_cust luôn nhỏ hơn hoặc bằng 1(nếu tất cả giá trị trong cột đều dương)
Ví dụ:
SQL> SELECT category, color, sale_price, CUME_DIST() OVER (partition by category order by sale_price) AS cume_dist_by_category FROM sample;Kết quả:
Ta có thể thấy câu truy vấn chọn ‘partition by category‘ nghĩa là sẽ nhóm dữ liệu theo trường category, ‘order by sale_price’ nghĩa là sẽ sắp xếp tỉ lệ dựa trên trường sale_price.
Với nhóm có category = Appliances, giá trị sale_price lớn nhất là 114.9 sẽ tương ứng với giá trị cume_cust là 1, giá trị sale_price nhỏ nhất là
Kết luận
Nhóm các hàm ranking function trong window function sẽ rất hữu ích cho chúng ta khi rút ngắn những công việc cần xếp loại các dữ liệu.
Nếu bạn là newbie có thể tham khảo bài viết này để tìm hiểu lộ trình học DA trong 3 tháng của SmartData
Nếu bạn thấy bài viết hay và hữu ích, bạn có thể tham gia các kênh sau của SmartData để nhận được nhiều hơn nữa:
