DML (データ操作言語)

DML (Data Manipulation Language) は、データベースのデータを参照、登録、変更、削除するための言語です。ここでは、データサイエンティストにとって特に重要なデータの取得（`SELECT`）を中心に解説します。

1. データ取得 (SELECT)

データベースからデータを取得するには`SELECT`文を使用します。

基本構文

SELECT 列名1, 列名2, ...
FROM テーブル名
[WHERE 条件]
[GROUP BY 列名]
[HAVING 条件]
[ORDER BY 列名];

詳細説明

指定したテーブルからデータを取得します。表示したい列名を指定するほか、「`*`」（アスタリスク）を指定すると全ての列を取得できます。ただし、アプリケーションに組み込む際は、予期せぬ列の変動を避けるために、列名を明示的に指定することが推奨されます。

`SELECT`句では、列名だけでなく、以下のような式（Expression）も使用できます。

• 集約関数 (`SUM`, `AVG`, `COUNT`など)
• ウィンドウ関数 (`ROW_NUMBER`, `RANK`など)
• 四則演算 (`+`, `-`, `*`, `/`)
• 文字列結合 (`||` や `CONCAT`関数)
• データ型変換 (`CAST`関数)
• 条件分岐 (`CASE`式)

SQLの記述順序と実行順序

SQLは記述する順序が決まっていますが、データベース内部での実行順序は異なります。この違いを理解することは、複雑なクエリを理解する上で非常に重要です。

2. SELECT文の主要な句

(1) WHERE - 行の絞り込み

`WHERE`句では、述語（条件式）を用いて取得するレコードを絞り込みます。

• 比較演算子: `=`, `!=` (`< >`), `>`, `<`, `>=`, `<=`
• 論理演算子: `AND`, `OR`, `NOT`
• 範囲指定: `BETWEEN a AND b` (aとbを含む範囲)
• NULL判定: `IS NULL`, `IS NOT NULL`
• リスト内判定: `IN ('a', 'b', 'c')`
• パターンマッチ: `LIKE 'パターン文字'`, `SIMILAR TO '正規表現'`
• 存在判定: `EXISTS (サブクエリ)`

  ※ `IN`と`EXISTS`は似ていますが、一般的に大きなテーブルに対しては`EXISTS`の方が高速に動作します。

また、`WHERE`句の中のサブクエリ（副問い合わせ）が外側のクエリに依存する「相関サブクエリ」も利用でき、行ごとに複雑な条件判定が可能です。

(2) JOIN - テーブルの結合

複数のテーブルを特定の条件で結合し、一つの結果セットとして取得します。

【INNER JOIN】(内部結合)

両方のテーブルに存在する、結合条件に一致する行のみを返します。最も一般的に使用される結合です。`INNER`は省略可能です。

【LEFT OUTER JOIN】(左外部結合)

左側のテーブルの全ての行と、右側のテーブルで結合条件に一致した行を返します。一致する行が右側にない場合、右側の列は`NULL`になります。`OUTER`は省略可能です。

【RIGHT OUTER JOIN】(右外部結合)

`LEFT JOIN`の逆で、右側のテーブルの全ての行を返します。一致する行が左側にない場合、左側の列は`NULL`になります。`OUTER`は省略可能です。

【FULL OUTER JOIN】(完全外部結合)

左右両方のテーブルの全ての行を返します。片方のテーブルにしか存在しない行は、もう片方のテーブルの列が`NULL`になります。`OUTER`は省略可能です。

【CROSS JOIN】(交差結合)

両方のテーブルの行の全ての組み合わせ（直積）を返します。結合条件は不要です。データ量が爆発的に増える可能性があるため、使用には注意が必要です。

(3) 集合演算子

二つの`SELECT`文の結果を、集合として扱うための演算子です。対象となる`SELECT`文のカラム数とデータ型は一致している必要があります。

• UNION: 両方の結果を結合し、重複行を除外します。重複を含めたい場合は `UNION ALL` を使用します。
• INTERSECT: 両方の結果に共通して存在する行のみを返します。
• EXCEPT: 最初の結果から、二番目の結果に存在する行を取り除いた差分を返します。

(4) GROUP BY / HAVING - グループ化

`GROUP BY`は、指定した列の値が同じ行を一つのグループにまとめます。`COUNT()`や`SUM()`などの集約関数と組み合わせて、グループごとの集計を行う際によく使われます。

`HAVING`は、`GROUP BY`でグループ化された後の結果に対して条件を指定します。`WHERE`がグループ化前の行を対象とするのに対し、`HAVING`はグループ化後の集約結果を対象とします。

SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;

(5) ORDER BY - 並び替え

結果セットを指定した列の値に基づいて並び替えます。`ASC`で昇順（デフォルト）、`DESC`で降順にソートします。

(6) LIMIT / OFFSET - 行数制限とオフセット

`LIMIT`は取得するレコード数を制限します。`OFFSET`は、指定した行数だけスキップしてからレコードの取得を開始します。Webアプリケーションのページネーション機能などで頻繁に利用されます。

3. よく使われる関数

SQLには便利な組み込み関数が多数用意されています。ここでは代表的なものをいくつか紹介します。

集約関数

グループ化された行の集計を行います。

• `COUNT(列名)`: 行数を数える（`COUNT(*)`ですべての行を数える）
• `SUM(列名)`: 数値の合計を計算する
• `AVG(列名)`: 数値の平均値を計算する
• `MAX(列名)`: 最大値を求める
• `MIN(列名)`: 最小値を求める

文字列関数

文字列の操作を行います。

• `LENGTH(文字列)`: 文字列の長さを返す
• `UPPER(文字列)` / `LOWER(文字列)`: 大文字/小文字に変換する
• `SUBSTRING(文字列 FROM 開始位置 FOR 文字数)`: 部分文字列を抽出する
• `REPLACE(文字列, 置換前, 置換後)`: 文字列を置換する
• `CONCAT(文字列1, 文字列2, ...)` または `||`: 文字列を連結する

その他の便利な関数

• `CAST(値 AS データ型)`: 値のデータ型を変換する
• `COALESCE(値1, 値2, ...)`: 引数の中で最初に見つかった`NULL`でない値を返す
• `CURRENT_DATE` / `CURRENT_TIMESTAMP`: 現在の日付やタイムスタンプを取得する

SQLでよく使われる関数

SQLには計算やデータ加工を効率的に行うための豊富な組み込み関数があります。ここでは、特によく利用されるものをカテゴリ別に紹介します。

集約（統計）関数

複数の行をグループ化して、そのグループ全体の値を計算します。主に `GROUP BY` 句と共に使用されます。

• COUNT(列名): 条件に合う行の数を返します。COUNT(*) とすると全ての行数を数えます。
• SUM(列名): 数値列の合計値を計算します。
• AVG(列名): 数値列の平均値を計算します。
• MAX(列名): 列内の最大値を返します。
• MIN(列名): 列内の最小値を返します。
• STDDEV(列名): 数値列の標本標準偏差を計算します。
• VARIANCE(列名): 数値列の標本分散を計算します。

※ DISTINCT は関数ではなく、SELECT DISTINCT 列名 のように使い、結果から重複行を除外するためのキーワードです。

文字列関数

• COALESCE(値1, 値2, ...): 引数の中で最初に見つかったNULLでない値を返します。
• CHAR_LENGTH(文字列): 文字列の文字数を返します。
• LOWER(文字列) / UPPER(文字列): 文字列を小文字または大文字に変換します。
• SUBSTRING(文字列 FROM 開始位置 FOR 文字数): 文字列の一部を抽出します。
• REPLACE(元の文字列, 検索文字列, 置換文字列): 文字列内の一部を別の文字列に置換します。
• TRIM([LEADING | TRAILING | BOTH] [除去文字 FROM] 文字列): 文字列の先頭、末尾、または両方から指定した文字（デフォルトは空白）を除去します。
• 文字列1 || 文字列2: 文字列を連結します（標準SQLの演算子）。

日付/時刻関数

• CURRENT_DATE: 現在の日付を返します。
• CURRENT_TIME: 現在の時刻を返します。
• CURRENT_TIMESTAMP / NOW(): 現在の日付と時刻（タイムスタンプ）を返します。トランザクションの開始時刻を基準とします。
• AGE(timestamp1, timestamp2): 2つのタイムスタンプの差（期間）を計算します。
• EXTRACT(フィールド FROM 日時): 日時データから年(YEAR), 月(MONTH), 日(DAY), 時(HOUR)などの特定の部分を抽出します。
• TO_CHAR(日時, '書式'): 日時データを指定した書式の文字列に変換します。

高度なクエリ機能

Window関数

Window関数は、GROUP BY のように行を集約せず、元の行を保持したまま、行をまたいだ計算（順位付け、累積計算など）を行うための強力な機能です。OVER() 句と一緒に使用します。

SELECT
  関数名() OVER (
    PARTITION BY 区切るための列名
    ORDER BY 順序を決めるための列名
  )
FROM テーブル名;

ROW_NUMBER()

順位に関わらず、一意の連続した行番号を振ります。

RANK()

順位を付けます。同順位がある場合、次の順位は飛びます (例: 1, 2, 2, 4)。

DENSE_RANK()

順位を付けます。同順位がある場合でも、次の順位は飛びません (例: 1, 2, 2, 3)。

LAG(列名, オフセット, デフォルト値)

現在の行から指定したオフセット分だけ前の行の値を取得します。

LEAD(列名, オフセット, デフォルト値)

現在の行から指定したオフセット分だけ後の行の値を取得します。

共通テーブル式 (CTE: Common Table Expression)

共通テーブル式（CTE）は、WITH句を使って、複雑なクエリを一時的な名前付きの結果セットに分割する機能です。サブクエリを多用するクエリの可読性を大幅に向上させ、同じ結果セットを複数回参照する場合にも役立ちます。

WITH
  cte_name1 AS (
    SELECT ... FROM ... -- 1つ目のクエリ
  ),
  cte_name2 AS (
    SELECT ... FROM cte_name1 -- 前のCTEを参照可能
  )
SELECT * FROM cte_name2;

データの追加・更新・削除

1. データ追加 (INSERT)

テーブルに新しい行を追加します。

-- 書式
INSERT INTO テーブル名 (列1, 列2) VALUES (値1, 値2);

-- 例: userテーブルにID=1, name='太郎'を追加
INSERT INTO public.user (id, name) VALUES (1, '太郎');

2. データ更新 (UPDATE)

既存の行のデータを変更します。WHERE句を省略するとテーブルの全ての行が更新されてしまうため、絶対に忘れないでください。

-- 書式
UPDATE テーブル名 SET 列1 = 新しい値1 WHERE 条件式;

-- 例: userテーブルのID=1のレコードのnameを'次郎'に変更
UPDATE public.user SET name = '次郎' WHERE id = 1;

3. データ削除 (DELETE)

テーブルから行を削除します。UPDATEと同様に、WHERE句を省略すると全ての行が削除されるため、細心の注意が必要です。

-- 書式
DELETE FROM テーブル名 WHERE 条件式;

-- 例: userテーブルからID=1のレコードを削除
DELETE FROM public.user WHERE id = 1;

補足：テーブルの全行を削除する場合、DELETE FROM table_name; よりも高速に動作する TRUNCATE TABLE table_name; というコマンドもあります。ただし、TRUNCATEはロールバックできないなど挙動が異なるため、用途に応じて使い分けます。