中野智文のブログ

データ・マエショリストのメモ

SQLクエリのみでCREATE TABLEを使わず一時的な小さなテーブルを作る

背景

SQLクエリのみでCREATE TABLEを使わず一時的な小さなテーブルを作りたい時がある。

例えば、{'ios': 1, 'android': 2, 'windows': 3} みたいな小さなテーブルが一時的に欲しいことはある。 それがないために、IF(os_id=1, 'ios', IF(os_id=2, 'android', IF(os_id=3, 'windows', NULL))) AS os_name みたいな謎のクエリを書いた上で WHERE os_name is not NULL のような条件式があったり、難しい。

もし小さなテーブルがあればJOINするだけなのにと思う。ある条件にて複数行にすることは ARRAY と UNNEST を使ってできなくはないが、より可読性が落ちる。(JOINの方が可読性が高いと思っているのは私だけかもしれないが)

UNION ALL でひたすら結合する方法

例:

SELECT
  1 AS os_id, 'ios' AS os_name
UNION ALL SELECT
  2 AS os_id, 'android' AS os_name
UNION ALL SELECT
  3 AS os_id, 'windows' AS os_name

ARRAY を UNNEST する方法

例:

SELECT
  _os.id as os_id,
  _os.name as os_name
FROM (
  SELECT
    [STRUCT(1 AS id, 'ios' AS name),
    STRUCT(2 AS id, 'android' AS name),
    STRUCT(3 AS id, 'windows' AS name)] os)
JOIN
  UNNEST(os) _os

両社とも BigQuery (標準SQL)にて確認。