Giới thiệu

Trong Bigquery các Window Functions, hoặc các Analytic Functions 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 . Chúng trả về một giá trị duy nhất cho mỗi dòng, ngược lại với các hàm tổng hợp (Aggregate function) trả về một giá trị duy nhất cho một nhóm dòng.

>>Đọc thêm:

KHOÁ HỌC GOOGLE BIGQUERY FOR DATA ANALYSIS

KHOÁ HỌC TRUY VẤN VÀ THAO TÁC DỮ LIỆU (SQL) TỪ CƠ BẢN ĐẾN NÂNG CAO

LỘ TRÌNH TRỞ THÀNH DATA ENGINEER CHO NGƯỜI MỚI BẮT ĐẦU

DATA ENGINEER LÀ GÌ? CÔNG VIỆC CHÍNH CỦA DATA ENGINEER? CÁC KỸ NĂNG CẦN THIẾT

Aggregate vs window/analytic functions:

SELECT
letter,
SUM(number) AS aggregate
FROM
(
SELECT
'A' AS letter,
2 AS number
UNION ALL
(   SELECT
'A' AS letter,
3 AS number)
UNION ALL
(   SELECT
'C' AS letter,
6 AS number)
) AS table_3
GROUP BY
letter

Kết quả:

SELECT
  letter,
  SUM(number) OVER (PARTITION BY letter) AS `window`
FROM
  (
    SELECT
      'A' AS letter,
      2 AS number
    UNION ALL
(    SELECT
      'A' AS letter,
      3 AS number)
    UNION ALL
(    SELECT
      'C' AS letter,
      6 AS number)
  ) AS table_3

Kết quả:

Trong ví dụ đầu tiên, dữ liệu kết quả được nhóm theo chữ cái, nhưng trong ví dụ thứ hai, chúng ta đã giữ nguyên các dòng của mình.

Tại sao sử dụng window functions?

Các chức năng của window functions rất mạnh mẽ và khi bạn đã hiểu rõ về cách sử dụng chúng, bạn sẽ ngạc nhiên về việc chúng cho phép bạn làm điều đó. Một số trường hợp sử dụng phổ biến là:

• Running/Cumulative Total (Tổng lũy kế)
• Moving Average (Trung bình động)
• Xếp hạng các hàng theo tiêu chí tùy chỉnh và nhóm
• Tìm% Thay đổi qua từng năm

Cú pháp:

window_function_name ([arguments]) OVER (
   [PARTITION BY partition_expression] 
   [ORDER BY expression ASC | DESC] 
   [ROWS frame_clause])

Trong đó PARTITION BY biểu thị cách GROUP các hàng thành các phân vùng, ORDER BY cách sắp xếp thứ tự các hàng trong các phân vùng đó và ROWS những hàng nào cần xem xét trong các phân vùng có thứ tự .

Window Functions

Nói chung, window functions có thể được nhóm thành 3 loại:

1. Navigation functions: Trả về giá trị được cung cấp cho một tiêu chí vị trí cụ thể (ví dụ: giá_trị_đầu tiên)
2. Numbering functions: Gán một số (ví dụ: Rank) cho mỗi hàng dựa trên vị trí của chúng trong window được chỉ định
3. Hàm phân tích: Thực hiện phép tính trên một tập giá trị (ví dụ: tổng)

Navigation functions

• FIRST_VALUE: FIRST_VALUE (value_expression [{RESPECT | IGNORE} NULLS]) -> Trả về value_expression cho hàng đầu tiên trong khung window hiện tại.
• LAST_VALUE: LAST_VALUE (value_expression [{RESPECT | IGNORE} NULLS]) -> Trả về value_expression cho hàng cuối cùng trong khung window hiện tại.
• NTH_VALUE: NTH_VALUE (value_expression, const_integer_expression [{RESPECT | IGNORE} NULLS]) -> Trả về value_expression cho hàng thứ N của khung window hiện tại.
• LEAD: LEAD (value_expression [, offset [, default_expression]]) -> Trả về value_expression trên hàng tiếp theo.
• LAG: LAG (value_expression [, offset [, default_expression]]) -> Trả về value_expression trên hàng trước.
• PERCENTILE_CONT: PERCENTILE_CONT (value_expression, percentile [{RESPECT | IGNORE} NULLS]) -> Trả về phân vị của value_expression với nội suy tuyến tính (tỷ lệ liên tục).
• PERCENTILE_DISC:  PERCENTILE_DIST (value_expression, percentile [{RESPECT | IGNORE} NULLS]) -> Trả về phân vị của value_expression (tỷ lệ rời rạc).

Đối với các chức năng này, chúng ta sẽ sử dụng dữ liệu demo sau:

SELECT
  'James Harden' AS player,
  2335 AS points,
  2020 AS season
UNION ALL
(SELECT
  'Damian Lillard' AS player,
  1978 AS points,
  2020 AS season)
UNION ALL
(SELECT
  'Devin Booker' AS player,
  1863 AS points,
  2020 AS season)
UNION ALL
(SELECT
  'James Harden' AS player,
  2818 AS points,
  2019 AS season)
UNION ALL
(SELECT
  'Paul George' AS player,
  1978 AS points,
  2019 AS season)
UNION ALL
(SELECT
  'Kemba Walker' AS player,
  2102 AS points,
  2019 AS season)
UNION ALL
(SELECT
  'Damian Lillard' AS player,
  2067 AS points,
  2019 AS season)
UNION ALL
(SELECT
  'Devin Booker' AS player,
  1700 AS points,
  2019 AS season)
UNION ALL
(SELECT
  'Paul George' AS player,
  1033 AS points,
  2020 AS season)
UNION ALL
(SELECT
  'Kemba Walker' AS player,
  1145 AS points,
  2020 AS season)

Kết quả:

Cách tìm sự thay đổi qua từng năm

SELECT DISTINCT
  player,
  season,
  FIRST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS first_season,
  LAST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS last_season,
  (100 * ((LAST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) - FIRST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)) / FIRST_VALUE(points) OVER (PARTITION BY player ORDER BY season ASC ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING))) AS per_change
FROM
  top_scorers

Kết quả:

Chúng ta đã sử dụng FIRST_VALUE và LAST_VALUE để tìm điểm số cho từng cầu thủ trong dữ liệu mùa giải sớm nhất và gần đây nhất. Sau đó, chúng ta tính toán phần trăm chênh lệch bằng cách sử dụng:

100 * ((new value - old value) / old value) per_difference

Numbering functions

• RANK: RANK () -> Trả về thứ hạng của mỗi hàng trong phân vùng có thứ tự (bắt đầu từ 1).
• DENSE_RANK: DENSE_RANK () -> Trả về thứ hạng, nhưng các giá trị có cùng giá trị sẽ có cùng thứ hạng (bắt đầu từ 1).
• PERCENT_RANK: PERCENT_RANK () -> Trả về thứ hạng phần trăm của một hàng.
• CUME_DIST: CUME_DIST () -> Trả về thứ hạng tương đối của một hàng.
• NTILE: NTILE (constant_integer_expression) -> Trả về số bucket sau khi chia mỗi phân vùng vào constant_integer_expression xô.
• ROW_NUMBER: ROW_NUMBER () -> Trả về số hàng tuần tự cho mỗi phân vùng được sắp xếp.

⚠️ Numbering functions không cho phép [ROWS frame_clause]

Làm thế nào để nhận được top 3 kết quả cho mỗi nhóm?

SELECT
  *
FROM
  (
    SELECT
      season,
      RANK() OVER (PARTITION BY season ORDER BY points DESC) AS points_rank,
      player,
      points
    FROM
      top_scorers
  ) AS table_1
WHERE
  (points_rank <= 3)

Kết quả:

Trong ví dụ này, chúng ta đã sử dụng RANK để xếp hạng từng người chơi theo điểm qua mỗi mùa giải. Sau đó, chúng ta sử dụng một truy vấn phụ để sau đó chỉ trả về top 3 người chơi được xếp hạng cao nhất cho mỗi mùa giải.

Aggregate functions

Các hàm tổng hợp có sẵn bên ngoài các window functions , nhưng có thể được áp dụng bổ sung trên một window cụ thể.

• ANY_VALUE: ANY_VALUE (expression) [OVER (…)] -> Trả về biểu thức cho một số dòng được chọn từ nhóm. Về cơ bản là một lựa chọn ngẫu nhiên từ một biểu thức.
• ARRAY_AGG: Trả về một mảng giá trị biểu thức.

ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS]
  [ORDER BY key [{ASC|DESC}] [, ... ]]  [LIMIT n]
) [OVER (...)]

• AVG: AVG ([DISTINCT] expression) [OVER (…)] -> Trả về giá trị trung bình của các giá trị đầu vào không phải NULL.
• CORR: CORR (X1, X2) [OVER (…)] -> Trả về hệ số tương quan Pearson cho một tập hợp các cặp số.
• COUNT: COUNT ([DISTINCT] expression] [OVER (…)] -> Trả về số phần tử [khác biệt] trong biểu thức
• COUNTIF: COUNTIF (expression) [OVER (…)] -> Trả về tổng số giá trị True cho biểu thức
• COVAR_POP: COVAR_POP (X1, X2) [OVER (…)] -> Trả về hiệp phương sai tổng thể của một tập hợp số
• COVAR_SAMP: COVAR_SAMP (X1, X2) [OVER (…)] -> Trả về hiệp phương sai mẫu của một tập hợp số
• MAX: MAX (expression) [OVER (…)] -> Trả về giá trị không NULL lớn nhất của biểu thức.
• MIN: MIN (expression) [OVER (…)] -> Trả về giá trị không NULL nhỏ nhất của biểu thức.
• ST_CUSTERDBSCAN: ST_CLUSTERDBSCAN (address_column, epsilon, Minim_geographies) OVER (…) -> Thực hiện phân nhóm DBSCAN trên một cột địa lý
• STDEV_POP: STDDEV_POP (expression [DISTINCT]) [OVER (…)] -> Trả về độ lệch chuẩn tổng thể của các giá trị
• STDEV_SAMP: STDDEV_SAMP (expression [DISTINCT]) [OVER (…)] -> Trả về độ lệch chuẩn mẫu của các giá trị
• STRING_AGG: STRING_AGG ([DISTINCT] expression [, delimiter] [ORDER BY key [{ASC | DESC}] [, …]] [LIMIT n]) [OVER (…)] -> Trả về giá trị nhận được bởi nối tất cả các giá trị không rỗng
• SUM: SUM (expression [DISTINCT]) [OVER (…)] -> Trả về tổng của tất cả các giá trị không rỗng
• VAR_POP: VAR_POP (expression [DISTINCT]) [OVER (…)] -> Trả về phương sai tổng hợp của kết quả
• VAR_SAMP: VAR_SAMP (expression [DISTINCT]) [OVER (…)] -> Trả về phương sai mẫu của kết quả

Làm thế nào để tìm một tổng lũy kế?

SELECT
  season,
  player,
  points,
  SUM(top_scorers.points) OVER (PARTITION BY player ORDER BY season ASC) AS running_total_points
FROM
  top_scorers

Kết quả:

Để tìm tổng lũy kế, chỉ cần sử dụng SUM với mệnh đề OVER trong đó bạn chỉ định các nhóm của mình ( PARTITION BY ) và thứ tự thêm chúng ( ORDER BY ).

Làm thế nào để tìm một moving average (đường trung bình)?

SELECT
  date,
  number,
  AVG(number) OVER (ORDER BY date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
  (
    SELECT
      date,
      number
    FROM
      UNNEST(GENERATE_DATE_ARRAY('2020-01-01', '2020-02-01')) AS date
      CROSS JOIN UNNEST(GENERATE_ARRAY(1, 30)) AS number
  ) AS table_1
LIMIT
  10

Kết quả:

Để đặt cửa sổ trung bình động 7 ngày, chúng ta có thể sử dụng ROWS BETWEEN 6 PRECEDING AND CURRENT ROW trong mệnh đề OVER.

Làm thế nào để tìm mặt hàng phổ biến nhất trong một nhóm?

SELECT
  item,
  category,
  qty,
  LAST_VALUE(item) OVER (PARTITION BY category ORDER BY qty DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS most_popular
FROM
  (
    SELECT
      'apple' AS item,
      'fruit' AS category,
      1 AS qty
    UNION ALL
(    SELECT
      'pear' AS item,
      'fruit' AS category,
      3 AS qty)
    UNION ALL
(    SELECT
      'green beans' AS item,
      'vegetable' AS category,
      4 AS qty)
    UNION ALL
(    SELECT
      'brussel sprouts' AS item,
      'vegetable' AS category,
      5 AS qty)
  ) AS table_4

Kết quả:

>>Đọc thêm:

KHOÁ HỌC GOOGLE BIGQUERY FOR DATA ANALYSIS

KHOÁ HỌC TRUY VẤN VÀ THAO TÁC DỮ LIỆU (SQL) TỪ CƠ BẢN ĐẾN NÂNG CAO

LỘ TRÌNH TRỞ THÀNH DATA ENGINEER CHO NGƯỜI MỚI BẮT ĐẦU

DATA ENGINEER LÀ GÌ? CÔNG VIỆC CHÍNH CỦA DATA ENGINEER? CÁC KỸ NĂNG CẦN THIẾT