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

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

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

例えば、映画や音楽の再生時間を記録したテーブルがあったとしましょう。そして時間は"時分秒"の文字列で管理されています。今回ご紹介する方法を利用すれば、各タイトルの再生時間をサマリーして何時間くらい再生されたのかや、月や日で平均どのくらいの再生だったのか・・・などを把握することができます。

まぁ、データが少なければダウンロードして Excel で集計ってこともできますが、膨大なデータ量だった場合やはり SQL で取りたくなりますね。

今回はそんな方のために SQLで"時分秒"の文字列を変換して合計や平均を算出する方法 を紹介します。


環境

  • ORACLE 11g

時分秒の文字列を変換して合計値を算出

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

PLAY_HISTORY
TITLENVARCHAR2(100)
PLAY_TIMENVARCHAR2(20)
MONTHNVARCHAR2(7)

データはこんな感じ。

TITLEPLAY_TIMEMONTH
俺の名は98:07:432016/09
ハテナの傾く城120:34:532004/11
俺の名は21:45:122016/10
万と千の神頼み437:59:582001/07
万と千の神頼み132:45:442001/08
万と千の神頼み116:23:452016/09

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

以下の 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

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

TITLEHMSMONTH
俺の名は352800420432016/09
ハテナの傾く城4320002040532004/11
俺の名は756002700122016/10
万と千の神頼み15732003540582001/07
万と千の神頼み4752002700442001/08
万と千の神頼み4176001380452016/09

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

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

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

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

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

後はそれぞれを加算してサマリしましょう。下記はタイトルごとの再生時間の合計を取得する 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

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

TITLETOTAL_PLAY_TIME
万と千の神頼み2473767
ハテナの傾く城434093
俺の名は431575

ただ、このままじゃ使えないですよね。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

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

TITLETOTAL_PLAY_TIME
万と千の神頼み687時間09分27秒
ハテナの傾く城120時間34分53秒
俺の名は119時間52分55秒

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

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

TITLETOTAL_PLAY_TIME1TOTAL_PLAY_TIME2
万と千の神頼み687時間09分27秒28日と15時間09分27秒
ハテナの傾く城120時間34分53秒5日と00時間34分53秒
俺の名は119時間52分55秒4日と23時間52分55秒

時分秒の文字列を変換して平均値を算出

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

やり方は合計値の算出と似たようなものです。下記の 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

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

TITLETOTAL_PLAY_TIMEMONTH
俺の名は3532632016/09
ハテナの傾く城4340932004/11
俺の名は783122016/10
万と千の神頼み15767982001/07
万と千の神頼み4779442001/08
万と千の神頼み4190252016/09

んで、この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
)

結果はこうでした。

CNTAVG_PLAY_TIME
6556572.5

例によって 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
)
)

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

CNTAVG_PLAY_TIME1AVG_PLAY_TIME2
6154時間36分12秒6日と10時間36分12秒

まとめ

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

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

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

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

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

おつかれさまでした。

この記事がお役に立ちましたら シェア をお願いいたします。