努力したWiki

推敲の足りないメモ書き多数

ユーザ用ツール

サイト用ツール

トレース:
documents:proglang:sql:sql-0009

差分

このページの2つのバージョン間の差分を表示します。

この比較画面へのリンク

両方とも前のリビジョン前のリビジョン
documents:proglang:sql:sql-0009 [2026/05/08 12:19] – ↷ documents:database:sql-0009 から documents:proglang:sql:sql-0009 へページを移動しました。 k896951documents:proglang:sql:sql-0009 [2026/05/16 11:36] (現在) – [SQLで時間の集計を行う] k896951
行 1: 行 1:
 +====== 004.SQLで時間の集計を行う ======
 +
 +2017年10月24日\\
 +ちょっと調べものしてしまったので自分用メモ。
 +
 +
 +===== 処理対象データ =====
 +
 +PostgreSQLに以下のデータがある。
 +
 +<code sql>
 +$ psql -h dbsvr05 kamonohashi kamo
 +psql (9.5.9, サーバー 9.6.1)
 +WARNING: psql major version 9.5, server major version 9.6.
 +         Some psql features might not work.
 +"help" でヘルプを表示します.
 +
 +kamonohashi=> \d timelog
 +       テーブル "public.timelog"
 +  列  |          型          |  修飾語
 +------+----------------------+----------
 + id   | integer              | not null
 + grp  | character varying(3) |
 + time | character varying(5) |
 +インデックス:
 +    "timelog_pkey" PRIMARY KEY, btree (id)
 +
 +kamonohashi=> select * from timelog order by grp,id;
 + id | grp | time
 +----+-----+-------
 +  1 | ca1 | 08:00
 +  2 | ca1 | 07:30
 +  5 | ca1 | 07:15
 +  3 | ca2 | 07:55
 + 10 | ca2 | 07:50
 + 11 | ca2 | 08:10
 +  4 | ca3 |
 +  8 | ca3 | 20:70
 + 21 | ca3 | 27:50
 + 23 | ca3 | aa:bb
 + 15 | ca4 | 48:30
 + 22 | ca4 | 67:50
 +(12 行)
 +
 +kamonohashi=>
 +</code>
 +
 +
 +これを集計して以下の結果を得たい。
 +
 +^ grp ^ time ^
 +| ca1  | 22:45 |
 +| ca2  | 23:55 |
 +| ca3  | 27:50 |
 +| ca4  | 99:99 |
 +
 +項目timeは
 +  * HH:MMの形式で時間が記録される。
 +  * MMは0～59、HHは0～99の範囲。
 +  * 形式が正しくないものもある。これは計算対象外。
 +
 +また、
 +  * 集計結果が99:59を超えたら99:99の表記とする。
 +
 +===== 形式不正の確認 =====
 +
 +
 +項目timeの形式は SIMILAR TO 演算子で検証する。最近のバージョンのSQL Serverだと、通常の Like 演算子で正規表現が使える模様。OracleだとREGEXP_LIKE()関数で正規表現が扱える。
 +
 +以下エラー行は('E'の付く行は)集計対象にしない。
 +<code sql>
 +kamonohashi=> select
 +kamonohashi->   case when "time" SIMILAR TO '[0-9][0-9]:[0-5][0-9]' then null else 'E' end as "err"
 +kamonohashi->  ,"id"
 +kamonohashi->  ,"grp"
 +kamonohashi->  ,"time"
 +kamonohashi-> from
 +kamonohashi->   timelog
 +kamonohashi-> order by grp,id
 +kamonohashi-> ;
 + err | id | grp | time
 +-----+----+-----+-------
 +     |  1 | ca1 | 08:00
 +     |  2 | ca1 | 07:30
 +     |  5 | ca1 | 07:15
 +     |  3 | ca2 | 07:55
 +     | 10 | ca2 | 07:50
 +     | 11 | ca2 | 08:10
 + E   |  4 | ca3 |
 + E   |  8 | ca3 | 20:70
 +     | 21 | ca3 | 27:50
 + E   | 23 | ca3 | aa:bb
 +     | 15 | ca4 | 48:30
 +     | 22 | ca4 | 67:50
 +(12 行)
 +
 +kamonohashi=>
 +</code>
 +
 +===== HH:MMから分単位へ変換 =====
 +
 +エラーが無いHH:MM形式の文字列で表現された時間を分単位に換算する。エラー行の時間はゼロ分にする。\\
 +HH部分は"時"なので60を乗じて"分"に換算、MM部分はそのまま。両者を足し合わせれば分単位の換算が完了。
 +  HH × 60 ＋ MM ＝ 分単位
 +
 +<code sql>
 +kamonohashi=> select
 +kamonohashi->   "id"
 +kamonohashi->  ,"grp"
 +kamonohashi->  ,case when "time" SIMILAR TO '[0-9][0-9]:[0-5][0-9]' then cast(left("time",2) as integer) * 60 + cast(right("time",2) as integer)
 +kamonohashi->                                                       else 0
 +kamonohashi->                                                         end as "time_mins"
 +kamonohashi-> from
 +kamonohashi->   timelog
 +kamonohashi-> ;
 + id | grp | time_mins
 +----+-----+-----------
 +  1 | ca1 |       480
 +  2 | ca1 |       450
 +  5 | ca1 |       435
 + 10 | ca2 |       470
 + 11 | ca2 |       490
 +  3 | ca2 |       475
 + 21 | ca3 |      1670
 +  4 | ca3 |         0
 + 23 | ca3 |         0
 + 22 | ca4 |      4070
 + 15 | ca4 |      2910
 +  8 | ca3 |         0
 +(12 行)
 +
 +kamonohashi=>
 +</code>
 +
 +上記を項目grpで集計すれば以下となる。
 +
 +<code sql>
 +kamonohashi=> select
 +kamonohashi->   "grp"
 +kamonohashi->  ,sum(case when "time" SIMILAR TO '[0-9][0-9]:[0-5][0-9]' then cast(left("time",2) as integer) * 60 + cast(right("time",2) as integer)
 +kamonohashi(>                                                           else 0
 +kamonohashi(>                                                             end  ) as "time_mins"
 +kamonohashi-> from
 +kamonohashi->   timelog
 +kamonohashi-> group by "grp"
 +kamonohashi-> order by "grp"
 +kamonohashi-> ;
 + grp | time_mins
 +-----+-----------
 + ca1 |      1365
 + ca2 |      1435
 + ca3 |      1670
 + ca4 |      6980
 +(4 行)
 +
 +kamonohashi=>
 +</code>
 +
 +===== 集計結果をHH:MM形式に変換 =====
 +
 +集計後、分単位を再度HH:MM形式に戻す。分単位の集計結果を MINS とすれば、
 +  HH = MINS ÷ 60 の商
 +  MM = MINS ÷ 60 の余り
 +となるので、この値を整形して文字列に落とせばいい。
 +
 +5999は
 +  99 × 60 ＋ 59 ＝ 5,940 ＋ 59 ＝ 5,999
 +でHH:MM形式で表現できる最大の値である5,999分の意。
 +
 +PostgreSQLやSQL Serverでは整数型を除算した結果は整数型になる。Oracleは型が変換されて実数型になるかもしれない。小数点以下を切り捨てするTRUNC()関数、剰余を求めるMOD()関数、を使う必要がある。
 +<code sql>
 +kamonohashi=> select
 +kamonohashi->   mt."grp"
 +kamonohashi->  ,mt."time_mins"
 +kamonohashi->  ,case when 5999 < mt."time_mins" then '99:99'
 +kamonohashi->                                   else to_char(mt."time_mins" / 60, 'FM99')||':'||to_char(mt."time_mins" % 60, 'FM99') end as "time"
 +kamonohashi-> from
 +kamonohashi-> (
 +kamonohashi(>     select
 +kamonohashi(>       "grp"
 +kamonohashi(>      ,sum(case when "time" SIMILAR TO '[0-9][0-9]:[0-5][0-9]' then cast(left("time",2) as integer) * 60 + cast(right("time",2) as integer)
 +kamonohashi(>                                                               else 0
 +kamonohashi(>                                                                 end ) as "time_mins"
 +kamonohashi(>     from
 +kamonohashi(>       timelog
 +kamonohashi(>     group by "grp"
 +kamonohashi(> ) mt
 +kamonohashi-> order by mt."grp"
 +kamonohashi-> ;
 + grp | time_mins | time
 +-----+-----------+-------
 + ca1 |      1365 | 22:45
 + ca2 |      1435 | 23:55
 + ca3 |      1670 | 27:50
 + ca4 |      6980 | 99:99
 +(4 行)
 +
 +kamonohashi=>
 +</code>
 +
 +===== 注意 =====
 +
 +分単位を INTEGER型で扱っている。集計結果がINTEGER型の最大値を超えるとエラーになるので注意が必要。
 +
 +INTEGER型最大値が 2,147,483,647 だとすれば、時間の合計が 2,147,483,647分を超える際にエラーとなるだろう。
 +
 +  * 2,147,483,647 ÷ 60 ＝ 35,791,394.11666667 時間
 +  * 35,791,394.11666667 ÷ 24 ＝ 1,491,308.088194444 日
 +  * 1,491,308.088194444 ÷ 30 ＝ 49,710.26960648148 ヶ月
 +
 +扱う時間の性格性質によっては、ここで上げた処理方法は使えない。
 +
 +===== 追記:SQL Server LocalDBで試してみる =====
 +
 +SQL Serverの真っ当なインスタンスを作るの面倒なんで、SQLServer LocalDBを使って確認する。
 +
 +==== 下準備 ====
 +
 +
 +インスタンスを作ってデータベースを作ってテーブル作ってデータをインサートします。
 +<code sql>
 +C:\Users\k896951>sqllocaldb create kamonohashi
 +LocalDB インスタンス "kamonohashi" はバージョン 13.0.1601.5 で作成されました。
 +
 +C:\Users\k896951>sqllocaldb start kamonohashi
 +LocalDB インスタンス "kamonohashi" が開始されました。
 +
 +C:\Users\k896951>sqllocaldb info kamonohashi
 +名前:               kamonohashi
 +バージョン:         13.0.1601.5
 +共有名:
 +所有者:             HOTATE\k896951
 +自動作成:        いいえ
 +状態:              実行中
 +前回の開始時刻:    2017/10/24 23:20:46
 +インスタンス パイプ名: np:\\.\pipe\LOCALDB#FE181F63\tsql\query
 +
 +C:\Users\k896951>sqlcmd -S np:\\.\pipe\LOCALDB#FE181F63\tsql\query
 +1> select name from sys.databases
 +2> go
 +name
 +--------------------------------------------------------------------------------------------------------------------------------
 +master
 +tempdb
 +model
 +msdb
 +
 +(4 行処理されました)
 +1> create database kamo
 +2> go
 +1> use kamo
 +2> go
 +Changed database context to 'kamo'.
 +1> create table timelog(
 +2>   id   int primary key
 +3>  ,grp  varchar(3)
 +4>  ,time varchar(5)
 +5> )
 +6> go
 +1> insert into timelog values( 1,'ca1','08:00')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values( 2,'ca1','07:30')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values( 5,'ca1','07:15')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values(10,'ca2','07:50')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values(11,'ca2','08:10')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values( 3,'ca2','07:55')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values(21,'ca3','27:50')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values( 4,'ca3', null  )
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values(23,'ca3','aa:bb')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values(22,'ca4','67:50')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values(15,'ca4','48:30')
 +2> go
 +
 +(1 行処理されました)
 +1> insert into timelog values( 8,'ca3','20:70')
 +2> go
 +
 +(1 行処理されました)
 +1> select * from timelog order by grp,id
 +2> go
 +id          grp time
 +----------- --- -----
 +          1 ca1 08:00
 +          2 ca1 07:30
 +          5 ca1 07:15
 +          3 ca2 07:55
 +         10 ca2 07:50
 +         11 ca2 08:10
 +          4 ca3 NULL
 +          8 ca3 20:70
 +         21 ca3 27:50
 +         23 ca3 aa:bb
 +         15 ca4 48:30
 +         22 ca4 67:50
 +
 +(12 行処理されました)
 +1>
 +</code>
 +
 +==== LIKEで正規表現が使えるか確認 ====
 +
 +LIKE演算子で正規表現が使えました。
 +
 +<code sql>
 +1> select
 +2>   case when [time] LIKE '[0-9][0-9]:[0-5][0-9]' then null else 'E' end as "err"
 +3>  ,[id]
 +4>  ,[grp]
 +5>  ,[time]
 +6> from
 +7>   timelog
 +8> order by [grp],[id]
 +9> go
 +err id          grp time
 +--- ----------- --- -----
 +NUL           1 ca1 08:00
 +NUL           2 ca1 07:30
 +NUL           5 ca1 07:15
 +NUL           3 ca2 07:55
 +NUL          10 ca2 07:50
 +NUL          11 ca2 08:10
 +E             4 ca3 NULL
 +E             8 ca3 20:70
 +NUL          21 ca3 27:50
 +E            23 ca3 aa:bb
 +NUL          15 ca4 48:30
 +NUL          22 ca4 67:50
 +
 +(12 行処理されました)
 +1>
 +</code>
 +
 +==== 集計してみる ====
 +
 +集計結果を"HH:MM"の文字列に直した後明示的にVARCHAR(5)へCASTしているのは、表示が横長になるのを防ぐため。なくても構わない。
 +
 +<code sql>
 +1> select
 +2>   mt.[grp]
 +3>  ,mt.[time_mins]
 +4>  ,cast(case when 5999 < mt.[time_mins]
 +5>          then '99:99'
 +6>          else format(mt.[time_mins] / 60, 'D2')+':'+format(mt.[time_mins] % 60, 'D2') end as varchar(5)) as "time"
 +7> from
 +8> (
 +9>     select
 +10>       [grp]
 +11>      ,sum(case when [time] LIKE '[0-9][0-9]:[0-5][0-9]' then cast(left([time],2) as integer) * 60 + cast(right([time],2) as integer)
 +12>                                                         else 0
 +13>                                                           end ) as "time_mins"
 +14>     from
 +15>       timelog
 +16>     group by [grp]
 +17> ) mt
 +18> order by mt.[grp]
 +19> go
 +grp time_mins   time
 +--- ----------- -----
 +ca1        1365 22:45
 +ca2        1435 23:55
 +ca3        1670 27:50
 +ca4        6980 99:99
 +
 +(4 行処理されました)
 +1></code>
 +
 +{{tag> database SQL 時間の集計 PostgreSQL SQLServer Oracle 技術資料}}
  
documents/proglang/sql/sql-0009.txt · 最終更新: by k896951
Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki