ORマッパーを使い始める前に理解したいSQL基礎

# 1. 概要

今までSQLを生で書いてきて(ストアド、テーブル値関数)、ある程度知見が溜まっている
ポートフォリオでサーバーサイドに`Rails`を使用する時、ORマッパーとして`Active Record`を使用する
`Active Record`のクエリインターフェースを使用する際、SQLの知識がすごく生きている事を実感した
それと同時に、SQLServerやMySQLなどのDBMS間で共通の物がないか調べていた
標準SQL(SQL2016)の存在を知ったので、標準SQLベースでSQLの知識を簡単にまとめることにした

### 参考資料：[標準SQL徹底入門](http://www.sql-post.biz/2019/05/02/標準sql/)

# 2. 標準SQLとは

SQLの標準規格は、ANSI（米国規格協会）やISO（国際標準化機構）といった標準化団体により、数年に一度改訂される
各RDBはこれに準拠するように努めるが、RDB独自の拡張機能(SQLServerのT-SQLやPostgreSQLのPL/pgSQL)もあるため、すべてが標準規格に沿っているわけではない
だがSQLの標準規格を理解しておくことは他のRDBを使用するうえでも共通理解が深まるので学ぶ価値はある
ORMを使用する際などは(恐らく)RDB間の差異をなくすために標準SQLの範囲での操作のみ行えるため、先に標準SQLを理解することは有用な事と考える

# 3. データベースの用途による言語の呼称

一応こういう種類で分けられているという知識だけ
`Truncate`とかがなぜDDLなのかはわからない(DMLでいいと思うが)

## 3.1. DML(Data Manipulation Language)

データ操作言語という
データベースに記録されたデータの参照や操作を行うための言語

- SELECT(項目を選択して検索)
- INSERT(データの挿入)
- UPDATE(データの更新)
- DELETE(データの削除)

## 3.2. DDL(Data Definition Language)

データ定義言語という
データを格納するための構造を定義するための言語
テーブル定義に何らかの影響を及ぼす操作ができる言語という認識(Truncateは違うが)

- CREATE(テーブル作成)
- DROP(テーブル削除)
- ALTER(テーブル定義変更)
- TRUNCATE(テーブル内データ全削除)

## 3.3. DCL(Data Control Language)

データ制御言語という
データベースのアクセス制御、状態管理を行うための言語

- GRANT(権限の付与)
- REVOKE(権限の剥奪)
- COMMIT(トランザクションの確定)
- ROLLBACK(トランザクションの破棄)

# 4. 命名ルール

RDB全てでこのルールはわけではなく、使用してはいけないものも使用自体はできるものもあるが互換性がなくなることに留意すること

## 4.1. 使用できる文字、使用してはいけない文字

#### 4.1.1. データベースやテーブル、カラムに使用できる文字は以下

- 半角アルファベット
- 半角数字
- アンダーバー(_)

#### 4.1.2. 使用してはいけない文字

- $,#,?,-のような記号

#### 4.1.3. その他ルール

- 名前の最初には必ずアルファベットを使用する

×：2020_salary
〇：salary_2020

# 5. 何はともあれDML

実際にシステム開発でよく使用する操作がDMLだと思う
DCLは最初期の論理設計時や都度の回収でちょいちょい触るくらいで、日々一番意識するのはやはりDMLだと思う

## 5.1. FROM

操作を行うテーブルを指定する
複数テーブルを「,」つなぎで指定する記法も存在するが、互換性がないので使用しない
通常は`FROM`にテーブル結合や`UNION`などで結果の統合などを行ってデータを取得する

```sql:sample.sql
select *
from
  user
```

## 5.2. SELECT

取得するデータを選択する
リソースの項目すべてを選択する場合は「*」とする
指定する場合は項目を「,」つなぎで指定する
カラムには`AS`を使用して別名をつけることもできる
カラムには`''`を使用して定数を指定することもできる

```sql:sample.sql
select
  id
, name
, address as jusho
, '2020' as nendo
from
  user
```

## 5.3. WHERE

リソースに条件を指定する
種々演算子を使用して条件を指定することで取得するデータを制限することができる

### 5.3.1. 比較演算子

左辺と右辺を比較して評価する

|演算子  |用途  |
|---|---|
|=  |左辺と右辺が等しい|
|>  |左辺が右辺より大きい|
|>=  |左辺が右辺以上|
|<  |左辺が右辺より小さい|
|<=  |左辺が右辺未満|
|<>  |左辺と右辺が異なる|
|BETWEEN A AND B  |A以上B以下|

```sql:sample.sql
select
  id
, name
, age
from
  user
where
  age = 20
```

```sql:sample2.sql
select
  id
, name
, age
from
  user
where
  age between 20 and 60
```

### 5.3.2. 論理演算子

論理演算子を使用して論理式を組み立てる

|演算子  |用途  |
|---|---|
|AND  |前後の条件が両方が真|
|OR  |前後の条件の片方が真|
|NOT  |式の結果を反転|

```sql:sample.sql
select
  id
, name
, age
, weight
from
  user
where
  age >= 20
  and weight <= 70
```

### 5.3.3. マッチング

マッチングさせたい条件を指定する

|演算子  |用途  |
|---|---|
|IN  |INの中の項目に一致する|
|LIKE  |LIKEの中のパターンに一致する|
|EXISTS   |EXISTS以下の条件が存在するか   |

```sql:sample.sql
select
  id
, name
, age
from
  user
where
  age in(20,30,40,50,60) --age%10=0でも可能だが敢えてこうしている
```

```sql:sample.sql(めちゃくちゃ無駄な処理なのは承知の上です)
select
  id
, name
, age
from
  user
  left join user_favorite
    on user_id = user.id
where
  exists(
    select *
    from
      user_favorite
    where
      favorite_fruits = 'apple'
      and user_id = user.id
  )
```
### 5.3.x その他

サブクエリ等を組み合わせて複雑な条件を指定することもできる
ここでは割愛

### 5.4. UPDATE

データを更新するために使用する
`where`を指定しないと全データをUPDATEするので使用には十分注意する

```sql:sample.sql
update user
  set weight = 100
  where name = 'samplename'
```

## 5.5. INSERT

対象のテーブルにデータを挿入する

### 5.5.1. テーブル定義の順番で挿入する

```sql:sample.sql()
insert into user
  values(
    100		--id
  , samplename2	--name
  , 29		--age
  )
```

### 5.5.2. 抽出したデータを指定のテーブルに挿入する

```sql:sample.sql
insert into adults_user(id,name,age)
  select
    id
  , name
  , age
  from
    user
  where
    age >= 20
```

## 5.6. DELETE

対象のデータを削除する
これも`where`を指定しないとテーブルデータを全削除するので注意すること

```sql:sample.sql
delete from user
  where
    name = 'sampleuser'
```

# 6. DMLの評価順序

DMLの評価順序を意識していないと、データ量が多くなるにつれて重いクエリになってしまう
インデックス等をうまく活用すると線形より緩やかに計算量は増えないが、やはり評価順序を意識した処理をかけるようになる必要がある

## 6.1. 評価順序

1. FROM(JOIN句も含まれる)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

具体的なTipsは別途になるが、上から順にデータを絞るようにすることで、扱うデータ量が少なくなる
また、`SELECT`でカラムに別名をつける際や`GROUP BY`でグループ化したものが`WHERE`で参照できない問題はたいていこの評価順序によるものが大きい
評価順序が後ろにある処理で行っていることは前の処理で参照することはできないことを理解する必要がある

例)`select`の別名は`where`で参照できない

```sql:sample.sql
select
  id
, name as namae
from
  user
where
  namae = 'user'
```

※SQLのスペースや書き方については諸説あるのでここでは触れないことにする(明確なルールが存在しないから言及するのが怖いw)

## 閑話休題【クエリオプティマイザについて】

標準SQLとは大きく話がそれるが、それぞれのDBMSにはクエリオプティマイザという実行計画の中でどれが最適化を選択する機構が存在する
実行計画とは、ユーザーが発行した問い合わせに対してDBMSが内部で処理方法を決定する計画書のことである
これは実行計画を見て、クエリオプティマイザが最適な方法を判断して決定する
クエリオプティマイザには、**ルールベースオプティマイザ**と**コストベースオプティマイザ**がある
ルールベースでは最初から組み込まれた判断基準の下実行計画を決定していた
コストベースでは、統計情報からコストという基準で見積もった中で最もコストの低い実行計画を決定する
現在では柔軟性の高いコストベースがオプティマイザとして多く用いられている
`SQLServer`の`T-SQL`や`MySQL`では、`EXPLAIN`の後にクエリを書くことで、対象のクエリの実行計画情報を確認することができる

# 7. 集約関数他

集計用の関数のこと
リソースをもとに合計値や平均値、行数を取得したいときに使用する
また、集約関数はリソース全体に対して行う他グルーピングを施して詳細な集計を行いたいときにも対応している

## 7.1. 集約関数の種類

|関数  |用途  |
|---|---|
|COUNT  |指定項目のレコード数を求める|
|SUM  |指定項目の合計を求める|
|AVG  |指定項目の平均を求める|
|MAX  |指定項目の最大を求める|
|MIN  |指定項目の最小を求める|

## 7.2. GROUP BY

対象のリソースに対して、まとめたい項目を指定することでリソースを項目単位にまとめることができる
それ以降は、まとまった単位に対して集約関数等を使用することで単位ごとの合計や平均値を出すこともできる

```sql:sample.sql
select
  sex
, count(*)
from
  user
group by sex
```

### 7.2.1. 注意事項

- GROUP BYに指定していない項目をSELECT句に指定はできない

`GROUP BY`で指定した項目でグルーピングすると、それ以外の項目は(正規化されていれば)一意に求まる値ではない
たとえそうであったとしてもDBは判断できないので、グルーピングしている項目以外をSELECT句で指定するとエラーになる

```sql:sample.sql
select
  id	--idはグループの中で一意に定まる値でないためエラーになる
, sex
from
  user
group by sex
```

### 7.3. HAVING

グルーピングした単位に対して条件を指定するときに使用する
SQLの評価順序的にWHEREにグループ化した内容で条件を指定はできない
上記のケースについてはHAVINGで指定することができる

# 8. トランザクション

ユーザーからの問い合わせ(特にDML)の一連の処理、ひとまとまりの単位のこと
少し複雑なシステムになれば、ある問い合わせの中で、複数のテーブルにまたがる更新や削除を行うことがある
これの途中で処理が失敗したとき、リレーションのあるテーブル同士で整合性を保てなくなる
整合性を保つために、トランザクション単位で処理を定義し

- 処理が成功したら一括で`COMMIT`
- 処理が失敗したらすべてを巻き戻すために`ROLLBACK`

とすることで、テーブル間の整合性を保つ仕組み
トランザクションの定義自体は標準で決まっているものではなく、DBMSの中でそれぞれあるのでそちらを参照すること

## 8.1. トランザクションが始まるタイミング

標準SQLではトランザクションを始めるタイミングについては明記していないので
各DBMSによって差異がある
この中でも二つのパターンが存在し

1. 1つのクエリが1つのまとまり(＝トランザクション)としてとらえて自動で設定
2. ユーザー定義でトランザクションを指定

1の場合は自動でトランザクションが設定されることになる
その中でも複数のクエリをひとまとまりにしたい場合はDBMSに従ったトランザクションの宣言を行うこと

2の場合はユーザーがトランザクションを定義しない限りトランザクションが終わらないことに注意する

# 9. 分析関数(Window関数)

標準SQLとして規定されているかどうかは定かではないが、各DBMSで使用できる(SQLServer,MySQL,PostgreSQLで観測)ため解説

## 9.1. Windowとは

Window関数ではテーブルを指定の区間ごとに集計できる
その区間の単位を`Window`という

## 9.2. GROUP BYとの違い

`GROUP BY`では指定の項目でグループピングされ、SELECT句以降でもグルーピングした結果しか返せない
`Window`関数ではグルーピングすることなくカラムに対して使用するので、それぞれの行を返すことができる

## 9.3. 構文

`partition by`でWindowの単位を指定する
`order by`でWindow内で並び替える項目を指定する

- <関数>(項目名)over(partition by <項目名> | order by <項目名>)

## 9.4. Window関数の種類

下記に加えて**集約関数も使用することができる**
代表的なものだけピックアップ

|関数  |用途  |
|---|---|
|row_number  |連番(ソート順で付番し、重複は考えない)|
|rank  |連番(ソート順で付番し、重複後は番号を飛ばして付番)|
|dense_rank  |連番(ソート順で付番し、重複後は番号を飛ばさず付番)|
|ntile  |連番(指定の値で折り返して連番を付番)|
|lag  |ソートした時の前の行の値|
|lead  |ソートした時の後の行の値|

参考：[Window関数(PostgreSQL)](https://lets.postgresql.jp/documents/technical/window_functions/1#:~:text=Window%E9%96%A2%E6%95%B0%E3%81%AF%20OLAP%20%E3%81%AA%E3%81%A9%E3%81%A7%E4%BD%BF%E3%82%8F%E3%82%8C%E3%82%8B%E8%A4%87%E9%9B%91%E3%81%AA%E9%9B%86%E8%A8%88%E3%82%AF%E3%82%A8%E3%83%AA%E3%82%92%E5%8A%B9%E7%8E%87%E3%82%88%E3%81%8F%E5%87%A6%E7%90%86%E3%81%99%E3%82%8B%E3%81%9F%E3%82%81%E3%81%AE%E6%A7%8B%E6%96%87%E3%81%A7%E3%81%99%E3%80%82%20Window%E9%96%A2%E6%95%B0%E3%81%AF,PostgreSQL%208.4%20%E3%81%A7%E5%88%9D%E3%82%81%E3%81%A6%E5%B0%8E%E5%85%A5%E3%81%95%E3%82%8C%E3%80%81%209.0%20%E3%81%A7%E3%81%95%E3%82%89%E3%81%AB%E6%8B%A1%E5%BC%B5%E3%81%95%E3%82%8C%E3%81%BE%E3%81%97%E3%81%9F%E3%80%82)

## 9.5. ケーススタディ

ここからは具体的なケーススタディということで、当方が`SQLServer`を使用したストアドなどをよく実装するためそれで例規する
また、一部標準SQLとは異なる機能が出てくる(T-SQLのover句を使用した分析関数の利用など)のでそこは各自で調べて欲しい

### 仕様

ある元テーブルのデータを
・UserID毎に
・BaseYMD昇順で
・連番を付番して表示

実際に`row_number`等を利用する際は、リソースにそのまま連番を付番するなどはあまりなく、下記実装のように
`partition by`などでWindowの中でグルーピングし
`order by`などでグループの中でソートして連番を振るのが定石と思われる

### 元テーブル(sample_tbl)

| UserID |  BaseYMD | etc... |
|:-:|:-:|:-:|
|  1 | 20190401 | ...  |
|  1 | 20200101 | ...  |
|  1 | 20200601 | ...  |
|  １ | 20210101 | ...  |
|  1 | 20210101 | ...  |
|  2 | 20200101 | ...  |
|  2 | 20201001 | ...  |
|  3 | 20200101 | ...  |

### 実装

```sql
SELECT
  UserID
, row_number()over(partition by UserID order by BaseYMD) RK
, BaseYMD
FROM
  sample_tbl
```

### 結果

| UserID | RK  | BaseYMD |
|:-:|:-:|:-:|
|1|	1|	20190401|
|1|	2|	20200101|
|1|	3|	20200601|
|1|	4|	20210101|
|1|	5|	20210101|
|2|	1|	20200101|
|2|	2|	20201001|
|3|	1|	20200101|


# 10. まとめ

割とよくまとまったのではないかと思う
ORMを使用するのなら生のSQLは書くことはほとんどないと思うが、標準SQLに対する理解を深めることでORM製のメソッドの動きも理解しやすいと感じる