BigQueryで節約して書く-SELECT句内のCASE式-

別にBigQueryに限った話ではないけれど、特にBigQueryはQuery課金なので
なるべく走査するデータ量を減らしてクエリを作りたい。

ということで、普段工夫していることをちょくちょく書いていこうと思う。
まずはSELECT句内でCASE式をつかった工夫について。
これってどの程度当たり前のことなんだろうか。

SELECT句でCASE式をつかう

同じテーブルを対象としていて、同じキーをもとに集計処理をしようとしているけど
それぞれ集計したいものが違うので2つのクエリを作って、それぞれで確認している場合に使う。

たとえば、以下の2つのクエリ。

それぞれの正規表現に一致するカテゴリごとに集計を行うクエリどす。

ページカテゴリ1について集計

#standardSQL

SELECT
    REGEXP_EXTRACT(table1.screen_name, r"^\/Category1\/［(.*)］\/.*$") AS category1_item
  , COUNT(table1.screen_name) AS item_number2
FROM
    (
        SELECT
            h.appInfo.screenName AS screen_name
          , visitId AS visitId
        FROM
            `project.dataset.ga_sessions_2017*` AS m
          , UNNEST(hits) AS h
        WHERE
            _TABLE_SUFFIX BETWEEN "0611" AND "0611"
            AND h.appInfo.screenName LIKE "/Category1/%"
        GROUP BY
            screen_name, visitId
    ) AS table1
GROUP BY
    category1_item

ページカテゴリ2について集計

#standardSQL

SELECT
    REGEXP_EXTRACT(table1.screen_name, r"^\/Category2\/［(.*)］\/.*$") AS category2_item
  , COUNT(table1.screen_name) AS item_number2
FROM
    (
        SELECT
            h.appInfo.screenName AS screen_name
          , visitId AS visitId
        FROM
            `project.dataset.ga_sessions_2017*` AS m
          , UNNEST(hits) AS h
        WHERE
            _TABLE_SUFFIX BETWEEN "0611" AND "0611"
            AND h.appInfo.screenName LIKE "/Category2/%"
        GROUP BY
            screen_name, visitId
    ) AS table1
GROUP BY
    category2_item

やっていることは結局同じで、(.*)の部分を取り出して、そいつでグループ化してカウントしようとしている。
見ているテーブルは同じで、｢そいつ｣を抽出する方法が違うだけだ。
この場合上記の2つのクエリを発行するよりも、以下の様にSELECT句内でCASE式を使って集計してやるほうが
走査が1回で済むので安上がりになる。

CASE式を使ってまとめたもの

#standardSQL

SELECT
  CASE
      WHEN table1.screen_name LIKE "/Category1/%" THEN REGEXP_EXTRACT(table1.screen_name, r"^\/Category1\/［(.*)］\/.*$")
      ELSE REGEXP_EXTRACT(table1.screen_name, r"^\/Category2\/［(.*)］\/.*$")
  END AS category_item
  , SUM(
      CASE
        WHEN table1.screen_name LIKE "/Category1/%" THEN 1
        ELSE 0
      END
    ) AS item_number1
  , SUM(
      CASE
        WHEN table1.screen_name LIKE "/Category2/%" THEN 1
        ELSE 0
      END
    ) AS item_number2
FROM
    (
        SELECT
            h.appInfo.screenName AS screen_name
          , visitId AS visitId
        FROM
            `project.dataset.ga_sessions_2017*` AS m
          , UNNEST(hits) AS h
        WHERE
            _TABLE_SUFFIX BETWEEN "0611" AND "0611"
            AND (
                h.appInfo.screenName LIKE "/Category1/%"
                OR h.appInfo.screenName LIKE "/Category2/%"
            )
        GROUP BY
            screen_name, visitId
    ) AS table1
GROUP BY
    category_item
;

特にBigQueryの場合、columnベースでhit単位で見始めると一気にコストが高くなるので上記のような方法が
かなりコストダウンに繋がることが多い。

業務でつかったことのあるクエリをマスキングしてるだけなので、例がわかりづらい気がする。。