稼働時間を営業時間換算で集計したい

Question

MySQLを使用して、機械の稼働時間を管理しております。

〇実現したいこと；
同じ「機械番号」が稼働している時間の差分を営業時間(土日祝日を除く)に換算して集計したい。
・営業時間：平日 9:00～17:30(8.5h)
・営業日：土日・祝日を除く平日

例 テーブル名：tb_run

機械番号	開始日時	終了日時
aaa	2020/12/4 9:00	2020/12/4 19:00
bbb	2020/12/4 7:30	2020/12/7 22:00

※機械番号：文字列型
※開始日時、終了日時：日時型

「稼働時間=終了日時-開始日時」で集計する際に、営業時間換算したいです。

〇計算イメージ：
・1行名：
そのまま計算(timediffを使用)すると、
稼働時間(h) = 終了日時(2020/12/4(金)19:00)-開始日時(2020/12/4(金) 9:00)
のため、10hになりますが、営業時間外の時間(17:30～19:00)は除外した8.5hという結果をしたい。

・2行目；
稼働時間(h) = 終了日時(2020/12/7(月)22:00)-開始日時(2020/12/4(金) 7:30)
同様に、そのまま計算すると、86.5hとなりますが
以下の営業時間外の分を引いた「23.5ｈ」という結果を集計したい。
①2020/12/4(金) 7:30～9:00 (1.5h)
②2020/12/4(金) 17:30～2020/12/7(月)9:00(61.5h)
→①+②=63h

テーブル(tb_run)には、曜日のカラムはありませんが、別テーブルで日付から曜日を特定するテーブルは管理していますので、Join等で曜日をカラムに追加することは可能です。

何卒、よろしくお願い致します。

★2020/12/12 16:43更新；
曜日を付加したテーブルに更新させていただきます。

曜日のテーブルは開始日時、終了日時の日付に対して、曜日を追加する予定です。

例 テーブル名：tb_run_2
|機械番号|開始日時|終了日時|開始日時_曜日|終了日時_曜日
|:--|:--:|--:|
|aaa|2020/12/4 9:00|2020/12/4 19:00|金|金
|bbb|2020/12/4 7:30|2020/12/7 22:00|金|月

※曜日のテーブル(tb_calender)は以下になります。「稼働時間(h)」を算出する上で、さらに集計しやすい結合案がありましたら、アドバイスをいただけると助かります。

曜日テーブル：tb_calender

日付	曜日
2020/12/4	金
2020/12/5	土
2020/12/6	日
2020/12/7	月
以下略

Answer 1

「稼働時間=終了日時-開始日時」で集計する際に、営業時間換算したいです。

「日付から曜日を特定するテーブル」と結合し、それらの情報を元に、営業時間換算するFunctionを作成するのが手っ取り早いと思います。
※FUnctionじゃなくCASEでもいけそうですね。
具体的なSQLを希望されるなら、「日付から曜日を特定するテーブル」の情報も質問に追記して下さい。

追記

tb_run

機械番号	開始日時	終了日時	開始日	終了日
aaa	2020/12/4 9:00	2020/12/4 19:00	2020/12/4	2020/12/4
bbb	2020/12/4 7:30	2020/12/7 22:00	2020/12/4	2020/12/7

tb_calender

カレンダー日	稼働
2020/12/4	1
2020/12/5	0
2020/12/6	0
2020/12/7	1

上記のテーブルとした場合のSQLの概要は以下です。

select run.機械番号
     , sum(
         case when 稼働日=1 then
           case when カレンダー日=開始日 and カレンダー日=終了日 then
              -- 開始日時と終了日時が日付を跨がない場合
           else
              case when カレンダー日=開始日 then
                -- 日を跨り開始日時の計算部分
              else
                case when カレンダー日=終了日 then
                -- 日を跨り終了日時の計算部分
                else
                -- 日を跨り終日稼働での計算部分
                end
              end
           end
         end
       )
from    tb_run run 
        inner join tb_calender cld
        on cld.カレンダー日 betwenn run.開始日 and run.終了日
group by run.機械番号

結合すると以下のイメージになります。

機械番号	開始日時	終了日時	開始日	終了日	カレンダー日	稼働
aaa	2020/12/4 9:00	2020/12/4 19:00	2020/12/4	2020/12/4	2020/12/4	1
bbb	2020/12/4 7:30	2020/12/7 22:00	2020/12/4	2020/12/7	2020/12/4	1
bbb	2020/12/4 7:30	2020/12/7 22:00	2020/12/4	2020/12/7	2020/12/5	0
bbb	2020/12/4 7:30	2020/12/7 22:00	2020/12/4	2020/12/7	2020/12/6	0
bbb	2020/12/4 7:30	2020/12/7 22:00	2020/12/4	2020/12/7	2020/12/7	1

上記の行は1日の情報になるのでそれをcaseで分岐して計算すれば良いことになります。(TIMEDIFF等を用いて計算)
営業時間外の時間(17:30～19:00)は除外部分についてさらにcaseで分割すれば良ですけど、やはり冗長になるので、fuctionにした方が良いかと思います。

Comments

[chanda-11]

sazi様、早速のご回答誠にありがとうございます。曜日を付加させていただいたテーブル案(tb_run_2)を質問の文末に追加させていただきました。
もし、tb_run_2のようなテーブルでしたら、SQL文はどのような記述になるでしょうか。
お知恵いただけると嬉しいです。よろしくお願いいたします。

また、テーブル案というのも、tb_runに対する加工や新テーブルの作成などは自由にできる状況のため、「稼働時間(h)」を求めやすい結合案などもあれば、アドバイスをいただけると幸いです。tb_calenderなども変更可能です。

[sazi]

曜日ではなく、開始日と終了日を追加した方が結合が効率的です。（生成カラムでも良いですが）
それから、目的は稼働に関してなので、曜日より稼働の情報（稼働/非稼働のフラグ）が必要ですね。

[chanda-11]

回答ありがとうございます。
なお、稼働時間(h)の算出方法(SQL文)はどのような記述になるでしょうか。お手数をお掛けしますが、ご教授いただけないでしょうか。

[chanda-11]

具体的にわかりやすく回答いただきまして、誠にありがとうございます。
いただいた内容をもとに、集計できるようにいたします。ありがとうございます。