集計してみるSQLで時間の集計を行う2017年10月24日ちょっと調べものしてしまったので自分用メモ。処理対象データPostgreSQLに以下のデータがある。kamonohashi=>これを集計して以下の結果を得たい。99:99項目timeはHH:MMの形式で時間が記録される。MMは0~59、HHは0~99の範囲。形式が正しくないものもある。集計結果が99:59を超えたら99:99の表記とする。演算子で正規表現が使える模様。OracleだとREGEXP_LIKE()関数で正規表現が扱える。以下エラー行は('E'の付く行は)集計対象にしない。kamonohashi=>HH:MMから分単位へ変換エラーが無いHH:MM形式の文字列で表現された時間を分単位に換算する。エラー行の時間はゼロ分にする。HH部分は“時”なので60を乗じて“分”に換算、MM部分はそのまま。両者を足し合わせれば分単位の換算が完了。kamonohashi=>上記を項目grpで集計すれば以下となる。再度HH:MM形式に戻す。余りとなるので、この値を整形して文字列に落とせばいい。5,999でHH:MM形式で表現できる最大の値である5,999分の意。Serverでは整数型を除算した結果は整数型になる。Oracleは型が変換されて実数型になるかもしれない。小数点以下を切り捨てするTRUNC()関数、剰余を求めるMOD()関数、を使う必要がある。INTEGER型で扱っている。集計結果がINTEGER型の最大値を超えるとエラーになるので注意が必要。2,147,483,647分を超える際にエラーとなるだろう。扱う時間の性格性質によっては、ここで上げた処理方法は使えない。LocalDBを使って確認する。下準備インスタンスを作ってデータベースを作ってテーブル作ってデータをインサートします。