【SQL】UNION ALLを使わずに、もっとスマートに即席テーブルを作る方法

【SQL】UNION ALLを使わずに、即席テーブルを作る方法

どうも、hosigakiです！

皆さんは、目の前のデータを簡単にテーブル化して、SQLで操作したい！と思ったことはありませんか？

即席テーブルを作成する方法としては、以下のように「UNION ALL」を使う方法が取り挙げられることが多いと思います。

SELECT
  101 AS user_id,
  "女性" AS gender,
  30 AS age
UNION ALL
SELECT
  102,
  "男性",
  25
UNION ALL
...

ただこの書き方の場合、テーブル化したいデータが多ければ多いほど、「SELECT」「UNION ALL」を都度書く必要があり、記述量が肥大化してしまうという欠点があります。

hosigaki

データが多ければ多いほど、 UNION ALL もたくさん書かなあかんて、ちょっと面倒やな～。UNION ALLを使わず、もうちょいスマートに即席テーブルが作れんやろか...

そこで今回、UNION ALLを使わずに即席テーブルを作成する方法を考えてみました！
本記事で書き方をご紹介しますので、どうか最後までお付き合いください( ^^) _旦~~

即席テーブルを作成するためのクエリ

今回は、以下のテーブルを作ってみます。

user_idgenderage
101女性30
102男性25
103女性27
作りたいテーブル

クエリは、以下になります。
ぱっと見複雑そうですが、実は結構簡単なクエリなので、以下でやさしく解説していきます！

WITH
t AS (
SELECT
  col1 AS user_id,
  col2 AS gender,
  col3 AS age
FROM
  UNNEST([101, 102, 103]) AS col1 WITH OFFSET AS offset1
  INNER JOIN UNNEST(["女性", "男性", "女性"]) AS col2 WITH OFFSET AS offset2
    ON offset1 = offset2
  INNER JOIN UNNEST([30, 25, 27]) AS col3 WITH OFFSET AS offset3
    ON offset1 = offset3
)

SELECT
  *
FROM
  t

FROM句内の解説

今回は、以下三つのテーブルを内部結合しています。
・user_idと連番の2カラムで構成されたテーブル
・genderと連番の2カラムで構成されたテーブル
・ageと連番の2カラムで構成されたテーブル

FROM
  UNNEST([101, 102, 103]) AS col1 WITH OFFSET AS offset1

上記のクエリは、1つ目のテーブルの定義部分でして、ここでは以下の順で処理が行われています。

  1. 配列 [101, 102, 103] を縦並びに展開しつつ、カラム名を「col1」とする
  2. 1のカラムの隣に、0から始まる連番を作成し、カラム名を「offset1」とする
  3. 以下のテーブルができあがる
col1offset1
1010
1021
1032
1つ目のテーブル

WITH OFFSET句について

WITH OFFSET句とは、配列をUNNESTで展開する際のオプションの一つです。
配列内の値それぞれに順序を割り振り、連番カラムを作成することができます。

連番カラムのカラム名は、デフォルトで「offset」となっていますが、WITH OFFSET句の後ろに「AS 〇〇」と記載することで、好きなカラム名を割り当てることも可能です。

同様に、二つ目と三つ目のテーブルも、以下のように定義されています。

UNNEST(["女性", "男性", "女性"]) AS col2 WITH OFFSET AS offset2
col2offset2
女性0
男性1
女性2
2つ目のテーブル
UNNEST([30, 25, 27]) AS col3 WITH OFFSET AS offset3
col3offset3
300
251
272
3つ目のテーブル

あとは簡単で、以下の条件で内部結合するだけです！

  • offset1 = offset2 となるように、1つ目のテーブルと2つ目のテーブルを内部結合
  • offset2 = offset3 となるように、上記テーブルと3つ目のテーブルを内部結合
FROM
  UNNEST([101, 102, 103]) AS col1 WITH OFFSET AS offset1
  INNER JOIN UNNEST(["女性", "男性", "女性"]) AS col2 WITH OFFSET AS offset2
    ON offset1 = offset2
  INNER JOIN UNNEST([30, 25, 27]) AS col3 WITH OFFSET AS offset3
    ON offset1 = offset3
col1offset1col2offset2col3offset3
1010女性0300
1021男性1251
1032女性2272
3つのテーブルを内部結合した結果

ここまでが、FROM句内の処理の解説でした！

SELECT句内の解説

WITH
t AS (
SELECT
  col1 AS user_id,
  col2 AS gender,
  col3 AS age
FROM
...

最後に上記のSELECT句内で、必要なカラムのみに絞りつつ、好きなカラム名を命名すれば、即席テーブルの完成です！

user_idgenderage
101女性30
102男性25
103女性27
最終的に完成したテーブル

補足として、例えば一つ目のテーブル定義部分で、「102」を「NULL」と書くと、その部分だけ空で出力されます。

FROM
  UNNEST([101, NULL, 103]) AS col1 WITH OFFSET AS offset1
user_idgenderage
101女性30
男性25
103女性27
一部の値を空にしてみたテーブル

もし空が含まれるテーブルを作成したい場合は、配列内に「NULL」を加えてみましょう！

二つのテーブル作成方法の使い分け

今回作成したテーブルは、以下のようにUNION ALLを使用する方法でも作成可能です。

WITH
t AS (
SELECT
  101 AS user_id,
  "女性" AS gender,
  30 AS age
UNION ALL
SELECT
  102,
  "男性",
  25
UNION ALL
SELECT
  103,
  "女性",
  27
)

SELECT
  *
FROM
  t

ただ前述通り、この書き方は簡単な反面、記述量が肥大化しやすくなってしまいます。
そのため、テーブル化したいデータ量に応じて、以下のように方法を使い分けてみても良いかもしれません！

テーブル化したいデータが少ない場合
→ 「UNION ALL」を用いた書き方

テーブル化したいデータが多い場合
→ 本記事で紹介した書き方

今回は、UNION ALLを用いない即席テーブルの作成方法を紹介しました！
ぜひ実務でご活用いただけると幸いです！

最後まで読んでいただき、ありがとうございました！
また次の記事でお会いしましょう(^^)/

-SQL
-, ,