SQL 時分秒を変換し合計や平均を算出する方法

• ORACLE 11g

まず、合計値の出し方ですが、以下のようなテーブルがあったとしましょう。

データはこんな感じ。

再生時間はタイトルの月毎に"時:分:秒"で管理されています。合計を取るには一旦"秒"単位に変換します。そのためには、一度文字列を分解します。

以下の SQL を実行してみましょう。

SELECT
TITLE
,SUBSTRB(PLAY_TIME,1,INSTR(PLAY_TIME,':',1,1)-1)*3600 H
,SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,1)+1,2)*60 M
,SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,2)+1,2) S
,MONTH
FROM PLAY_HISTORY

結果は下表のようになります。単位が"秒"になりました。

はい、関数がゴチャゴチャしてわかりにくいですよね。簡単に説明します。

まず INSTR 関数を使って、文字列 ":"(コロン) を探します。その後 SUBSTRB 関数を使って必要な文字数を切り出します。

"時"部分は、２桁を超えるので、最初の ":"(コロン) が見つかるまでを範囲とし、3600をかけて秒単位に変換しています。

"分"は2桁なので、2つ目の ":"(コロン) から先２桁を取得し、60をかけて秒単位にしています。

"秒"も同じく２桁なので、３つ目の ":"(コロン) から先２桁を取得します。

後はそれぞれを加算してサマリしましょう。下記はタイトルごとの再生時間の合計を取得する SQL です。

SELECT
TITLE
,SUM((SUBSTRB(PLAY_TIME,1,INSTR(PLAY_TIME,':',1,1)-1)*3600) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,1)+1,2)*60) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,2)+1,2)))
  TOTAL_PLAY_TIME
FROM PLAY_HISTORY
GROUP BY TITLE

結果はこんな感じです。ふむ、タイトルごとに分単位でサマリされました。

ただ、このままじゃ使えないですよね。2473767秒って何時間？って感じで＾＾

なので、更に変換して"時分秒"の書式にします。ついでに再生時間の多い順に並び替えて、人気のタイトルを探しましょう。

SELECT 
TITLE
,TRUNC(TOTAL_PLAY_TIME/3600)||'時間'||
 TO_CHAR(MOD(TRUNC(TOTAL_PLAY_TIME/60),60),'FM00')||'分'||
 TO_CHAR(MOD(TOTAL_PLAY_TIME,60),'FM00') ||'秒'
 TOTAL_PLAY_TIME
FROM (
SELECT
TITLE
,SUM((SUBSTRB(PLAY_TIME,1,INSTR(PLAY_TIME,':',1,1)-1)*3600) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,1)+1,2)*60) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,2)+1,2)))
  TOTAL_PLAY_TIME
FROM PLAY_HISTORY
GROUP BY TITLE
)
ORDER BY TOTAL_PLAY_TIME DESC

結果はこんな感じです。ふむ、タイトルごとに時分単位で表示され、人気タイトルがわかりましたね。

TRUNC や MOD を使って、ゴニョゴニョやっている感じです＾＾

TRUNCの詳しい使い方はこちらをどうぞ。

・・と、687時間って何日なんだよ・・・って声が聞こえてきそうなので、"日時分秒"の変換もやっておきましょう。

SELECT 
TITLE
,TRUNC(TOTAL_PLAY_TIME/3600)||'時間'||
 TO_CHAR(MOD(TRUNC(TOTAL_PLAY_TIME/60),60),'FM00')||'分'||
 TO_CHAR(MOD(TOTAL_PLAY_TIME,60),'FM00') ||'秒'
 TOTAL_PLAY_TIME1
,TRUNC(TOTAL_PLAY_TIME/3600/24)||'日と'||
 TO_CHAR(MOD(TRUNC(TOTAL_PLAY_TIME/3600),24),'FM00' )||'時間'||
 TO_CHAR(MOD(TRUNC(TOTAL_PLAY_TIME/60),60),'FM00')||'分'||
 TO_CHAR(MOD(TOTAL_PLAY_TIME,60),'FM00') ||'秒'
 TOTAL_PLAY_TIME2
FROM (
SELECT
TITLE
,SUM((SUBSTRB(PLAY_TIME,1,INSTR(PLAY_TIME,':',1,1)-1)*3600) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,1)+1,2)*60) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,2)+1,2)))
  TOTAL_PLAY_TIME
FROM PLAY_HISTORY
GROUP BY TITLE
)
ORDER BY TOTAL_PLAY_TIME DESC

はい、結果はこうなりましたよー。

さて、今度は平均値を求めてみましょう。データは同じものを使います。ここでは、件数に対する平均再生時間を算出します。

やり方は合計値の算出と似たようなものです。下記の SQL で各タイトルの再生時間を"秒"単位にします。

SELECT
TITLE
,(SUBSTRB(PLAY_TIME,1,INSTR(PLAY_TIME,':',1,1)-1)*3600) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,1)+1,2)*60) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,2)+1,2))
  TOTAL_PLAY_TIME
,MONTH
FROM PLAY_HISTORY

結果はこうなります。再生時間の"秒"変換ができましたね。

んで、この6件の再生時間の平均値を求めます。SQL はこうです。

SELECT
 COUNT(1) CNT
,AVG(TOTAL_PLAY_TIME) AVG_PLAY_TIME
FROM (
SELECT
TITLE
,(SUBSTRB(PLAY_TIME,1,INSTR(PLAY_TIME,':',1,1)-1)*3600) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,1)+1,2)*60) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,2)+1,2))
  TOTAL_PLAY_TIME
,MONTH
FROM PLAY_HISTORY
)

結果はこうでした。

例によって 556572.5 秒って何時間ですか？って言われないために、さっきの要領で変換します。

SELECT
 CNT
,TRUNC(AVG_PLAY_TIME/3600)||'時間'||
 TO_CHAR(MOD(TRUNC(AVG_PLAY_TIME/60),60),'FM00')||'分'||
 TO_CHAR(MOD(TRUNC(AVG_PLAY_TIME),60),'FM00') ||'秒'
 AVG_PLAY_TIME1
,TRUNC(AVG_PLAY_TIME/3600/24)||'日と'||
 TO_CHAR(MOD(TRUNC(AVG_PLAY_TIME/3600),24),'FM00' )||'時間'||
 TO_CHAR(MOD(TRUNC(AVG_PLAY_TIME/60),60),'FM00')||'分'||
 TO_CHAR(MOD(TRUNC(AVG_PLAY_TIME),60),'FM00') ||'秒'
 AVG_PLAY_TIME2
FROM (
SELECT
 COUNT(1) CNT
,AVG(TOTAL_PLAY_TIME) AVG_PLAY_TIME
FROM (
SELECT
TITLE
,(SUBSTRB(PLAY_TIME,1,INSTR(PLAY_TIME,':',1,1)-1)*3600) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,1)+1,2)*60) +
 (SUBSTRB(PLAY_TIME,INSTR(PLAY_TIME,':',1,2)+1,2))
  TOTAL_PLAY_TIME
,MONTH
FROM PLAY_HISTORY
)
)

結果はこうです。ふむ、わかりやすい＾＾

SQLで"時分秒"の文字列を変換して合計や平均を算出する方法を紹介しました。

時間の表現方法は、どのプログラム言語でも使われてるのではないでしょうか。状況に合わせたプログラム言語を利用するのが一番ですが、慣れ親しんだ言語を使ってやるのも悪くないと思います。もちろん Excel で集計ってのもいいと思います。ご自身の環境に合わせて、最適なソリューションを見つけてください。

今回は再生時間というテーマでしたが、勤務時間や作業工数の管理など、企業では様々なシーンで時間管理がおこなわれています。「時は金なり」なんて言葉もあります。現代社会では、企業による「時間の見える化」が重要な要素の一つではないかと思っています。

あ、たくさん SQL 書きましたけど、ちょっとが長いなーとも思ったので、使うときはうまいことアレしてみてください。

サンプルは SQLで時分秒を変換し合計や平均を算出するサンプル からダウンロードできます。