BigQueryで重複を排除した最新レコードを取り出す

最近社内の勉強会でなるほどと思ったので試して備忘録として残しておく

色々調べた後だと頻出するイディオムだった感はある…

BigQueryで重複を除いて最新状態のデータを出力する方法

データの取り込みもしくは入れ込む際にtimestampカラムを追加する
timestampで降順、特定キーでグルーピング、分析関数ROW_NUMBERを用いて特定のグループの中での行番号を出力
行番号が1のものを取り出す

これだけで特定キーでのユニークを担保してレコードを抽出できる

WITH
  data AS(
  SELECT
    *
  FROM
    UNNEST(ARRAY<STRUCT<ts TIMESTAMP, item STRING, store STRING, sales INT64>> [
       ("2021-05-01 00:00:00", "hoge", "A", 100),
       ("2021-05-02 00:00:00", "hoge", "A", 200),
       ("2021-05-02 00:00:00", "fuga", "A", 100),
       ("2021-05-01 00:00:00", "hoge", "B", 100),
       ("2021-05-01 00:00:00", "fuga", "B", 100),
       ("2021-05-02 00:00:00", "hoge", "B", 200)
    ])
  ),
  numbered AS (
  SELECT
    ts,
    item,
    store,
    sales,
    ROW_NUMBER() OVER (partition by item,store order by ts desc) as rn
  FROM data
  )
SELECT * FROM numbered WHERE rn = 1

サンプルでは

data
- サンプルデータを生成
numbered
- 特定キーでグループ化、tsでソートして行番号を出力
rn=1の行を抜き出す

item,storeでユニークな最新レコードのみを抽出できた

後から追加された想定のレコードに関してはsalesの値が200になっているはず

f:id:swfz:20210623201029p:plain

QUALIFYで抽出する

上記ではWITH句で一度分析関数を使用した結果を保持させたが最近はQUALIFY句でさっと取れるようになっている

WITH
  data AS(
  SELECT
    *
  FROM
    UNNEST(ARRAY<STRUCT<ts TIMESTAMP, item STRING, store STRING, sales INT64>> [
       ("2021-05-01 00:00:00", "hoge", "A", 100),
       ("2021-05-02 00:00:00", "hoge", "A", 200),
       ("2021-05-02 00:00:00", "fuga", "A", 100),
       ("2021-05-01 00:00:00", "hoge", "B", 100),
       ("2021-05-01 00:00:00", "fuga", "B", 100),
       ("2021-05-02 00:00:00", "hoge", "B", 200)
    ])
  )
  SELECT
    ts,
    item,
    store,
    sales,
    ROW_NUMBER() OVER (partition by item,store order by ts desc) as rn
FROM data
  WHERE TRUE QUALIFY rn = 1