どうも、hosigakiです！
皆さんは、目の前のデータを簡単にテーブル化して、SQLで操作したい！と思ったことはありませんか？
即席テーブルを作成する方法としては、以下のように「UNION ALL」を使う方法が取り挙げられることが多いと思います。
SELECT
101 AS user_id,
"女性" AS gender,
30 AS age
UNION ALL
SELECT
102,
"男性",
25
UNION ALL
...ただこの書き方の場合、テーブル化したいデータが多ければ多いほど、「SELECT」「UNION ALL」を都度書く必要があり、記述量が肥大化してしまうという欠点があります。
データが多ければ多いほど、 UNION ALL もたくさん書かなあかんて、ちょっと面倒やな～。UNION ALLを使わず、もうちょいスマートに即席テーブルが作れんやろか...
そこで今回、UNION ALLを使わずに即席テーブルを作成する方法を考えてみました！
本記事で書き方をご紹介しますので、どうか最後までお付き合いください( ^^) _旦~~
即席テーブルを作成するためのクエリ
今回は、以下のテーブルを作ってみます。
| user_id | gender | age |
|---|---|---|
| 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
tFROM句内の解説
今回は、以下三つのテーブルを内部結合しています。
・user_idと連番の2カラムで構成されたテーブル
・genderと連番の2カラムで構成されたテーブル
・ageと連番の2カラムで構成されたテーブル
FROM
UNNEST([101, 102, 103]) AS col1 WITH OFFSET AS offset1上記のクエリは、1つ目のテーブルの定義部分でして、ここでは以下の順で処理が行われています。
- 配列 [101, 102, 103] を縦並びに展開しつつ、カラム名を「col1」とする
- 1のカラムの隣に、0から始まる連番を作成し、カラム名を「offset1」とする
- 以下のテーブルができあがる
| col1 | offset1 |
|---|---|
| 101 | 0 |
| 102 | 1 |
| 103 | 2 |
WITH OFFSET句について
WITH OFFSET句とは、配列をUNNESTで展開する際のオプションの一つです。
配列内の値それぞれに順序を割り振り、連番カラムを作成することができます。
連番カラムのカラム名は、デフォルトで「offset」となっていますが、WITH OFFSET句の後ろに「AS 〇〇」と記載することで、好きなカラム名を割り当てることも可能です。
同様に、二つ目と三つ目のテーブルも、以下のように定義されています。
UNNEST(["女性", "男性", "女性"]) AS col2 WITH OFFSET AS offset2| col2 | offset2 |
|---|---|
| 女性 | 0 |
| 男性 | 1 |
| 女性 | 2 |
UNNEST([30, 25, 27]) AS col3 WITH OFFSET AS offset3| col3 | offset3 |
|---|---|
| 30 | 0 |
| 25 | 1 |
| 27 | 2 |
あとは簡単で、以下の条件で内部結合するだけです！
- 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| col1 | offset1 | col2 | offset2 | col3 | offset3 |
|---|---|---|---|---|---|
| 101 | 0 | 女性 | 0 | 30 | 0 |
| 102 | 1 | 男性 | 1 | 25 | 1 |
| 103 | 2 | 女性 | 2 | 27 | 2 |
ここまでが、FROM句内の処理の解説でした！
SELECT句内の解説
WITH
t AS (
SELECT
col1 AS user_id,
col2 AS gender,
col3 AS age
FROM
...最後に上記のSELECT句内で、必要なカラムのみに絞りつつ、好きなカラム名を命名すれば、即席テーブルの完成です！
| user_id | gender | age |
|---|---|---|
| 101 | 女性 | 30 |
| 102 | 男性 | 25 |
| 103 | 女性 | 27 |
補足として、例えば一つ目のテーブル定義部分で、「102」を「NULL」と書くと、その部分だけ空で出力されます。
FROM
UNNEST([101, NULL, 103]) AS col1 WITH OFFSET AS offset1| user_id | gender | age |
|---|---|---|
| 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を用いない即席テーブルの作成方法を紹介しました！
ぜひ実務でご活用いただけると幸いです！
最後まで読んでいただき、ありがとうございました！
また次の記事でお会いしましょう(^^)/
