✅

SQLの基本

に公開
SQL
tech

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

また数値ではなく条件に文字列の完全一致や部分一致を指定したい場合は

  • 完全一致：WHERE column in ('任意の文字列')
  • 部分一致：WHERE column like '%任意の文字列%'

3.グループ毎にまとめて集計する 

SELECT
    column_a as column_a
    SUM(indicates) as indicates
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合計が出てくる。

category sales
food 2300
drink 300

見たいcategoryが複数ある場合はGROUP BYに複数のcolumnをカンマで区切って記述する。

GROUP BY
    column1, column2

GROUP BYは忘れがちなので注意すること

※〇〇毎に集計する場合はGROUP BYが必要だが、とにかく全てを合計した値が知りたいという場合はGROUP BYは不要である。このsample_dataをGROUP BYなしで集計すると2600という値が得られる。

なおメインでよく使う集計関数は以下に掲げるものが多い。

  • 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のようにデータ名と列名をセットにすること

  • データの連結は等号だけでなく、不等号も使える

  • 複数の連結キーを用いる場合は、以下のように連結キーとなる列をANDで並列に記述する。

    FROM
    TABLE_A as a
LEFT JOIN
    TABLE_B as b
ON
    a.id = b.id AND
    a.date = b.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
    CAST(column as int) as cloumn
FROM
    Data1

データテーブルに存在する各columnには「データ型」という概念が存在している。この「データ型」とは文字データ（string, varchar）、数字データ（int）、タイムスタンプ（timestamp, date）等のタイプがあってそれぞれデータを最適に扱えるように各データにはそれぞれ最適な「データ型」を持っている。

ただしこのデータ型が邪魔をする時があって、例えば数値を合計したいのに数値の列が「文字データ」として登録されていた場合、SUM()を使っても正しく集計が行われなずエラーとなってしまう。そういう場合に使えるのがCAST()で、これを用いることで特定の列のデータ型を任意のものに変更することが出来る。

具体例

sample_data

date category sales
0101 food 1500
0101 drink 300
0102 food 800

このsample_dataのうちsalesの値をdate毎に合計したい。しかし、salesが文字データとなっていてSUM()を使ってもエラーとなってしまう場合は

SELECT
    date,
    SUM(CAST(sales as int)) as sales
FROM
    sample_data
GROUP BY
    date

とすることでsalesを数値データに変換してSUM()を行えるので集計でエラーが発生しなくなる。

6.複雑な集計を実施する際のテクニック

①一度集計したテーブルの再利用〜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の合計を一つのテーブルにしたい。

この場合、JOINでsample_data1とsample_data2を結合してから集計する方法でも良いが、事前にsample_data1とsample_data2を集計しておいてそれを最後にJOINで結合するという方法も取れる。この後者の集計方法を実施する際に用いるのがwithで以下のように使う。

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

with 〇〇としておくことで事前集計したものを〇〇というテーブル名で利用することが出来る。
上の例ではsample_data1の集計結果をAとし、sample_data2の集計結果をBとして最後のSELECT文で再利用している。集計結果は以下のようになる。

category sales quantity
food 2300 40
drink 300 90

②条件に応じて特定の値を出力する列を作る〜case when~ 

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

③タイムスタンプの処理

Discussion