SQL 3週目

8439 ワード
テキストリンク
★ Join
-たくさんの情報を一目で見たいなら!!
👉 [今日の秘訣!]
すべての情報を1つのテーブルに配置できますが、必要でないのは、テーブルが大きくなります.
したがって、データをタイプ別に分離し、接続が必要な場合に接続するために別のテーブルに配置します.
たとえば、ユーザーとcheckinsテーブルに同時に存在するuser idのように.
これらのフィールドは「key」と呼ばれ、2つのテーブルを関連付けるキーです.
-ジョン?
👉 ジョイって何?
これは、2つのテーブルの共通情報(キー値)に基づいてテーブルを接続し、1つのテーブルと見なすことを意味します.
例えば、user idフィールドに基づいて、usersテーブルとordersテーブルを接続して、一目で見ることができると思います!
👉 上記の例のように、2つのテーブルの情報をつなげて一緒に見たい場合がありますよね?
このような状況を回避するために、関連情報がある場合、2つのテーブルのフィールドの名前と情報はuser idと同じです.このフィールドは2つのテーブルを接続する鍵なので「key」と呼ばれます.
  • Joinタイプ:Left Join,Inner Join
    1)Left Join:左を基準に貼り付けるので左join!
    だからね.left joinはどこ→何を貼るか、順番が重要!
    • SELECT * FROM users u
left join point_users p
 on u.user_id = p.user_id
    2)Inner Join:加入すると価値のあるものしか出てこない!交わる.
    SELECT * FROM users u
inner join point_users p
 on u.user_id = p.user_id
    ユーザー・テーブルを
  • [実践]オーダー・テーブル
    • に接続してみます.
    SELECT * FROM orders o 
inner join users u
 on o.user_id = u.user_id
    ユーザー・テーブルを
  • 「実践」checkinsテーブル
    • に接続してみます.
    SELECT * FROM checkins c 
inner join users u 
 on c.user_id = u.user_id
    カリキュラムを
  • [実習]登録edsテーブルに接続してみます
    select * FROM enrolleds e 
inner join courses c 
 on e.course_id = c.course_id
    ★SQLクエリの実行順序
    select * from enrolleds e
inner join courses c
on e.course_id = c.course_id;
    👉 上記のクエリを実行する順序:from→join→select
    1)from registreds:registredsテーブル全体のデータをインポートします.
    2)e.course id=c.course id:coursesを登録されたテーブルに貼り付けます.coursesのテーブルは登録されたテーブルのcourse idと同じです.
    3)select*:貼り付けられたすべてのデータを出力します.
    カリキュラムを
  • checkinsテーブルに接続して統計をエクスポート
    👉 course idカウント
    • SELECT c1.course_id, count(*) from checkins c1 
inner join courses c2
 on c1.course_id = c2.course_id
 GROUP by c1.course_id;
    👉 course idカウントに基づいて、別名をカウントにループ
    SELECT c1.course_id, count(*) as cnt from checkins c1 
inner join courses c2
 on c1.course_id = c2.course_id
 GROUP by c1.course_id;
    👉 '今日の決意情報と科目情報を結びつけて、科目ごとに今日の決意を数えましょう.
    SELECT c1.course_id, c2.title, count(*) as cnt from checkins c1 
inner join courses c2
 on c1.course_id = c2.course_id
 GROUP by c1.course_id;
 
 SELECT c1.course_id, count(*) from checkins c1 
inner join courses c2
 on c1.course_id = c2.course_id
 GROUP by c1.course_id;
    usersテーブルを
  • point usersテーブルに接続し、順番に並べ替えます.
    👉 プレイヤー情報をプレイヤーポイント情報を含むテーブルにリンクし、大量のポイントを得る順にプレイヤーデータを抽出します!
    • SELECT * from point_users pu
inner join users u 
 on pu.user_id = u.user_id
 order by pu.point desc
    👉 プレイヤー情報をプレイヤーポイント情報を含むテーブルにリンクし、大量のポイントを得る順にプレイヤーデータを抽出します!user id、名前、電子メール、pointもあります!
    SELECT pu.user_id, u.name, u.email, pu.point from point_users pu
inner join users u 
 on pu.user_id = u.user_id
 order by pu.point desc
    統計をエクスポートするためにusersテーブルをordersテーブルに接続する
    👉 ユーザー情報を注文情報に接続し、NAVERメールを使用しているユーザーの中で、苗字別に注文回数を統計します!
    SELECT u.name, COUNT(*) as cnt from orders o 
inner join users u 
 on o.user_id = u.user_id 
 WHERE o.email LIKE '%naver.com'
 GROUP by u.name
    ★SQLクエリの実行順序
    select u.name, count(u.name) as count_name from orders o
    inner join users u
    on o.user_id = u.user_id
    where u.email like '%naver.com'
    group by u.name
    👉 上記のクエリを実行する順序:from→join→where→groupby→select
    1)from orders o:ordersテーブル全体のデータをインポートし、oと名前を付けます.
    2)inner join users u on o.user id=u.user id:usersテーブルをordersテーブルに貼り付け、usersテーブルデータはordersテーブルのuser idと同じである.(*usersテーブルで別名uを使用)
    3) where u.email like '%naver.com":usersテーブルemailフィールド値はnaverです.comで終わる値のみ取得します.
    4)groupbyu.name:usersテーブルのname値は同じ値を集約します.
    5)select u.name,count(u.name)as count name:usersテーブルのnameフィールドとnameフィールドに基づいて、集計数をカウントして出力します.
    👉 Joinの実行順がずっとfromと一緒なら便利!
  • [テスト]Join練習1
    👉 異なる支払い方法のユーザーポイントの平均値を理解する
    (どの決済方式が一番真面目なのかな~)
    結合するテーブル:point users、ordersの追加
    秘訣!→round(数字、桁数)を利用して四捨五入しましょうか?
    • SELECT o.payment_method, ROUND(AVG(pu.point),1) from point_users pu 
inner join orders o 
on pu.user_id = o.user_id 
group by o.payment_method
  • [テスト]Join練習2
    👉 姓で数えてお会計してから始まらなかったプレイヤー(どの姓が一番始まらなかったか~)
    追加するテーブル:登録edsにユーザーを追加する
    秘訣!→is登録=0の人数ちょっと!
    秘訣!→orderbyを利用して降順で並べ替えてみると素敵でしょうか?
    • SELECT u.name, COUNT(*) as cnt from enrolleds e 
inner join users u 
on e.user_id = u.user_id 
WHere e.is_registered = 0
group by u.name 
order by COUNT(*) desc
  • [テスト]Join練習3
    👉 科目別に開始していないプレイヤーを数えます
    参加するテーブル:カリキュラムに登録edsを追加
    秘訣!→is登録=0の人数ちょっと!
    • SELECT c.course_id, c.title, COUNT(*) as cnt_notstart from courses c 
inner join enrolleds e 
 on c.course_id = e.course_id 
 WHERE e.is_registered = 0
  group by c.course_id
  • [テスト]Join練習4
    👉 Web開発、応用開発総合クラスの毎週の入居人数を数えましょうか?ちゃんと整理して!
    参加する表:レッスンで、チェックインの貼り付け
    秘訣!→groupby、order by、2つのフィールドをカンマでつなぎます!
    • SELECT c1.title, c2.week, COUNT(*) as cnt FROM courses c1
inner join checkins c2
 on c1.course_id = c2.course_id
 group by c1.title, c2.week
  order by c1.title, c2.week
  • [テスト]Join練習5
    👉 練習4日、8月1日以降に購入したお客様に塗ってみてください!
    追加するテーブル:coursesにcheckinsを貼り付けます!
  • checkinsで、もう一回注文!
    秘訣!→Ordersテーブルにもう一度inner joinを掛けて、どこで自動終了!
    • select c1.title, c2.week, count(*) as cnt from courses c1
inner join checkins c2 on c1.course_id = c2.course_id
inner join orders o on c2.user_id = o.user_id
where o.created_at >= '2020-08-01'
group by c1.title, c2.week
order by c1.title, c2.week
    ★もう一度Left Join
    1.左join usersテーブルと
    select * from users u
left join point_users pu on u.user_id = pu.user_id
    この状態では、これは可能です.
    💡 プレイヤーの中で、ポイントがない人(=つまりスタートしていない人)の統計!
    一緒にisNULL、isnot NULLを勉強します!>NULLは大文字で!
    select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is NULL
group by name
    select name, count(*) from users u
left join point_users pu on u.user_id = pu.user_id
where pu.point_user_id is not NULL
group by name
  • [クイズ]ここでクイズ!-勝手にやってみる
    💡 7月10日~7月19日に登録したお客様のうち、ポイントがあるお客様の数、そして総数、そしてパーセンテージ、見てみたいと思います!
    以下の結果を見たい場合は、どうすればいいですか?
    そうしましょうか.
    ヒント1→カウント数NULL!
    ヒント2→Alias(別名)もしっかり貼ってね!
    ヒント3→比例小数点2位で四捨五入!
    • SELECT COUNT(pu.point_user_id) as pnt_user_cnt,
       COUNT(u.user_id) as tot_user_cnt,
       ROUND(COUNT(pu.point_user_id) / COUNT(u.user_id),2) as ratio
FROM users u
LEFT JOIN point_users pu
ON u.user_id = pu.user_id
WHERE u.created_at BETWEEN '2020-07-10' and '2020-07-20'
    ★合成成果!ユニオンを学ぶ
    -2回選ぶのではなく、一度に集めて見たいならある!そんなときに使います!
    -共通点があります.7月と8月があるので貼りたい時?
    (
	select '7월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at >= '2020-08-01'
	group by c1.title, c2.week
	order by c1.title, c2.week
)
UNION ALL 
(
	select '8월' as month, c1.title, c2.week, count(*) as cnt from courses c1
	inner join checkins c2 on c1.course_id = c2.course_id
	inner join orders o on c2.user_id = o.user_id
	where o.created_at >= '2020-08-01'
	group by c1.title, c2.week
	order by c1.title, c2.week
)
    tabをインデント
    ユニオンALLはOrder byが食べたものではありません:マージの場合にソートしなければなりません.ソートとマージはできません.
    [作業終了]
    登録されたidで授業の完了数(don=1)を数え、完了した授業数の順に並べます.user idも一緒に出力すべきです.
    ヒント!
    -.チェックインするテーブル:registreds、registreds detail
    -.ログインフィールド:IDの登録
    SELECT e.enrolled_id,e.user_id, COUNT(*) as max_count from enrolleds e
inner join enrolleds_detail ed 
 on e.enrolled_id = ed.enrolled_id
 WHERE ed.done = 1
 GROUP by e.enrolled_id
 ORDER by max_count desc
 
 select e.enrolled_id,
e.user_id,
count(*) as cnt
from enrolleds e
inner join enrolleds_detail ed on e.enrolled_id = ed.enrolled_id
where ed.done = 1
group by e.enrolled_id, e.user_id
order by cnt desc
    モーテルによるアポロクライアント認証
    顔認識APIについての個人的まとめ2017年秋版