1. 概要
今までSQLを生で書いてきて(ストアド、テーブル値関数)、ある程度知見が溜まっている
ポートフォリオでサーバーサイドにRailsを使用する時、ORマッパーとしてActive Recordを使用する
Active Recordのクエリインターフェースを使用する際、SQLの知識がすごく生きている事を実感した
それと同時に、SQLServerやMySQLなどのDBMS間で共通の物がないか調べていた
標準SQL(SQL2016)の存在を知ったので、標準SQLベースでSQLの知識を簡単にまとめることにした
参考資料：標準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などで結果の統合などを行ってデータを取得する
select *
from
user
5.2. SELECT
取得するデータを選択する
リソースの項目すべてを選択する場合は「*」とする
指定する場合は項目を「,」つなぎで指定する
カラムにはASを使用して別名をつけることもできる
カラムには''を使用して定数を指定することもできる
select
id
, name
, address as jusho
, '2020' as nendo
from
user
5.3. WHERE
リソースに条件を指定する
種々演算子を使用して条件を指定することで取得するデータを制限することができる
5.3.1. 比較演算子
左辺と右辺を比較して評価する
| 演算子 | 用途 |
|---|---|
| = | 左辺と右辺が等しい |
| > | 左辺が右辺より大きい |
| >= | 左辺が右辺以上 |
| < | 左辺が右辺より小さい |
| <= | 左辺が右辺未満 |
| <> | 左辺と右辺が異なる |
| BETWEEN A AND B | A以上B以下 |
select
id
, name
, age
from
user
where
age = 20
select
id
, name
, age
from
user
where
age between 20 and 60
5.3.2. 論理演算子
論理演算子を使用して論理式を組み立てる
| 演算子 | 用途 |
|---|---|
| AND | 前後の条件が両方が真 |
| OR | 前後の条件の片方が真 |
| NOT | 式の結果を反転 |
select
id
, name
, age
, weight
from
user
where
age >= 20
and weight <= 70
5.3.3. マッチング
マッチングさせたい条件を指定する
| 演算子 | 用途 |
|---|---|
| IN | INの中の項目に一致する |
| LIKE | LIKEの中のパターンに一致する |
| EXISTS | EXISTS以下の条件が存在するか |
select
id
, name
, age
from
user
where
age in(20,30,40,50,60) --age%10=0でも可能だが敢えてこうしている
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するので使用には十分注意する
update user
set weight = 100
where name = 'samplename'
5.5. INSERT
対象のテーブルにデータを挿入する
5.5.1. テーブル定義の順番で挿入する
insert into user
values(
100 --id
, samplename2 --name
, 29 --age
)
5.5.2. 抽出したデータを指定のテーブルに挿入する
insert into adults_user(id,name,age)
select
id
, name
, age
from
user
where
age >= 20
5.6. DELETE
対象のデータを削除する
これもwhereを指定しないとテーブルデータを全削除するので注意すること
delete from user
where
name = 'sampleuser'
6. DMLの評価順序
DMLの評価順序を意識していないと、データ量が多くなるにつれて重いクエリになってしまう
インデックス等をうまく活用すると線形より緩やかに計算量は増えないが、やはり評価順序を意識した処理をかけるようになる必要がある
6.1. 評価順序
- FROM(JOIN句も含まれる)
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
具体的なTipsは別途になるが、上から順にデータを絞るようにすることで、扱うデータ量が少なくなる
また、SELECTでカラムに別名をつける際やGROUP BYでグループ化したものがWHEREで参照できない問題はたいていこの評価順序によるものが大きい
評価順序が後ろにある処理で行っていることは前の処理で参照することはできないことを理解する必要がある
例)selectの別名はwhereで参照できない
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
対象のリソースに対して、まとめたい項目を指定することでリソースを項目単位にまとめることができる
それ以降は、まとまった単位に対して集約関数等を使用することで単位ごとの合計や平均値を出すこともできる
select
sex
, count(*)
from
user
group by sex
7.2.1. 注意事項
- GROUP BYに指定していない項目をSELECT句に指定はできない
GROUP BYで指定した項目でグルーピングすると、それ以外の項目は(正規化されていれば)一意に求まる値ではない
たとえそうであったとしてもDBは判断できないので、グルーピングしている項目以外をSELECT句で指定するとエラーになる
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の場合は自動でトランザクションが設定されることになる
その中でも複数のクエリをひとまとまりにしたい場合は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 | ソートした時の後の行の値 |
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 | ... |
実装
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製のメソッドの動きも理解しやすいと感じる