始めに
こちらは、筆者がSQLを学習中に必要となった知識をまとめたものです。
間違いなどがあるかもしれません。その時は、コメントで教えていただけると幸いです。
行・列の操作
SELECT, FROM, LIMIT
SELECT カラム名 FROM テーブル名 LIMIT 表示レコード数;
説明・パラメータ
- SELECT * ：'*'を指定することでテーブルにあるすべてのカラムを取得する
- AS : 取得したカラムやテーブルに別名をつけられる
- 列は指定した順番で表示される。
絞り込み・検索
- WHERE 条件
- WHERE カラム LIKE 条件
説明・パラメータ
- AND, ORを使うと複数の条件を組み合わせられる
- LIKE '単語'：部分一致するものを取得する
- 単語に'%'をつけることでそれ以降の文字については考慮しない
- 正規表現を使う時はLIKEの代わりに'~'をつける
- SQLの正規表現について(https://www.postgresql.jp/document/9.4/html/functions-matching.html)
ソート・ランク付け
- ORDER BY カラム ASC/DESC：ASC（昇順）、DESC（降順）
- RANK() OVER(ORDER BY カラム):
説明・パラメータ
- RANK：同率があった場合順位は同じになり、その次の順位を飛ばす
- DENS_RANK : 同率があった場合順位は同じになり、その次の順位は飛ばさない
- ROW_NUMBER : 同率があっても同じ順位にはならず、順位をその場合のみランダムでカウント
OVER
[集計関数] OVER([PARTITION BY 式] [ORDER BY][{ROWS 範囲}] [RANGE 範囲])
説明・パラメータ
説明
通常、集計関数はGROUP BYで指定された範囲ごとにしか値を返せないが、OVER関数を使用することで、任意の範囲で集計関数を利用できる。
パラメータ
- [集計関数]：COUNT, SUM, AVRなど
- [PARTITION BY 式]：グループごとにまとめて集計する場合に使用
- [ORDER BY]：集計関数に対してじゅにを指定する際に使用
- [ROWS 範囲]：集計する範囲の行を指定する際に使用
- [RANGE 範囲]：集計する範囲を指定する際に使用
集計
COUNT
- COUNT(カラム)：カラムの行数を返す（NULLを含まない）
- COUNT(1) or COUNT(*)：NULLを含む、テーブルの全用件数を返す
説明・パラメータ
- (DISTINCT カラム)：値の重複を除外して計算する
GROUP BY
集計関数 FROM テーブル GROPU BY (カラム)
説明・パラメータ
- GROUP BY：指定された行でグループ化
- 集計関数の例
- SUM：合計
- MAX:最大値
- MIN：最小値
- AVG：平均値
HAVING
GROUP BY カラム
HAVING 条件
説明・パラメータ
- かならずGROUP BYとセットで使う
- グループ化されたものに対してWHEREのように絞り込みを行う
統計量
PERCENTILE_CONT：パーセンタイル
PERCENTILE_CONT(値) WITHIN GROUP(ORDER BY カラム)
- PERCENTILE_CONT(値)：指定したパーセントタイルの値を引数にとり、該当のデータを返す。
- OVER句ではなく、WITHIN GROPU(ORDER BY カラム)でソート順を指定する必要がある。
MODE：最頻値
MODE() WITHIN GROUP(ORDER BY カラム)
説明・パラメータ
- MODE：指定した行の最頻値を返す。
- PERCENTITlE_CONTと同様にWITHIN GROUPでソートする。
分散
VAR_SAMP(カラム)
説明・パラメータ
- VAR_POP, VARIANCE：標本分散を返す
- VAR_SAMP:不偏分散を返す
- 分散、標準偏差関数の詳細(https://dev.mysql.com/doc/refman/5.6/ja/group-by-functions.html#function_std)
標準分散
STDDEV_SAMP(カラム)
説明・パラメータ
- STDDEV, STDDEV_POP：標本標準偏差を返す
- STDDEV_SMAP：不偏標準偏差を返す
LEAST
LEAST(値1,値2,...)
- 引数の最小値を返す
WITH
WITH サブクエリ名 AS(slectなど)
説明・パラメータ
結合
(INNER, RIGHT, LEFT ,FULL)JOIN
- FROM テーブル1 JOIN テーブル2 ON テーブル1.カラム1 = テーブル2.カラム2
- FROM テーブル1 {LEFT/RIGHT/FULL} JOIN テーブル2 ON テーブル1.カラム1 = テーブル2.カラム2
説明・パラメータ
- JOIN : 内部結合
- LEFT JOIN : 左外部結合
- RIGHT JOIN : 右外部結合
- FULL JOIN : 完全外部結合
- 外部都合にて対応データが存在しない方のテーブルの列の内容は、すべてNULLとなる。
- ONの代わりにUSING 共通のカラム名とすることもできる。
CROSS JOIN
FROM テーブル CROSS JOIN カラム
説明・パラメータ
- CROSS JOIN：テーブル同士の交差結合を行う
- 全てのテーブルの列と全てのテーブルの列を持ち、全ての組み合わせを持つテーブルが生成される
LAG
LAG(カラム, 値)
説明・パラメータ
- 指定されたカラムを値分だけ下にずらす。
- 元のデータと結合することで過去のデータと同じレコードで比較できる
WHEN
CASE
WHEN 条件式1 THEN 返り値1
[WHEN 条件式2 THEN 返り値2] [ELSE 返り値]
END
説明・パラメータ
- 条件分岐を行う。何個に分岐してもWHENで条件節を書き始める
データ変換
CAST, TO_CHAR, TO_DATE, TO_TIMESTAMP,
- CAST(値 AS 書式)：値を指定された型に変換
- TO_CHAR(値, 書式)：文字列型の値を指定された書式の日付型に変換
- TO_DATE(値、書式)：数値型の値を指定された書式の日付型に変換
- TO_TIMESTAMP(値)：数値型のUNIX秒のデータをTIMESTAMP型に変換
説明・パラメータ
CHAR型：固定長の文字列
VARCHAR型：可変調の文字列
INTEGER型：整数
DATE型 : 日付を文字列として入力する場合の型
| Format | Sample |
|---|---|
| 'YYYY-MM-DD' | '2020-01-12' |
| 'YY-MM-DD' | '20-01-12' |
| 'YYYYMMDD' | '20200112' |
| 'YYMMDD' | '200112' |
(区切り文字は'-'のほかに'.'や'/','@'でも代用できる)
TIME型 : 時刻を文字列として入力する場合の型
| Format | Sample |
|---|---|
| 'HH:MM:SS' | '21-03-12' |
| 'YY:MM' | '21-03' |
| 'HHMMSS' | '210312' |
| 'D HH:MM:SS' | '2 21:03:12' |
その他にも'MMSS','SS','D HH:MM','D HH'などがある。
DATETIME型 : 日付と時刻を一緒人文字列として入力する場合の型
| Format | Sample |
|---|---|
| 'YYYY-MM-DD HH:MM:SS' | '2020-01-12 21:03:12' |
| 'YY-MM-DD HH:MM:SS' | '20-01-12 21:03:12' |
| 'YYYYMMDDHHMMSS' | '20200112210312' |
| 'YYMMDDHHMMSS' | '200112210312' |
TIMESTAMP型 : 日付と時刻を一緒に文字列として表示する型。実態は整数なので計算ができる。DATETIMEが64bitに対してこちらは32bitそのため、2038年には使えなくなる。
SUBSTR : 文字の切り取り
SUBSTR(文字列, 開始位置, 切り取り文字数)
- 文字列を切り取る（開始位置は1から始める）
ファイル入出力
COPY テーブル名 TO/FROM ファイルPath [options...]
- TO：ファイルのエクスポート
- FROM：ファイルのインポート
説明・パラメータ
オプション
- WITH CSV：CSVファイル時に必須
- HEADER：ヘッダーの有無（出力では、各列名が追加され、入力では各列名が省かれる）
- DELIMITERS：区切り文字の指定(テキスト書式でのデフォルトはタブ文字、CSV書式ではカンマ)(CSVファイルで E'\t' を追加するとTSVファイルになる)
- ENCODING：文字コードの指定（'UTF-8'もしくは'SJIS'）