SQLの基本
SQLの基本
SQLとはデータの抽出を実施するために用いるプログラミング言語。
以下に基本的なSQLの文法、使用法を記載する。
1.データの抽出
SELECT
column
FROM
Data
これでData上にあるcolumnの列のデータを取得してくるという意味になる。
SQLは基本的に、どんなデータ抽出を行うにせよ基本的にはSELECT ~ FROMの形式を取る。
具体例
sample_data
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0101 | drink | 300 |
| 0102 | food | 800 |
上記のsample_dataに、以下のようなSQLで集計をすることで
SELECT
date,
sales
FROM
sample_data
| date | sales |
|---|---|
| 0101 | 1500 |
| 0101 | 300 |
| 0102 | 800 |
↑このようにデータが集計出来る。
SELECT 列名の後にasを付けることで、列名を任意のものに変更する事もできる。
SELECT
date as yyyymmdd,
sales
FROM
sample_data
| yyyymmdd | sales |
|---|---|
| 0101 | 1500 |
| 0101 | 300 |
| 0102 | 800 |
全ての列を取得したい場合には*と入力すると便利。
SELECT
*
FROM
sample_data
なお、SELECTでは既存のデータに存在する列以外にも作成できる。
例えば、sample上には存在していない会員_flgという文字列を1列分追加したい場合は
SELECT
"会員_flg" as flg,
*
FROM
sample_data
とクエリを入力すれば、以下のようなテーブルを集計することが出来る。
| flg | date | category | sales |
|---|---|---|---|
| 会員_flg | 0101 | food | 1500 |
| 会員_flg | 0101 | drink | 300 |
| 会員_flg | 0102 | food | 800 |
2.条件指定
SELECT
column
FROM
Data
WHERE
column = XXXX
条件指定にはWHERE 列名＋条件という文を使う。
これでData上にあるcolumnの列がXXXXであるデータのみを取得してくるという意味になる。
具体例
sample_data
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0101 | drink | 300 |
| 0102 | food | 800 |
上記のsample_dataのうちdateが0101のデータのみを集計したい場合には
SELECT
*
FROM
sample_data
WHERE
date = 0101
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0101 | drink | 300 |
WHEREは、不等号にも対応している。
SELECT
*
FROM
sample_data
WHERE
sales >= 500
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0102 | food | 800 |
3.グループ毎にまとめて集計する
SELECT
SUM(column) as column
FROM
Data
GROUP BY
column
グループ毎に数を足し合わせたり、最大値や最小値を計算したくなることがある。
その場合には、SUM()やMAX()等の集計関数とGROUP BYを用いる。
具体例
sample_data
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0101 | drink | 300 |
| 0102 | food | 800 |
上記のsample_dataのうちcategory毎にsalesの合計が見たい場合は、
SELECT
distinct category,
SUM(sales) as sales
FROM
Data
GROUP BY
category
とすることで、以下のようにcategory毎のsales合計が出てくる。
GROUP BYは忘れがちなので注意すること
| category | sales |
|---|---|
| food | 2300 |
| drink | 300 |
なおメインでよく使う集計関数は以下に掲げるものが多い。
-
SUM()：数値の合計 -
COUNT():データ個数のカウント（COUNT(DISTINCT ~)とすることでユニーク数カウント） -
MIN():最小値を求める -
MAX():最大値を求める -
AVERAGE():平均値を求める
4.複数のデータを連結する
SELECT
column
FROM
Data1
LEFT JOIN
Data2
ON
Data1.column = Data2.column
複数のデータを参照して、連結して集計したくなることがある。
こういうときには〇〇 JOINを用いる。そして複数のデータを連結するキーとなる列はON~で指定する。
具体例
sample_data1
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0101 | drink | 300 |
| 0102 | food | 800 |
sample_data2
| date | category | quantity |
|---|---|---|
| 0101 | food | 10 |
| 0101 | drink | 90 |
| 0102 | food | 30 |
sample_data1とsample_data2を連結して、category別のsalesの合計とquantityの合計を見たい場合は...
SELECT
d1.cateogry,
SUM(d2.sales) as sales,
SUM(d2.quantity) as quantity
FROM
sample_data1 as d1
LEFT JOIN
sample_data2 as d2
ON
d1.date = d2.date AND
d1.category = d2.category
GROUP BY
category
| category | sales | quantity |
|---|---|---|
| food | 2300 | 40 |
| drink | 300 | 90 |
ポイントとしては以下の3つ
-
ON〜の連結キーやSELECT以下の列名等を指定するときは必ず、d1.dateのようにデータ名と列名をセットにすること -
データの連結は等号だけでなく、不等号も使える
-
JOINにはいくつか種類がある.
INNER JOIN: sample_data1とsample_data2のうちマッチしたものだけが結合される
LEFT JOIN: sample_data1とsample_data2のうちマッチしたものと、sample_data1全て
RIGHT JOIN: sample_data1とsample_data2のうちマッチしたものと、sample_data2全て
FULL JOIN: sample_data1とsample_data2のうちマッチしたものと、sample_data1、sample_data2の全て
5.条件に応じて特定の値を出力する列を作る
SELECT
CASE WHEN column == XXXX then 'aaaa'
ELSE 'bbbb' END AS flg,
column
FROM
Data1
条件に応じて特定の値を出力するような（エクセルでいうところのIF文）みたいなことをやりたい時にはCASE WHEN ~ ELSE ~ END AS ~を使う。
具体例
sample_data1
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0101 | drink | 300 |
| 0102 | food | 800 |
salesが500を超えているものを'clear'それ以外のものをNullと表示するflgという列を作りたい場合は...
SELECT
*,
CASE WHEN sales >= 500 then 'clear'
ELSE null END AS flg
FROM
sample_data1
| date | category | sales | flg |
|---|---|---|---|
| 0101 | food | 1500 | clear |
| 0101 | drink | 300 | |
| 0102 | food | 800 | clear |
↑このようなアウトプットになる。複数条件がある場合は以下のようにWHEN〜を増やして記載する
SELECT
CASE WHEN sales >= 100 then '100over'
WHEN sales >= 200 then '200over'
ELSE null END AS flg
FROM
sample_data1
6.複雑な集計を実施する際のテクニック
①with
一回データの集計をして、その集計したものを更に集計したい！みたいなことがある。その時にはwithを用いる。
具体例
sample_data1
| date | category | sales |
|---|---|---|
| 0101 | food | 1500 |
| 0101 | drink | 300 |
| 0102 | food | 800 |
sample_data2
| date | category | quantity |
|---|---|---|
| 0101 | food | 10 |
| 0101 | drink | 90 |
| 0102 | food | 30 |
やりたいこと.
1.sample_data1でcategory毎にsalesの合計を出し、2.sample_data2でcategory毎にquantityの合計を出し、3.最後にcateogyを結合キーにしてsales、categoryの合計を一つのテーブルにしたい。
with A as(
SELECT
category,
SUM(sales) as sales
FROM
sample_data1
GROUP BY
cateogy
),
B as (
SELECT
category,
SUM(quantity) as quantity
FROM
sample_data2
GROUP BY
cateogy
)
SELECT
A.cateogy,
A.sales,
B.quantity
FROM
A
INNER JOIN
B
ON
A.category = B.category
| category | sales | quantity |
|---|---|---|
| food | 2300 | 40 |
| drink | 300 | 90 |
Discussion