blog.amedama.jp Open in urlscan Pro
54.199.90.60  Public Scan

Form analysis
1 forms found in the DOM

GET https://blog.amedama.jp/search

<form class="search-form" role="search" action="https://blog.amedama.jp/search" method="get">
  <input type="text" name="q" class="search-module-input" value="" placeholder="記事を検索" required="">
  <input type="submit" value="検索" class="search-module-button">
</form>

Text Content

CUBE SUGAR CONTAINER


技術系のこと書きます。


2017-06-28


SQL: UNION を使ってテーブルを縦に連結する

MariaDB Mac OS X SQL

今回は SQL の UNION を使ってみる。

試した環境は次の通り。

$ sw_vers 
ProductName:    Mac OS X
ProductVersion: 10.12.5
BuildVersion:   16F73
$ mysql --version
mysql  Ver 15.1 Distrib 10.2.6-MariaDB, for osx10.12 (x86_64) using readline 5.1



典型的な使い方

例えばスキーマ設計において、パフォーマンスの向上などを目的として同じカラムを持ったテーブルが複数あったりすることがある。
特定の月のデータを入れるテーブルとか、あるいは毎月新しいテーブルを作っていくとか、そんな感じ。

次のサンプルでは同じカラムを持ったテーブルが日付で分かれている。

> CREATE TABLE purchases_20170627 (
    ->   purchase_id INTEGER,
    ->   user_id VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.04 sec)

> CREATE TABLE purchases_20170628 (
    ->   purchase_id INTEGER,
    ->   user_id VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.02 sec)


上記のテーブルに、それぞれサンプルのレコードを追加しておこう。

> INSERT INTO
    ->   purchases_20170627
    -> VALUES
    ->   (1, 'Alice'),
    ->   (2, 'Bob'),
    ->   (3, 'Carol');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

> INSERT INTO
    ->   purchases_20170628
    -> VALUES
    ->   (4, 'Alice'),
    ->   (5, 'Bob');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0


さて、上記のようなテーブルを一つのクエリで処理したいとする。 そんなときこそ今回扱う UNION の出番といえる。 UNION では複数の SELECT
文を繋げて扱うことができる。 もちろん同じカラムという前提はあるけど。

次のクエリでは先ほどの二つのテーブルの内容をまとめて取得している。

> SELECT
    ->   '20170627' AS date,
    ->   purchase_id,
    ->   user_id
    -> FROM purchases_20170627
    -> UNION ALL
    -> SELECT
    ->   '20170628' AS date,
    ->   purchase_id,
    ->   user_id
    -> FROM purchases_20170628;
+----------+-------------+---------+
| date     | purchase_id | user_id |
+----------+-------------+---------+
| 20170627 |           1 | Alice   |
| 20170627 |           2 | Bob     |
| 20170627 |           3 | Carol   |
| 20170628 |           4 | Alice   |
| 20170628 |           5 | Bob     |
+----------+-------------+---------+
5 rows in set (0.00 sec)


同じカラムを持った内容をどんどん縦に繋げていくイメージ。


擬似的なテーブルを作るのに使う

続いては UNION を使って擬似的なテーブルを作るやり方について。 例えば SELECT 文を UNION で繋いでいけば CREATE TABLE ...
しなくても擬似的なテーブルが用意できる。

次のクエリでは WITH と共に使うことで擬似的に作ったテーブルの内容を表示している。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION ALL
    ->   SELECT 2 AS device_type, 'L2SW' AS device_name
    ->   UNION ALL
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   *
    -> FROM device_types;
+-------------+-------------+
| device_type | device_name |
+-------------+-------------+
|           1 | LB          |
|           2 | L2SW        |
|           3 | L3SW        |
+-------------+-------------+
3 rows in set (0.00 sec)


ちなみに、これまでは UNION ALL を使っていたけど、重複する内容を省きたいときは UNION DISTINC を使う。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION DISTINCT
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION DISTINCT
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   *
    -> FROM device_types;
+-------------+-------------+
| device_type | device_name |
+-------------+-------------+
|           1 | LB          |
|           3 | L3SW        |
+-------------+-------------+
2 rows in set (0.00 sec)


同じ内容を持ったレコードが削除されている。

UNION で作った擬似的なテーブルも CREATE TABLE ... で作ったテーブルと同じように扱うことができる。
例えば、次のように擬似的なテーブルと関連するようなテーブルを作っておく。

> CREATE TABLE devices(
    ->   id INTEGER,
    ->   type INTEGER
    -> );
Query OK, 0 rows affected (0.02 sec)

> INSERT INTO
    ->   devices
    -> VALUES
    ->   (1, 1),
    ->   (2, 1),
    ->   (3, 2),
    ->   (4, 3);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0


そして両者を JOIN してみよう。

> WITH
    -> device_types AS (
    ->   SELECT 1 AS device_type, 'LB' AS device_name
    ->   UNION ALL
    ->   SELECT 2 AS device_type, 'L2SW' AS device_name
    ->   UNION ALL
    ->   SELECT 3 AS device_type, 'L3SW' AS device_name
    -> )
    -> SELECT
    ->   devices.id,
    ->   device_types.device_name AS device_type
    -> FROM devices
    -> JOIN device_types
    -> ON devices.type = device_types.device_type;
+------+-------------+
| id   | device_type |
+------+-------------+
|    1 | LB          |
|    2 | LB          |
|    3 | L2SW        |
|    4 | L3SW        |
+------+-------------+
4 rows in set (0.01 sec)


ちゃんと上手く JOIN できた。

まあ上記くらいの内容なら擬似的なテーブルを作るよりも CASE を使った方が楽ちんかな。

> SELECT
    ->   devices.id,
    ->   CASE
    ->     WHEN devices.type = 1 THEN 'LB'
    ->     WHEN devices.type = 2 THEN 'L2SW'
    ->     WHEN devices.type = 3 THEN 'L3SW'
    ->   END AS device_type
    -> FROM devices;
+------+-------------+
| id   | device_type |
+------+-------------+
|    1 | LB          |
|    2 | LB          |
|    3 | L2SW        |
|    4 | L3SW        |
+------+-------------+
4 rows in set (0.00 sec)



再帰クエリの中で使う

UNION のもう一つの重要な使い方として再帰クエリ (WITH RECURSIVE) の中での用法がある。
これは再帰的にクエリを実行して得られた内容を結合するのに UNION を使うということ。 再帰クエリについては以下に詳しく書いた。

blog.amedama.jp

ちなみに MySQL 5.7 には再帰クエリが実装されていないけど MariaDB 10.2 なら使える。


まとめ

今回は UNION を使う場面について見てみた。



ビッグデータ分析・活用のためのSQLレシピ

 * 作者: 加嵜長門,田宮直人,丸山弘詩
 * 出版社/メーカー: マイナビ出版
 * 発売日: 2017/03/27
 * メディア: 単行本（ソフトカバー）
 * この商品を含むブログを見る







もみじあめ (id:momijiame) 5年前



関連記事
 * 2020-05-08
   MySQL の InnoDB でトランザクション分離レベルの違いを試す
   今回は MySQL の InnoDB を使ってトランザクション分離レベル (…
 * 2017-06-30
   SQL: CASE 句を使って縦持ちのデータを横持ちに変換する
   使った環境は次の通り。 $ sw_vers ProductName: Mac OS X Prod…
 * 2017-06-29
   SQL: COALESCE() 関数で NULL を別の値に置き換える
   RDB のスキーマ設計において、なるべくなら Nullable なカラム…
 * 2017-06-27
   SQL:2003 のウィンドウ関数を MariaDB 10.2 で試す
   今回は SQL:2003 の規格で追加されたウィンドウ関数を使ってみ…
 * 2017-06-18
   SQL: 内部的なコードを人間に分かりやすいラベルに変換して表示する
   RDB のスキーマには、たまに対応表などを参照しながらでないと…


« SQL: NULLIF() 関数を使ってゼロ除算を防ぐ SQL:2003 のウィンドウ関数を MariaDB 10.2… »

プロフィール
読者です 読者をやめる 読者になる 読者になる
603

このブログについて
検索

本を書きました

連絡先

問い合わせフォーム

最新記事
 * Linux の Network Namespace で L2TP (IPsec なし) を試す
 * Linux の Network Namespace で PPPoE を試す
 * Python: scikit-learn の LabelEncoder を説明変数の変換に使うのは誤り
 * strongSwan の IPsec VPN を Network Namespace で試す (Route-based / VTI デバイス)
 * WireGuard の VPN を Linux の Network Namespace で試す

注目記事
 * Python: Streamlit を使って手早く WebUI 付きのプロトタイプを作る
 * stress コマンドを使ってマシンに負荷をかける
 * Docker のホストとコンテナ間でファイルをやり取りする
 * Python: ipywidgets で Jupyter に簡単な UI を作る
 * Python: seaborn を使った可視化を試してみる

スポンサードリンク






引用をストックしました

ストック一覧を見る 閉じる

引用するにはまずログインしてください

ログイン 閉じる

引用をストックできませんでした。再度お試しください

閉じる

限定公開記事のため引用できません。

読者です 読者をやめる 読者になる 読者になる
603