LoginSignup
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ken-diver

達人に学ぶSQL徹底指南書をまとめてみた

Posted at

NULLに関して

はじめに

  • 3値理論
    • SQLでは値として「true」「false」「unknown」がある
    • プログラミング言語などは通常2値理論「true」「false」である
  • NULLは値ではない

    • 「unknown」は値、「null」は値ではない

      unknown =unknown →true
NULL = NULL →unknown

    • NULLの判断は「= null」ではなく「IS NULL」を使用している

    • SQLではNULLは値ではなく、述語もうまく動かない

    • 無理にNULLを使用すると「unknown」になる

    • 「unknown」が論理演算に紛れ込むと直感とは反する動きをする

      --以下の式は全部unknown に評価される。
1 = NULL
2 > NULL
3 < NULL
4 <> NULL
NULL = NULL

    • 段階的なステップに分けてSQLを追うことが有効

真理表

3値論理の真理表（NOT）

x NOT x
t f
u u
f t

3値論理の真理表（AND）

AND t u f
t t u f
u u u f
f f f f

3値論理の真理表（OR）

OR t u f
t t t t
u t u u
f t u f
  • ANDの場合： false ＞ unknown ＞ true
  • ORの場合： true ＞ unknown ＞ false

※強い方が弱い方を飲み込む

実践編

非中立が成立しない

  • ジョンは20歳か、20歳ではないか、どちらかである

    • 現実世界では「true」になる
    • SQLの世界では「unknown」になる
    -- ジョンの年齢がnullの場合
-- 1
select *
from students
where age = null
or age <> null

    -- 2
select *
from students
where unknown
or unknown

    -- 2
select *
from students
where unknown
    • 結果はunknownになってしまう
    -- 正解
select *
from students
where age = 20
and age <> 20
or age IS NULL

CASE式とNULL

  • NULLを値として扱ってはいけない

    case col_1 
	when 1 then 〇
	when null then ×
end
    • 結果はunknownになる。
    • when null は col_1 = null と同じなので
    -- 正解
case
	when col_1 = 1 then 〇
	when col_1 is null then ×
end

NOT IN と NOT EXISTは同義ではない

  • NOT IN

    select *
from class
where age NOT IN (23, 21, NULL)

    select *
from class
where NOT ((age = 23) OR (age = 21) OR (age = NULL))

    select *
from class
where (age <> 23) AND (age <> 21) AND unknown

    select *
from class
where unknown
    • なにもとれない

  • NOT EXISTS

    select *
from class_A
where NOT EXISTS ( select * from class_B where A.age = B.age and B.city = '東京')

    select *
from class_A
where NOT EXISTS ( select * from class_B where A.age = null and B.city = '東京')

    select *
from class_A
where NOT EXISTS ( select * from class_B where unknown and B.city = '東京')

    select *
from class_A
where NOT EXISTS ( select * from class_B where unknown)

    select *
from class_A
where true
    • 値が取れる

限定述語

  • ALL

    select *
from class
where age < ALL (23, 21, NULL)

    select *
from class
where (age < 23) AND (age < 21) AND unknown

    select *
from class
where unknown

  • MIN

    select *
from class_A
where age < ( select MIN(age) from class_B where B.city = '東京')
    • これならOK
    • ALLの場合：彼は東京の誰よりも若い
    • MINの場合：彼は東京在住の最も若い生徒よりも若い

集約関数（COUNT()以外）

-- 集約関数内にnullがまぎれるとunknownになってしまう
select *
from class_A
where age < ( select AVG(age) from class_B where B.city = '東京')

HAVING句に関して

連番IDに歯抜けがあるかを確認

select '歯抜けあり' as gap
from ~
HAVING COUNT(*) <> MAX(seq)
  • HAVING句は単独で使用できる
  • テーブル全体を一つの集合体とみなし、その集合自身の性質を調べる

平均の出し方

  • AVGでほんとにいいの？？（外れ値に影響を受ける）

  • 最頻値を求める（集団の中で最も数が多い）

-- ALL述語の利用
select income, count(*) 
from A
group by income
HAVING COUNT(*) >= ALL (select COUNT(*) from A group by income)

-- 極地関数の利用
select income, count(*) 
from A
group by income
HAVING COUNT(*) >= select MAX(cnt) 
															from (select COUNT(*) as cnt from A group by income) TMP

  • メジアンを求める（中央値）

    SELECT AVG(DISTINCT income)
FROM (SELECT T1.income
				FROM Graduates T1, Graduates T2
				GROUP BY T1.income
				--S1の条件
				HAVING SUM(CASE WHEN T2.income >= T1.income THEN 1
				ELSE 0 END) >= COUNT(*) / 2
				--S2の条件
				AND SUM(CASE WHEN T2.income <= T1.income THEN 1
				ELSE 0 END) >= COUNT(*) / 2
) TMP;

    NULLを含まない集合を探す

    • COUNT(*)

      • NULLを数える（全行を数える）

    • COUNT(列名)

      • NULLを数えない

    • 提出日にNULLを含まない学部を選択する

      select 学部
from A
group by 学部
HAVING COUNT(*) = COUNT(提出日) 

      -- caseでも書ける（sum case）
select 学部
from A
group by 学部
HAVING COUNT(*) = SUM ( CASE WHEN 学部 IS NOT NULL THEN 1
															ELSE 0 END);

    • COUNT()まとめ

      -- colの値が一意である
count (distinct col) = count(col)

-- colにNULLが存在しない
count(*) = count(col)

-- colは歯抜けのない連番(開始値は1)
count(*) = max(col)

-- colは歯抜けのない連番(開始値は任意の整数)
count(*) = max(col) - min(col) + 1

-- colが1つだけの値を持つか、またはNULLである
min(col) = max(col)

-- すべてのcol_xの符号が同じである
min(col) * max(col) > 0

-- 最大値の符号が正で最小値の符号が負
min(col) * max(col) < 0

-- colは少なくとも1つのゼロを含む
min(abs(col)) = 0

-- colの最大値と最小値が指定した定数から同じ幅の距離がある
min(col - 定数) = - max(col - 定数)

      処理速度

      -- SELECT文の実行順序
-- この順序でSQLを書く

FROM句
↓
JOIN句
↓
WHERE句
↓
GROUP BY句
↓
HAVING句
↓
SELECT句
↓
ORDER BY句
↓
LIMIT句

      INとEXISTSの違い

      • EXISTSの方が早い
        • 結合キーにインデックスが利用できる
        • 一行でも合致する行があれば、検索を打ち切る

      ソートを回避する

      • ソートが発生するもの

        • GROUP BY
        • ORDER BY
        • 集約関数（SUM, COUNT, AVG, MAX, MIN）
          • 引数の列にインデックスが存在する場合は、実表への検索を対処できる（count(*)は実テーブル。count(id)はインデックス）
        • DISTINCT
          • ２つのテーブルからの重複を検知する場合は、EXISTSを使用しよう
        • 集合演算子（UNION, INTERSECT, EXCEPT）
          • 上記は重複排除の為、ソートを行う
          • 重複して良い場合は、UNION ALLを使用しよう（ソートが発生しない）
        • OLAP関数（RANK, ROW_NUMBER等）

        WHERE句でかけるものはHAVING句で書かない

        • GROUP BYはソートを行う
        • HAVINGは集約後の条件なので、インデックスが聞かない場合が多い

        GROUP BYやORDER BYでインデックスを使う

        • インデックスのソートは高速になる
        • ユニーク・インデックスの場合はソート自体もスキップできるかも。

      インデックスの基本

      インデックスを利用する時は条件式の左辺は裸

      WHERE column * 1.1 > 10

      ではなく

      WHERE column > 10 / 1.1

      にする

      IS NULLをつかった場合はインデックスが無効

      インデックスにはNULLは存在しないため(posgreはきく)

      非定型をつかっているとインデックスが無効

      <>とか!=とかはインデックスが無効

      ORをつかっているとインデックスが無効

      後方一致、または中間一致のLIKEを用いているとインデックスが無効

      前方一致のみ有効です。

      ビューで集約関数や集合演算が入っていると速度低下の原因になりがち

      集約関数、AVG,SUM,COUNT,MAX,MIN

      集合演算子、UNION,INTERSECT,EXCEPT

1
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?