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 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() 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 tham số vào biểu thức : thứ tự theo 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: Trước hết, hiện được sắp xếp theo thứ tự giảm dần. nhân viên_id 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ị 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. worker_id Thứ tự theo tham số trong biểu thức 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. over() Trong biểu thức , cả thuộc tính phân vùng theo và thứ tự theo đều có thể được chỉ định. over() 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. NB! Nếu cả hai thuộc tính đều được chỉ định trong biểu thức thì phân vùng luôn đứng đầu, theo sau là . Ví dụ: . over() thứ tự over(phân vùng theo thứ tự bộ phận theo nhân viên_id) 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 trong mệnh đề 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. ORDER BY OVER Để 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ổ đã 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? row_number() over() 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: Hàm cửa sổ 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. row_number() over(thứ tự theo lương desc) Hàm cửa sổ ) 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. xếp hạng () over(thứ tự theo lương desc Hàm cửa 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. sổdense_rank() over(thứ tự theo lương desc) Hàm cửa sổ 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). Perc_rank() over(thứ tự theo lương desc) Hàm cửa sổ 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) over(thứ tự theo lương desc) ntile(5) NB! Không giống như các hàm tổng hợp, ví dụ: tổng (lương), hàm xếp hạng, ví dụ: row_number(), không lấy một cột bên trong. Tuy nhiên, trong hàm ntile(5), số phần được chỉ định. 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ổ và tất nhiên là . row_number() over() 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 NB! Trong cả ba chức năng cửa sổ, nó là thiết yếu để chỉ định phân vùng theo tham số. Thứ tự theo tham số không bắt buộc, nhưng bằng cách chỉ định nó, bạn có thể thay đổi thứ tự các hàng trong phân vùng. Ví dụ: trong truy vấn bên dưới, chúng tôi đã sắp xếp theo mức lương trong phần (bộ phận) và bây giờ first_value là mức lương cao nhất trong phần. 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 và hiển thị giá trị lương đầu tiên và cuối cùng trong phần (phòng ban). first_value(salary) over(phân vùng theo phòng ban) Last_value(salary) over(phân vùng theo phòng ban) Đến lượt mình, hàm hiển thị giá trị lương thứ hai trong phần (bộ phận). Xin lưu ý rằng trong , 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. nth_value(salary, 2) over(phân vùng theo bộ phận) nth_value() Ngoài những chức năng trên, còn có các hàm và . Hàm đượ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. lag() lead() lag() 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 sẽ dịch chuyển lương xuống một hàng và hàm 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 sẽ thuận tiện hơn. lag (lương) trên (thứ tự theo lương) lead(lương) over(thứ tự theo lương) lag() NB! Đối với các hàm này, bắt buộc phải chỉ định thứ tự theo tham số trong biểu thức over(). Bạn cũng có thể chỉ định phân vùng bằng cách sử dụng phân vùng theo nhưng điều này không bắt buộc. select t1.*, lag(salary)over(partition by department order by salary) from salary as t1 order by department Ở đây, 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). lag() 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 biểu thức xác định cơ sở để xếp hạng. thứ tự theo Các chức năng của cửa sổ offset bao gồm f , và , 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 và . Hàm có thể hữu ích để lấy giá trị từ hàng trước hàng hiện tại, trong khi hàm được sử dụng để lấy giá trị từ hàng tiếp theo hàng hiện tại. irst_value() Last_value() nth_value() lag() lead() lag() lead() 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.
