Open3

達人に学ぶSQL徹底指南書

hamaguchihamaguchi

https://github.com/akmhmgc/tatsujin_sql
1,2,7は終わったので3からメモ

3 自己結合

結合だと紐づくレコードを取ってくるだけの使い方しかしたことなかったが、
値段が同じ商品を値段をキーとして取る、といった使い方ができて便利

Data

name price
りんご 50
みかん 100
みかん 100
みかん 100
バナナ 80 
select distinct p1.name, p1.price
  from Products p1
  inner join Products p2
  on p1.price = p2.price
  where p1.name != p2.name
hamaguchihamaguchi

4 3論理値とNULL

NULLは値ではなく、「値を決めることができない」という意味
値ではないので比較した時にTRUE/FALSEとならない

極値関数を使用する場合、集合が空の場合NULLを返すので注意

  • Class_A
name age city
ブラウン 22 東京
ボギー 21 千葉
ラリー 19 埼玉
  • Class_B
name age city
山田 東京
斎藤 22 東京
田尻 23 東京 
select *
  from `Class_A`
  where age < (
    select
    min(age) from `Class_B`
    where city = "群馬"
  )

result

name age city

NULLを他の値に変換するCOALESCE

SQL

select *
  from `Class_B` LIMIT 0,100

Data

name age city
山田 東京
斎藤 22 東京
田尻 23 東京

SQL

select name, COALESCE(age, 100)
  from `Class_B`
  order by age desc LIMIT 0,100

Data

name COALESCE(age, 100)
田尻 23
斎藤 22
山田 100
hamaguchihamaguchi

5 EXISTS句の使い方

同値変換

全てのxがPを満たす = Pを満たさないxが存在しない

後者はEXISTS句で表現することができる

例）

  • 全ての教科の点数が50点以上 = 50点以上の教科が存在しない
student_id subject score
100 国語 80
100 理科 80
100 算数 100
200 国語 95
200 算数 80
300 国語 90
300 社会 55
300 算数 40
400 算数 80

すべての教科の点数が50点以上の生徒 = 50点未満の教科が存在しない

select DISTINCT t1.student_id
  from `TestScores` t1
  WHERE not EXISTS (
    select *
      from `TestScores` t2
      where t1.student_id = t2.student_id
      and t2.score < 50
  ) LIMIT 0,100

result

student_id
100
200
400

算数であれば80点以上、国語であれば50点以上を満たす生徒
where句の中でcase文を使って絞り込むのがテクい

慣れないので多分次の章で忘れる

SELECT student_id, subject
  FROM TestScores TS1
 WHERE subject IN ('算数', '国語')
   AND NOT EXISTS
        (SELECT *
           FROM TestScores TS2
          WHERE TS2.student_id = TS1.student_id
            -- 算数であれば点数が80点未満・国語であれば点数が50点未満
            AND 1 = CASE WHEN subject = '算数' AND score < 80 THEN 1
                         WHEN subject = '国語' AND score < 50 THEN 1
                         ELSE 0 END) LIMIT 0,100
student_id subject
100 国語
100 算数
200 国語
200 算数
400 算数

Data

project_id step_nbr status
AA100 0 完了
AA100 1 待機
AA100 2 待機
B200 0 待機
B200 1 待機
CS300 0 完了
CS300 1 完了
CS300 2 待機
CS300 3 待機
DY400 0 完了
DY400 1 完了
DY400 2 完了

1番の工程まで完了しているproject_idの取得

集合指向的な回答

select
  project_id
  from `Projects`
  group by project_id
  having count(*) = sum(
    case
      when step_nbr <= 1 and status = "完了" then 1
      when step_nbr > 1 and status = "待機" then 1
      else 0
    end
  ) LIMIT 0,100
project_id
CS300

EXISTSを使用した回答

1番の工程まで完了 and 2番以降の工程が待機中
= 1番の工程まででは待機中・2番以降の工程であれば完了
のものが存在しない

で同値変換できる

select
  P1.project_id, P1.step_nbr, P1.status
  from `Projects` P1
  where not EXISTS (
    select *
      from `Projects` P2
      where P1.project_id = P2.project_id
      and 1 = 
        case 
          when step_nbr <= 1 and status = "待機" then 1
          when step_nbr >= 2 and status = "完了" then 1
          else 0
        end
  ) LIMIT 0,100

result

project_id step_nbr status
CS300 0 完了
CS300 1 完了
CS300 2 待機
CS300 3 待機

havingではなくexistsで書くメリットは

  • パフォーマンス
    • インデックスを利用する
    • exisitsはtrueが返ったらそれ以降スキャンしない
  • 残る情報が多い

素数を求める

seq
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 
select N1.seq
  from `Nums` N1
  where not EXISTS (
    SELECT *
      from `Nums` N2
      where N2.seq > 1 and N2.seq < N1.seq
      and N1.seq % N2.seq = 0
  ) LIMIT 0,100
seq
1
2
3
5
7
11
13
17
19