関係言語の分類
- **[[DML]] (Data Manipulation Language)**: データの参照・更新・挿入・削除 (`SELECT`, `INSERT`, `UPDATE`, `DELETE`)
- **[[DDL]] (Data Definition Language)**: スキーマ・テーブルの定義 (`CREATE`, `ALTER`, `DROP`)
- [[DCL]] (Data Control Language)**: 権限管理 (`GRANT`, `REVOKE`)
- その他: ビュー定義 (View)、整合性・参照整合性制約 (Constraints)、トランザクション制御
SQLは「集合 (Set)」ではなく「バッグ (Bag / Multiset)」に基づく
- 関係代数の理論では重複のない「集合」を基本とするが、実用的なSQLは重複を許容する「バッグ」に基づく（`DISTINCT` を明示しない限り重複行が残る）。
---
## 集約 (Aggregates) とグループ化 (Grouping)
### 集約関数 (Aggregate Functions)
タプルのバッグ（複数行）を入力とし、**単一の集約値**を返す関数。原則として `SELECT` 句の出力リスト（または `HAVING` 句）で使用する。
- `AVG(col)`: 列の平均値を返す
- `MIN(col)`: 最小値を返す
- `MAX(col)`: 最大値を返す
- `SUM(col)`: 合計値を返す
- `COUNT(col)`: 列の値の数（NULLを除外）を返す
- `COUNT(*)` や `COUNT(1)` は NULL を含めた全行数をカウントする。
```sql
SELECT COUNT(login) AS cnt FROM student WHERE login LIKE '%@cs';
SELECT COUNT(*) AS cnt FROM student WHERE login LIKE '%@cs';
```
集約関数と一緒に集約外の列を `SELECT` に含めると、出力結果は未定義（または構文エラー）になる。DBMSによっては `ANY_VALUE(col)` などの関数で代表値を取り出す。
### GROUP BY
タプルを指定したキーごとにサブセットへ分割（射影）し、サブセットごとに集約関数を適用する。
```sql
SELECT AVG(s.gpa), e.cid
FROM enrolled AS e JOIN student AS s ON e.sid = s.sid
GROUP BY e.cid;
```
### GROUPING SETS
単一クエリ内で**複数の異なるグループ化キー**を同時に指定できる機能。従来 `UNION ALL` で複数の `GROUP BY` クエリを結合していた処理を1回で効率的に記述・実行できる。
```sql
SELECT c.name AS c_name, e.grade, COUNT(*) AS num_students
FROM enrolled AS e
JOIN course AS c ON e.cid = c.cid
GROUP BY GROUPING SETS (
(c.name, e.grade), -- コースと成績ごとの集計
(c.name), -- コースごとの小計
() -- 全体の総計
);
```
### HAVING
**集約後の計算結果に基づいてグループをフィルタリング**する句（`GROUP BY` に対する `WHERE` のような役割）。
※ `WHERE` 句は集約前に各行をフィルタリングするため、集約関数の結果を条件に指定することはできない。
```sql
SELECT AVG(s.gpa) AS avg_gpa, e.cid
FROM enrolled AS e JOIN student AS s ON e.sid = s.sid
GROUP BY e.cid
HAVING AVG(s.gpa) > 3.9;
```
---
## 文字列・日時操作 (String & Date/Time)
### 文字列の扱いとDBMS間の差異
- **大文字小文字の区別**: SQL-92標準やPostgreSQL/SQLite/Oracleは区別する (Case Sensitive)。MySQLはデフォルトで区別しない (Case Insensitive)。
- **クォート**: 標準は単一引用符 `'...'` のみ。MySQLやSQLiteでは二重引用符 `"..."` も文字列リテラルとして許容される場合がある。
### パターンマッチング
- `LIKE`:
- `%`: 0文字以上の任意の文字列にマッチ
- `_`: 任意の1文字にマッチ
- `SIMILAR TO`: SQL標準の正規表現マッチング構文（POSIX正規表現をサポートするDBMSも多い）。
```sql
SELECT * FROM enrolled WHERE cid LIKE '15-%';
SELECT * FROM student WHERE login LIKE '%@c_';
SELECT * FROM student WHERE login SIMILAR TO '[\w]{3}@cs';
```
### 文字列関数・連結
- 部分文字列抽出: `SUBSTRING(str, start, len)`
- ケース変換: `UPPER(str)`, `LOWER(str)`
- 文字列連結:
- SQL標準: `||` 演算子 (`LOWER(name) || '@cs'`)
- DBMS依存: `CONCAT(...)` (MySQL等), `+` (SQL Server)
### 日時操作 (Date/Time Operations)
日付・時刻の加減算、抽出（`EXTRACT` / `DATE_PART` 等）が可能。ただし実装構文はDBMSによって大きく異なる。
---
## 出力制御とリダイレクト (Output Control & Redirection)
### ORDER BY / 取得件数の制限
- `ORDER BY <col> [ASC|DESC]`: 結果を指定列でソート。
- 標準的なページネーション構文 (SQL:2008以降):
- `OFFSET <#> ROWS FETCH {FIRST|NEXT} <#> ROWS [WITH TIES]`
- ※ `LIMIT <#>` や `TOP <#>` はDBMS依存の独自拡張構文。
```sql
SELECT sid, name FROM student
WHERE login LIKE '%@cs'
ORDER BY gpa DESC
OFFSET 5 ROWS
FETCH FIRST 5 ROWS WITH TIES; -- 同率タイの行も含める
```
### 出力のリダイレクト (Output Redirection)
クエリ結果をそのまま新しいテーブルに保存する。
```sql
-- INTO 構文
SELECT DISTINCT cid INTO CourseIds FROM enrolled;
-- CREATE TABLE AS SELECT 構文
CREATE TABLE CourseIds AS (SELECT DISTINCT cid FROM enrolled);
```
---
## ネストされたクエリ (Nested Queries / サブクエリ)
クエリ内部で別のクエリを実行して複雑な条件や計算を組み立てる。`SELECT`, `FROM`, `WHERE` 句などほぼあらゆる箇所に配置可能。
### サブクエリ比較演算子
- `ALL`: サブクエリが返す**すべての行**に対して条件が真であること。
- `ANY` (または `SOME`): サブクエリが返す**少なくとも1つの行**に対して条件が真であること。
- `IN`: ANYと同等。
- `EXISTS` / `NOT EXISTS`: サブクエリの結果が**1行以上存在するか（または存在しないか）**を判定。
```sql
-- 受講者が1人もいないコースを検索 (相関サブクエリ + NOT EXISTS)
SELECT * FROM course
WHERE NOT EXISTS (
SELECT * FROM enrolled
WHERE course.cid = enrolled.cid
);
```
---
## LATERAL Joins (ラテラル結合)
`LATERAL` 演算子を使用すると、サブクエリ内で**そのサブクエリより前に記述されたテーブルやサブクエリの列を参照**できる。
「テーブルの各行に対してサブクエリを呼び出す for ループ」のように捉えることができる。
```sql
-- 各コースごとの登録者数と平均GPAを計算
SELECT c.cid, c.name, t1.cnt, t2.avg
FROM course AS c,
LATERAL (
SELECT COUNT(*) AS cnt FROM enrolled
WHERE enrolled.cid = c.cid
) AS t1,
LATERAL (
SELECT AVG(gpa) AS avg FROM student AS s
JOIN enrolled AS e ON s.sid = e.sid
WHERE e.cid = c.cid
) AS t2
ORDER BY t1.cnt ASC;
```
---
## 共通テーブル式 (CTE: Common Table Expressions)
`WITH` 句を用いて、クエリ内で一時的に参照可能な名前付き結果セットを定義する。
- 複雑なネストクエリ、ビュー、明示的な一時テーブル（Temporary Table）の代替として利用可能。
- クエリの構造化と可読性が飛躍的に向上する。
```sql
-- 少なくとも1つのコースを履修している中で、最大の学生IDを持つ学生名を取得
WITH maxCTE (maxId) AS (
SELECT MAX(sid) FROM enrolled
)
SELECT s.name FROM student AS s
JOIN maxCTE ON s.sid = maxCTE.maxId;
```
---
## ウィンドウ関数 (Window Functions)
### 概要
現在の行に関連する一連のタプル集合（ウィンドウ）に対して計算を行うが、**通常の集約関数と異なり行を集約（1行に圧縮）せず、元の行の粒度を保ったまま計算結果を付与**する。
- 順位付け (Ranking)、累計 (Running totals)、移動平均 (Moving averages) などに利用。
### 基本構文
```sql
SELECT FUNC(...) OVER (
[PARTITION BY partition_col]
[ORDER BY sort_col]
)
FROM table_name;
```
- `OVER`: ウィンドウの範囲・分割方法を指定。
- `PARTITION BY`: 集計対象とするグループの境界を指定（省略時は全行が1つのウィンドウ）。
- `ORDER BY`: ウィンドウ内での並び順を指定。
### 代表的な関数
- **集約関数全般**: `AVG()`, `SUM()`, `COUNT()`, `MIN()`, `MAX()`
- **専用ウィンドウ関数**:
- `ROW_NUMBER()`: 現在の行番号（一意の連番）
- `RANK()`: 順位（同値がある場合は同順位となり、次はスキップされる。例: 1, 2, 2, 4）
- `DENSE_RANK()`: 順位（同値があっても次の順位をスキップしない。例: 1, 2, 2, 3）
```sql
-- 各コース内で成績順にランク付けし、コース内で2番目の成績の学生を取得
SELECT * FROM (
SELECT *,
RANK() OVER (PARTITION BY cid ORDER BY grade ASC) AS rank
FROM enrolled
) AS ranking
WHERE ranking.rank = 2;
```
---
## 設計原則 (Key Takeaway)
> **"You should (almost) always strive to compute your answer as a single SQL statement."**
> （答えは（ほぼ）常に**単一のSQLステートメント**で計算するよう努めるべきである）
- アプリケーション側のループ（forループ等）でデータを1行ずつ取得・処理するのではなく、宣言的言語であるSQLに計算を委ねる。
- DBMSのクエリオプティマイザが最適な結合順序やアクセスパス、並列実行を自動選択できるため、大幅な性能向上が得られる。