Hướng dẫn cho người mới bắt đầu hiểu các hàm cửa sổ SQL - Phần 2

bên trong bài báo trước , chúng ta đã nói về các hàm cửa sổ - là một công cụ tuyệt vời để giải quyết các vấn đề phân tích khác nhau; họ có thể cấp cho bạn quyền truy cập vào các tính năng như phân tích nâng cao và thao tác dữ liệu mà không cần phải viết các truy vấn phức tạp. Trước khi bạn tiếp tục đọc, tôi khuyên bạn nên bắt đầu với phần đầu tiên vì nó sẽ giúp bạn nắm được ý tưởng cơ bản về cách hoạt động của các hàm cửa sổ trong SQL.

Bây giờ, khi bạn đã quen với những điều cơ bản, hãy cùng khám phá các khái niệm SQL nâng cao hơn. Thoạt nghe có vẻ hơi phức tạp, nhưng tôi sẽ cung cấp các ví dụ đơn giản phù hợp cho người mới bắt đầu cho tất cả các trường hợp có thể xảy ra, để tôi có thể dễ dàng nắm bắt được sự trôi dạt của mình.

Tổng quan về nội dung

• Cumulative Sum
• Ranking Window Functions
• Use cases
• Offset window functions
• Key Takeaways

Tổng tích lũy

Chúng ta đã xem xét các ví dụ trong đó biểu thức over() không có tham số hoặc có sự phân vùng theo tham số. Bây giờ, chúng ta sẽ xem xét tham số thứ hai có thể có cho biểu thức over() - order by.

Hãy yêu cầu ID nhân viên, tên nhân viên, bộ phận, mức lương và tổng của tất cả các khoản tiền lương:

 select employee_id, employee_name, department, salary, sum(salary) over() from salary 

Bây giờ, chúng ta sẽ thêm thứ tự theo tham số vào biểu thức over() :

 select employee_id, employee_name, department, salary, sum(salary) over(order by employee_id desc) from salary 

Tôi đoán chúng ta cần xem xét kỹ hơn những gì đã xảy ra ở đây:

1. Trước hết, nhân viên_id hiện được sắp xếp theo thứ tự giảm dần.

   
   

2. Trong cột kết quả từ việc áp dụng hàm cửa sổ, hiện có tổng tích lũy.

   
   

Tôi tin rằng bạn đã quen thuộc với số tiền tích lũy. Bản chất của nó rất đơn giản - tổng tích lũy hoặc tổng số hiện có có nghĩa là "cho đến nay là bao nhiêu". Định nghĩa của tổng tích lũy là tổng của một dãy nhất định đang tăng dần hoặc ngày càng lớn hơn với nhiều phép cộng hơn.

Đây là những gì chúng tôi có trong ví dụ của mình: đối với nhân viên có giá trị worker_id cao nhất, mức lương là 3700 và tổng tích lũy cũng là 3700. Nhân viên thứ hai có mức lương là 1500 và tổng tích lũy là 5200. Nhân viên thứ ba , với mức lương là 2900, có tổng tích lũy là 8100, v.v.

Thứ tự theo tham số trong biểu thức over() chỉ định thứ tự. Trong trường hợp các hàm cửa sổ tổng hợp, nó xác định thứ tự của tổng tích lũy.

Trong biểu thức over() , cả thuộc tính phân vùng theo và thứ tự theo đều có thể được chỉ định.

 select employee_id, employee_name, department, salary, sum(salary) over(partition by department order by employee_id desc) from salary 

Trong trường hợp này, tổng tích lũy sẽ được tính theo từng phần.

Sau khi thảo luận về tổng tích lũy, chúng ta cần phải nói rằng có lẽ đó là loại tổng tích lũy duy nhất được sử dụng thường xuyên. Ngược lại, trung bình tích lũy và số đếm tích lũy hiếm khi được sử dụng.

Tuy nhiên, chúng tôi sẽ đưa ra một ví dụ về phép tính trung bình tích lũy - nó cho chúng ta biết giá trị trung bình của một chuỗi các giá trị cho đến một điểm nhất định:

 select employee_id, employee_name, department, salary, avg(salary) over(order by employee_id desc) from salary 

Chức năng cửa sổ xếp hạng

Chúng tôi sử dụng các hàm cửa sổ xếp hạng để xác định vị trí của một giá trị trong một tập hợp các giá trị. Biểu thức ORDER BY trong mệnh đề OVER chỉ ra cơ sở để xếp hạng, với mỗi giá trị được gán một thứ hạng trong phân vùng được chỉ định của nó. Khi các hàng có cùng giá trị cho tiêu chí xếp hạng, chúng sẽ được gán cùng thứ hạng.

Để xem các chức năng của cửa sổ xếp hạng hoạt động như thế nào, hãy yêu cầu các cột sau từ bảng lương: ID nhân viên, tên nhân viên, bộ phận và mức lương:

 select employee_id, employee_name, department, salary from salary 

Bây giờ, chúng ta thêm một cột nữa với hàm cửa sổ row_number() over() :

 select employee_id, employee_name, department, salary, row_number() over() from salary 

Hàm cửa sổ row_number() over() đã gán số cho các hàng mà không thay đổi thứ tự của chúng. Cho đến nay, điều này không mang lại nhiều giá trị cho chúng ta phải không?

Nhưng nếu chúng ta muốn đánh số các hàng theo thứ tự lương giảm dần thì sao? Để đạt được điều này, chúng ta cần chỉ định thứ tự sắp xếp, hay nói cách khác là truyền thứ tự theo tham số cho biểu thức over().

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Chúng tôi sẽ thêm các hàm xếp hạng còn lại vào truy vấn để so sánh:

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc), rank() over(order by salary desc), dense_rank() over(order by salary desc), percent_rank() over(order by salary desc), ntile(5) over(order by salary desc) from salary

Chúng ta hãy đi qua từng chức năng của cửa sổ xếp hạng:

1. Hàm cửa sổ row_number() over(thứ tự theo lương desc) xếp các hàng theo thứ tự lương giảm dần và gán số hàng. Lưu ý rằng Annie và Tony có cùng mức lương nhưng họ được gán những con số khác nhau.

   
   

2. Hàm cửa sổ xếp hạng () over(thứ tự theo lương desc ) gán các cấp bậc theo thứ tự lương giảm dần. Nó gán cùng thứ hạng cho các giá trị giống hệt nhau, nhưng giá trị tiếp theo sẽ nhận được số hàng mới.

   
   

3. Hàm cửa sổdense_rank() over(thứ tự theo lương desc) gán các cấp bậc theo thứ tự lương giảm dần. Nó chỉ định cùng một thứ hạng cho các giá trị giống hệt nhau.

   
   

4. Hàm cửa sổ Perc_rank() over(thứ tự theo lương desc) là thứ hạng (phần trăm) tương đối của hàng hiện tại, được tính theo công thức: (rank - 1) / (tổng số hàng trong phân vùng - 1).

   
   

5. Hàm cửa sổ ntile(5) over(thứ tự theo lương desc) chia số hàng thành 5 phần bằng nhau và gán một số cho mỗi phần. Số phần được chỉ định bên trong hàm ntile(5) .

   
   

Trường hợp sử dụng

Đã đến lúc khám phá các nhiệm vụ thực tế bằng cách sử dụng các chức năng của cửa sổ xếp hạng. Chúng tôi sẽ hiển thị ID nhân viên, tên nhân viên, bộ phận và mức lương, đồng thời gán số hàng theo thứ tự lương giảm dần.

 select employee_id, employee_name, department, salary, row_number() over(order by salary desc) from salary 

Đôi khi, bạn có thể cần đánh số hàng theo thứ tự lương giảm dần trong các phòng ban (bộ phận). Điều này có thể được thực hiện bằng cách thêm phân vùng theo thuộc tính vào biểu thức over():

 select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) from salary 

Hãy làm cho nhiệm vụ trở nên khó khăn hơn. Chúng tôi chỉ cần giữ lại một nhân viên cho mỗi bộ phận có mức lương cao nhất. Điều này có thể đạt được bằng cách sử dụng truy vấn con:

 select * from ( select employee_id, employee_name, department, salary, row_number() over(partition by department order by salary desc) as rn from salary ) as t2 where rn = 1 

Và một ví dụ nữa, nếu chúng ta cần hiển thị ba nhân viên ở mỗi thành phố có mức lương cao nhất, chúng ta sẽ làm như sau:

 select * from ( select employee_id, employee_name, city, salary, row_number() over(partition by city order by salary desc) as rn from salary ) as t2 where rn <= 3 

Những loại tác vụ này rất phổ biến, đặc biệt khi bạn cần hiển thị một số hàng cụ thể trong các phần (nhóm) theo thứ tự tăng dần hoặc giảm dần của một số thuộc tính. Trong thực tế, tôi thường xuyên sử dụng hàm cửa sổ row_number() over() và tất nhiên là cảdense_rank() over() .

Chức năng cửa sổ bù đắp

Các hàm này cho phép bạn trả về dữ liệu từ các hàng khác dựa trên khoảng cách của chúng với hàng hiện tại. Để làm cho nó trực quan hơn, chúng ta hãy xem qua các hàm first_value(), Last_value() và nth_value().

 select t1.*, first_value(salary)over(partition by department), last_value(salary)over(partition by department), nth_value(salary,2)over(partition by department) from salary as t1 order by department 

 select t1.*, first_value(salary)over(partition by department order by salary decs), last_value(salary)over(partition by department order by salary decs), nth_value(salary,2)over(partition by department order by salary decs) from salary as t1 order by department

Các hàm first_value(salary) over(phân vùng theo phòng ban) và Last_value(salary) over(phân vùng theo phòng ban) hiển thị giá trị lương đầu tiên và cuối cùng trong phần (phòng ban).

Đến lượt mình, hàm nth_value(salary, 2) over(phân vùng theo bộ phận) hiển thị giá trị lương thứ hai trong phần (bộ phận). Xin lưu ý rằng trong nth_value() , một đối số bổ sung được chỉ định – số hàng trong phần này. Trong trường hợp của chúng tôi, số hàng là 2, do đó hàm hiển thị giá trị lương thứ hai.

Ngoài những chức năng trên, còn có các hàm lag() và lead() . Hàm lag() được sử dụng để lấy giá trị từ hàng trước hàng hiện tại. Hàm lead() được sử dụng để lấy giá trị từ hàng tiếp theo hàng hiện tại.

 select t1.*, lag(salary)over(order by salary), lead(salary)over(order by salary) from salary as t1 

Như bạn có thể thấy, hàm lag (lương) trên (thứ tự theo lương) sẽ dịch chuyển lương xuống một hàng và hàm lead(lương) over(thứ tự theo lương) sẽ dịch lương lên một hàng. Mặc dù các chức năng này khá giống nhau nhưng tôi thấy sử dụng lag() sẽ thuận tiện hơn.

 select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department 

Ở đây, lag() thực hiện chức năng tương tự như trước, nhưng bây giờ cụ thể là trong các phần (phòng ban).

Bài học chính

Và cuối cùng, tổng quan nhanh về những gì chúng ta đã trình bày hôm nay:

• Tổng tích lũy biểu thị tổng số chạy của một chuỗi, tích lũy với mỗi lần thêm tiếp theo.

  
  

• Các hàm cửa sổ xếp hạng được sử dụng để xác định vị trí của một giá trị trong một tập hợp các giá trị, với thứ tự theo biểu thức xác định cơ sở để xếp hạng.

  
  

• Các chức năng của cửa sổ offset bao gồm f irst_value() , Last_value() và nth_value() , cho phép truy xuất dữ liệu từ các hàng khác dựa trên khoảng cách của chúng với hàng hiện tại. Đừng quên các hàm lag() và lead() . Hàm lag() có thể hữu ích để lấy giá trị từ hàng trước hàng hiện tại, trong khi hàm lead() được sử dụng để lấy giá trị từ hàng tiếp theo hàng hiện tại.

  
  

Cảm ơn vì đã tham gia cùng tôi. Tôi hy vọng bài viết này giúp bạn hiểu rõ hơn về khả năng của các hàm cửa sổ trong SQL và giúp bạn tự tin và nhanh chóng hơn trong các công việc thường ngày.