3.3 行と列の選択
最初にテーブルから指定した列だけを表示しましょう. SQLではまずどの列を出力するかを指定するため SELECT
句を書きます. SELECT
のあとに抽出したい列名をカンマ区切りで書きます. 今回は従業員一覧から,「名前」と「年齢」を出力したいので SELECT age, name
となります. その後 FROM
と書いて, さらにその後に読み込みたいテーブルの名前を指定します. 最後に, クエリの文の末尾を表すセミコロン ;
を入力して実行します. なお SQL の構文ではクエリを改行するタイミングは自由です. SELECT
の後の列名が多ければそこで改行しても構いません. 一方で, 句の順番は決まっているので SELECT
句より前に FROM
句を書くことはできません. ここまでの説明をまとめると以下のクエリとなり, その結果が表 3.3 となります.
SELECT
name, ageFROM
<DB>.employees
;
age | name |
---|---|
41 | アンドレイ・コルモゴロフ |
29 | ロナルド・フィッシャー |
59 | ピエール・ラプラス |
28 | ルドルフ・カルマン |
48 | 赤池 弘次 |
51 | 林 知己夫 |
35 | 伊藤 清 |
SELECT
句は FROM
句で読み込んだテーブルにない列も指定できるため, 新しい列を作ることもできます. 以下のクエリを実行すると, 元のテーブルに存在しなかった列が現れます.
SELECT
1, 'a'
FROM
<DB>.employees
;
SQLでは FROM
句で読み込んだテーブルの1レコードごとに SELECT
で行っている計算を繰り返しているのを想像するとわかりやすいです. また, 新たな列は _col1
という名前になっていると思います. 新しい列に名前をつけたり, 既存の列を改名するには AS
を使います.
SELECT
1 AS var1 , 'a' AS var2
FROM
<DB>.employees
;
もう少し実用的な列の追加の例を紹介しましょう. employees
テーブルでは name
列に氏名を格納していますが, 姓と名を別々の列に分けてみましょう. 残念がら SQL の関数名は実装によってかなりばらつきがありますが, 例えば presto
では SPLIT_PART()
関数を使います.
SELECT
id,
' ', 1) AS shi,
SPLIT_PART(name, ' ', 2) AS mei,
SPLIT_PART(name,
department_id,
ageFROM
<DB>.employees
;
特定の条件にマッチする行だけを取り出したい場合は, FROM
句の後に WHERE
句を書きます. 一般的なプログラミング言語のような条件式を描くことができます. たとえば, 30代以上の従業員の名前と年齢の一覧を出力したい場合は以下のようになります. 四則演算も使用できます.
SELECT
name, ageFROM
<DB>.employees
WHERE
30 <= age
;
3.3.1 集約関数とグループ化
表と列の選択ができるようになったので次はまた違った軸でテーブルの情報を要約してみましょう. SQL では列全体を入力として, 値を1つだけ返す集約関数 (aggregate functions) が用意されています. まず, employees
テーブルには何人分の名前があるか, SQL で調べてみましょう. 列に何件の値があるかは, COUNT()
関数を使って知ることができます.
SELECT
COUNT(age)
FROM
<DB>.employees
;
すると, 1レコードだけの結果が出力されます(表 3.4).
_col0 |
---|
7 |
テーブルのレコード数と一致すると予想したかもしれませんが, COUNT()
関数は値が欠損しているものをカウントしません. よって, 欠損も含めて数えるには COUNT(1)
と書く, という方法があります. なぜこれでできるかというと, 先ほど説明した列の追加を考えるとイメージし易いと思います. まず, 1
と書けば, 全レコードで値が 1
になっている列が作られます. それに対して COUNT()
で要約をすれば, 欠損値がないので必ずレコード数と同じ数が返ってきます. 同様に COUNT(NULL)
とすると, 全てが欠損値となる列に対して COUNT()
を適用するため, 結果がゼロになります (試してみましょう).
もちろん条件をつけることもできます. 30歳以上の社員数を数えてみましょう.
そのほか, 集約関数には, 合計値を求める SUM()
や, 平均値を求める AVG()
, 最大値・最小値を求める MAX()
, MIN()
があります. 次は平均年齢を求めてみましょう.
データの特徴をとらえるには, 全体の要約だけではなく, もっと細かい軸でグループ分けしそれぞれで要約する必要もしばしばあります. このようなグループ別集計は GROUP BY
句を併用します. 例えば, 部署ごとの平均年齢を求めたい場合は GROUP BY
の後に部署IDを書きます. 公式の用語ではないですが, 便宜上, GROUP BY
の後に書いた変数をグループ列と呼ぶことにします.
SELECT
department_id, AVG(age) as average
FROM <DB>.employees
GROUP BY department_id
;
さらに, グループ化する上での注意点がいくつかあります.
- グループ列は,
SELECT
句のようにカンマ,
で区切ることで複数指定可能です. GROUP BY
句を使った場合,SELECT
句にはグループ列と集約関数しか指定できず, またグループ列をかならず含める必要があります(グループ列を省略することが可能なエンジンもありますが, TDはそうではなりません).- 関数で変換後の列をグループ列に指定できます. ただし,
AS
で命名した列名ではなく, 関数を含めて記述する必要があります(これも,AS
で命名した名前でも指定できるエンジンもありますが, TDはそうではありません).