[ad_1]
Lĩnh vực CNTT được biết đến với những thay đổi liên tục, với các công cụ mới, khuôn khổ mới, nhà cung cấp đám mây mới và LLM mới được tạo ra mỗi ngày. Tuy nhiên, ngay cả trong thế giới bận rộn này, một số nguyên tắc, mô hình và công cụ dường như đang thách thức hiện trạng của ‘không có gì là mãi mãi’. Và, trong vùng dữ liệu, không có ví dụ nào về điều này ấn tượng như ngôn ngữ SQL.
Kể từ khi được tạo ra vào những năm 80, nó đã vượt qua thời đại Kho dữ liệu, tự hiện thực hóa trong Hadoop/Hồ dữ liệu/Dữ liệu lớn dưới dạng Hive và vẫn còn tồn tại cho đến ngày nay với tư cách là một trong các API Spark. Thế giới đã thay đổi rất nhiều nhưng SQL không chỉ tồn tại mà còn rất quan trọng và hiện tại.
Nhưng SQL giống như cờ vua, luật cơ bản dễ hiểu nhưng khó thành thạo! Đó là một ngôn ngữ có nhiều khả năng, nhiều cách để giải quyết cùng một vấn đề, nhiều chức năng và từ khóa, và thật không could, nhiều chức năng bị đánh giá thấp mà nếu được biết rõ hơn có thể giúp chúng ta rất nhiều khi xây dựng truy vấn.
Vì lý do này, trong bài đăng này, tôi muốn nói về một trong những tính năng SQL không quá nổi tiếng mà tôi thấy cực kỳ hữu ích khi xây dựng các truy vấn hàng ngày của mình: Hàm cửa sổ.
SGBD truyền thống và nổi tiếng nhất (PostgreSQL, MySQL và Oracle) đều dựa trên các khái niệm đại số quan hệ. Trong đó, các dòng được gọi là bộ dữ liệu và các bảng là các quan hệ. Mối quan hệ là một tập hợp (theo nghĩa toán học) các bộ, tức là không có thứ tự hoặc kết nối nào giữa chúng. Do đó, không có thứ tự mặc định của các dòng trong bảng và phép tính được thực hiện trên một dòng không ảnh hưởng cũng như không bị ảnh hưởng bởi kết quả của dòng khác. Ngay cả các mệnh đề như ORDER BY, chỉ sắp xếp các bảng và không thể thực hiện phép tính trên một dòng dựa trên giá trị của các dòng khác.
Nói một cách đơn giản, các hàm cửa sổ sẽ khắc phục điều này, mở rộng các chức năng SQL và cho phép chúng ta thực hiện các phép tính trong một hàng dựa trên giá trị của các dòng khác.
1-Tổng hợp mà không tổng hợp
Ví dụ tầm thường nhất để hiểu các chức năng của Home windows là khả năng ‘tổng hợp mà không tổng hợp‘.
Khi chúng tôi thực hiện tổng hợp bằng GROUP BY truyền thống, toàn bộ bảng được cô đọng thành bảng thứ hai, trong đó mỗi dòng đại diện cho một thành phần của nhóm. Với Home windows Capabilities, thay vì rút gọn các dòng, bạn có thể tạo một cột mới trong cùng một bảng chứa các kết quả tổng hợp.
Ví dụ: nếu bạn cần cộng tất cả các chi phí trong bảng chi phí của mình, theo truyền thống, bạn sẽ làm:
SELECT SUM(worth) AS whole FROM myTable
Với các chức năng của Home windows, bạn sẽ tạo ra một cái gì đó như thế:
SELECT *, SUM(worth) OVER() FROM myTable
-- Observe that the window perform is outlined at column-level
-- within the question
Hình ảnh dưới đây cho thấy kết quả:
Thay vì tạo một bảng mới, nó sẽ trả về giá trị tổng hợp trong một cột mới. Lưu ý rằng giá trị giống nhau nhưng bảng thì không ‘tóm tắt‘, các dòng ban đầu vẫn được giữ nguyên — chúng tôi vừa tính toán một tổng hợp mà không tổng hợp cái bàn 😉
Mệnh đề OVER là dấu hiệu cho thấy chúng ta đang tạo một hàm cửa sổ. Mệnh đề này xác định đường nào sẽ được tính toán. Đoạn mã trên trống nên nó sẽ tính SUM() trên tất cả các dòng.
Điều này rất hữu ích khi chúng ta cần thực hiện các phép tính dựa trên tổng (hoặc trung bình, tối thiểu, tối đa) của các cột. Ví dụ: để tính toán mỗi khoản chi phí đóng góp bao nhiêu phần trăm so với tổng số.
Trong trường hợp thực tế, chúng ta cũng có thể muốn chi tiết theo một số danh mục, như trong ví dụ ở hình 2, trong đó chúng ta có chi phí công ty theo bộ phận. Một lần nữa, chúng ta có thể đạt được tổng chi tiêu của từng bộ phận bằng một NHÓM THEO đơn giản:
SELECT depto, sum(worth) FROM myTable GROUP BY depto
Hoặc chỉ định logic PHẦN THAM GIA trong hàm cửa sổ:
SELECT *, SUM(worth) OVER(PARTITION BY depto) FROM myTable
Xem kết quả:
Ví dụ này giúp hiểu tại sao thao tác này được gọi là hàm ‘cửa sổ’ — mệnh đề OVER xác định một tập hợp các dòng mà hàm tương ứng sẽ thực hiện trên đó, một ‘cửa sổ’ trong bảng.
Trong trường hợp trên, hàm SUM() sẽ hoạt động trong các phân vùng được tạo bởi cột depto (RH và SALES) — nó sẽ tính tổng tất cả các giá trị trong cột ‘giá trị’ cho từng mục trong cột depto một cách riêng biệt. Nhóm mà dòng này là một phần của (RH hoặc SALES) xác định giá trị trong cột ‘Tổng’.
2 — Nhận thức về thời gian và thứ tự
Đôi khi chúng ta cần tính giá trị của một cột trong một hàng dựa trên giá trị của các hàng khác. Một ví dụ kinh điển là tốc độ tăng trưởng GDP hàng năm của một quốc gia, được tính bằng giá trị hiện tại và giá trị trước đó.
Các phép tính kiểu này, trong đó chúng ta cần giá trị của năm vừa qua, sự khác biệt giữa hàng hiện tại và hàng tiếp theo, giá trị đầu tiên của chuỗi, v.v. là minh chứng cho sức mạnh của chức năng Home windows. Trên thực tế, tôi không biết liệu hành vi này có thể đạt được bằng các lệnh SQL tiêu chuẩn hay không! Nó có thể có thể, nhưng sẽ là một truy vấn rất phức tạp…
Nhưng các chức năng của home windows đã làm cho việc này trở nên đơn giản, hãy xem hình ảnh bên dưới (bảng ghi lại chiều cao của một số trẻ):
SELECT
12 months, peak,
LAG(peak) OVER (ORDER BY 12 months) AS height_last_year
FROM myTable
Hàm LAG( ‘column’ ) chịu trách nhiệm tham chiếu giá trị của ‘cột’ ở hàng trước. Bạn có thể tưởng tượng nó như một chuỗi các bước: Ở dòng thứ hai, hãy xem xét giá trị của dòng đầu tiên; Trong phần thứ ba, giá trị của phần thứ hai; v.v… Dòng đầu tiên không được tính (do đó VÔ GIÁ TRỊ), vì nó không có tiền thân.
Đương nhiên, cần có một số tiêu chí đặt hàng để xác định ‘dòng trước’ là gì. Và đó là một khái niệm quan trọng khác trong các chức năng của Home windows: chức năng phân tích.
Ngược lại với các hàm SQL truyền thống, các hàm phân tích (như LAG) cho rằng tồn tại một thứ tự trong các dòng – và thứ tự này được xác định bởi mệnh đề ORDER BY bên trong OVER(), tức là khái niệm về dòng thứ nhất, thứ hai, thứ ba và v.v. được định nghĩa bên trong từ khóa OVER. Đặc điểm chính của các hàm này là khả năng tham chiếu các hàng khác liên quan đến hàng hiện tại: LAG tham chiếu hàng trước, LEAD tham chiếu các hàng tiếp theo, FIRST tham chiếu hàng đầu tiên trong phân vùng, v.v.
Một điều thú vị về LAG và LEAD là cả hai đều chấp nhận đối số thứ hai, độ lệch, chỉ định số lượng hàng tiến (đối với LEAD) hoặc lùi (đối với LAG) cần xem xét.
SELECT
LAG(peak, 2) OVER (ORDER BY 12 months) as height_two_years_ago,
LAG(peak, 3) OVER (ORDER BY 12 months) as height_three_years_ago,
LEAD(peak) OVER (ORDER BY 12 months) as height_next_year
FROM ...
Và cũng hoàn toàn có thể thực hiện các phép tính với các hàm này:
SELECT
100*peak/(LAG(peak) OVER (ORDER BY 12 months))
AS "annual_growth_%"
FROM ...
3 – Nhận thức và tổng hợp thời gian
Thời gian và không gian chỉ là một — Einsteinm từng nói, hay đại loại như thế, tôi không biết ¯_(ツ)_/¯
Bây giờ chúng ta đã biết cách phân vùng và sắp xếp, chúng ta có thể sử dụng cả hai thứ này cùng nhau! Quay lại ví dụ trước, giả sử có nhiều trẻ hơn ở bàn đó và chúng ta cần tính tốc độ tăng trưởng của từng trẻ. Rất đơn giản, chỉ cần kết hợp sắp xếp và phân vùng! Hãy sắp xếp theo năm và phân chia theo tên con.
SELECT 1-height/LAG(peak) OVER (ORDER BY 12 months PARTITION BY title) ...
Truy vấn trên thực hiện như sau – Phân vùng bảng theo phần tử con và trong mỗi phân vùng, sắp xếp các giá trị theo năm và chia giá trị chiều cao của năm hiện tại với giá trị trước đó (và trừ kết quả đi một).
Chúng ta đang tiến gần hơn đến khái niệm đầy đủ về ‘cửa sổ’! Đó là một lát bảng, một tập hợp các hàng được nhóm theo các cột được xác định trong PARTITION BY được sắp xếp theo các trường trong ORDER BY, trong đó tất cả các phép tính được thực hiện chỉ xem xét các hàng trong cùng một nhóm (phân vùng) và một thứ tự cụ thể.
4-Xếp hạng và vị trí
Các hàm của Home windows có thể được chia thành ba loại, hai trong số đó chúng ta đã nói đến: Hàm tổng hợp ( COUNT, SUM, AVG, MAX, … ) và Hàm phân tích ( LAG, LEAD, FIRST_VALUE, LAST_VALUE, … ).
Nhóm thứ ba là đơn giản nhất — Hàm xếp hạng, với số mũ lớn nhất là hàm row_number(), trả về một số nguyên biểu thị vị trí của một hàng trong nhóm (dựa trên thứ tự đã xác định).
SELECT row_number() OVER(ORDER BY rating)
Các hàm xếp hạng, như tên cho thấy, trả về các giá trị dựa trên vị trí của dòng trong nhóm, được xác định theo tiêu chí đặt hàng. ROW_NUMBER, RANK và NTILE là một số được sử dụng nhiều nhất.
Trong hình trên, số hàng được tạo dựa trên điểm của mỗi người chơi
… và vâng, nó phạm phải tội lỗi lập trình khủng khiếp khi bắt đầu từ 1.
Kích thước 5 cửa sổ
Tất cả các hàm được trình bày cho đến thời điểm này đều xem xét TẤT CẢ các hàng trong phân vùng/nhóm khi tính toán kết quả. Ví dụ: hàm SUM() được mô tả trong ví dụ đầu tiên sẽ xem xét tất cả các hàng của phòng ban để tính tổng.
Nhưng có thể chỉ định kích thước cửa sổ nhỏ hơn, tức là có bao nhiêu dòng trước và sau dòng hiện tại cần được xem xét trong tính toán. Đây là một chức năng hữu ích để tính toán đường trung bình động/cửa sổ cuộn.
Hãy xem xét ví dụ sau, với một bảng chứa số ca mắc bệnh nhất định hàng ngày, trong đó chúng ta cần tính số ca trung bình xét đến ngày hiện tại và hai ngày trước đó. Lưu ý rằng có thể giải quyết vấn đề này bằng hàm LAG, được hiển thị trước đó:
SELECT
( n_cases + LAG(n_cases, 1) + LAG(n_cases, 2) )/3
OVER (ORDER BY date_reference)
Nhưng chúng ta có thể đạt được kết quả tương tự một cách tinh tế hơn bằng cách sử dụng khái niệm khung:
SELECT
AVG(n_cases)
OVER (
ORDER BY date_reference
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)
Khung trên chỉ định rằng chúng ta phải tính giá trị trung bình chỉ cho hai hàng trước đó (PRECEDING) và hàng hiện tại. Nếu chúng ta muốn xem xét dòng trước, dòng hiện tại và dòng sau, chúng ta có thể thay đổi khung:
AVG(n_cases)
OVER (
ORDER BY date_reference
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
)
Và đó chỉ là một khung – một cách để giới hạn phạm vi tiếp cận của hàm ở một giới hạn cụ thể. Theo mặc định (trong hầu hết các trường hợp), các hàm của home windows sẽ xem xét khung sau:
ROWS BETWEEN UNBOUDED PRECEDING AND CURRENT ROW
-- ALL THE PREVIOUS ROWS + THE CURRENT ROW
[ad_2]
Source link