BigQueryのPIVOTとUNPIVOTについてまとめる

BigQuery の PIVOT と UNPIVOT について

BigQuery では 2021/05/10 のリリースで PIVOT と UNPIVOT がサポートされています。この記事は PIVOT と UNPIVOT について書いていきます。

PIVOT

PIVOT演算子はFROM句の一部であり、行を列に回転して集計します。

PIVOT 例

以下のようなフルーツの販売数を示したテーブル「FruitSales」テーブルがあるとします。

WITH FruitSales AS (
  SELECT 'Banana' as fruit, 12 as sales, 'q1' as quarter, 2020 as year UNION ALL
  SELECT 'Banana', 21, 'q2', 2020 UNION ALL
  SELECT 'Banana', 16, 'q3', 2020 UNION ALL
  SELECT 'Banana', 9, 'q4', 2020 UNION ALL
  SELECT 'Banana', 57, 'q1', 2021 UNION ALL
  SELECT 'Banana', 25, 'q2', 2021 UNION ALL
  SELECT 'Apple', 35, 'q1', 2020 UNION ALL
  SELECT 'Apple', 0, 'q2', 2020 UNION ALL
  SELECT 'Apple', 0, 'q1', 2021 UNION ALL
  SELECT 'Orange', 8, 'q3', 2020 UNION ALL
  SELECT 'Orange', 2, 'q4', 2020 UNION ALL
  SELECT 'Orange', 64, 'q2', 2021)
SELECT * FROM fruit_sales

+---------+-------+---------+------+
| fruit   | sales | quarter | year |
+---------+-------+---------+------|
| Banana  | 12    | q1      | 2020 |
| Banana  | 21    | q2      | 2020 |
| Banana  | 16    | q3      | 2020 |
| Banana  | 9     | q4      | 2020 |
| Banana  | 57    | q1      | 2021 |
| Banana  | 25    | q2      | 2021 |
| Apple   | 35    | q1      | 2020 |
| Apple   | 0     | q2      | 2020 |
| Apple   | 0     | q1      | 2021 |
| Orange  | 8     | q3      | 2020 |
| Orange  | 2     | q4      | 2020 |
| Orange  | 64    | q2      | 2021 |
+---------+-------+---------+------+

PIVOT演算子により、カラム quarter の縦並びを横並びにして sales の合計値を算出します。quarter 以外の列は、集計関数 SUM を使うと、集計されない列（今回の場合はfruitとyear）は暗黙的にグループ化されます。

SELECT *
FROM FruitSales
  PIVOT(
    SUM(sales)
    FOR quarter
    IN ('q1', 'q2', 'q3', 'q4')
  )

+---------+------+------+------+------+------+
| fruit   | year | q1   | q2   | q3   | q4   |
+---------+------+------+------+------+------+
| Banana  | 2020 | 12   | 21   | 16   | 9    |
| Banana  | 2021 | 57   | 25   | NULL | NULL |
| Apple   | 2020 | 35   | 0    | NULL | NULL |
| Apple   | 2021 | 0    | NULL | NULL | NULL |
| Orange  | 2020 | NULL | NULL | 8    | 2    |
| Orange  | 2021 | NULL | 64   | NULL | NULL |
+---------+------+------+------+------+------+

yearを含めない場合は関数はfruitのみでグループ化されます。

SELECT *
FROM (SELECT fruit, sales, quarter FROM FruitSales)
  PIVOT(
    SUM(sales)
    FOR quarter
    IN ('q1', 'q2', 'q3', 'q4')
  )

+---------+------+------+------+------+
| fruit   | q1   | q2   | q3   | q4   |
+---------+------+------+------+------+
| Banana  | 69   | 46   | 16   | 9    |
| Apple   | 35   | 0    | NULL | NULL |
| Orange  | NULL | 64   | 8    | 2    |
+---------+------+------+------+------+

全てのフルーツの合計を q1、q2 のみ表示することも可能です。

SELECT * FROM
  (SELECT sales, quarter FROM FruitSales)
  PIVOT(SUM(sales) FOR quarter IN ('q1', 'q2'))

+-----+-----+
| q1  | q2  |
+-----+-----+
| 104 | 110 |
+-----+-----+

UNPIVOT

UNPIVOT演算子はFROM句の一部であり、列を行に回転させます。

UNPIVOT 例

以下のようなテーブルが四半期ごとのフルーツの販売数を示したテーブル「Sales」があるとします。

WITH Sales AS (
  SELECT 'Banana' as fruit, 13 as Q1, 24 as Q2, 75 as Q3, 1 as Q4 UNION ALL
  SELECT 'Apple', 4, 10, 24, 3 UNION ALL
  SELECT 'Orange', 31, 0, 29, 8
)
SELECT * FROM Sales

+---------+----+----+----+----+
| fruit   | q1 | q2 | q3 | q4 |
+---------+----+----+----+----+
| Banana  | 13 | 24 | 75 | 1  |
| Apple   | 4  | 10 | 24 | 3  |
| Orange  | 31 | 0  | 29 | 8  |
+---------+----+----+----+----+

UNPIVOT演算子により、q1~q4 の横並びを縦並びにします。

SELECT *
FROM Sales
  UNPIVOT(
    sales
    FOR quarter
    IN (q1, q2, q3, q4)
  )

+---------+-------+---------+
| fruit   | sales | quarter |
+---------+-------+---------+
| Banana  | 13    | q1      |
| Banana  | 24    | q2      |
| Banana  | 75    | q3      |
| Banana  | 1     | q4      |
| Apple   | 4     | q1      |
| Apple   | 1     | q2      |
| Apple   | 10    | q3      |
| Apple   | 24    | q4      |
| Orange  | 31    | q1      |
| Orange  | 0     | q2      |
| Orange  | 29    | q3      |
| Orange  | 8     | q4      |
+---------+-------+---------+

半期ごとの集計にする場合は以下のようなクエリを記述します。

SELECT *
FROM Sales
  UNPIVOT(
    (s1_sales, s2_sales)
    FOR semester
    IN ((q1, q2) AS 's1', (q3, q4) AS 's2')
  )

+---------+----------+----------+-----------+
| fruit   | s1_sales | s2_sales | semester  |
+---------+----------+----------+-----------+
| Banana  | 13       | 24       | s1        |
| Banana  | 75       | 1        | s2        |
| Apple   | 4        | 10       | s1        |
| Apple   | 24       | 3        | s2        |
| Orange  | 31       | 0        | s2        |
| Orange  | 29       | 8        | s2        |
+---------+----------+----------+-----------+

参考

備考

Hakky では社内のデータ活用やサービスとしてデータ提供を行うためのソリューションを展開しております。

「BigQuery の管理や使い方に困っている」など具体的な相談はもちろんのこと、「どんなことをお願いできるのか知りたい」や「こんなことをやりたい」など、ご検討段階でも構いませんので、ぜひお気軽にフォームよりお問い合わせくださいませ。