<jupyter_start><jupyter_text>More SELECT 文 🍳このノートブックでは SELECT 文におけるサブクエリ・JOIN・UNIONの使い方を確認します。  1. 初期設定Jupyter Notebook を再起動した場合などはここから実行してください。<jupyter_code>! pip install ipython-sql pymysql
%load_ext sql<jupyter_output><empty_output><jupyter_text>2. 接続確認<jupyter_code>%%sql mysql+pymysql://hello:world@10.0.1.100/employees
select 'hello' as world<jupyter_output>1 rows affected.<jupyter_text>3. サブクエリサブクエリはクエリ中に記述された SELECT 文のことです。サブクエリを利用することで集合的な操作を簡単に記述することができ、集計処理を行う分析クエリや分散処理基盤を使った大規模データの整形を行う際に重宝します。<jupyter_code>%%sql
select
    *
from
    (select
        emp_no,
        first_name
    from
        employees
    ) as t
limit 3<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
3 rows affected.<jupyter_text>FROM 句以外の部分でもサブクエリを利用することができます。以下は `salaries` テーブルから給料が平均を下回っているレコードを10件抽出するクエリです。<jupyter_code>%%sql
select
    *
from
    salaries
where
    salary < (select avg(salary) from salaries)
limit 10<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
10 rows affected.<jupyter_text>4. EXISTS 句WHERE 句の検索条件に `EXISTS` 句を渡すとサブクエリの結果が存在するかどうかでレコードを絞り込むことができます。また、あるレコードに対応するデータをサブクエリ内で検索するケースのように「サブクエリの外側」にあるデータにアクセスするようなサブクエリを「相関サブクエリ」と呼びます。以下は EXISTS 句を利用して `title` が `Technique Leader` であるような社員を検索するクエリです。<jupyter_code>%%sql
select
    *
from
    employees as e
where
    exists
    (select
         emp_no
    from
         titles as t
    where
         e.emp_no = t.emp_no -- 相関サブクエリ
         and title = 'Technique Leader'
    )
limit 5<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
5 rows affected.<jupyter_text>5. IN 句とサブクエリIN 句を使うことで複数条件からいずれかにマッチするレコードを検索することができます。<jupyter_code>%%sql
select
    *
from
    employees
where
    emp_no in (10021, 10022, 10023)<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
3 rows affected.<jupyter_text>IN 句もサブクエリと組み合わせることができ、先ほど EXISTS 句を使って書いた検索クエリを以下のようにして IN 句で書き換えることが可能です。<jupyter_code>%%sql
select
    *
from
    employees
where
    emp_no in
    (select
        emp_no
    from
        titles
    where
        title = 'Technique Leader'
    )
limit 5<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
5 rows affected.<jupyter_text>6. 内部結合`JOIN` を利用することで SELECT 文を実行するときに複数のテーブルまたはサブクエリを結合した結果を取得することができます。結合形式には内部結合（`INNER JOIN`）と外部結合（`OUTER JOIN`）とクロス結合（`CROSS JOIN`）の3種類があり、それぞれ役割が異なります（外部結合については LEFT か RIGHT かという違いがあります）。INNER JOIN と OUTER JOIN の構文については以下のようになっており複数のテーブルを JOIN することもできます。MySQL で `JOIN` とだけ記述した場合は `INNER JOIN` がデフォルトの動作になっています。```sqlSELECT    FROM    JOIN    ON    JOIN    ON    ````INNER JOIN` は ID などが共通のレコードを結合させることができます。以下は `employees` テーブルと `salaries` テーブルについて `emp_no` が一致するレコードを結合します。<jupyter_code>%%sql
select
    first_name,
    salary
from
    employees as l
    
inner join
    salaries as r
on l.emp_no = r.emp_no
-- ここは using (emp_no) とも書ける

where
    to_date = '9999-01-01' -- salaries テーブルには過去の給与情報も入っているのでこの条件で最新の給与を絞り込む

limit 5<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
5 rows affected.<jupyter_text>7. 外部結合`INNER JOIN` だと片方のテーブルに含まれないレコードが消失してしまいます。`OUTER JOIN` を利用することでマッチするデータが存在しない場合は `NULL` 扱いにして結合を行うことができます。`LEFT OUTER JOIN` と `RIGHT OUTER JOIN` の2種類があり、どちらのテーブルをベースに外部結合するかで使い分けます。<jupyter_code>%%sql
create temporary table tmp.some (
    id int,
    name text
)
;

create temporary table tmp.another (
    id int,
    attribute text
)
;

insert into tmp.some values (1, "alice"), (2, "bob"), (3, "carlie");
insert into tmp.another values (1, "hello"), (3, "world");<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
0 rows affected.
0 rows affected.
3 rows affected.
2 rows affected.<jupyter_text>`tmp.some` テーブルには `id` と `name` が紐づいたレコードが入っています。<jupyter_code>%%sql
select * from tmp.some;<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
3 rows affected.<jupyter_text>`tmp.another` テーブルには `id` と `attribute` が紐づいたレコードが入っています。<jupyter_code>%%sql
select * from tmp.another;<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
2 rows affected.<jupyter_text>`tmp.another` には `id=2` のレコードが存在しないので、`id` で `INNER JOIN` すると `id=2` のレコードは結合されません。<jupyter_code>%%sql
select
    *
from
    tmp.some
join
    tmp.another
using (id)<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
2 rows affected.<jupyter_text>`LEFT OUTER JOIN` の場合は `tmp.some` に入っているレコードは全て残り、`tmp.another` に対応するレコードがない場合は `NULL` であるものとして結合を行います。<jupyter_code>%%sql
select
    *
from
    tmp.some
left outer join
    tmp.another
using (id)<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
3 rows affected.<jupyter_text>`RIGHT OUTER JOIN` の場合については `tmp.another` のレコードがベースになるので `id=2` のレコードは結合されません。<jupyter_code>%%sql
select
    *
from
    tmp.some
right outer join
    tmp.another
using (id)<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
2 rows affected.<jupyter_text>8. 交差結合`CROSS JOIN` は二つのテーブルについて各レコードの組み合わせを列挙できる結合方法です。```sqlSELECT    FROM    CROSS JOIN    ```CROSS JOIN には ON などの条件指定は付きません。以下は tmp.some テーブルと tmp.another テーブルについて name と attribute の組み合わせを列挙するクエリです。<jupyter_code>%%sql
select
    name,
    attribute
from
    tmp.some
cross join
    tmp.another<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
6 rows affected.<jupyter_text>これだけだと何が嬉しいのか分かりにくいですが、日付に抜けがあるようなログデータに対して穴埋めを行なったり、ネストした構造に対応しているクエリエンジン（Presto や BigQuery など）では配列データをレコードに変換するときなどによく使います。ここでは MySQL 5.7 からサポートされた `JSON` 型を使って `CROSS JOIN` で JSON の配列をレコードに変換してみましょう。まずは JSON 型のカラムを持つテーブルを作ってみます。<jupyter_code>%%sql
-- drop table tmp.hello_json;
create temporary table tmp.hello_json (
    data json
)
;<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
0 rows affected.<jupyter_text>続けて `data` カラムに JSON の文字列を格納します。<jupyter_code>%%sql
insert into tmp.hello_json values ('{"hello":"world", "list": ["item-1", "item-2", "item-3"]}')<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
1 rows affected.<jupyter_text>JSON のプロパティにアクセスするには `json_extract` 関数を利用します。<jupyter_code>%%sql
select json_extract(data, '$.hello') from tmp.hello_json;<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
1 rows affected.<jupyter_text>MySQL 8.0 で追加された `json_table` 関数を利用すると JSON 文字列をテーブルとして操作することが可能です<jupyter_code>%%sql
select
    *
from
    json_table('{"hello":"world"}', "$" columns (hello text path '$.hello')) as t<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
1 rows affected.<jupyter_text>これを `CROSS JOIN` と組み合わせることで JSON の配列データをレコードに分割することができます。<jupyter_code>%%sql
select
    item
from
    tmp.hello_json
cross join
    -- data は tmp.hello_json のカラム
    json_table(data, '$.list[*]' columns (item varchar(20) path '$')) as t<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
3 rows affected.<jupyter_text>CROSS JOIN はテーブルをカンマ区切りにすることでも表現できます<jupyter_code>%%sql
select
    item
from
    tmp.hello_json,
    json_table(data, '$.list[*]' columns (item varchar(20) path '$')) as t<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
3 rows affected.<jupyter_text>9. UNION`UNION` 句を使うとクエリの結果を縦方向に結合することができます。<jupyter_code>%%sql
select
    1 as id,
    'hello' as name

union
select
    2 as id,
    'world' as name<jupyter_output>* mysql+pymysql://hello:***@10.0.1.100/employees
2 rows affected.<jupyter_text>🌱 練習問題  1/2`dept_emp` テーブルと `salaries` テーブルの情報を元に、所属する社員の平均給与（salary）が最も高い部署（department）の部署名（dept_name）を調査してください。`dept_emp` テーブルと `salaries` テーブルはそれぞれ `to_date` カラムが `9999-01-01` であるレコードの情報を対象とするものとします。Hint:- `employees` テーブルと `dept_emp` テーブルを INNER JOIN し社員が所属する部署 `dept_no` を紐付けます- 上記結果と `salaries` テーブルを INNER JOIN し社員の給与 `salary` を紐付けます- 部署ごとに `salary` カラム の平均値を求め、平均給与が最大の `dept_no` を求めます- 上記結果について `departments` テーブルを INNER JOIN し `dept_name` を求めます<jupyter_code>%%sql
-- ここにクエリを入力してください
select 1
# 実行後、"your answer" の右側に表示される入力ボックスに答えを入力し Enter キーを押してください m(_ _)m
import urllib.request

answer = input('your answer (dept_name): ')

url = 'http://10.0.1.100:18080/submit'
data = 'q=q401&a={}'.format(answer.strip()).encode('utf-8')
req = urllib.request.Request(url, data=data, method='POST')
with urllib.request.urlopen(req) as res:
    print(res.read().decode('utf-8'))<jupyter_output><empty_output><jupyter_text>🌱 練習問題  2/2先ほど記述したクエリと `titles` テーブルと `salaries` テーブルの情報を元に、所属する社員の平均給与が最大である部署の中で `title` が `Staff` の社員のうち給与 `salary` が最も高い社員の `first_name` を調べてください。ただし `titles` テーブルと `salaries` テーブルは `to_date` カラムが `9999-01-01` であるレコードの情報を対象とするものとします。<jupyter_code>%%sql
-- ここにクエリを入力してください
select 1
# 実行後、"your answer" の右側に表示される入力ボックスに答えを入力し Enter キーを押してください m(_ _)m
import urllib.request

answer = input('your answer (first_name): ')

url = 'http://10.0.1.100:18080/submit'
data = 'q=q402&a={}'.format(answer.strip()).encode('utf-8')
req = urllib.request.Request(url, data=data, method='POST')
with urllib.request.urlopen(req) as res:
    print(res.read().decode('utf-8'))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13065610859728508, 'fraction_numerical': 0.032918552036199096, 'mean_word_length': 5.578125, 'pattern_counts': {'":': 3, '<': 77, '<?xml version=': 0, '>': 76, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 23, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '24', 'dir': 'jupyter-structured-clean-dedup', 'id': '200472', 'license': 'Unlicense', 'n_tokens_mistral': 5075, 'n_tokens_neox': 4688, 'n_words': 654, 'path': 'notebooks/40_MORE_SELECT.ipynb', 'repo_name': 'mixi-inc/2019ProperTrainingDataBase', 'size': '8840'}