はじめに
以下はスッキリわかるSQL入門第5章から第6章までの学習備忘録です。
引用されている個所は書籍の内容をそのまま書き、引用されていない箇所は私が調べて補足した内容を書いてあります。SQLのタグが付いているコードブロックに関しては、書籍の引用と私が調べて補足したものを織り交ぜた内容になっています。
また私が曖昧な理解や、知識として定着していない個所を記述していますので、基本的な文法や構文に関しては記述されていない可能性があることをご了承ください。
RDMSについてはMySQLを想定しています。
第5章 式と関数
5.1 式と演算子
式の種類
- 条件式・・・式の評価の結果が真か偽の場合は条件式と呼ぶ。SQLにおいて条件式となる主な演算子は比較演算子と論理演算子。
- 計算式・・・式の表かの結果が真か偽にならない場合、この書籍では計算式と定義する。SQLにおいて計算式となる主な演算子は算術演算子と代入演算子。
選択リストへのさまざまな指定
SELECT 出金額, -- 列名での指定
出金額 + 100, -- 計算式での指定
'SQL' -- 固定値での指定
FROM 家計簿
上記の選択列リストへの指定と結果
- 列名: 列の内容がそのまま出力される
- 計算式: 計算式の評価結果が出食される
- 固定値: 固定値がそのまま出力される
※ 選択列リストで計算式を使う場合は、必ずASを併用すると列名がわかりやすくなる。
5.2 さまざまな演算子
CASE演算子 ー 値を変換する
CASE演算子は、列の値や条件式を評価し、その結果に応じて好きな値に変換することができる
/* CASE演算子を使った利用構文(1) */
case 評価する列や式
when 値1 then 値1のときに返す値
[when 値2 then 値2のときに返す値]
[else デフォルト値]
end
/* 上記を基にCASE演算子を使ったSELECT文(1)
費目の値に応じて変換する
*/
SELECT 費目, 出金額,
CASE 費目 WHEN '移住費' THEN '固定費'
WHEN '水道光熱費' THEN '固定費'
ELSE '変動費'
END AS '出費の分類'
FROM 家計簿
WHERE 出金額 > 0
----------------------------------------------------------------------------------------------------
/* CASE演算子を使った利用構文(2) */
case
when 条件1 then 値1 条件1のときに返す値
[when 条件2 then 値2 条件2のときに返す値]
[else デフォルト値]
end
/* 上記を基にCASE演算子を使ったSELECT文(2)
条件に応じた値に変換する
*/
SELECT 費目, 出金額,
CASE 費目 WHEN 入金額 < 5000 THEN 'お小遣い'
WHEN 入金額 < 100000 THEN '一時収入'
WHEN 入金額 < 300000 THEN '給料でたー!'
ELSE '想定外の収入です!'
END AS '収入の分類'
FROM 家計簿
WHERE 入金額 > 0
5.3 さまざまな関数
※この章におけるSQLで使用可能な関数についての説明は割愛します。
関数について定められていること
- 名前: その関数の名前
- 引数: その関数を呼び出す際に引き渡す情報(関数によっては2つ以上の場合もある)
- 戻り値: その関数の呼び出し結果として得られる情報
関数はDBMSによって大きく異なる
関数については、DBMS製品によって構文が大きく異なるため、詳細は製品マニュアルを参照する必要がある
ユーザー定義関数とストアドプロシージャ
- ユーザー定義関数・・・SQL文の中で関数として呼び出され、戻り値を返すために使用される。
- ストアドプロシージャ・・・SQL文をまとめた一連の手続きを作成することができ、その手続きを必要なときに呼び出して実行することができる。
SELECT文にFROM句がない
計算式や関数は、リテラルなどの具体的な値を材料にすれば、テーブルの列を1つも記述していなくてもSELECT文として成り立ってしまう。
これを利用して、まずは式や関数の動作確認だけをしたい場合、次のようなSELECT文を実行することが可能。
SELECT 式や関数
関数の多用で負荷増大
複数の関数を複雑に組み合わせた場合はもちろん、関数を使う事によりインデックス検索が無効化された場合などは、レスポンスが悪化することもあるので、使用の前には十分な検証を行う必要がある。
第6章 集計とグループ化
6.1 データを集計する
集計関数の特徴
- 検索対象の全行をひとまとめに扱い、1回だけ集計処理を行う
- 集計関数の結果は必ず1行になる
- 集計関数には、SUM、MAX、MIN、AVG、COUNTがある。
スカラー関数の特徴(第5章で扱った関数)
- 検索対象の各行に対して、同じ処理や計算を行うように命令したもの
- スカラ―関数を使うことによって、検索結果の行が増えたり減ったりすることはない
- スカラー関数には、LENGTH、TRIM、REPLACE、CONCAT、ROUNDE、POWER、CASTなどがある。
6.2 集計関数の使い方
代表的な集計関数
| 分類 | 関数名 | 説明 |
|---|---|---|
| 集計 | SUM | 各行の値の合計を求める |
| MAX | 各行の値の最大値を求める | |
| MIN | 各行の値の最小値を求める | |
| AVG | 各行の値の平均値を求める | |
| 行数 | COUNT | 行数をカウントする |
検索結果の行数を求める(COUNT)
- COUNT( * )`は単純に行数をカウントする(NULLの行も含める)
- COUNT( 列 )`は、指定列の値がNULLである行を無視してカウントする
重複した値を除いた集計
- AVG、SUM、COUNTの各関数では、『DISTINCT』を指定する事によって、その列で重複している値を除いた状態で集計が行われる。
SELECT COUNT(DISTINCT 費目) FROM テーブル名
6.3 集計に関する4つの注意点
1. 集計関数は、SELECT文の選択リストかORDER BY句、HAVING句にのみ記述可能
2. 集計関数は結果表が必ず長方形型になり、結果表がデコボコになるようなSQL文は実行できない(一部のDBMS製品ではNULLを補うなどして動作するものがあるが、非推奨)
3. 引数に許される型が集計関数ごとに異なる(詳細は↓の表6.2参照)
4. NULLの取り扱いが異なる(詳細は↓の表6.3参照)
表6-2 集計関数に渡す引数の型と戻り値
| 関数名 | 数値型 | 文字列型 | 日付型 |
|---|---|---|---|
| SUM | 各数値の合計 | ✖ | 各行の値の合計を求める |
| MAX | 各数値の最大 | 並び替えて最後の文字列 | 最も新しい日付 |
| MIN | 各数値の最小 | 並び替えて最初の文字列 | 最も古い日付 |
| AVG | 各数値の平均 | ✖ | ✖ |
| COUNT | 行数 | 行数 | 行数 |
表6-3 集計関数におけるNULLの取り扱い
| 集計関数 | 集計時のNULLの扱い | 全行がNULLの場合の集計結果 |
|---|---|---|
| SUM | 無視 (NULLは集計に影響を与えない) |
NULL |
| MAX | ||
| MIN | ||
| AVG | ||
| COUNT(列名指定) | 0 | |
| COUNT( * ) | NULLを含んでカウントする | 該当行数 |
6.4 データをグループに分ける
グループ化して集計する基本構文
SELECT グループ化の基準列名・・・, 集計関数
FROM テーブル名
(WHERE 絞り込み条件)
GROUP BY グループ化の基準列名・・・
グループ化した集計関数のポイント
- グループ化するには、GROUP BY句に基準となる列を指定する
- 集計関数は、データの値をグループごとにまとめて計算する
- 集計関数の結果表の行数は、必ずグループの数と一致する
複数の列によるグループ化
GROUP BY句に複数の列をカンマで区切って指定することで、複数の列を基準にしたグループ化が可能。
複数の列によるグループ化は、それらの列を組み合わせて、値が同じになるものを収集してグループが作られる。
グループ化してから絞り込む基本構文
SELECT グループ化の基準列名・・・, 集計関数
FROM テーブル名
(WHERE もとの表に対する絞り込み条件)
GROUP BY グループ化の基準列名・・・
HAVING 集計結果に対する絞り込み案件
HAVING句は、集計結果がすべて揃った最後の段階で実行される。そのためWHERE句とは異なり、集計関数を記述することが可能になる。
グループ集計と選択列リスト
グループ集計を行うSELECT文の選択列リストに指定する列は、以下のどちらかにあてはまる必要がある。
なぜなら、以下に当てはまらない列を抽出しようとすると『デコボコな結果表』になってしまうため。
1. GROUP BYでグループ化の基準列として指定されている
2. 集計関数による集計の対象となっている