今回は基礎編最後ということで、SQLの関数を一部紹介していきます！関数自体はめちゃくちゃ多いですが、正直全部紹介するというよりは、関数というものがあるんだということをご理解いただければと思っているので、そんなに長い記事にはならないです！
ただ、この記事も前提は、ここまで紹介してきたSQLの基礎①～③になりますので、ここまでのものも一度復習してみてください！あとは、今回の基礎編を終えたら具体例の紹介なども将来的に出来ればなぁと思います。
www.digitaldolphin.jp
www.digitaldolphin.jp
www.digitaldolphin.jp
目次
今回使うデータ
今回も使うデータは前回（SQLの基礎③ 〜 簡単なSQL文のご紹介（JOIN句） 〜）にて、利用したものを利用しますので、長くならないためにもここではリンクのみで許してください！（そういう問題じゃないかもしれませんが、ちゃんと実行結果は出すので！笑）
SQLの関数を使う場面
前回までにデータの抽出や統合を実施してきた担当者は次なる課題にぶち当たってました。
「今までは、正直データを組み合わせたり、条件で絞込をかけたりしてきたから、正直データの関係性や中身がわかっていれば出来たけど、値を変換したり、ExcelのIF文とかはSQLでは出来るのかな？」、「上司からも『年代に変換できないのか？』とか、『店舗とオンラインで分けるとかできない？』とかも言われるし、何か方法を知りたいなぁ」といった悩みを抱えてました。
そんなときにネットや参考書籍で色々調べてみると、SQLにもExcelとかと同じように関数という概念があるということを知りました。まずはどんな関数があるのかを少し見ていきたいと思います。
SQLで使う関数例
今までも、SUM関数などの集計関数やNULLIF()などの関数を見てきましたが他にはどんなものがあるのかをここでは紹介していきます。ただし、前提として、SQLの場合は使用するソフトウェアによっても使える関数が変わってしまうため、今回紹介したものと同じようなものがそれぞれのソフトウェア独自で定義があるということは注意してください！
まずは一覧で一挙に関数を書いていきます！今回は、MySQLを元にします！（あくまでも個人の主観ですが、かなり頻度の高いものを優先的に記載していきます笑）
表1：SQLで利用する関数（日付および時間関数編）
| # | 関数 | 意味 |
|---|---|---|
| 1 | CURDATE() | 現在の日付を取得するときに利用。 |
| 2 | DATEDIFF() | 2つの日付型のデータの差を算出する際に利用。 |
| 3 | TIMESTAMPDIFF() | 2つの日時型のデータの差を算出する際に利用。 |
| 4 | NOW() | 現在の日時を算出する際に利用。 |
| 5 | SYSDATE() | この関数が実行される日時を取得する際に利用。 |
| 6 | UNIX_TIMESTAMP() | UNIXタイムスタンプ*1を取得する際に利用。 |
| 7 | FROM_UNIXTIME() | UNIXタイムスタンプから日付形式を取得する際に利用。 |
| 8 | YEAR() | 日付型、若しくは日時型のデータの年部分を返す際に利用。 |
| 9 | MONTH() | 日付型、若しくは日時型のデータの月部分を返す際に利用。 |
| 10 | DATE() | 日付型、若しくは日時型のデータの日付部分を返す際に利用。 |
| 11 | HOUR() | 日時型のデータの時間部分を返す際に利用。 |
| 12 | MINUTE() | 日時型のデータの分部分を返す際に利用。 |
| 13 | DATE_ADD() | 日付型のデータに時間間隔を加算する際に利用。 |
表2：SQLで利用する関数（制御フロー関数編）
| # | 関数 | 意味 |
|---|---|---|
| 1 | IF() | 条件により返り値を制御する際に利用。 |
| 2 | CASE | 条件により返り値を制御する際に利用。IF文よりも条件が多いときによく利用 |
| 3 | IFNULL() | IFNULL(expr1,expr2)として、"expr1"がNULLでない場合、IFNULL() は"expr1"を返し、それ以外の場合は"expr2"を返す。NULLの対処方法としてよく利用される。 |
| 4 | NULLIF() | NULLIF(expr1,expr2)として、expr1 = expr2 がtrueの場合は NULL を返し、それ以外の場合は"expr1"を返す。 |
表3：SQLで利用する関数（文字列演算子編）
| # | 関数 | 意味 |
|---|---|---|
| 1 | CONCAT() | 複数のカラムの値を連結した値を返す際に利用。 |
| 2 | LEFT() | カラム内の値を左から指定の文字列分抽出する際に利用。 |
| 3 | RIGHT() | カラム内の値を右から指定の文字列分抽出する際に利用。 |
| 4 | LOWER() | 引数*2の小文字を取得する際に利用。 |
| 5 | UPPER() | 引数の大文字を取得する際に利用。 |
| 6 | MID() | 指定された位置から始まる部分文字列を取得する際に利用。 |
| 7 | SUBSTRING() | SUBSTRING(str,pos,len)の形で、"str"文字列から"pos"で指定された位置から始まる"len"の文字数を取得する際に利用。 |
| 8 | REGEXP | 正規表現を使用したパターン一致を判定する際に利用。 |
| 9 | REGEXP_REPLACE() | 正規表現を利用した置換をする際に利用。 |
表4：SQLで利用する関数（数値関数と演算子、集約関数編）
| # | 関数 | 意味 |
|---|---|---|
| 1 | TRUNCATE() | 指定された小数点以下の桁数に切り捨てる際に利用。 |
| 2 | MOD() | MOD(N,M)の形で、NをMで割った余りを算出する際に利用。 |
| 3 | RAND() | 乱数を取得する際に利用。 |
| 4 | ROUND() | ROUND(X,D)の形で、"X"を小数点以下第"D"位までで四捨五入した値を取得したい際に利用。"D"を記載しない場合は、デフォルトで"D"は0となる。 |
| 5 | COUNT() | レコード数を算出する場合に利用。 |
| 6 | COUNT(DISTINCT) | カラム中の値のユニーク数を算出する場合に利用。 |
| 7 | GROUP_CONCAT() | GROUP BYで集約した際に、集約されたカラムの値を連結された文字列で取得する際に利用。 |
| 8 | MAX() | 最大値を取得する際に利用。 |
| 9 | MIN() | 最大値を取得する際に利用。 |
| 10 | SUM() | 合計値を算出する際に利用。 |
表5：SQLで利用する関数（キャスト関数と演算子編）
| # | 関数 | 意味 |
|---|---|---|
| 1 | CAST() | カラムのデータ型を変更する際に利用。 |
表6：SQLで利用する関数（WINDOW関数編）
| # | 関数 | 意味 |
|---|---|---|
| 1 | ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2 ASC) | レコードに対して行番号を振る際に利用。col1単位で、col2を昇順（降順の場合はASCをDESCにする）並び替えた際の行番号（重複無しの連番）を出す。 |
| 2 | RANK() OVER (PARTITION BY col1 ORDER BY col2 ASC) | レコードに対して行番号を振る際に利用。col1単位で、col2を昇順（降順の場合はASCをDESCにする）並び替えた際の行番号（重複ありで、1,1,3,4,5,5,7,・・・という順位）を出す。 |
| 3 | DENSE_RANK() OVER (PARTITION BY col1 ORDER BY col2 ASC) | レコードに対して行番号を振る際に利用。col1単位で、col2を昇順（降順の場合はASCをDESCにする）並び替えた際の行番号（重複ありで、1,1,2,3,4,4,5,・・・という順位）を出す。 |
| 4 | FIRST_VALUE() OVER (PARTITION BY col1 ORDER BY col2 ASC) | col1単位で、col2を昇順に並べた際の最初の値を取得する際に利用。 |
| 5 | LAST_VALUE() OVER (PARTITION BY col1 ORDER BY col2 ASC) | col1単位で、col2を昇順に並べた際の最後の値を取得する際に利用。 |
| 6 | NTH_VALUE() OVER (PARTITION BY col1 ORDER BY col2 ASC) | col1単位で、col2を昇順に並べた際のN番目の値を取得する際に利用。 |
| 7 | SUM(col3) OVER(PARTITION BY col1 ORDER BY col2 ROWS UNBOUNDED PRECEDING) | col1単位で、col2を昇順に並べた際の対象レコードまでの累積値を算出する際に利用。"UNBOUNDED PRECEDING"は、前にあるレコードを全てという意味。 |
実際の関数の使用例
ここまで関数という概念があること、そしてよく使われる関数を一覧でご紹介してきましたが、正直覚えることが必須というわけではありません。実際に覚えてほしいのは、表で示した関数の意味のほうを難なく覚えておいて、実現可否を知っておくことが重要です。そして、何か実際に使う場面では調べて使えばいいと思います！
まずは簡単な例ですが、顧客テーブルに存在する生年月日から年齢や年代を算出する場合の方法を以下に共有します。
サンプル1
SELECT
顧客ID
,生年月日
,TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) AS 年齢
,CASE
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) <20 THEN "20歳未満"
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) <30 THEN "20代"
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) <40 THEN "30代"
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) <50 THEN "40代"
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) <60 THEN "50代"
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) <70 THEN "60代"
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) <80 THEN "70代"
WHEN TIMESTAMPDIFF(YEAR, 生年月日, CURDATE()) IS NULL THEN "値無し"
ELSE "80歳以上"
END AS 年代
FROM
顧客データ
;表7：サンプル1の実行結果
| 顧客ID | 生年月日 | 年齢 | 年代 |
|---|---|---|---|
| c001 | 1990-03-10 | 32 | 30代 |
| c002 | 1995-02-05 | 27 | 20代 |
| c003 | 2001-08-08 | 20 | 20代 |
| c004 | 1993-09-20 | 28 | 20代 |
| c005 | 1997-12-02 | 24 | 20代 |
| c006 | 2002-11-24 | 19 | 20歳未満 |
| c007 | 1984-02-26 | 38 | 30代 |
| c008 | 1999-07-30 | 22 | 20代 |
| c009 | 2005-01-10 | 17 | 20歳未満 |
| c010 | 1988-03-13 | 34 | 30代 |
※実行日は2022年7月1日想定
次にもう少し複雑ですが、WINDOW関数の例を共有します！
サンプル2
SELECT
顧客ID
,受注ID
,受注日時
,購入金額
,DENSE_RANK() OVER (PARTITION BY 顧客ID ORDER BY 受注日時 ASC) AS 顧客毎の購入回数
,FIRST_VALUE() OVER (PARTITION BY 購入店舗 ORDER BY 受注日時 ASC) AS 顧客毎の初回購入日時
,TIMESTAMPDIFF(DAY, FIRST_VALUE() OVER (PARTITION BY 購入店舗 ORDER BY 受注日時 ASC), 購入日時) AS 顧客毎の初回購入からの経過日数
,SUM(購入金額) OVER(PARTITION BY 顧客ID ORDER BY 受注日時 ROWS UNBOUNDED PRECEDING) AS 顧客毎の累計購入金額
FROM
受注データ
WHERE
キャンセル日時 IS NULL
AND 返品日時 IS NULL
AND 受注日時<= CURDATE()
ORDER BY
顧客ID
,受注ID
,受注日時
,購入金額
;表8：サンプル2の実行結果
| 顧客ID | 受注ID | 受注日時 | 購入金額 | 顧客毎の購入回数 | 顧客毎の初回購入日時 | 顧客毎の初回購入からの経過日数 | 顧客毎の累計購入金額 |
|---|---|---|---|---|---|---|---|
| c001 | o002 | 2022-06-24 19:46:01 | 32000 | 1 | 2022-06-24 19:46:01 | 0 | 32000 |
| c001 | o008 | 2022-07-04 03:13:40 | 7000 | 2 | 2022-06-24 19:46:01 | 9 | 39000 |
| c004 | o004 | 2022-06-29 08:46:59 | 14000 | 1 | 2022-06-29 08:46:59 | 0 | 14000 |
| c005 | o003 | 2022-06-27 00:59:09 | 9000 | 1 | 2022-06-27 00:59:09 | 0 | 9000 |
| c005 | o007 | 2022-07-03 01:39:28 | 7000 | 2 | 2022-06-27 00:59:09 | 6 | 16000 |
| c005 | o009 | 2022-07-04 23:25:35 | 11000 | 3 | 2022-06-27 00:59:09 | 7 | 27000 |
| c007 | o005 | 2022-06-30 13:04:19 | 24000 | 1 | 2022-06-30 13:04:19 | 0 | 24000 |
| c007 | o012 | 2022-07-11 21:01:41 | 9000 | 2 | 2022-06-30 13:04:19 | 11 | 33000 |
※実行日は2022年7月1日想定
また、もう一つORDER BY句を一回も紹介したことがなかったなと思いますが、ORDER BY句は対象の項目で昇順や降順に並び替えをする際に利用するクエリになります。アウトプットを出す際に、整列させたい場合によく利用するため、覚えておいてください！
今日はかなり簡単ですが、基礎①～③で記載したことをベースにすれば、関数自体は概念を覚えておくことで応用できるものだったりしますので、これだけにします。また、データを実際に扱われている人にとっては、データの加工や統合という言葉がよく出てきますが、データの加工というのは今回作成した関数や四則演算を利用して、もともとの値から別の値を作成することを指し、統合は前回実施した基礎③で紹介したJOIN句のことを指すということも折角なので覚えておいてください！
さいごに
今回は、SQLの関数という概念をご紹介してきましたが、基礎編としてご紹介した基礎①～④はいかがでしたでしょうか。正直、まだ共有しないといけないこともありますが、、、笑
まずは基礎としてしっかりと復習しておいてください。また、基礎①～④までがちゃんと使えれば、ある程度SQLでデータを抽出するだけなら出来るようになってきます。ただ一方で、実際にやろうとするとわからないことも多々あると思いますが、まずはそれは無視してSQLを触ってみたり、コードを見てみたりしてください！基礎①～④の記事を見る前と比べて、何を言っているかわかるようになっていると思います！
データ関連は引き続き、ご紹介していきますので、ご期待ください！
