概要

任意のスキーマのテーブルに対してクエリを試したいがわざわざ新しいテーブルを作りたくないときや一連のクエリの中で何らかのマスタテーブルを作りたい時は仮想テーブルが便利です。

日付や連番のマスタテーブルのようなカラムが1つしかない仮想テーブルの作り方は調べると出てきますが、複数カラムの仮想テーブルの作り方の情報が見当たらなかったのでまとめておきます。

やり方

行数が少ないとき

行数が少ない時は愚直に SELECT文を UNION ALL を使って繋げるやり方が簡単です。

WITH users AS(
  SELECT 1 as id, '佐々木' as name, 'sasaki@example.com' as email
  UNION ALL
  SELECT 2 as id, '田中' as name, 'tanaka@example.com' as email
)

SELECT
  *
FROM
  users

行数が多いとき

行数が多い時に UNION ALL を使うとクエリが縦に長くなって可読性が下がりますし、各カラムにデータを入力するのも面倒です（短形選択などを使って効率的に作成はできるかもしれませんが）。

各カラムのデータの入った配列を展開してレコードを作成し、 WITH OFFSET の連番を使ってそれらを結合します。

このようにすればSELECT文はカラムの数の分しか増えず、レコードのデータを変数に格納できるのでデータの見やすさや追加・修正のしやすさも向上します。

DECLARE ids ARRAY<INT64> DEFAULT [1,2];
DECLARE names ARRAY<STRING> DEFAULT ['佐々木', '田中'];
DECLARE emails ARRAY<STRING> DEFAULT ['sasaki@example.com', 'tanaka@example.com'];

WITH users AS(
  SELECT 
    id, name, email
  FROM 
    UNNEST(names) as name 
    WITH OFFSET as offset
  JOIN (
    SELECT 
      id, offset
    FROM 
      UNNEST(ids) as id
      WITH OFFSET as offset
  ) as table2 ON offset = table2.offset
  JOIN (
    SELECT 
      email, offset
    FROM 
      UNNEST(emails) as email
      WITH OFFSET as offset
  ) as table3 ON offset = table3.offset
)


SELECT
  *
FROM
  users

※ 上記のように id カラムがシンプルな連番で良いのなら offset + 1 でも良いですね

参考:
配列内の要素をテーブル内の行に変換する