6章 JOINとUNION

2020.04.18 03:58

■■■ JOIN句によるテーブル結合 ■■■

■内部結合■

・テーブル1：item_purchase_log

　　---------|-------------|-----------|-------|-----|

　　コーヒー |beverage | 2019-01-29|UID0001| 280|

　　緑茶 |food | 2019-01-29|UID0001| 280|

　　緑茶 |food | 2019-01-30|UID0004| 200|

　　コーヒー |beverage | 2019-01-30|UID0004| 750|

　　砂糖 |food | 2019-01-30|UID0011| 200|

　　コーヒー |beverage | 2019-01-30|UID0011| 750|

　　メイプルシロップ |food | 2019-02-01|UID0011| 450|

　　メイプルシロップ |food | 2019-02-02|UID0011| 450|

　　ティーカップ |zakka | 2019-02-03|UID0002| 980|

　　砂糖 |food | 2019-02-04|UID0006| 280|

・テーブル2：mst_user_place

　　user_id|pref_name|city_name|

　　-------|---------|---------|

　　UID0001|東京都 |中央区 |

　　UID0002|東京都 |世田谷区 |

　　UID0003|福岡県 |福岡市 |

　　UID0004|東京都 |渋谷区 |

　　UID0005|神奈川県 |横浜市 |

　　UID0006|愛知県 |名古屋市 |

　　UID0007|東京都 |北区 |

　　UID0008|福岡県 |北九州市 |

　　UID0009|東京都 |渋谷区 |

　　UID0010|愛知県 |名古屋市 |

　　UID0011|神奈川県 |川崎市 |

・テーブル１とテーブル２の内部結合SQL

　　SELECT

　　　　mup.user_id,

　　　　mup.pref_name,

　　　　mup.city_name,

　　　　ip1.item_name,

　　　　ip1.item_category,

　　　　ip1.action_date,

　　　　ip1.price

　　FROM

　　　　mst_user_place AS mup

　　　　INNER JOIN

　　　　　　item_purchase_log AS ip1

　　　　ON mup.user_id = ip1.user_id ;

・結果

　　-------|---------|---------|---------|-------------|-----------|-----|

　　UID0001|東京都 |中央区 |緑茶 |food | 2019-01-29| 280|

　　UID0001|東京都 |中央区 |コーヒー |beverage | 2019-01-29| 280|

　　UID0002|東京都 |世田谷区 |ティーカップ |zakka | 2019-02-03| 980|

　　UID0004|東京都 |渋谷区 |コーヒー |beverage | 2019-01-30| 750|

　　UID0004|東京都 |渋谷区 |緑茶 |food | 2019-01-30| 200|

　　UID0006|愛知県 |名古屋市 |砂糖 |food | 2019-02-04| 280|

　　UID0011|神奈川県 |川崎市 |メイプルシロップ |food | 2019-02-02| 450|

　　UID0011|神奈川県 |川崎市 |メイプルシロップ |food | 2019-02-01| 450|

　　UID0011|神奈川県 |川崎市 |コーヒー |beverage | 2019-01-30| 750|

　　UID0011|神奈川県 |川崎市 |砂糖 |food | 2019-01-30| 200|

■LEFT JOINによる外部結合■

例）LEFT JOINで売れていない商品を見つける(priceがNULLの商品が売れていない商品)

・mst_item_category テーブル

　　item_id|item_name|item_category|

　　-------|---------|-------------|

　　IID0001|コーヒー |beverage |

　　IID0002|緑茶 |food |

　　IID0003|メイプルシロップ |food |

　　IID0004|ティーカップ |zakka |

　　IID0005|砂糖 |food |

　　IID0006|ティースプーン |zakka |

　　IID0007|ハチミツ |food |

　　IID0008|紅茶 |beverage |

・SQL

　　SELECT

　　　　mic.item_id,

　　　　mic.item_name,

　　　　mic.item_category,

　　　　ip1.price

　　FROM

　　　　mst_item_category AS mic

　　　　LEFT OUTER JOIN

　　　　　　item_purchase_log AS ip1

　　　　ON mic.item_name = ip1.item_name

　　WHERE

　　　　ip1.price IS NULL;

・結果

　　item_id|item_name|item_category|price|

　　-------|---------|-------------|-----|

　　IID0006|ティースプーン |zakka | 　|

　　IID0007|ハチミツ |food | 　|

　　IID0008|紅茶 |beverage | 　|

■FULL JOINによる完全外部結合■

※MySQLではFULL JOINが使えないため、UNIONで対応する

・mst_stockテーブル

　　item_name|item_category|stock|

　　---------|-------------|-----|

　　コーヒー |beverage | 80|

　　緑茶 |food | 49|

　　メイプルシロップ |food | 5|

　　ティーカップ |zakka | 5|

　　砂糖 |food | 100|

　　ティースプーン |zakka | |

・SQL

　　SELECT

　　FROM

　　　　mst_item_category AS mic

　　　　LEFT JOIN

　　　　　　mst_stock AS ms

　　　　ON mic.item_name = ms.item_name

　　UNION

　　SELECT

　　FROM

　　　　mst_item_category AS mic

　　　　RIGHT JOIN

　　　　　　mst_stock AS ms

　　　　ON mic.item_name = ms.item_name;

・結果

　　-------|---------|-------------|---------|-------------|-----|

　　IID0002|緑茶 |food |緑茶 |food | 49|

　　IID0005|砂糖 |food |砂糖 |food | 100|

　　IID0008|紅茶 |beverage | | | |

■■■特殊な結合■■■

■同じテーブル同士を結合する自己結合■

・item_purchase_logテーブル

　　---------|-------------|-----------|-------|-----|

　　コーヒー |beverage | 2019-01-29|UID0001| 280|

　　緑茶 |food | 2019-01-29|UID0001| 280|

　　緑茶 |food | 2019-01-30|UID0004| 200|

　　コーヒー |beverage | 2019-01-30|UID0004| 750|

　　砂糖 |food | 2019-01-30|UID0011| 200|

　　コーヒー |beverage | 2019-01-30|UID0011| 750|

　　メイプルシロップ |food | 2019-02-01|UID0011| 450|

　　メイプルシロップ |food | 2019-02-02|UID0011| 450|

　　ティーカップ |zakka | 2019-02-03|UID0002| 980|

　　砂糖 |food | 2019-02-04|UID0006| 280|

・同じ価格で、商品名が違う行を探すSQL

　　SELECT

　　　　ipl1.item_name,

　　　　ipl1.price

　　FROM

　　　　item_purchase_log AS ipl1

　　　　INNER JOIN

　　　　　　item_purchase_log AS ipl2

　　　　ON ipl1.price = ipl2.price AND ipl1.item_name <> ipl2.item_name

　　ORDER BY

　　　　ipl1.price ;

・結果

　　item_name|price|

　　---------|-----|

　　砂糖 | 200|

　　緑茶 | 200|

　　砂糖 | 280|

　　緑茶 | 280|

　　砂糖 | 280|

　　コーヒー | 280|

　　緑茶 | 280|

　　コーヒー | 280|

■テーブルのすべての行の組み合わせを得るクロス結合■

ーCROSS JOIN句の利用

例）以下の２つのテーブルをクロス結合で総当りの組み合わせを得る

・mst_sample_aテーブル

　　item_name|

　　---------|

　　アップル |

　　パイナップル |

・mst_sample_bテーブル

　　item_name|

　　---------|

　　アップル |

　　ペン |

　　サングラス |

・SQL

　　SELECT

　　FROM

　　　　mst_sample_a

　　　　CROSS JOIN

　　　　mst_sample_b ;

・結果

　　item_name|item_name|

　　---------|---------|

　　パイナップル |アップル |

　　アップル |アップル |

　　パイナップル |ペン |

　　アップル |ペン |

　　パイナップル |サングラス |

　　アップル |サングラス |

■CROSS JOINで合計のカラムをつける■

例）集計結果をカラムとして追加する

　ースカラサブクエリを用いてクロス集計すると、そのスカラ値が全行に表示されるカラムとして追加される。

・SQL

　　SELECT

　　　　item_name,

　　　　item_category,

　　　　price,

　　　　sq.*

　　FROM

　　　　item_purchase_log

　　　　CROSS JOIN

　　　　　　(

　　　　　　　　SELECT

　　　　　　　　　　SUM(price) AS goukei

　　　　　　　　FROM

　　　　　　　　　　item_purchase_log

　　　　　　) AS sq;

・結果

　　item_name|item_category|price|goukei|

　　---------|-------------|-----|------|

　　コーヒー |beverage | 280| 4620|

　　緑茶 |food | 280| 4620|

　　緑茶 |food | 200| 4620|

　　コーヒー |beverage | 750| 4620|

　　砂糖 |food | 200| 4620|

　　コーヒー |beverage | 750| 4620|

　　メイプルシロップ |food | 450| 4620|

　　ティーカップ |zakka | 980| 4620|

　　砂糖 |food | 280| 4620|

■■■UNIONで複数のテーブルを扱う■■■

■UNIONでテーブルを縦につなげる■

※似通った構造を持つテーブルを一つにまとめたい時はUNION ALLを使って結合する。UNION ALLを使う時は、SELECT結果のカラム名、カラム数、型が全て同じでなければならない。片方のテーブルにしか存在しないカラムがある場合は、SELECTで選択しないようにするか、デフォルト値を設定する。

例）２つのテーブルを縦につなげる

・mst_user_jpnテーブル

　　user_id|user_name|email |

　　-------|---------|-------------------|

　　UID0001|yamauchi |yamauchi@sample.com|

　　UID0002|kimura |kimura@sample.com |

・mst_user_usテーブル

　　user_id|user_name|sns_id |

　　-------|---------|---------|

　　UID0001|smith |@jsith105|

　　UID0002|jobs |@ss5jj |

・SQL

　　SELECT

　　　　'JPN' AS エリア,　　# 'エリア'カラムの作成

　　　　user_id,

　　　　user_name,

　　　　email

　　FROM

　　　　mst_user_jpn

　　UNION ALL

　　SELECT

　　　　'USA' AS エリア,　　# 'エリア'カラムの作成

　　　　user_id,

　　　　user_name,

　　　　NULL AS email　　# 'email'カラムを作成して、カラムを合わせる

　　FROM

　　　　mst_user_us;

・結果

　　エリア|user_id|user_name|email |

　　---|-------|---------|-------------------|

　　JPN|UID0001|yamauchi |yamauchi@sample.com|

　　JPN|UID0002|kimura |kimura@sample.com |

　　USA|UID0001|smith | |

　　USA|UID0002|jobs | |

■UNIONで合計の行を付ける■

例）各グループの集計値だけでなく、全グループの合計値も集計

・item_purchase_logテーブル

　　---------|-------------|-----------|-------|-----|

　　コーヒー |beverage | 2019-01-29|UID0001| 280|

　　緑茶 |food | 2019-01-29|UID0001| 280|

　　緑茶 |food | 2019-01-30|UID0004| 200|

　　コーヒー |beverage | 2019-01-30|UID0004| 750|

　　砂糖 |food | 2019-01-30|UID0011| 200|

　　コーヒー |beverage | 2019-01-30|UID0011| 750|

　　メイプルシロップ |food | 2019-02-01|UID0011| 450|

　　メイプルシロップ |food | 2019-02-02|UID0011| 450|

　　ティーカップ |zakka | 2019-02-03|UID0002| 980|

　　砂糖 |food | 2019-02-04|UID0006| 280|

・価格毎に商品数を集計し、最後の行に合計数を追加するSQL

　　SELECT

　　　　price,

　　　　COUNT(item_name) AS item_name_count

　　FROM

　　　　item_purchase_log

　　GROUP BY

　　　　price

　　UNION ALL

　　SELECT

　　　　SUM(price) AS price,

　　　　COUNT(item_name) AS item_name_count

　　FROM

　　　　item_purchase_log;

・結果

　　price|item_name_count|

　　-----|---------------|

　　280| 3|

　　200| 2|

　　750| 2|

　　450| 2|

　　980| 1|

　　4620| 10|

■UNIONで擬似テーブルを作成■

ーテーブルの作成権限が無い時に擬似テーブルで対応。

　１行ずつSELECT句でデータを作り、それらをUNION ALLで一つにまとめる。

例）性別を表す疑似テーブルの作成

・SQL

　　WITH mock_sex AS(

　　　　SELECT

　　　　　　0 AS sex,

　　　　　　'不明' AS sex_name

　　　　UNION ALL

　　　　SELECT

　　　　　　1 AS sex,

　　　　　　'男性' AS sex_name

　　　　UNION ALL

　　　　SELECT

　　　　　　2 AS sex,

　　　　　　'女性' AS sex_name

　　)

　　SELECT

　　FROM

　　　　mock_sex;

・結果

　　sex|sex_name|

　　---|--------|

　　0|不明 |

　　1|男性 |

　　2|女性 |

機械学習Tips保管庫

データ解析、機械学習のための学習内容の保管庫。復習用。

0コメント

1000 / 1000