Analytic Functions trong Google Cloud BigQuery

Khi làm việc với Bigquery của google cloud, chắc hẳn chúng ta sẽ rất hay gặp và sử dụng các analytic functions, vậy analytic functions là gì, chúng có ý nghĩa gì và cách sử dụng như thế nào, bài viết này chúng ta sẽ lần lượt tìm hiểu về chúng Trong cơ sở dữ liệu, một analytic function là một hàm để tính các giá trị tổng hợp trên một nhóm các dòng. Không giống như "aggregate functions", cái mà trả về một giá trị tổng hợp duy nhất trên một nhóm các hàng, các analytic function trả về một giá trị cho mỗi hàng bằng cách tính toán hàm trên một nhóm hàng đầu vào. Analytic function là một cơ chế mạnh mẽ để diễn tả ngắn gọn các hoạt động phân tích phức tạp, và thực sự chúng đã đem lại nhiều giá trị đầy hiệu quả bởi việc tránh được các câu lệnh JOIN tốn kém hay các tính toán bên ngoài truy vấn SQL. Analytic function còn được gọi là "(analytic) window functions" trong SQL tiêu chuẩn hay một vài cơ sở dữ liệu thương mại, đó là bởi vì các analytic function được đánh giá qua một nhóm các hàng, ám chỉ như là một window, hay window frame. Trong một vài cơ sở dữ liệu khác, chúng còn được gọi là Online Analytical Processing (OLAP). Cú pháp đơn giản:

    analytic_function_name ( [ argument_list ] )
      OVER (
        [ PARTITION BY partition_expression_list ]
        [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
        [ window_frame_clause ]
      )

Một analytich function yêu cầu một mệnh đề OVER, cái mà sẽ định nghĩa một window frame, cái mà analytic function sẽ được đánh giá trên đó. Mệnh đề OVER gồm 3 mệnh đề optional. Bigquery sẽ đánh giá trên các mệnh đề con này theo thứ tự mà chúng được viết:

• PARTITION BY: Mệnh đề này sẽ chia các dòng đầu vào thành các phân vùng partition, tương tự như group by nhưng chúng sẽ không tổng hợp các hàng lại theo key giống nhau nào đó.
• ORDER BY: mệnh đề nhằm sắp xếp trên mỗi phân vùng partition
• window_frame_clause: định nghĩa window frame trên partition hiện tại

Mệnh đề OVER cũng có thể là rỗng(OVER()) trong trường hợp window frame bao gồm tất cả các hàng đầu vào Ví dụ: Giả sử có 1 công ty muốn tạo một bảng thành tích cho mỗi bộ phận để show cấp bậc cho mỗi nhân viên ở phòng ban của họ, và show xem nhân viên nào thâm niên lâu nhất. Ta có bảng Employees có 3 cột là: Name, StartDate, và Department. Đoạn query dưới đây sẽ tính toán rank của mỗi nhân viên tại phòng ban của họ:

    SELECT firstname, department, startdate,
      RANK() OVER ( PARTITION BY department ORDER BY startdate ) AS rank
    FROM Employees;

Quá trình tính toán được minh họa như trong hình dưới đây: Bigquery sẽ tính toán trên mỗi mệnh đề con của mệnh đề over theo thứ tự mà chúng xuất hiện:

1. PARTITION BY: bảng đầu tiên được chia làm 2 phân vùng bởi deparment
2. ORDER BY: từng dòng của mỗi nhân viên được sắp xếp theo startdate
3. Framing: None, mệnh đề window frame được disallowed với method RANK()
4. RANK(): thứ hạng thâm niên được tính cho mỗi hàng trên window frame

Analytic Function Syntax:

    analytic_function_name ( [ argument_list ] )
      OVER { window_name | ( [ window_specification ] ) }

    window_specification:
      [ window_name ]
      [ PARTITION BY partition_expression_list ]
      [ ORDER BY expression [{ ASC | DESC }] [, ...] ]
      [ window_frame_clause ]

    window_frame_clause:
    { ROWS | RANGE }
    {
      { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
      |
      { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
    }

    window_frame_boundary_start:
    { UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

    window_frame_boundary_end:
    { UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

Analytic function có thể xuất hiện ở 2 nơi sau trong câu query:

• SELECT LIST: nếu analytic function xuất hiện trong list select thì list_arguments của nó không thể tham chiếu đến các alias đã khai báo trong list select.
• mệnh đề ORDER BY: nếu analytic function xuất hiện trong mệnh đề order by của câu query, list_argument của nó có thể tham chiếu đến các alias đã khai báo trong list select. Hơn nữa, một analytic function không thể tham chiếu đến đến một analytic function khác trong list argument của nó, hay trong mệnh đề OVER, ngay cả khi gián tiếp thông qua một alias Câu query sau là invalid:

    SELECT ROW_NUMBER() OVER () AS alias1
    FROM Singers
    ORDER BY ROW_NUMBER() OVER(PARTITION BY alias1)

PARTITION BY Clause

Syntax:

    PARTITION BY expression [, ... ]

Mệnh để PARTITION BY chia các dòng đầu vào thành các partition riêng biệt, qua đó analytic function được đánh giá độc lập. Multi expression được cho phép trong mệnh đề PARTITION BY. Kiểu dữ liệu của expression phải là groupable, có nghĩa là expression không phải là một trong các kiểu dữ liệu sau:

• Floating point
• Struct
• Array

Nếu không tồn tại mệnh đề PARTITION BY, Bigquery sẽ hiểu tất cả dòng nhập vào là một partition đơn

ORDER BY Clause

Syntax:

    ORDER BY expression [ ASC | DESC ] [, ... ]

Mệnh đề ORDER BY sẽ định nghĩa sự sắp xếp trên mỗi partition, hỗ trợ các kiểu dữ liệu giống với mệnh đề order by thông thường, ngoại trừ các kiểu dữ liệu:

• Array
• Struct

Nếu một mệnh để OVER chứa mệnh đề ORDER BY, nhưng không tồn tại window_frame_clause, thì ORDER BY sẽ ngầm định window_frame_clause là:

    RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Window Frame Clause

Syntax:

    { ROWS | RANGE }
    {
      { UNBOUNDED PRECEDING | numeric_expression PRECEDING | CURRENT ROW }
      |
      { BETWEEN window_frame_boundary_start AND window_frame_boundary_end }
    }

    window_frame_boundary_start:
    { UNBOUNDED PRECEDING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

    window_frame_boundary_end:
    { UNBOUNDED FOLLOWING | numeric_expression { PRECEDING | FOLLOWING } | CURRENT ROW }

window_frame_clause định nghĩa một window frame, trên các hàng hiện tại của một partition, qua đó analytic function được đánh giá. window_frame_clause cho phép cả 2 loại window frame: vật lý(định nghĩa bởi ROWS()) và logic(được định nghĩa bởi RANGE()).