初心者向けbigqueryでよく使うSQLのメモ

bigqueryの標準SQLでよく使うコードのメモ（適宜改定＆追加予定）
※2019/10月のアップデートのScripting in standard SQLでbigqueryで変数などが使えるようになったので、そのコードも追加していきます。

用途

毎日SQL叩いてる人には不要ですが、久々に叩く時にあれ？どうだったけ？ということが多い方に役に立つ感じにしていきたい。
→コピペして中身変えればそのまま使える感じが理想。

選択範囲実行

2021/8月ごろから新UIに変わって、選択範囲実行できなくなった！？と思ってましたが、選択範囲だけ指定すればその中身を実行してくれるように変更されたようです。ありがたし。
なお、選択範囲を選んでから、Command + E でも同様に選択範囲実行できます（これは旧UI、真UI共通で利用可能）

Window関数

特定項目で連番をつける

SELECT
  user_id
  , buy_date
  -- buy_date毎に並び替えてから、user_id毎に連番をつける。
  , ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY buy_date) AS rownum
FROM
  `dataset.table`

特定項目の累積値を算出する

SELECT
    id
    , sales_date
    , price
    -- 累積金額の算出　id単位でsales_date順に並べ替えて、priceを累積していく
    , SUM(price) OVER (PARTITION BY id ORDER BY sales_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as price_cumulative
FROM
    `dataset.table1`

特定ランキング情報から、四分位数を求める

SELECT
    range
    , min
    , percent[offset(25)] AS per_25
    , percent[offset(50)] AS per_50
    , percent[offset(75)] AS per_75
    , max
  FROM 
  (
    SELECT
      range 
      , MIN(ranking) AS min
      , MAX(ranking) AS max
      , APPROX_QUANTILES(ranking, 100) AS percent
    FROM
      `dataset.table1`
    GROUP BY
      range
  )

前後のレコードから値を取得する

SELECT
    -- id毎に日付順に並び替えて、１つ前のレコードのitem情報を取得
    LAG (item, 1) OVER (PARTITION BY id ORDER BY date) AS before_item 

    -- id毎に日付順に並び替えて、１つ後のレコードのitem情報を取得
    , LEAD (item, 1) OVER (PARTITION BY id ORDER BY date) AS next_item

FROM
    `dataset.table1`  

関数

対象項目にnullが入っていた場合、別値を取得する

-- 2つのテーブルからflg情報を取得し、値が存在したものを設定する。
SELECT
  ID
  -- # 左から参照し、値にnull以外が入っていたものを設定する
  , COALESCE(t1.flg, t2.flg, 0) AS status 
FROM 
  `dataset.table` AS t1
FULL OUTER JOIN
  `dataset.table` AS t2
ON
  t1.id = t2.id

CREATE

基本のCREATE

-- 新規作成時に同名のテーブルがなければ作成する
CREATE TABLE IF NOT EXISTS
  sample.table
(
  name STRING
  , num INT64       
  , buy_day DATE
);


-- テーブルの作成、同名のテーブルがあれば上書きする
CREATE OR REPLACE TABLE
  sample.table
(
  name STRING
  , num INT64       
  , buy_day DATE
);

TEMP TABLE(一時利用テーブル）の作成

-- tempテーブルの作成
CREATE OR REPLACE TEMP TABLE temp_table1 AS
    SELECT '1' as id, 'みかん' as name, '2021-01-01' as start_date, '2021-01-30' as end_date, 100 as price union all 
    SELECT '2', 'りんご', null,         '2021-01-30', 200 union all 
    SELECT '3', 'バナナ', '2021-01-01', '2021-01-30', 150 union all 
    SELECT '4', 'スイカ', '2021-01-01',  null       , 800 union all 
    SELECT '5', 'いちご', '2021-01-01', '2021-01-30', 500 union all 
    SELECT '6', 'メロン', '2021-01-01', '2021-01-30', 900
;
-- 作成したtempテーブルを確認する
SELECT 
    * 
FROM
    temp_table1
ORDER BY
    id

bigqueryでは、TEMP TABLEはCREATEするクエリとSELECTなどのスクリプトを組み合わせないと使えないです。
単体でCREATE TEMP TABLEをすると Use of CREATE TEMPORARY TABLE requires a script 的なエラーが出るので注意です。

Functionの作成（定義関数）

bigqueryで定義関数を作成する方法

まずは、プロジェクト名の横３点リーダーをクリックすると、「データセットを作成」が表示されるのでそこをクリック
次に以下のようなウインドウが出るので、作成したいデータセットIDとロケーションを設定して「データセットを作成」をクリック

あとはCREATE FUNCTIONで処理を作成する

以下例は dataset.func.jst({UTC日時})を入力として日時をJST変換した結果を出力する

-- functionの作成
CREATE OR REPLACE FUNCTION `dataset.func.jst`(t TIMESTAMP) AS (
datetime(extract(datetime from t at time zone 'Asia/Tokyo'))
);

上記のFunctionを実行した結果

-- funcionを使ってUTC→JST変換する
SELECT
 `dataset.func.jst`("2020-01-01 00:00:00.000");

> 2020-01-01T09:00:00

INSERT

-- レコードのinsert処理
INSERT
  dataset.table(name, num, buy_day)
VALUES
  ('みかん', 10, '2019-11-05')

UPDATE

-- レコードのupdate処理
UPDATE
  dataset.table
SET
  num = 15
WHERE
  name = 'みかん' 
AND 
  buy_day = '2019-11-05';

DELETE

-- レコードのdelete処理
DELETE FROM
  dataset.table
WHERE
  name = 'みかん' 
;

TRANCATE

-- テーブルを空にする（レコード全件削除)
TRUNCATE TABLE
  dataset.table;

DROP

-- テーブルを削除する
 DROP TABLE
  dataset.table;

DECLAREとSET

-- 事前にDECLAREで変数定義必要
DECLARE from_day, to_day DATE;

-- 変数に値を設定
SET from_day = DATE('2019-11-01');
SET to_day = DATE('2019-11-30');

SELECT
  name, num, buy_day
FROM
  　`dataset.table`  -- DECLAREを利用時にはエラーになることがある為、引用符(`)で囲む
WHERE
  buy_day BETWEEN from_day AND to_day
;
 

日付操作

UTC→JST変換

SELECT
  created_at AS UTC
  -- created_atがtimestamp型の時
  , FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', created_at, 'Asia/Tokyo') AS JST
  -- created_atがstring型の時はこちらを利用
  , FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S", TIMESTAMP(SUBSTR(created_at, 1, 19)),"Asia/Tokyo") AS JST_ex2

日付の切り捨てなど

SELECT
  DATE(TIMESTAMP('2020-10-20 00:00:00'), 'Asia/Tokyo') as Date
  , DATE_TRUNC(DATE(TIMESTAMP('2020-10-20 00:00:00'), 'Asia/Tokyo'), WEEK(MONDAY)) as Week_mondy
  , DATE_TRUNC(DATE(TIMESTAMP('2020-10-20 00:00:00'), 'Asia/Tokyo'), MONTH) as month
  , DATE_TRUNC(DATE(TIMESTAMP('2020-10-20 00:00:00'), 'Asia/Tokyo'), YEAR) as year

日付項目を年月で表示する

SELECT
  FORMAT_DATE("%Y-%m", target_date) as ym
FROM
  dataset.table

timestamp項目を年月日などに変換する

SELECT
  SUBSTR(CAST(timestamp_field_0 AS STRING), 1, 4) as year
  SUBSTR(CAST(timestamp_field_0 AS STRING), 1, 7) as ym
  SUBSTR(CAST(timestamp_field_0 AS STRING), 1, 10) as ymd
FROM
  dataset.table

timestamp項目の変換その②

SELECT
  FORMAT_TIMESTAMP("%Y-%m-%d", timestamp_field_0) AS ymd  -- 年月日に変換する
  FORMAT_TIMESTAMP("%F　%T", timestamp_field_0) AS target_at  -- 年月日時刻に変換する
FROM
  dataset.table

_PARTITIONの日時をSELECTで表示する方法

SELECT
  _PARTITIONDATE AS target_date  -- 必ずAS指定して名称を変換必要。そのままだとエラーになる
  , _PARTITIONTIME AS target_time -- 必ずAS指定して名称を変換必要。そのままだとエラーになる
  FROM
    dataset.table
  WHERE
    _PARTITIONDATE >= DATE_ADD(current_date(), INTERVAL -3 DAY) -- 3日分のPARTITIONデータを取得

日付差分を算出する方法

SELECT 
    ID
    , start_date
    , end_date
    -- start_dateからend_dateまでの日数を取得
    , DATE_DIFF( DATE(end_date), DATE(start_date) , DAY) as n_days

    -- start_dateからend_dateまでの週数を取得
    , DATE_DIFF( DATE(end_date), DATE(start_date) , WEEK) as n_weeks

    -- start_dateからend_dateまでの月数を取得
    , DATE_DIFF( DATE(end_date), DATE(start_date) , MONTH) as n_months
FROM 
    dataset.table

経過満月数を算出する方法

-- 事前にDECLAREで変数定義必要
DECLARE from_date, to_date DATE;

-- 変数に値を設定
SET from_date = DATE('2020-02-20');
SET to_date = DATE('2020-03-10');

--　上記設定だと、日付的には19日差分だが、DATE_DIFFで、パラメータにMONTH指定すると、1ヶ月差分ありとなってしまう。
SELECT 
    DATE_DIFF(to_date, from_date, MONTH);
--　実行結果 > 1 

-- 以下のコードを利用すると、0ヶ月差分と表記することが可能。 
SELECT
    DATE_DIFF(to_date, from_date, MONTH) 
    + IF(DATE_DIFF(to_date, DATE_ADD(from_date, INTERVAL DATE_DIFF(to_date, from_date, MONTH) MONTH), DAY) >= 0, 0, -1)
--　実行結果 > 0 

このやり方は、こちらのブログを参考にさせていただきました。
月別でのLTVなど正確な経過月数を出す時に使えます。

対象日付を基準にした、月曜日や水曜日などの特定曜日の日付を抽出する

SELECT
  target_date
  , DATE_TRUNC(target_date, WEEK(MONDAY)) AS target_monday
  , DATE_TRUNC(target_date, WEEK(WEDNESDAY)) AS target_wednesday
FROM
  ( SELECT DATE('2021-03-18') AS target_date)

↓実行結果

不揃いな日付の整理

例えば、スプレッドシートやExcelなどからデータを取得する場合に、

target_date
2021/3/25
2021-3-25
2021/3/25 12:00

といった不揃いな日付データがSTRING型で混在しているものをDBに取り込む必要がある場合などは、以下コードを利用することで、target_dateのレイアウトをDATE型に変換して綺麗に揃えることができます。

SELECT
  -- SUBSTRでtarget_dateの１０文字目までを取得し、REPLACEで "/"　を "-"に置き換える、そしてDATEで日付型にする。
  DATE(REPLACE(SUBSTR(target_date, 1, 10), "/","-")) AS target_date
FROM
  `dataset.table`

CURRENT_DATEでJST指定の日付情報を取得する

SELECT
  DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 1 DAY)

過去分の税抜き価格から税込価格を算出する

SELECT
  ID
  , price  -- 税抜き価格 
  , CASE
      WHEN sales_month BETWEEN '1989-04' AND '1997-03' THEN price * 1.03
      WHEN sales_month BETWEEN '1997-04' AND '2014-03' THEN price * 1.05
      WHEN sales_month BETWEEN '2014-04' AND '2019-09' THEN price * 1.08
      WHEN sales_month >= '2019-10' THEN price * 1.1
      ELSE price 
    END AS price_including_tax  -- 税込価格
FROM
  `dataset.table'

大抵は、消費税テーブル的なもの用意して参照することが多いと思いますが、そういうのがない場合での一時しのぎ的な感じで利用。

時刻操作

10分単位にレコード集計する

SELECT
  CONCAT(SUBSTR(FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP(time), 'Asia/Tokyo'), 0, 15), '0') AS target_date
  , COUNT(1) AS COUNT
FROM
  `dataset.table'
GROUP BY
  1

時間差分を取得する

SELECT
  -- start_time から end_time　までの秒差分を取得
  TIMESTAMP_DIFF(TIMESTAMP(end_time), TIMESTAMP(start_time), SECOND) AS diff_second

  -- start_time から end_time　までの分差分を取得
  TIMESTAMP_DIFF(TIMESTAMP(end_time), TIMESTAMP(start_time), MINUTE) AS diff_minute

  -- start_time から end_time　までの時間差分を取得
  TIMESTAMP_DIFF(TIMESTAMP(end_time), TIMESTAMP(start_time), HOUR) AS diff_hour
FROM
  `dataset.table'

UnixタイムスタンプをUTC日時に変換する

SELECT
 TIMESTAMP_SECONDS(1706255255) AS converted_datetime
FROM
  `dataset.table'

ファンクションの利用例

CREATE TEMP FUNCTION FROM_DATE() AS (DATE('2020-10-26'));
CREATE TEMP FUNCTION TO_DATE() AS (DATE('2020-10-27'));

SELECT
  *
FROM
  dataset.table
WHERE
  target_date BETWEEN FROM_DATE() AND TO_DATE() 

文字列操作

区切り位置を指定して文字列を分割して取り出す

SELECT 
  -- SPLITで文字列を' '(スペース)で区切り、offsetで値を取り出す
  SPLIT("hello world everyone", ' ')[offset(0)]   -- 0番目の要素　hello
  , SPLIT("hello world everyone", ' ')[offset(1)] -- 1番目の要素　world
  , SPLIT("hello world everyone", ' ')[offset(2)] -- 2番目の要素　everyone

出力結果

row	f0_	f1_	f2_
1	hello	world	everyone

時刻にminなどの項目が入っている項目を計算できるように変換する

例えば、時間項目の中に、「20min」などの単位が混在しているデータを数値型にして、秒に変換する必要がある場合、

SELECT
  date_time
  -- SPLIT関数で min を取り外す
  , SPLIT(date_time, 'min')[OFFSET(0)]

  -- SPLITで min を取り外した上で、CASTで数値型に変換する
  , CAST(SPLIT(date_time, 'min')[OFFSET(0)] AS INT64)

  -- SPLITで min を取り外した上で、CASTで数値型に変換して分単位→秒単位に変更する
  , CAST(SPLIT(date_time, 'min')[OFFSET(0)] AS INT64) * 60

出力結果

date_time	f0_	f1_	f2_
10min	10	10	600

小数点以下の操作

SELECT
  price
  , ROUND(price)  -- 小数点以下四捨五入
  , TRUNC(price) -- 小数点以下切り捨て

テーブル操作

特定のカラム名を持っているテーブル情報を取得する

SELECT
  CONCAT(table_catalog, '.', table_schema, '.', table_name) as table_name
  , column_name
  , is_nullable
  , data_type
FROM
  `dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE
  column_name = 'target_date'  -- 検索したい項目名を入れる

テーブルからカラム名をワイルドカード検索する（大きなテーブルから特定項目名のみを抽出する場合に活用）

SELECT
  table_schema
  , table_name
  , column_name
FROM
  `dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name = 'table名'  -- 検索したいテーブル名を入れる
AND
  column_name like '%date%'  -- ワイルドカード検索したい項目名を入れる

テーブルのカラム名一覧を取得する

SELECT
  column_name
FROM
  `dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE
  table_name = 'table名'  -- 検索したいテーブル名を入れる

数字操作

数値に1000桁単位で,を入れる方法

SELECT
  FORMAT("%'d", SUM(price)) AS item_price -- 100,000,000 という感じで数値表記変換する

0除算を回避する

SELECT
  target_date
  , price
  , paid_user
  , price / paid_user AS ARPU  -- > どちらかの項目に0が入ってると 0除算エラーになり処理できない
  , IFNULL(SAFE_DIVIDE(price, paid_user), 0) as ARPU  -- > IFNULLとSAFE_DIVIDEでエラーを回避可能

上記のようにtarget_date毎にレコードを集計して、price / paid_user を算出した時に特定の日は price=0, paid_user=0 のパターンが存在した場合、 0 / 0 = 0除算エラーとなって処理できないことがある。その場合は上記のようにIFNULLとSAFE_DIVIDEで回避可能。

テーブル結合

table1とtable2の同一IDで結合する

SELECT
  t1.id
  , t2.sales
FROM
  `dataset.table1` AS t1
JOIN
  `dataset.table2` AS t2
  ON
    t1.id = t2.id

table1のレコードは全て抽出対象で、table2と一致した情報のみsalesを表示する

SELECT
  t1.id
  , COALESCE(t2.sales, 0) AS sales  -- 結合対象にならなかった場合は、NULLになる為、NULLの場合何を入れるか？を指定しておくことが多い 
FROM
  `dataset.table1` AS t1
LEFT JOIN
  `dataset.table2` AS t2
  ON
    t1.id = t2.id

個人的にですが、LEFT JOINする場合、COALESCE指定漏れてしまうことが多くて、その場合、NULLが入ってしまうので、これ以降の処理でなんかうまくデータ抽出できない。ってハマる場合が多いので要注意。

table1のレコードからtable2に存在する情報は全て抽出対象外にする処理その①

SELECT
  t1.id
FROM
  `dataset.table1` AS t1
WHERE
  NOT EXISTS
  (
     SELECT
       id
     FROM
       `dataset.table2` AS t2
     WHERE
       t1.id = t2.id
   )

例えば、NGリストとか、テスト用のデモid情報などが存在した場合に、それらを抽出から除外する場合などに利用する事が多い。
WHERE以下でNOT IN で指定することもできますが、件数が多いと効率悪いので、なるべく NOT EXISTS を使う方がパフォーマンス的にも良い。

table1のレコードからtable2に存在する情報をすべて対象外にする処理その②

SELECT 
    *
FROM 
    `dataset.table1`

EXCEPT DISTINCT

SELECT 
    *
FROM
    `dataset.table2`

処理その①と同様な処理ですが、こちらのメリットは項目が複数あっても一気に重複除外するので、内容によって使い分ける感じです。
ただし処理その①よりも処理が重くなると思うので、その辺りだけ注意していただければ。

サンプリング

ランダムにデータを絞って抽出する方法

SELECT 
  *
FROM
  `dataset.table`
WHERE 
    -- 以下を指定することでテーブルの10%のデータをランダムに抽出する
    RAND() < 0.1

繰り返し処理

LOOP処理

-- ループ項目の定義
DECLARE from_date DATE;
-- 変数に値を設定
SET from_date = DATE('2022-01-01'); -- 処理を開始する日付を指定する

-- LOOP処理
LOOP
    --　LOOPを抜ける条件を設定（この例では処理日前日まで繰り返す）
    IF from_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) 
        THEN
            LEAVE;
    ELSE
    -- ↓繰り返し処理をここに記述する

 
    -- ↑繰り返し処理の記述
    END IF;

    -- 繰り返し作成する期間を指定
    SET from_date = DATE_ADD(from_date, INTERVAL 1 DAY); 

END LOOP;

上記の応用で、過去分のパーティションテーブルを一気に作成する処理例はこちらの記事を参考に

PARTITIONを絞ることによるクエリコストの削減

このあたりの指定をすることで、biguqeryでの参照データ量をかなり削減できるので、おすすめです。

PARTITIONから曜日単位のデータのみを抽出する

SELECT 
  *
FROM
  `dataset.table`
WHERE 
  _PARTITIONDATE = DATE_TRUNC(_PARTITIONDATE, WEEK(MONDAY)) -- 月曜日のPARTITONのみを抽出

PARTITIONから月初のデータのみを抽出する

SELECT 
  *
FROM
  `dataset.table`
WHERE 
  _PARTITIONDATE = DATE_TRUNC(_PARTITIONDATE, MONTH) -- 月初１日目のみ抽出

応用的な抽出方法

複数項目を一気に判定する

SELECT
  *
FROM
  `dataset.table`
WHERE
  1 IN (flg1, flg2, flg3)  -- flg1, flg2, flg3 のどれかに1が入っているものを抽出する

特定項目のみをSELECT対象外にする方法

SELECT
  * EXCEPT(item_id) -- EXCEPTで指定した項目をSELECT対象外にする
FROM
  dataset.table

項目をたくさん持っているテーブルの中から、特定項目をSELECT対象外にしたい場合などに便利です。

配列を使った処理

min_byとmax_byの再現コード

SELECT
  id,
  -- min_byと同じ結果(id毎、date毎に昇順で並び替えた時に一番最初にくる priceを取得する)
  ARRAY_AGG(price ORDER BY id, date)[OFFSET(0)]　AS　first_date_price, 

  -- max_byと同じ結果(id毎、date毎に降順で並び替えた時の一番最初にくる priceを取得する) 
  ARRAY_AGG(price ORDER BY id, date　 DESC)[OFFSET(0)] AS latest_date_price

FROM
  dataset.table
GROUP BY
  id

PRESTOを触っていた時は、min_byとmax_byが標準的に提供されてましたが、bigqueryでは関数提供はない。と思っていたのですが、bigqueryは項目を配列として処理できる仕組みがあったため、上記のようにすれば再現できるようです。

min_byとmax_by（biguqeryでも実装されました@2023/8月)

-- MIN_BYのコード例
SELECT
  product_id,
  MIN(time) AS min_time,
  MIN_BY(product_name, time) AS product_name_of_min_time
FROM
  dataset.table
GROUP BY
  product_id
；

-- MAX_BYのコード例
SELECT
  product_id,
  MAX(time) AS max_time, 
  MAX_BY(product_name, time) AS product_name_of_max_time
FROM 
  dataset.table
GROUP BY
  product_id
；

集計関数やウィンドウ関数で作成した項目をフィルタで利用する方法

-- product単位で平均価格を算出した上で、平均価格が500以上のもののみ抽出するクエリ例
SELECT
  product,
  AVG(price) AS avgPrice
FROM
  dataset.table
GROUP BY
  1
QUALIFY
  avgPrice >= 500
;

-- userId単位でcreatedAtの連番を付与して、2番目の登録情報を取得するクエリ例
SELECT
  userId,
  createdAt,
  ROW_NUMBER() OVER (PARTITION BY userId ORDER BY createdAt) AS rownum
FROM
  dataset.table
QUALIFY
  rownum = 2
;