SQL グループ関数

概要

グループ化した複数行のデータに対して単一の値を返す関数
※単一行関数=1行のデータに対して単一の値を返す
グループ化については「SQL DML:集計」参照

※以下のグループ関数でグループ化を行う表

ID NAME DATE
1 GROUP-1 99-01-01
2 GROUP-1 99-01-01
3 GROUP-1 99-01-02
4 GROUP-1 99-01-03
5 GROUP-1 99-01-04
6 GROUP-1 99-01-05
7 GROUP-2 99-02-01
8 GROUP-2 99-02-02
9 GROUP-2 99-02-03
10 GROUP-2 99-02-04
11 GROUP-2 99-02-05
12 GROUP-2 99-02-06
一覧

関数 取得 * 数値 文字 日付
COUNT データ件数
MIN 最小
MAX 最大
AVG 平均
SUM 合計
STDDEV 標準偏差
VARANCE 母集団標準偏差

COUNT

SELECT
  COUNT(ID) AS COUNT,
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
COUNT YYYY/MM
-----------
6 1999/02
6 1999/01

SUM

SELECT
  SUM(ID),
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);
SUM YYYY/MM
-----------
57 1999/02
21 1999/01

MAX/MIN

SELECT
  MAX(ID),
  MIN(ID),
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

MAX  MIN YYYY/MM
-----------
12  7 1999/02
66  1 1999/01

AVG

平均
SELECT
  AVG(ID) AS AVG,
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

AVG YYYY/MM
-----------
~ 1999/01

STDDEV

標準偏差
SELECT
  STDDEV(ID) AS STDDEV,
  TO_CHAR(MYDATE,'YYYY/MM') AS "YYYY/MM"
FROM MYTABLE
GROUP BY TO_CHAR(MYDATE,’MM’);

STDDEV YYYY/MM
-----------
~ 1999/01

全列指定(*)

全ての列で使用可能
SELECT COUNT(*)
SELECT COUNT('X')
FROM 表名
WHERE ~;

NULLの集計

データ型 NULL
MIN (列名) 数値、文字、日付 無視
MAX (列名) 数値、文字、日付 無視
COUNT (列名) 数値、文字、日付 無視 COUNT(*) :NULLが集計される
AVG (列名) 数値 無視
SUM (列名) 数値 無視

ID列:1,1,(NULL),2,2 の場合
COUNT(ALL ID) = 4
※(NULL)は集計されない
COUNT(ID) = 4
※ALLと同じ
COUNT(DISTINCT ID) = 2
COUNT(*) = 5
※(NULL)も集計する

NULLの判定/置換は「SQL 変換関数NULL値の判定・変換」参照

グループ関数が利用可能な句

・SELECT句
SELECT
 COUNT(~)
FROM ~

・HAVING句
SELECT ~ FROM ~ GROUP BY ~
HAVING COUNT(~) = ~

・ORDER BY句
SELECT ~ FROM ~
ORDER BY COUNT(~)

SQL 変換関数

型変換

TO_CHAR( 値, 書式)
TO_NUMBER( 値 )
TO_DATE( 値, 書式 )

CONVERT(INTEGER , 値)
CONVERT(CHAR , 値)
CONVERT(VARCHAR , 値)
CONVERT(DATE, 値)

文字列系

カンマ区切り(通貨)

L:通貨記号
0:0埋めする/9:0埋めしない

SELECT TO_CHAR('1000', 'L9,999,999') FROM MYTABLE;
— ¥1,000
SELECT TO_CHAR('1000', '$9,999,999') FROM MYTABLE;
— $1,000
SELECT TO_CHAR('1000', '9,999,999') FROM MYTABLE;
— 1,000
SELECT TO_CHAR('1000', 'L0,000,000') FROM MYTABLE;
— ¥0,001,000
SELECT TO_CHAR('1000', '$0,000,000') FROM MYTABLE;
— $0,001,000
SELECT TO_CHAR('1000', '0,000,000') FROM MYTABLE;
— 0,001,000
SELECT TO_CHAR('1000', '0G000G000') FROM MYTABLE;
— 0,001,000

通貨型⇒文字列型(カンマ区切り)
10000→10,000

'通貨型の場合
DECLARE @TestValue MONEY
SET @TestValue = 10000

CONVERT(VARCHAR, @TestValue, 1 )
⇒結果:10,000

CONVERT(VARCHAR, '10000', 1)
⇒結果:10000 '文字列型の直接変換は不可

'文字列型の場合は通貨型に変換してから
CONVERT(VARCHAR, CONVERT(MONEY, '10000'), 1)
⇒結果:10,000

小数点表示

SELECT TO_CHAR(0.25, '0,000.000') FROM MYTABLE;
→ 0,000.250 ※整数部:0埋め 少数部:0埋め

SELECT TO_CHAR(0.25, '9,999.999') FROM MYTABLE;
→  .250 ※整数部:スペース埋め 少数部:0埋め

SELECT TO_CHAR(10.25, '0,000.000') FROM MYTABLE;
→ 0,010.250 ※整数部:0埋め 少数部:0埋め

SELECT TO_CHAR(10.25, '9,999.999') FROM MYTABLE;
→  10.250 ※整数部:スペース埋め 少数部:0埋め

SELECT TO_CHAR(10.25, '9G999D999') FROM MYTABLE;
→  10.250

色々な日付表示

TO_CHARは日付型データを指定した書式の文字列に変換する。

UPDATE MYTABLE SET DATE1 = '2015-06-18';
SELECT * FROM MYTABLE;

SELECT TO_CHAR(DATE1,'RR-MM-DD') FROM MYTABLE;
→'15-06-18'

SELECT TO_CHAR(DATE1,'YY-MM-DD') FROM MYTABLE;
→'15-06-18'

SELECT TO_CHAR(DATE1,'YY-MON-DD') FROM MYTABLE;
→'15-6月 -18'

SELECT TO_CHAR(DATE1,'RR-MM-DD(D)') FROM MYTABLE;
→'15-06-18(7)'
日:1 月:2 火:3 水:4 木:5 金:6 土:7

SELECT ~ ORDER BY TO_CHAR(DATE1,'D')
曜日順(日曜先頭)
SELECT ~ ORDER BY TO_CHAR(DATE1 – 1,'D')
曜日順(曜先頭)

SELECT TO_CHAR(DATE1,'RR-MM-DD(DD)') FROM MYTABLE;
→'15-06-18(土)'

SELECT TO_CHAR(DATE1,'RR-MM-DD(DAY)') FROM MYTABLE;
→'15-06-18(土曜日)'

SELECT TO_CHAR(DATE1,'YY-MON-DD', 'NLS_DATE_LANGUAGE=ENGLISH') FROM MYTABLE;
→'15-JUN-18'

SELECT TO_CHAR(DATE1,'YY-MON-DD', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月 -18'

SELECT TO_CHAR(DATE1,'RR-MM-DD HH24:MI:SS') FROM MYTABLE;
→'15-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'YY-MM-DD HH24:MI:SS') FROM MYTABLE;
→'15-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'RR-MM-DD HH12:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'YY-MM-DD HH12:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'RR-MM-DD HH:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'YY-MM-DD HH:MI:SS') FROM MYTABLE;
→'15-06-18 12:00:00'

SELECT TO_CHAR(DATE1,'RRRR-MM-DD HH24:MI:SS') FROM MYTABLE;
→'2015-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'YYYY-MM-DD HH24:MI:SS') FROM MYTABLE;
→'2015-06-18 00:00:00'

SELECT TO_CHAR(DATE1,'AM HH24:MI:SS') FROM MYTABLE;
SELECT TO_CHAR(DATE1,'PM HH24:MI:SS') FROM MYTABLE;
→'午前 00:00:00'
※AM/PMを指定すると常に正しい午前/午後が表示される'

SELECT TO_CHAR(DATE1,'YY-MON-DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月 -18 09:09:09'
SELECT TO_CHAR(DATE1,'fmYY-MON-DD HH24:MI:SS', 'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月-18 9:9:9' ※スペース、0削除
SELECT TO_CHAR(DATE1,'fmYY-MON-DD HH24:fmMI:SS',
'NLS_DATE_LANGUAGE=JAPANESE') FROM MYTABLE;
→'15-6月-18 9:09:09' ※複数回のfmで更に切替

SELECT TO_CHAR(SYSDATE,'YY-MM-DDTH') FROM MYTABLE;
→'15-06-08TH' ※日付の文字表記

SELECT TO_CHAR(SYSDATE,'YY-MM-DDSP') FROM MYTABLE;
→'15-06-EIGHTEEN' ※日付の文字表記

SELECT TO_CHAR(SYSDATE,'YY-MM-DDSPTH') FROM MYTABLE;
→'15-06-EIGHTEENTH' ※日付の文字表記

ミリ秒の変換
SELECT TO_CHAR(SYSTIMESTAMP,'YY-MM-DD HH24:MI:SS.FF3') FROM MYTABLE;

WHERE句で日付型を指定する時の注意点
DATE1:'15-06-18 10:30:00'の場合、
SELECT * FROM MYTABLE WHERE DATE1 = '15-06-18'
では抽出できない。

SELECT * FROM MYTABLE WHERE TRUNC(DATE1) = '15-06-18'
※時間部分の切り捨て(='15-06-18 00:00:00')
SELECT * FROM MYTABLE WHERE TO_CHAR(DATE1,'YY-MM-DD') = '15-06-18'
※'YY-MM-DD'書式で文字列化(='15-06-18')

SELECT TO_CHAR(9999.99, '9G99B9D999') FROM MYTABLE;
→  9,99 9.990

数値系

SELECT TO_NUMBER('3') FROM MYTABLE;
→3

数値判定

※非数値=0/数値=1

DECLARE @TestValue VARCHAR(2)
SET @TestValue = 'A'

CASE ISNUMERIC( @TestValue )
WHEN 0 THEN '0'
WHEN 1 THEN @TestValue
END AS STAN
※数値以外の時は0に変換

日付系

SELECT TO_DATE('日付を表す文字列', 日付書式) FROM MYTABLE;
SELECT TO_DATE('2015/01/01 10:10:10', 'YYYY/MM/DD HH:MI:SS') FROM MYTABLE;
SELECT TO_DATE('15/01/01 10:10:10', 'YY/MM/DD HH:MI:SS') FROM MYTABLE;
SELECT TO_DATE('150101 101010', 'YYMMDD HHMISS') FROM MYTABLE;

【変換時:書式を省略した場合】
SELECT TO_DATE("02:00:00","HH:MI:SS") FROM MYTABLE;
SELECT TO_CHAR(TO_DATE("02:00:00","HH:MI:SS"),"YYYY/MM/DD HH24:MI:SS") FROM MYTABLE;
– – 2015/07/01 02:00:00 ※年月日を省略した場合、年月はシステム日付が適用される。日は強制01日。

SELECT TO_DATE("000101","RRMMDD") FROM MYTABLE;
SELECT TO_CHAR(TO_DATE("000101","RRMMDD"),"YYYY/MM/DD HH24:MI:SS") FROM MYTABLE;
– – 2000/01/01 00:00:00 時刻を省略した場合、00:00:00が適用される。

【TIMESTAMP型】
TO_TIMESTAMP('2016-04-01 00:00:00' , 'YYYY-MM-DD HH24:MI:SS')

DECLARE @dteFuriDay DATE;
SET @dteFuriDay = CONVERT(DATE,'20130101')
@dteFuriDay:2013-01-01

DECLARE @chrFuriDay VARCHAR(8);
SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,111)
@chrFuriDay:2013/01/01

SET @chrFuriDay = CONVERT(VARCHAR,@dteFuriDay,112)
@chrFuriDay:20130101

NULL

NULL値の判定・変換

SELECT NVL((NULL),0) FROM MYTABLE;
→0

NVL2(~1, ~2, ~3)
~1がNULLでない時⇒~2を、NULLの時⇒~3を返す。

SELECT NVL2('NULL',1, 0) FROM MYTABLE;
→1
SELECT NVL2((NULL),1, 0) FROM MYTABLE;
→0

戻り値:第二引数
第二引数の型 != 第三引数の型 の場合 → 第二引数の型へ暗黙変換

NULLIF(~1, ~2)
~1 = ~2の時⇒NULLを、~1 ≠ ~2の時⇒~1を返す

COALESCE(~1, ~2, ~3)
リスト内からNULLで無い値を返す(コウアレス)
リスト:NULL, NULL, 5の時⇒5
リスト:NULL, NULL, NULLの時⇒NULL
リスト内は全て同型の必要あり

DECODE関数を用いたNULL変換も可能

NULLの変換
TO_CHAR( (NULL) )
→(NULL)
TO_NUMBER( (NULL) )
→(null)
TO_DATE( (NULL) )
→(null)

NULLの集計は「SQL グループ関数/NULLの集計」参照

SELECT ISNULL( 列名, 0 ) FROM ~
Nullの場合、0を取得

SELECT ISNULL( 列名, '' ) FROM ~
Nullの場合、''を取得

SQL システム関数

存在チェック


IF OBJECT_ID( 'dbo.TestTable', 'U') IS NOT NULL
  DROP TABLE TestTable;
CREATE TABLE TestTable
(
  ~
);

更新された行数を取得

IF @@ROWCOUNT = 0
PRINT 'エラーメッセージ';

エラー時の値

BEGIN TRY
  ~
END TRY

BEGIN CATCH
  SELECT
    エラー内容を表す番号
    ERROR_NUMBER() AS ErrorNumber,
    
    エラーが発生したストアド名/トリガー名等
    ERROR_PROCEDURE() AS ErrorProcedure,
    
    エラーが発生した行
    ERROR_LINE() AS ErrorLine,
    
    エラーメッセージ
    ERROR_MESSAGE() AS ErrorMessage;
    
END CATCH;

SQL 単一行関数:数値関数

一覧

関数 取得 第二引数 結果
ROUND 四捨五入 小数点以下の桁数 ROUND(5.55 , 1) 5.6
TRUNC 切り捨て 小数点以下の桁数 TRUNC(5.55 , 1) 5.5
MOD 余り MOD(10, 3) 1
GREATEST 最大 GREATEST(1, 3, 5) 5
LEAST 最小 LEAST(1, 3, 5) 1

四捨五入

ROUND(対象、実行桁数)
※実行桁数 = 四捨五入後の小数点以下の表示数
⇒実行桁数の更に下の桁の四捨五入を行う
ROUND(555.555, 1) ⇒555.6
ROUND(555.555, 2) ⇒555.56
ROUND(555.554, 2) ⇒555.55

ROUND(対象)
※ROUND(~,0)と同じ
ROUND(555) ⇒555
ROUND(555.55) ⇒556
ROUND(555.40) ⇒555

日時の四捨五入
※例:ROUND('15-06-22 17:30:30', '~')

四捨五入ルール
時間:~11時→当日/12時~→翌日
日:~15日→当月/16日~→翌月
月:~6月→当年/7月~→翌年

ROUND(日時) ※引数省略時
=ROUND(日時, 'DD')
ROUND(日時, 'YYYY')
→15-01-01 00:00:00
ROUND(日時, 'MM')
→15-07-01 00:00:00
ROUND(日時, 'DD')
→15-06-23 00:00:00
ROUND(日時, 'HH')
→15-06-22 17:00:00

ROUND(対象、実行桁数)
※実行桁数 = 四捨五入後の小数点以下の表示数

ROUND(145.50 , 0 ) ⇒ 146.00(145.50)
ROUND(145.45 , 0) ⇒ 145.00(145.45)
ROUND(-145.50 , 0) ⇒ -146.00(-145.50)
ROUND(-145.45 , 0) ⇒ -145.00(-145.45)

ROUND(145.45 , 1) ⇒ 145.50(145.45)
ROUND(145.45 , 2) ⇒ 145.45(145.45[])
ROUND(145.45 , -1) ⇒ 150.00(145.45)
ROUND(145.45 , -2) ⇒ 100.00(145.45)

切り捨て

※Truncate(切り取り)の略

TRUNC(対象、実行桁数)
※実行桁数 = 切り捨て後の小数点以下の表示数
⇒実行桁数の更に下の桁の切り捨てを行う
TRUNC(555.555, 1) ⇒555.5
TRUNC(555.555, 2) ⇒555.55
TRUNC(555.559, 2) ⇒555.55

TRUNC(対象)
※TRUNC(~,0)と同じ
TRUNC(555) ⇒555
TRUNC(555.55) ⇒555
TRUNC(555.99) ⇒555

日時の切り捨て
※例:TRUNC('15-06-22 17:30:30', '~')

TRUNC(日時) ※引数省略時
=TRUNC(日時, 'DD')
TRUNC(日時, 'YYYY')
→15-01-01 00:00:00
TRUNC(日時, 'MM')
→15-06-01 00:00:00
TRUNC(日時, 'DD')
→15-06-22 00:00:00
TRUNC(日時, 'HH')
→15-06-22 17:00:00
TRUNC(日時, 'MI')
→15-06-22 17:30:00
TRUNC(日時, 'SS')
→エラー

余り

MOD(10, 3)
⇒ 1

SQL 単一行関数:日付関数

加減

年の加算
ADD_MONTHS(日付型列名 , 1 * 12)
ADD_MONTHS( TO_DATE(列名,'YYYY/MM/DD'),1 * 12)

月の加算
ADD_MONTHS(日付型列名,1)
ADD_MONTHS( TO_DATE(列名,'YYYY/MM/DD') ,1)

日の加減算
日付型列名 + 1
日付型列名 – 1
TO_DATE('2005/03/29','YYYY/MM/DD') + 1
TO_DATE('2005/03/29','YYYY/MM/DD') – 1

時間の加減算
日付型列名 + 1/24
日付型列名 – 1/24
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24

分の加減算
日付型列名 + 1/24/60
日付型列名 – 1/24/60
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24/60
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24/60

秒の加減算
日付型列名 + 1/24/60/60
日付型列名 – 1/24/60/60
TO_DATE('2005/03/29','YYYY/MM/DD') + 1/24/60/60
TO_DATE('2005/03/29','YYYY/MM/DD') – 1/24/60/60

DECLARE @dteYMD DATETIME

'3年減算
SET @dteYMD= DATEADD(yy, -3, @dteNIPYMD)

取得

月末
LAST_DAY(日付型列名)
LAST_DAY(TO_DATE('2005/03/29','YYYY/MM/DD'))

次の指定曜日の日
NEXT_DAY(日付型列名, 曜日)
NEXT_DAY('2005/03/29' ,1)
NEXT_DAY('2005/03/29' ,'MONDAY')

年/月/日/時/分/秒
EXTRACT(FORMAT FROM 日付)

EXTRACT(YEAR FROM 日付)
EXTRACT(MONTH FROM 日付)
EXTRACT(DAY FROM 日付)
EXTRACT(HOUR FROM 日付)
EXTRACT(MINUTE FROM 日付)
EXTRACT(SECOND FROM 日付)

年の取得
DECLARE @dteYMD DATETIME
DECLARE @dteYEAR DATETIME

SET @dteYEAR= YEAR(@dteYMD)

月の取得
DECLARE @dteYMD DATETIME
DECLARE @dteMONTH DATETIME

SET @dteMONTH= MONTH(@dteYMD)

日の取得
DECLARE @dteYMD DATETIME
DECLARE @dteDAY DATETIME

SET @dteDAY= DAY(@dteYMD)

DATEPART関数
DATEPART( yyyy, TestDate )
⇒ 2000
DATEPART( d, TestDate )
⇒ 1
DATEPART( m, TestDate )
⇒ 4
DATEPART( w, TestDate )
⇒ 1~7(日、月…金、土を表す数字)

時間差

時間差(日)
'2000-08-25' '2000-08-24'
→1
'2000-08-25 12:00:00' '2000-08-25 10:00:00'
→0.083333(2時間差=2/24)
'2000-08-25' + '2000-08-24'
エラー

時間差(月)
MONTHS_BETWEEN(日付型列名, 日付型列名)

MONTHS_BETWEEN('2000-08-25', '2015-06-25')
→2
MONTHS_BETWEEN('2000-08-25', '2015-05-10')
→3.48387097
※1ヶ月未満の差がある場合は31日の日割り計算される。
3ヶ月15日=3+15/31=0.48387097

MONTHS_BETWEEN('2000-04-31', '2015-02-28')
→2
※引数が両方月末の場合は月数のみの差が算出される

時間差(年)
TRUNC(MONTHS_BETWEEN(日付型列名, 日付型列名) /12)
※TRUNC:切り捨て

年齢
TRUNC(MONTHS_BETWEEN(SYSDATE, 誕生日) /12)
TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE('2005/03/29','YYYY/MM/DD')) /12)
※SYSDATE:システム日付