Open23日前にコメント追加2

BigQueryのちょっとだけ便利なスニペット集

重複削除した結果を取得する

あるテーブル(path.to.table)に入っているデータについて、idについてcreated_atを降順に並べたときの最初の行(この場合は最新のもの)だけを残して重複を削除する。

WITH drop_duplicated AS (
  SELECT
    ARRAY_AGG(t ORDER BY created_at DESC)[OFFSET(0)] AS row
  FROM
    `path.to.table` t
  WHERE hoge = 'fuga' -- 条件は必要に応じて設定
  GROUP BY
    id
)
SELECT
  row.*
FROM
  drop_duplicated

重複削除した結果を元のテーブルに戻す

上記の重複削除に加えて、一時テーブルにそのデータを退避、元テーブルの異常部分を削除、元テーブルに戻して異常部分を回復する。一時テーブルはjob終了時に自動で削除される。

CREATE TEMP TABLE temp_table AS
WITH drop_duplicated AS (
  SELECT
    ARRAY_AGG(t ORDER BY created_at DESC)[OFFSET(0)] AS row
  FROM
    `path.to.table` t
  WHERE hoge = 'fuga' -- 条件は必要に応じて設定
  GROUP BY id
)
SELECT row.*
FROM drop_duplicated;

DELETE FROM `path.to.table` WHERE hoge = 'fuga'; -- 条件は必要に応じて設定

INSERT INTO `path.to.table` SELECT * FROM temp_table;

yoooo

表データを加工して集計する (PIVOT演算子)

以下のようなテーブルがある。

WITH Produce AS (
  SELECT 'Kale' as product, 51 as sales, 'Q1' as quarter, 2020 as year UNION ALL
  SELECT 'Kale', 23, 'Q2', 2020 UNION ALL
  SELECT 'Kale', 45, 'Q3', 2020 UNION ALL
  SELECT 'Kale', 3, 'Q4', 2020 UNION ALL
  SELECT 'Kale', 70, 'Q1', 2021 UNION ALL
  SELECT 'Kale', 85, 'Q2', 2021 UNION ALL
  SELECT 'Apple', 77, 'Q1', 2020 UNION ALL
  SELECT 'Apple', 0, 'Q2', 2020 UNION ALL
  SELECT 'Apple', 1, 'Q1', 2021)
SELECT * FROM Produce

以下のような表データになっている。

product	sales	quarter	year
Kale	51	Q1	2020
Kale	23	Q2	2020
Kale	45	Q3	2020
Kale	3	Q4	2020
Kale	70	Q1	2021
Kale	85	Q2	2021
Apple	77	Q1	2020
Apple	0	Q2	2020
Apple	1	Q1	2021

これを、カラムをproduct, year, Q1, Q2, Q3, Q4とし、QNの部分にsalesを入れるような表にしたい。

SELECT * FROM
  (SELECT product, sales, quarter, year FROM Produce) -- 不要なカラムはこのようにして落とす
  PIVOT(SUM(sales) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4'))

とすると、以下のような表を得られる。

product	year	Q1	Q2	Q3	Q4
Kale	2020	51	23	45	3
Kale	2021	70	85		
Apple	2020	77	0		
Apple	2021	1

注意点

SELECT句に書きそうな雰囲気はあるが、FROM句に書くこと
PIVOT演算子を使うと必ず集約関数を使うことになるので、必要に応じてMAXやらANY_VALUEやらを使う
無関係なカラムがあるとそこも見られるため、上記の例のように不要なカラムは除いてからPIVOT演算子に渡すこと