Tìm hiểu PIVOT trong BigQuery

Đôi khi, bạn muốn định dạng lại kết quả truy vấn của bảng để có các cột riêng biệt cho từng giá trị duy nhất. Đây được gọi là Pivot table, nó chỉ là một chức năng hiển thị được hỗ trợ bởi các công cụ BI. Tuy nhiên, đôi khi có thể hữu ích khi tạo pivot table trong SQL. Dưới đây là cách sử dụng toán tử PIVOT trong Google BigQuery

Pivot hoạt động như thế nào?

Pivot có chức năng thay đổi hàng thành cột. Ví dụ: giả sử chúng ta có một bảng các chuyến bay và các chuyến bay bị delays như sau:

Và chúng ta muốn đầu ra sẽ là thời gian trễ cho từng cặp sân bay-hãng hàng không, trong đó mã hãng hàng không là các cột:

Chúng tôi sẽ thực hiện một PIVOT. Lưu ý rằng nếu chúng tôi chỉ muốn thời gian trễ trung bình ở các hàng khác nhau, chúng tôi có thể chỉ cần làm như sau:

SELECT 
  airline,
  departure_airport,
  AVG(departure_delay)
FROM `bigquery-samples.airline_ontime_data.flights`
GROUP BY 1, 2

và kết quả:

Cú pháp PIVOT

FROM from_item[, ...] pivot_operator

pivot_operator:
    PIVOT(
        aggregate_function_call [as_alias][, ...]
        FOR input_column
        IN ( pivot_column [as_alias][, ...] )
    ) [AS alias]

as_alias:
    [AS] alias

Pivot operator trong BigQuery cần bạn để xác định ba điều:

• from_item có chức năng như đầu vào. Ba cột (hãng hàng không, sân bay khởi hành, thời gian trễ) trong bảng chuyến bay là from_item.
• aggregate vì mỗi ô của bảng đầu ra bao gồm nhiều giá trị. Ở đây sẽ là AVG của thời gian trễ
• pivot_column , cột có giá trị tạo thành các cột trong bảng đầu ra. 

Flights table PIVOT

Chúng tôi muốn lấy ra thời gian delay trung bình của sân bay và hãng hàng không. Truy vấn tổng hợp sẽ là:

SELECT * FROM
(
  -- #1 from_item
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  -- #2 aggregate
  AVG(departure_delay) AS avgdelay
  -- #3 pivot_column
  FOR airline in ('AA', 'KH', 'DL', '9E')
)

Kết quả:

Tự động tạo pivot column values

Điều gì sẽ xảy ra nếu bạn không thể liệt kê trước được tất cả các giá trị có thể có hoặc pivot column? Một giải pháp là sử dụng script. Ở đây, chúng tôi tạo một biến có tên là airlines và nó in ra chuỗi ở định dạng phù hợp:

DECLARE airlines STRING;
SET airlines = (
  SELECT 
    CONCAT('("', STRING_AGG(DISTINCT airline, '", "'), '")'),
  FROM `bigquery-samples.airline_ontime_data.flights`
);

Sau đó, tôi có thể sử dụng EXECUTE IMMEDIATE để chạy truy vấn:

EXECUTE IMMEDIATE format("""
SELECT * FROM
(
  SELECT 
    airline,
    departure_airport,
    departure_delay
  FROM `bigquery-samples.airline_ontime_data.flights`
)
PIVOT
(
  AVG(departure_delay) AS avgdelay
  FOR airline in %s
)
ORDER BY departure_airport ASC
""", airlines);

Kết quả: