この記事では、SQLで使えるいろいろな関数について説明します。
前回の記事で紹介した集約関数以外にも 、SQL では様々な関数がありデータの加工などができるようになっています。
関数の種類は非常にたくさんあるのですが、関数を4つほど紹介します。まずはこちらのSUBSTR(サブストリング) 関数を紹介します。
今回も以前の記事と同じテーブルを対象にしてSQLを実行します。
- Purchase_log
- product
- user
テーブルの準備方法は以前の記事をご覧ください！
SUBSTR(サブストリング) 関数
SUBSTR 関数は文字列を切り出す関数になります。
書式/コード
SELECT
SUBSTR(user_id, 4, 5)
FROM
purchase_log引数の１つ目は文字列、２つ目は切り出す最初の 文字の位置、３つ目は切り出す文字の最後の位置 です。
この場合はuser_id の4番目から5番目を切り出したいということになります。
結果は下記になります。
| SUBSTR(user_id4,5) |
|---|
| 01 |
| 02 |
| 02 |
| 04 |
| 03 |
| 99 |
LENGTH(レングス)関数
次はLENGTH(レングス)関数です。
引数に指定した文字列の文字の長さを返します。
SELECT
LENGTH(NAME)
FROM
product
;結果は下記になります。
| LENGTH(name) |
|---|
| 3 |
| 8 |
| 6 |
| 5 |
| 2 |
| 11 |
ROUND(ラウンド)関数
次はROUND(ラウンド)関数です。
ROUND 関数は小数点以下の数値を四捨五入します。
SELECT
sex,
COUNT(*),
SUM(age),
AVG(age),
ROUND(AVG(age))
FROM
user
GROUP BY
sex
;結果は下記になります。
| SEX | COUNT(＊) | SUM(age) | AVG(age) | ROUND(AVG(age)) |
|---|---|---|---|---|
| 女性 | 3 | 89 | 29.666666 | 30.0 |
| 男性 | 3 | 107 | 35.666666 | 36.0 |
COALESCE(コアレス) 関数
最後に COALESCE(コアレス) 関数を紹介します。
COALESCE 関数は引数の１つ目に指定した値が NULLつまりデータが無い場合、２つ目の引数に指定した値を表示するという関数になります。
SELECT
purchase_log.*,
COALESCE(user.id, 'データ無し'),
COALESCE(user.sex, 'データ無し'),
COALESCE(user.age, 'データ無し')
FROM
purchase_log
LEFT JOIN user ON
purchase_log.user_id = user.id
;こちらのSQLは LEFT JOIN の記事で紹介した SQL で、データがなくNoneが表示された場合がありました。
結果は下記になります。
| purchase_id | product_id | user_id | quantity | id | sex | age |
|---|---|---|---|---|---|---|
| 2021-5-20 | 1 | uid01 | 20 | uid01 | 男性 | 19 |
| 2021-5-20 | 2 | uid02 | 2 | uid02 | 女性 | 35 |
| 2021-5-20 | 3 | uid02 | 2 | uid02 | 女性 | 35 |
| 2021-5-21 | 4 | uid04 | 10 | uid04 | 女性 | 22 |
| 2021-5-22 | 5 | uid03 | 10 | uid03 | 男性 | 57 |
| 2021-5-22 | 6 | uid99 | 2 | データ無し | データ無し | データ無し |
今回いくつかの関数を紹介しましたが、他にも様々な関数があります。
ここで注意点ですが、関数はデータベースごとに使えるものが異なっています。
どのような関数が使えるのかは、データベース仕様確認する必要があります。
今回利用しているデータベースである SQL ライトの場合、英語ですが公式サイトなどで確認できます。
自分がやりたいことが関数でできのるか、こういった公式サイトやブログなど技術記事を調べてみましょう。