データベースとSQL（13/20） - 「大人の教養・知識・気付き」を伸ばすブログ

はじめに

　データ分析を行う場合、データの管理運用、蓄積も重要な論点となる。そのためにはSQLやデータベースの知識が求められる。そこでまずは

SQL 第2版ゼロからはじめるデータベース操作

作者:ミック
翔泳社

Amazon

を基にSQLの勉強をしていく。

前回

power-of-awareness.com

はじめに
前回
10.　いろいろな関数
次回

10.　いろいろな関数

SQLに内蔵された関数には大きく分けて、①算術関数、②文字列関数、③日付関数、④変換関数、⑤集約関数がある。

関数は多いため、必要に応じて調べる方が良い。

10.1　関数の種類

　関数とは「ある値を“入力”するとそれに対応した値を“出力”する機能」である。このとき入力を引数、出力を戻り値という。

	(1)算術関数	数値の計算を行うための関数
	(2)文字列関数	文字列を操作するための関数
	(3)日付関数	日付を操作するための関数
	(4)変換関数	データ型や値を変換するための関数
	(5)集約関数	データの集計を行うための関数

10.2　主な算術関数

	関数	内容
	+	加算
	-	減算
	$\mathrm{*}$	乗算
	/	除算
	ABS(数値)	絶対値を返す
	MOD(被除数,除数)/%	剰余(被除数を除数で割った際の余り(SQL Serverは%))

1 + 2 -- 3
2 - 1 -- 1
3 * 4 -- 12
4 / 3 --1.3333333
ABS(-3) --3

MOD(4, 3) -- 1 
4 % 3 -- 1: SQL Serverの場合の剰余

10.3　主な文字列関数

　 $\mathrm{LENGTH}$ 関数や $\mathrm{SUBSTRING}$ 関数をはじめとする長さを数える関数は、 $\mathrm{DBMS}$ によってはバイト数を数えるので注意が必要である。文字列の連結は、MySQLでは「CONCAT(文字列1,文字列2)」、それ以外では「文字列1||文字列2」を用いる。ただしSQL Server 2012以降のSQL ServerではCONCATも利用可能である。

	関数	内容
	文字列1+文字列2	連結(SQL Serverの場合)
	LENGTH(文字列)	文字列の長さ
	LEN(文字列)	文字列の長さ(SQL Serverの場合)
	UPPER(文字列)	文字列を大文字にする*1。
	LOWER(文字列)	文字列を小文字にする*2。
	REPLACE(対象文字列,置換前文字列,置換後文字列)	対象文字列にある置換前文字列を置換後文字列へ置換する。
	SUBSTRING(対象文字列 FROM 切り出し開始位置 TO 切り出す文字数)	対象文字列の切り出し開始位置から切り出す文字数分の文字列を切り出す(PostgreSQL, MySQLの場合)。
	SUBSTRING(対象文字列,切り出し開始位置, 切り出す文字数)	対象文字列の切り出し開始位置から切り出す文字数分の文字列を切り出す(SQL Serverの場合)。
	SUBSTR(対象文字列,切り出し開始位置, 切り出す文字数)	対象文字列の切り出し開始位置から切り出す文字数分の文字列を切り出す(Oracle, DB2の場合)。

'abc' + 'def' -- abcdef
'abc'||'def' -- abcdef
LENGTH('abc') -- 3
LEN('abc') --3
UPPER('abcです') --ABCです
LOWER('ABCです') --abcです
REPLACE('DBMS','D','B') -- BBMS
SUBSTRING('abc' FROM 3 TO 2) -- c
SUBSTRING('abcde' FROM 3 TO 2) -- cd
SUBSTRING('abcdef',3, 2) -- cd, SQL Serverの場合

10.4　日付関数

　 $\mathrm{DBMS}$ 次第で実装が異なるため、都度調べて欲しい。なおSQL Serverは現在の日付および時間を単独で取得する関数は無いため、CURRENT_TIMESTAMPをCASTする。
　以下、「時点」という用語は日付+時間で表されるものを表すための便宜的な用語である。

	関数	内容
	CURRENT_DATE	現在の日付を取得する*3。
	CURRENT_TIMESTAMP	現在の日付および時間を取得する(SQL Server,PostgreSQL,MySQLの場合)*4。
	CURRENT_TIMESTAMP	現在の時間を取得する(Oracleの場合)。
	CURRENT_TIME	現在の時間を取得する(PostgreSQL, MySQLの場合)。
	CURRENT TIME	現在の時間を取得する(DB2の場合)。
	EXTRACT(日付要素 FROM 日付)	DATE型から日付要素(YEAR, MONTHなど)のみを切り出す*5。
	DATEPART(日付要素, 時点)	時点から日付要素(YEAR, MONTHなど)のみを切り出す(SQL Serverの場合)。

-- 現在の日付を取得する
SELECT CURRENT_DATE

-- 現在の日付を取得する(SQL Serverの場合)
SELECT CAST(CURRENT_DATE AS DATE)

-- 現在の日付を取得する(Oracleの場合)
SELECT CURRENT_DATE
FROM dual

-- 現在の日付を取得する(DB2の場合)
SELECT CURRENT_DATE
FROM SYSIBM.SYSDUMMY1

-- 現在の時間を取得する(PostgreSQL, MySQLの場合)
SELECT CURRENT_TIME

-- 現在の時間を取得する(Oracleの場合)
SELECT CURRENT_TIMESTAMP
FROM dual

-- 現在の時間を取得する(DB2の場合)
SELECT CURRENT TIME
FROM SYSIBM.SYSDUMMY1

-- 現在の時間を取得する(SQL Serverの場合)
SELECT CAST(CURRENT_TIMESTAMP AS TIME)

-- DATE型の値から特定の日付要素のみを切り出す(PostgreSQL,MySQLの場合。SQL Serverでは使えない)
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

-- DATE型の値から特定の日付要素のみを切り出す(Oracleの場合)
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
FROM	dual

-- DATE型の値から特定の日付要素のみを切り出す(DB2の場合)
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
FROM	SYSIBM.SYSDUMMY1

10.5　変換関数

	関数	内容
	CAST(変換前の値 AS 変換するデータ型)	指定したデータ型に変換
	COALESCE(データ1,データ2,...)	可変個*6の引数を左から順にみて最初に $\mathrm{NULL}$ でない値を返す。

-- CAST(SQL Server, PostgreSQLの場合)
SELECT CAST('0001' AS INTEGER)

-- CAST(MySQLの場合)
SELECT CAST('0001' AS SIGNED INTEGER)

-- CAST(Oracleの場合)
SELECT CAST('0001' AS INTEGER)
FROM DUAL

-- CAST(DB2の場合)
SELECT CAST('0001' AS INTEGER)
FROM SYSIBM.SYSDUMMY1

-- COALESCE(SQL Server, PostgreSQL, MySQLの場合)
SELECT COALESCE(NULL, 1),
	COALESCE(NULL, 'test', NULL),
	COALESCE(NULL, NULL, '2009-11-01')

-- CAST(Oracleの場合)
SELECT COALESCE(NULL, 1),
	COALESCE(NULL, 'test', NULL),
	COALESCE(NULL, NULL, '2009-11-01')
FROM DUAL

-- CAST(DB2の場合)
SELECT COALESCE(NULL, 1),
	COALESCE(NULL, 'test', NULL),
	COALESCE(NULL, NULL, '2009-11-01')
FROM SYSIBM.SYSDUMMY1

次回

power-of-awareness.com

*1:アルファベット以外には何もしない。またはじめから大文字の場合も何もしない。

*2:アルファベット以外には何もしない。またはじめから小文字の場合も何もしない。

*3:Oracle, DB2ではそれぞれダミーテーブルを指定する必要がある。凡例を参照。

*4:SQL Serverで現在の日付を得たい場合、これをDATE型にCASTする。もし現在の時間を得たい場合、これをTIME型にCASTする。

*5:SQL Serverではこの関数は使えない。

*6:引数の数が指定されておらず、記述する数を自由に変更できる引数。

はじめに

前回

10. いろいろな関数

10.1 関数の種類

10.2 主な算術関数

10.3 主な文字列関数

10.4 日付関数

10.5 変換関数

次回