SQL 重なっている時間を按分して作業時間を求める

Question

SQLで質問です。

• 作業時間を求めたい。
• 同じ工程を複数の作業者が作業した時間は按分。

MySQL: 5.7.31

Reportsテーブル
|id|process_id|employee_id|started_on|finished_on|
|--:|--:|--:|
|1|1|1|2021-05-18 9:00:00|2021-05-18 17:00:00|
|2|1|2|2021-05-18 10:00:00|2021-05-18 11:00:00|

単純に作業時間(working_time)を求めると
9:00～18:00 = 8時間 = 8時間×60分×60秒 = 28800秒
10:00～11:00 = 1時間 = 1時間×60分×60秒 = 3600秒
|id|process_id|employee_id|started_on|finished_on|working_time|
|--:|--:|--:|
|1|1|1|2021-05-18 9:00:00|2021-05-18 17:00:00|28800秒|
|2|1|2|2021-05-18 10:00:00|2021-05-18 11:00:00|3600秒|

＜＜求めたい作業時間＞＞
10:00～11:00は重なっているので、重なっている件数で割る
9:00～18:00 = 8時間 = 7時間×60分×60秒 + 1時間×60分×60秒 / 2 = 27000秒
10:00～11:00 = 1時間 = 1時間×60分×60秒 / 2 = 1800秒
|id|process_id|employee_id|started_on|finished_on|working_time|
|--:|--:|--:|
|1|1|1|2021-05-18 9:00:00|2021-05-18 17:00:00|27000秒|
|2|1|2|2021-05-18 10:00:00|2021-05-18 11:00:00|1800秒|

10:00～11:00は重なっているので、重なっている件数で割る
1:9:00～18:00 = 8時間 = 7時間×60分×60秒 + 1時間×60分×60秒 / 3 = 26400秒
2:10:00～11:00 = 1時間 = 1時間×60分×60秒 / 3 = 1200秒
3:10:00～11:00 = 1時間 = 1時間×60分×60秒 / 3 = 1200秒
|id|process_id|employee_id|started_on|finished_on|working_time|
|--:|--:|--:|
|1|1|1|2021-05-18 9:00:00|2021-05-18 17:00:00|26400秒|
|2|1|2|2021-05-18 10:00:00|2021-05-18 11:00:00|1200秒|
|3|1|3|2021-05-18 10:00:00|2021-05-18 11:00:00|1200秒|

10:00～11:00と13:00～13:30は重なっているので、重なっている件数で割る
1:9:00～18:00 = 8時間 = 6.5時間×60分×60秒 + 1時間×60分×60秒 / 2 + 0.5時間×60分×60秒 / 2 = 26100秒
2:10:00～11:00 = 1時間 = 1時間×60分×60秒 / 2 = 1800秒
3:13:00～13:30 = 1時間 = 0.5時間×60分×60秒 / 2 = 900秒
|id|process_id|employee_id|started_on|finished_on|working_time|
|--:|--:|--:|
|1|1|1|2021-05-18 9:00:00|2021-05-18 17:00:00|26100秒|
|2|1|2|2021-05-18 10:00:00|2021-05-18 11:00:00|1800秒|
|3|1|3|2021-05-18 13:00:00|2021-05-18 13:30:00|900秒|

※日付を跨ぐことはないのが前提

これをSQLにて求めることはできますでしょうか？

少し考えたのは以下のSQL

select
  sum(seconds)
from
( 
  select 60 / count(*) as seconds from reports where process_id = 1 and started_on < '2021-05-18 9:00:00' and finished_on > '2021-05-18 9:00:00'
  union all
  select 60 / count(*) as seconds from reports where process_id = 1 and started_on < '2021-05-18 9:01:00' and finished_on > '2021-05-18 9:01:00'
  union all
  select 60 / count(*) as seconds from reports where process_id = 1 and started_on < '2021-05-18 9:02:00' and finished_on > '2021-05-18 9:02:00'
  ・・・
  union all
  select 60 / count(*) as seconds from reports where process_id = 1 and started_on < '2021-05-18 18:00:00' and finished_on > '2021-05-18 18:00:00'
) a

Comments

[sousuke]

開始、終了時間に30分などは入りますか？

[m.ts10806]

アプリケーション側では使わないのですか？
アプリケーション側で計算したほうが楽にできそうですが

[yamakawa03]

>開始、終了時間に30分などは入りますか？
はい、入ります。1分単位で変動します。

>アプリケーション側では使わないのですか？
アプリケーション側で処理することも可能です。
SQLで取得できる方法があるかなと思い投稿させて頂きました。

[sazi]

MySQLのバージョンは何でしょう？

[yamakawa03]

>MySQLのバージョンは何でしょう？
MySQL: 5.7.31 です。
追記しておきます。

[sazi]

@m.ts10806 さん
> アプリケーション側で計算したほうが楽にできそう
SQLだとemployee_idの総当たりは思いつくところですけど、アプリケーション側で楽できそうなのはどんなロジックでしょうか？

[sazi]

@yamakawa03さん
ちょっと考えてみましたが、複数人や日付を跨る事などを考慮すると、決め事が必要だと思われますので、もう少し掘り下げたロジックを追記された方が良いかと思います。

[yamakawa03]

@sazi
ありがとうございます。
少し条件が足りなかったので、追記させて頂きます。

[neko_the_shadow]

一度に処理したいprocess_idは1種類だけでしょうか? (上の例であればprocess_id=1のみ、計算がされればよくて、process_id=2やprocess_id=3を同時に計算する必要はないということであっていますか?)

[sazi]

時間の刻みの最小単位は何ですか？
スケール（目盛）を作ってその単位でカウントするのが一番シンプルになりそうなんですけど、スケールの単位が細かすぎるとレスポンス的にちょっとどうかなと思うので。

[m.ts10806]

saziさん
私のSQLスキルもあると思いますが、「按分する」というところがあるのでアプリケーションロジックでやってしまいたいなと感じた次第です。

[yamakawa03]

＞一度に処理したいprocess_idは1種類だけでしょうか? (上の例であればprocess_id=1のみ、計算がされればよくて、process_id=2やprocess_id=3を同時に計算する必要はないということであっていますか?)
できれば、複数process取れるのが理想です。

>時間の刻みの最小単位は何ですか？
1秒単位で欲しいですが、1分単位でも可。

Answer 1

個人ごとに作業時間が違うので、基準となる目盛を作り、その目盛単位で重複を数えるのがシンプルです。
例えば、以下の様なテーブルを用意します。
※値は計算範囲に合わせる。
時間帯が9:00～17:00などと決まっているなら、日付は無しにして固定でも良いでしょう。
time_scale

from_time	to_time
2021-05-18 09:00:00	2021-05-18 10:00:00
2021-05-18 10:00:00	2021-05-18 11:00:00
2021-05-18 11:00:00	2021-05-18 12:00:00
2021-05-18 12:00:00	2021-05-18 13:00:00
2021-05-18 13:00:00	2021-05-18 14:00:00
2021-05-18 14:00:00	2021-05-18 15:00:00
2021-05-18 15:00:00	2021-05-18 16:00:00
2021-05-18 16:00:00	2021-05-18 17:00:00
2021-05-18 17:00:00	2021-05-18 18:00:00
2021-05-18 18:00:00	2021-05-18 19:00:00
2021-05-18 19:00:00	2021-05-18 20:00:00

上記に対してそれぞれの単位での案分値を求めます。
※目盛は６０分なので、６０分に対して重なりで除算します。

select process_id, from_time, to_time
     , (
         60 / sum(case when from_time <= finished_on and to_time > started_on then 1 end)
       ) as Profit_time
from Reports, time_scale
group by process_id, from_time, to_time

これに対し、各々の作業時間を上記と突合したものを合計します。

追記

時間帯が日付を跨らず固定にできる場合は、以下のようになります。

from_time	to_time
09:00:00	10:00:00
10:00:00	11:00:00
11:00:00	12:00:00
12:00:00	13:00:00
13:00:00	14:00:00
14:00:00	15:00:00
15:00:00	16:00:00
16:00:00	17:00:00
17:00:00	18:00:00
```SQL
select process_id, from_time, to_time

 , (60 / sum(
           case when from_time <= time(finished_on) and to_time > time(started_on) 
           then 1 end
         )
   ) as Profit_time

from Reports, time_scale
group by process_id, from_time, to_time

Comments

[yamakawa03]

ありがとうございます！
あらかじめテーブルを作るのは目から鱗でした。
　※レスポンス的にも良さげです。
こちらで行けそうです。

[sousuke]

個人的に思いつく現実的なラインはこの手法なんだけど
ここまでして求めた秒数だが何に使うにしても微妙な感じがしてしまうなあ
あくまで個人的な感想だけど。１秒の価値を何人で作業しても変わらない前提ならほかに
やりようがある気がしないでもない。

[sazi]

@sousukeさん
根本的な事を言うと、複数人で作業すると時間の価値が下がる（生産性が落ちる）というのは在りえないと思いますけどね。
共同作業でフォローする人の生産性が落ちる事はあるでしょうけど、全体でみると上がるから共同作業するのでしょうし。

[sousuke]

@sazi氏
「複数人で作業すると時間の価値が下がる（生産性が落ちる）」
これは私もそう思います、オブラートに包んで発言していました笑
考えられることとしてはこの秒数を何かの分母に使っていて、間接的に価値を上げているのかな？
と想像はしていますが…エスパーになるのでわかりませんね～

[sazi]

これって、本来は人を軸にプロセスで案分するって事じゃないかなぁ。

[yamakawa03]

@sazi
ご指摘の通りです。
こちらの仕様の把握が間違っておりました。
プロセスと人を変えて対応いたしました。