SQLの様々な関数について解説する

SQL
2021.02.24

さてさて、今回はSQLの学習シリーズ第4弾ということで、SQLで頻繁に使われる代表的な関数について説明していきます。

SQLの便利な関数

SQLには多くの関数がありますが、大きく分けると以下の関数があります。

  • 算術関数
  • 文字列関数
  • 日付関数
  • 変換関数
  • 集約関数

今回はこれらの代表的な関数について説明していきます。

算術関数

算術関数は加減乗除の四則演算の関数です。

まず、サンプルとなるテーブルを用意します。

create table SampleMath
(m numeric(10,3),
n integer,
p integer);

「numeric」というデータ型は数値の大きさを表していて、numeric(全体の桁数, 少数の桁数)という書き方をします。

ここで、numericというデータ型を使っているのはのちのち説明する、ROUND関数ではnumericなどのデータ型でしか使うことができないからです。

次に、データを登録していきます。

begin transaction;
insert into SampleMath(m,n,p) values(500,0,null);
insert into SampleMath(m,n,p) values(-180,0,null);
insert into SampleMath(m,n,p) values(null,null,null);
insert into SampleMath(m,n,p) values(null,7,3);
insert into SampleMath(m,n,p) values(null,5,2);
insert into SampleMath(m,n,p) values(null,4,null);
insert into SampleMath(m,n,p) values(8,null,3);
insert into SampleMath(m,n,p) values(2.27,1,null);
insert into SampleMath(m,n,p) values(5.555,2,null);
insert into SampleMath(m,n,p) values(null,1,null);
insert into SampleMath(m,n,p) values(8.76,null,null);
commit;

確認してみます。

select * from SampleMath;
-- 確認
   m     | n | p
----------+---+---
  500.000 | 0 |
 -180.000 | 0 |
          |   |
          | 7 | 3
          | 5 | 2
          | 4 |
    8.000 |   | 3
    2.270 | 1 |
    5.555 | 2 |
          | 1 |
    8.760 |   |
(11 行)

はい。

ABS関数

それでは、まずはABS関数を使ってみます。

ABS関数は数値の絶対値を求める関数です。

-- abs_col列に絶対値を表示
select m,
  abs(m) as abs_col
from SampleMath;

実行結果は以下になります。

    m     | abs_col
----------+---------
  500.000 | 500.000
 -180.000 | 180.000
          |
          |
          |
          |
    8.000 |   8.000
    2.270 |   2.270
    5.555 |   5.555
          |
    8.760 |   8.760
(11 行)

2行目の「-180」という値が絶対値の「180」になっていることを確認してください。

MOD関数

次に、MOD関数です。

MOD関数は割り算の余りを求める関数です。

-- mol_col列に割り算の余りを表示
select n, p,
  mod(n, p) as mol_col
from SampleMath;

実行結果です。

 n | p | mod_col
---+---+---------
 0 |   |
 0 |   |
   |   |
 7 | 3 |       1
 5 | 2 |       1
 4 |   |
   | 3 |
 1 |   |
 2 |   |
 1 |   |
   |   |
(11 行)

mod(n, p)と書いた場合は、「n÷p」の余りを求めます。

ROUND関数

さてさて、次にROUND関数です。

ROUND関数は、四捨五入を行います。

日本語でも「丸める」とよく表現しますよね。

書き方は、ROUND(対象数, 丸め桁数)となります。

-- round_col列にm列の数値をn列の丸め桁数で四捨五入した値を表示
select m, n,
  round(m, n) as round_col
from SampleMath;

実行結果です。

  m     | n | round_col
----------+---+-----------
  500.000 | 0 |       500
 -180.000 | 0 |      -180
          |   |
          | 7 |
          | 5 |
          | 4 |
    8.000 |   |
    2.270 | 1 |       2.3
    5.555 | 2 |      5.56
          | 1 |
    8.760 |   |
(11 行)

はい。算術関数は以上です。

文字列関数

さて、続いて文字列関数について説明していきます。

では、まずテーブルを用意します。

create table SampleStr
  (str1 varchar(40),
   str2 varchar(40),
   str3 varchar(40));

データも用意しておきます。

begin transaction;
insert into SampleStr(str1,str2,str3) values('あいう','えお',null);
insert into SampleStr(str1,str2,str3) values('abc','def',null);
insert into SampleStr(str1,str2,str3) values('山田','太郎','です');
insert into SampleStr(str1,str2,str3) values('aaa',null,null);
insert into SampleStr(str1,str2,str3) values(null,'あああ',null);
insert into SampleStr(str1,str2,str3) values('@!#$%',null,null);
insert into SampleStr(str1,str2,str3) values('ABC',null,null);
insert into SampleStr(str1,str2,str3) values('abc',null,null);
insert into SampleStr(str1,str2,str3) values('aBC',null,null);
insert into SampleStr(str1,str2,str3) values('abc太郎','abc','ABC');
insert into SampleStr(str1,str2,str3)　values('abcdefabc','abc','ABC');
insert into SampleStr(str1,str2,str3) values('ミックマック','ッ','っ');
commit;

確認。

select * from SampleStr;
     str1     |  str2  | str3
--------------+--------+------
 あいう       | えお   |
 abc          | def    |
 山田         | 太郎   | です
 aaa          |        |
              | あああ |
 @!#$%        |        |
 ABC          |        |
 abc          |        |
 aBC          |        |
 abc太郎      | abc    | ABC
 abcdefabc    | abc    | ABC
 ミックマック | ッ     | っ
(12 行)

||関数

まずは、「||関数」について。

この関数は、文字列を連結させる関数となります。

-- str1とstr2を連結
select str1, str2,
str1 || str2 as str_concat
from SampleStr;

実行結果です。

     str1     |  str2  |   str_concat
--------------+--------+----------------
 あいう       | えお   | あいうえお
 abc          | def    | abcdef
 山田         | 太郎   | 山田太郎
 aaa          |        |
              | あああ |
 @!#$%        |        |
 ABC          |        |
 abc          |        |
 aBC          |        |
 abc太郎      | abc    | abc太郎abc
 abcdefabc    | abc    | abcdefabcabc
 ミックマック | ッ     | ミックマックッ
(12 行)

LENGTH関数

次に、LENGTH関数です。

これは、文字列の長さを求める関数です。

select str1,
length(str1) as len_str
from SampleStr;

実行結果です。

    str1     | len_str
--------------+---------
 あいう       |       3
 abc          |       3
 山田         |       2
 aaa          |       3
              |
 @!#$%        |       5
 ABC          |       3
 abc          |       3
 aBC          |       3
 abc太郎      |       5
 abcdefabc    |       9
 ミックマック |       6
(12 行)

はい。各行のstr1の文字数が表示できました。

LOWER関数

次はLOWER関数です。

LOWER関数はアルファベットを小文字に変換する関数です。

アルファベット以外だと小文字変換はできないので注意してください。

select str1,
lower(str1) as low_str
from SampleStr
where str1 in ('ABC','aBC','abc','山田');

実行結果です。

 str1 | low_str
------+---------
 abc  | abc
 山田 | 山田
 ABC  | abc
 abc  | abc
 aBC  | abc
(5 行)

はい。ちなみに、大文字化はUPPER関数を使います。

REPLACE関数

REPLACE関数は、文字列中のある一部分の文字列を、別の文字に置き換える時に使います。

書き方は、REPLACE(対象文字列, 置換前の文字列, 置換後の文字列)です。

select str1, str2, str3,
replace(str1, str2, str3) as rep_str
from SampleStr;

実行結果です。

     str1     |  str2  | str3 |   rep_str
--------------+--------+------+--------------
 あいう       | えお   |      |
 abc          | def    |      |
 山田         | 太郎   | です | 山田
 aaa          |        |      |
              | あああ |      |
 @!#$%        |        |      |
 ABC          |        |      |
 abc          |        |      |
 aBC          |        |      |
 abc太郎      | abc    | ABC  | ABC太郎
 abcdefabc    | abc    | ABC  | ABCdefABC
 ミックマック | ッ     | っ   | ミっクマっク
(12 行)

SUBSTRING関数

続いて、SUBSTRING関数です。

この関数は、文字列中のある一部分の文字列を切り出す場合に使います。切り出し開始位置は、「左から何文字目」という数え方をします。

また、書き方はSUBSTRING(対象文字列　FROM　切り出し開始位置　FOR　切り出す文字数)となります。

select str1,
SUBSTRING(str1 from 3 for 2) as sub_str
from SampleStr;

実行結果です。

     str1     | sub_str
--------------+---------
 あいう       | う
 abc          | c
 山田         |
 aaa          | a
              |
 @!#$%        | #$
 ABC          | C
 abc          | c
 aBC          | C
 abc太郎      | c太
 abcdefabc    | cd
 ミックマック | クマ
(12 行)

ふー。これで文字列関数が終わりました。疲れました。

しかし、まだいきますよ。

日付関数

この日付関数は、標準SQLで定められていて、ほとんどのDBMSで使えるものに限って説明していきます。

CURRENT_DATE関数について

この関数は、実行された日を戻り値として返します。

たとえば、この記事執筆時点では「2021-02-24」なので、この値が返されるはずです。

-- 本日の日付を表示
select current_date;

実行結果です。

 current_date
--------------
 2021-02-24
(1 行)

はい。正確に今日の日付が返されました。

CURRENT_TIME関数

続いて、CURRENT_TIME関数。

これは、この関数が実行された時間を出力します。

-- 現在の時間を表示
select current_time;

実行結果です。

   current_time
--------------------
 09:54:27.025297+09
(1 行)

はい。

朝から喫茶店でこの記事を書いてました。

CURRENT_TIMESTAMP

この関数は、CURRENT_DATE関数＋CURRENT_TIME関数だと思ってください。

つまり、現在の日にちと時間を出力します。

-- 現在の日時を表示
select current_timestamp;

実行結果です。

       current_timestamp
-------------------------------
 2021-02-24 09:58:51.719143+09
(1 行)

EXTRACT関数

さあ、日付関数の最後の関数です。

この関数は、日付のデータから一部分、たとえば「年」や「月」、「時間」、「秒」だけを切り出します。

このときの戻り値は数値型になります。

select current_timestamp,
extract(year from current_timestamp) as year,
extract(month from current_timestamp) as month,
extract(day from current_timestamp) as day,
extract(hour from current_timestamp) as hour,
extract(minute from current_timestamp) as minute,
extract(second from current_timestamp) as second;

実行結果です。

    current_timestamp       | year | month | day | hour | minute | second
------------------------------+------+-------+-----+------+--------+---------
 2021-02-24 10:06:08.12398+09 | 2021 |     2 |  24 |   10 |      6 | 8.12398
(1 行)

変換変数

最後の関数は「変換変数」です。

この関数は少し特殊な働きをする関数の一群です。

この「変換」というのは、主に「型変換」と「値変換」の意味になります。

CAST関数

型変換の関数は、CASTという関数を使います。

-- 文字型から数値型への変換
select cast('0001' as integer) as int_col;

実行結果です。

 int_col
---------
       1
(1 行)

COALESE関数

COALESCE(コアリース)関数は、NULLのデータを別の値に変換するときに使われます。

書き方はCOALESCE(データ1, データ2, データ3, ・・・)と書き、引数の左から順に見て最初にNULLでない値を返します。

select 
coalesce(NULL, 1) as col_1,
coalesce(NULL, 'test', NULL) as col_2,
coalesce(NULL, NULL, '2019-11-01') as col_3;

実行結果です。

 col_1 | col_2 |   col_3
-------+-------+------------
     1 | test  | 2019-11-01
(1 行)

これで、たとえばSampleStrテーブルのNULLのデータを「NULLです」という文字列に変換してみます。

-- NULLデータを「NULLです」に変換
select coalesce(str2, 'NULLです')
from SampleStr;

実行結果です。

 coalesce
----------
 えお
 def
 太郎
 NULLです
 あああ
 NULLです
 NULLです
 NULLです
 NULLです
 abc
 abc
 ッ
(12 行)

データにNULLが含まれると不都合な時などに使用されます。

今日はここまで。次回はSQLの述語、CASE式について解説します。

以上！

コメント

タイトルとURLをコピーしました