Hướng dẫn cho người mới bắt đầu để hiểu các chức năng của cửa sổ SQL và khả năng của chúng

Trong suốt quá trình tuyển dụng, tôi rất vui được gặp nhiều ứng viên tài năng cho đội của mình. Vì công việc của chúng tôi liên quan đến việc xử lý các tập dữ liệu phức tạp nên điều quan trọng đối với tôi là đánh giá khả năng tìm ra giải pháp thông minh của từng ứng viên. Tôi đã hỏi về trải nghiệm của họ với các hàm cửa sổ trong SQL để đánh giá trình độ của họ. Mặc dù hầu hết họ đều biết về các chức năng này, nhưng rất ít người có thể sử dụng chúng một cách hiệu quả.

Mặc dù các chức năng của cửa sổ đã tồn tại gần 20 năm, nhưng nhiều nhà phát triển SQL vẫn cảm thấy khó nắm bắt chúng. Không có gì lạ khi ngay cả các nhà phát triển có kinh nghiệm cũng chỉ sao chép và dán mã từ StackOverflow mà không thực sự hiểu nó làm gì. Bài viết này là ở đây để giúp đỡ! Tôi sẽ giải thích các chức năng của cửa sổ theo cách dễ hiểu và cung cấp các ví dụ để cho bạn thấy chúng hoạt động như thế nào trong thế giới thực.

Bạn đã nghe nói về các chức năng của cửa sổ chưa? Chúng là những công cụ phân tích tuyệt vời có thể giải quyết rất nhiều vấn đề. Chẳng hạn, giả sử bạn cần tính toán một tập hợp các hàng có chung một thuộc tính, chẳng hạn như ID khách hàng. Đây là nơi các chức năng của cửa sổ có ích! Chúng hoạt động giống như các hàm tổng hợp nhưng cho phép bạn giữ tính duy nhất của mỗi hàng thay vì nhóm chúng lại với nhau. Ngoài ra, kết quả của các chức năng cửa sổ hiển thị dưới dạng trường bổ sung trong lựa chọn đầu ra. Điều này cực kỳ hữu ích khi bạn đang tạo báo cáo phân tích, tính toán các đường trung bình động và chạy tổng số hoặc tìm ra các mô hình phân bổ khác nhau.

Chào mừng bạn đến với thế giới của SQL và các hàm cửa sổ! Nếu bạn chỉ mới bắt đầu, bạn đang ở đúng nơi. Bài viết này thân thiện với người mới bắt đầu, có giải thích rõ ràng và không có thuật ngữ phức tạp hoặc khái niệm nâng cao. Bạn sẽ có thể làm theo một cách dễ dàng, ngay cả khi bạn là người hoàn toàn mới đối với chủ đề này.

Tổng quan nội dung

• Các loại chức năng được sử dụng với chức năng cửa sổ
  • Chức năng tổng hợp
  • chức năng xếp hạng
  • hàm giá trị
• Chức năng cửa sổ tổng hợp
• Chìa khóa rút ra

Các loại chức năng được sử dụng với chức năng cửa sổ

Có ba loại hàm chính mà các hàm cửa sổ có thể được áp dụng trên một tập hợp các hàng (cái gọi là cửa sổ): đó là các hàm tổng hợp, xếp hạng và giá trị. Trong hình ảnh bên dưới, bạn có thể thấy tên của các chức năng khác nhau thuộc từng danh mục.

Chức năng tổng hợp

Chúng thực hiện các phép toán trên một nhóm dữ liệu, dẫn đến một giá trị tích lũy duy nhất. Chúng được sử dụng để tính toán các tập hợp khác nhau, bao gồm giá trị trung bình, tổng số hàng, giá trị tối đa hoặc tối thiểu hoặc tổng số trong mỗi cửa sổ hoặc phân vùng.

• SUM: cộng tất cả các giá trị trong cột

• COUNT: tính số lượng giá trị trong cột, không bao gồm giá trị NULL

• AVG: tìm giá trị trung bình trong cột

• MAX: xác định giá trị cao nhất trong cột

• MIN: xác định giá trị thấp nhất trong cột

  
  

chức năng xếp hạng

Chúng được sử dụng để cung cấp cho mỗi hàng trong một phân vùng một thứ hạng hoặc thứ tự. Điều này được thực hiện bằng cách đánh giá các tiêu chí cụ thể, như chỉ định các số thứ tự hoặc xếp hạng dựa trên các giá trị cụ thể.

• ROW_NUMBER: chỉ định một số thứ hạng liên tiếp cho mỗi bản ghi mới trong một phân vùng
• RANK: chỉ định thứ hạng cho mỗi hàng trong tập kết quả. Trong trường hợp này, nếu hệ thống phát hiện các giá trị giống hệt nhau, nó sẽ gán cùng thứ hạng cho chúng và bỏ qua giá trị tiếp theo.
• DENSE_RANK: chỉ định thứ hạng cho mỗi hàng trong một phân vùng của tập hợp kết quả. Không giống như hàm RANK, hàm này trả về thứ hạng cho các giá trị giống hệt nhau mà không bỏ qua bất kỳ giá trị nào tiếp theo.
• NTILE: cho phép ta xác định dòng hiện tại thuộc nhóm nào. Số lượng các nhóm được đưa ra trong ngoặc.
  

hàm giá trị

Những điều này giúp dễ dàng so sánh các giá trị giữa các hàng khác nhau trong một nhóm và cũng cho phép bạn so sánh các giá trị với giá trị đầu tiên hoặc giá trị cuối cùng trong nhóm đó. Điều này có nghĩa là bạn có thể dễ dàng di chuyển qua các hàng khác nhau trong một cửa sổ và kiểm tra các giá trị ở đầu hoặc cuối cửa sổ.

• LAG hoặc LEAD: truy cập dữ liệu từ hàng trước hoặc hàng tiếp theo mà không phải thực hiện thao tác tự nối. Các hàm này đặc biệt hữu ích khi giải các bài toán yêu cầu so sánh một hàng với một hàng khác trong cùng một tập hợp kết quả hoặc phân vùng, chẳng hạn như tính toán sự khác biệt theo thời gian.
• FIRST_VALUE hoặc LAST_VALUE: truy xuất giá trị đầu tiên hoặc giá trị cuối cùng từ một cửa sổ hoặc phân vùng đã xác định. Các chức năng này đặc biệt hữu ích khi bạn muốn tính toán sự khác biệt trong một khoảng thời gian cụ thể.

Để bắt đầu với các chức năng của cửa sổ, hãy tạo một bảng 'lương' giả định và điền dữ liệu vào đó.

Tạo bảng:

 create table salary ( employee_id smallint, employee_name varchar(10), department varchar(20), city varchar(20), salary real )

Điền vào bảng:

 insert into salary values ( 1 ,'Tony' ,'R&D', 'New York', 3000); insert into salary values ( 2 ,'James' ,'Project management', 'London', 4000); insert into salary values ( 3 ,'Dina' ,'Engineering', 'Tokyo', 5000); insert into salary values ( 4 ,'Annie' ,'Security', 'London', 3000); insert into salary values ( 5 ,'Tom' ,'R&D', 'New York', 3500); insert into salary values ( 6 ,'Stan' ,'Project management', 'New York', 4200); insert into salary values ( 7 ,'Jessa' ,'Sales', 'London', 5300); insert into salary values ( 8 ,'Ronnie' ,'R&D', 'Tokyo', 2900); insert into salary values ( 9 ,'Andrew' ,'Engineering', 'New York', 1500); insert into salary values (10,'Dean' ,'Sales', 'Tokyo', 3700)

Hãy kiểm tra xem chúng ta đã điền thành công bảng 'lương' chưa:

 select * from salary 

Truy vấn tiếp theo sẽ hiển thị tên và lương của nhân viên từ bảng của chúng tôi:

 select employee_name, salary from salary 

Tính tổng lương, lương trung bình, tối đa, tối thiểu và số hàng là một số trường hợp sử dụng phổ biến của hàm tổng hợp:

Khi một chức năng tổng hợp được áp dụng, tiền lương được tổng hợp và hiển thị trong một dòng.

Nhưng nếu chúng ta muốn hiển thị tên và lương của nhân viên từ bảng 'lương', và trong cột thứ ba, tổng của tất cả các khoản lương thì sao? Giá trị này phải giống nhau cho tất cả các hàng.

Đó là một cơ hội tuyệt vời để sử dụng chức năng cửa sổ!

 select employee_name, salary, sum(salary) over() as sum_salary from salary 

Chúng ta hãy xem xét kỹ hơn hàm cửa sổ tính tổng tiền lương trong mỗi hàng của sum(salary) over() .

Biểu thức over() xác định một cửa sổ hoặc một tập hợp các hàng mà hàm hoạt động trên đó. Trong ví dụ của chúng tôi, cửa sổ là toàn bộ bảng, nghĩa là hàm sẽ áp dụng trên tất cả các hàng.

Biểu thức over() chỉ hoạt động khi được ghép nối với các hàm được yêu cầu trước over() .

Ví dụ: sum(salary) over() , trong đó sum() là một hàm tổng hợp. Và toàn bộ biểu thức sum(salary) over() là một hàm cửa sổ tổng hợp.

Như tôi đã nói trước đây, tất cả các chức năng mà chức năng cửa sổ được áp dụng có thể được chia thành ba nhóm: chức năng tổng hợp, xếp hạng và giá trị.

Các hàm tổng hợp sum() , count() , avg() , min() , max() cùng với biểu thức over() tạo thành một nhóm các hàm cửa sổ tổng hợp.

Trong bài viết này, chúng tôi sẽ tập trung vào loại chức năng cửa sổ cụ thể này.

Chức năng cửa sổ tổng hợp

Quay lại các ví dụ!

Hãy yêu cầu tên của nhân viên; tiền lương của họ; tổng của tất cả các khoản tiền lương; mức lương trung bình, tối đa và tối thiểu; số lượng nhân viên.

 select employee_name, salary, sum(salary) over(), avg(salary) over(), max(salary) over(), min(salary) over(), count(*) over() from salary 

Giờ đây, các chức năng của cửa sổ là gì đã rõ ràng hơn, chúng ta hãy khám phá một số trường hợp mà chúng có thể hữu ích trong công việc của bạn.

 select employee_name, salary, sum(salary)over(), salary/sum(salary)over() as share from salary order by salary/sum(salary)over() desc 

Chúng tôi đã tính tỷ lệ phần trăm của tổng ngân sách tiền lương cho mỗi mức lương trong cột thứ tư. Lương của Jessa chiếm gần 15% tổng ngân sách dành cho lương.

Lưu ý rằng chúng tôi cũng đã đặt công thức tính tỷ lệ phần trăm salary/sum(salary)over() trong sắp xếp theo order by . Chức năng cửa sổ có thể được tìm thấy không chỉ trong đầu ra select , mà còn trong order by .

Một ví dụ khác: hãy so sánh tiền lương với mức lương trung bình của công ty.

 select employee_name, salary, avg(salary)over(), salary-avg(salary)over() as diff_salary from salary order by salary-avg(salary)over() 

Như chúng ta có thể thấy, lương của Andrew thấp hơn mức trung bình vào năm 2110 và của Jessa cao hơn mức trung bình là 1690.

Hãy yêu cầu ba cột: tên nhân viên, bộ phận và tiền lương. Ngoài ra, chúng tôi sẽ sắp xếp chúng theo bộ phận.

 select employee_name, department, salary from salary order by department 

Bây giờ chúng tôi sẽ yêu cầu ba cột giống nhau, cộng với một cột có tổng tiền lương của tất cả nhân viên. Bạn đã biết rằng nó có thể được thực hiện với chức năng cửa sổ.

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

Nhưng điều gì sẽ xảy ra nếu chúng ta không muốn yêu cầu tổng lương, mà là tổng lương cho từng bộ phận, như thể hiện trong cột cuối cùng:

Chúng ta có thể làm điều đó bằng cách thêm partition by tham số vào biểu thức over() :

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

Partition by cho phép chúng tôi áp dụng chức năng cửa sổ không phải cho tất cả các hàng (toàn bộ cửa sổ) mà cho các phần cột.

Nó không giống như một nhóm đơn giản? Để tính tổng tiền lương cho từng bộ phận, chúng tôi sẽ tạo một nhóm theo các bộ phận (các phần trong tiếng lóng của các hàm cửa sổ) và tính số tiền:

 select department, sum(salary) from salary group by department 

Về bản chất, sự khác biệt giữa phân nhóm và partition by là group by đó trả về một hàng trên mỗi nhóm, trong khi partition by , mặc dù kết quả của hàm giống hệt với kết quả của hàm tổng hợp với group by , cung cấp hàm tổng hợp dựa trên một nhóm cho tất cả các hàng.

Hãy quay trở lại các chức năng của cửa sổ:

 select employee_name, department, salary, sum(salary)over(partition by department), salary/sum(salary)over(partition by department) as shape from salary order by department 

Sử dụng chức năng cửa sổ, đặc biệt là partition by tham số, chúng ta có thể tính phần chia lương của mỗi nhân viên từ tổng lương của bộ phận. Hoặc, ví dụ, để so sánh mức lương với mức lương trung bình trong bộ phận.

Chìa khóa rút ra

Tóm lại:

• Hàm cửa sổ thực hiện phép tính trên một tập hợp các hàng có liên quan nào đó đến hàng hiện tại,

• Các loại chức năng chính mà chức năng cửa sổ được áp dụng là chức năng tổng hợp, xếp hạng và giá trị,

• Để sử dụng hàm cửa sổ, bạn cần áp dụng mệnh đề over() xác định cửa sổ (một tập hợp các hàng) trong tập hợp kết quả truy vấn. Sau đó, hàm cửa sổ sẽ tính toán một giá trị cho mỗi hàng trong cửa sổ,

• Để chỉ định cột mà bạn muốn thực hiện tổng hợp, bạn cần thêm mệnh đề partition by vào mệnh đề over() . Partition by hơi giống với nhóm nhưng trả về tất cả các hàng có áp dụng hàm tổng hợp, thay vì một hàng cho mỗi nhóm.

  
  

Điều này là nó cho bây giờ! Trong vài bài viết tiếp theo, tôi sẽ khám phá các khái niệm SQL nâng cao hơn với các ví dụ đơn giản phù hợp cho người mới bắt đầu, vì vậy hãy chú ý theo dõi!