前提・実現したいこと
現在、スケジューラのようなシステムを作成しています。
そのシステムでは、大まかに以下のような要件があります。
- チームが存在する。
- チームはリソース(人数)の上限まで、複数の作業を並行で実施することができる。
- 作業には、作業するのに必要なリソース(人数)を持つ。
- 作業のタスクを登録できるのは、チームのリソースに余裕があり時間内に収まる場合のみ。
- チームのリソース(人数)、作業の必要リソース(人数)は固定。
- 作業に必要な時間(作業量)は変動する。
現在は、上記の4点目
「作業のタスクを登録できるのは、チームのリソースに余裕があり時間内に収まる場合のみ。」
をどのように判断するかで悩んでいます。
テーブル構成は以下のようになります。
今回新たにチームという概念が加わったため、チームマスタ及びチーム⇔作業紐付けマスタを追加しております。
※チーム名がない点は、気にしないでください。
検討内容
上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せができると考えています。
※タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
問い合わせの方法ですが、クライアントアプリから以下を引数としたストアドファンクションを実行します。
- 作業ID
- 作業時間
- 作業開始可能日時
ストアドファンクションでは、上記テーブルのデータより以下の結果を返します。
- 開始可能日時
※開始日時が一番早い1件のみ。(TOP 1かつORDER BY 開始日時 DESC)
なお、引数で作業開始可能日時を渡すため、残リソースViewの開始日時が作業開始可能日時を超えている場合、
作業時間も作業開始可能日時~終了日時の分で算出し、返す開始日時は作業開始可能日時とする必要があります。
ソース
2018/09/22:追記しました。
- チームマスタ
lang
1CREATE TABLE TeamMaster 2( 3 チームID varchar(10) NOT NULL, 4 所有リソース decimal(5,2) NOT NULL 5) 6ON PRIMARY 7ALTER TABLE TeamMaster ADD CONSTRAINT PK_TeamMaster 8PRIMARY KEY (チームID) 9ON PRIMARY 10 11INSERT INTO TeamMaster (チームID, 所有リソース) VALUES ('A', 5)
- 作業マスタ
lang
1CREATE TABLE WorkMaster 2( 3 作業ID varchar(10) NOT NULL, 4 作業名 varchar(20) NOT NULL, 5 必要リソース decimal(4,2) NOT NULL 6) 7ON PRIMARY 8ALTER TABLE WorkMaster ADD CONSTRAINT PK_WorkMaster 9PRIMARY KEY (作業ID) 10ON PRIMARY 11 12INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W1', 'Work1', 2) 13INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W2', 'Work2', 2)
- チーム⇔作業紐付けマスタ
lang
1CREATE TABLE Team_WorkMaster 2( 3 チームID varchar(10) NOT NULL, 4 作業ID varchar(10) NOT NULL 5) 6ON PRIMARY 7ALTER TABLE Team_WorkMaster ADD CONSTRAINT PK_Team_WorkMaster 8PRIMARY KEY (チームID, 作業ID) 9ON PRIMARY 10 11INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W1') 12INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W2')
- 計画済タスクトランザクション
lang
1CREATE TABLE PlanedTaskTran 2( 3 タスクID decimal(10,0) NOT NULL, --実際には自動採番を設定している 4 作業ID varchar(10) NOT NULL, 5 開始日時 datetime NOT NULL, 6 終了日時 datetime NOT NULL 7) 8ON PRIMARY 9ALTER TABLE PlanedTaskTran ADD CONSTRAINT PK_PlanedTaskTran 10PRIMARY KEY (タスクID) 11ON PRIMARY 12 13INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (1, 'W1', '2018/09/21 10:00:00', '2018/09/21 15:00:00') 14INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (2, 'W2', '2018/09/21 11:00:00', '2018/09/21 14:00:00')
試したこと
Viewの多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。
2018/09/22:誤記等もありましたので、修正しました。
- 一旦、日時情報を1カラムにまとめる
SQL
1CREATE VIEW tmp1 2 (種別, チームID, 作業ID, 所有リソース, 必要リソース, 日時) 3AS 4SELECT 5 1 AS 種別 6 ,Team_WorkMaster.チームID 7 ,PlanedTaskTran.作業ID 8 ,TeamMaster.所有リソース 9 ,WorkMaster.必要リソース 10 ,PlanedTaskTran.開始日時 AS 日時 11FROM PlanedTaskTran 12JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID 13JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID 14JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID 15UNION ALL --データは重複することはないため、UNION ALLに修正しました。 16SELECT 17 -1 18 ,Team_WorkMaster.チームID 19 ,PlanedTaskTran.作業ID 20 ,TeamMaster.所有リソース 21 ,WorkMaster.必要リソース 22 ,PlanedTaskTran.開始日時 23FROM PlanedTaskTran 24JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID 25JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID 26JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID
種別 | チームID | 作業ID | 所有リソース | 必要リソース | 日時 |
---|---|---|---|---|---|
1 | A | W1 | 5 | 2 | 2018/09/21 10:00:00 |
1 | A | W2 | 5 | 2 | 2018/09/21 11:00:00 |
-1 | A | W2 | 5 | 2 | 2018/09/21 14:00:00 |
-1 | A | W1 | 5 | 2 | 2018/09/21 15:00:00 |
- 日時毎の残リソースを算出する
lang
1CREATE VIEW tmp2 2 (チームID, 日時, 残リソース) 3AS 4SELECT 5 tmp1.チームID 6 ,tmp1.日時 7 ,(SELECT 8 MAX(t.所有リソース) - (SUM(t.種別) * MAX(t.必要リソース)) 9 FROM tmp1 AS t 10 WHERE t.日時 < tmp1.日時 11 OR (t.日時 = tmp1.日時 AND t.種別 <= tmp1.種別)) AS 残リソース 12FROM tmp1
チームID | 日時 | 残リソース |
---|---|---|
A | 2018/09/21 10:00:00 | 3 |
A | 2018/09/21 11:00:00 | 1 |
A | 2018/09/21 14:00:00 | 3 |
A | 2018/09/21 15:00:00 | 5 |
- 時間帯毎の残リソースを算出する
lang
1CREATE VIEW RemainResource 2 (チームID, 開始日時, 終了日時, 残リソース, 作業可能時間) 3AS 4SELECT 5 tmp3.* 6 ,DATEDIFF(ss,tmp3.開始日時,tmp3.終了日時) AS 作業可能時間 7FROM 8 (SELECT 9 MAX(tmp2.チームID) AS チームID 10 ,CASE 11 WHEN MAX(t.日時) IS NULL THEN 12 CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00') --6時の情報は、別テーブルに定義した値をSELECTします。 13 ELSE 14 MAX(t.日時) 15 END AS 開始日時 16 ,MAX(tmp2.日時) AS 終了日時 17 ,MAX(tmp2.残リソース) AS 残リソース 18 FROM tmp2 19 LEFT OUTER JOIN tmp2 AS t 20 ON t.日時 < tmp2.日時 21 GROUP BY tmp2.日時) AS tmp3
チームID | 開始日時 | 終了日時 | 残リソース | 作業可能時間 |
---|---|---|---|---|
A | 2018/09/21 06:00:00 | 2018/09/21 10:00:00 | 5 | 14400 |
A | 2018/09/21 10:00:00 | 2018/09/21 11:00:00 | 3 | 3600 |
A | 2018/09/21 11:00:00 | 2018/09/21 14:00:00 | 1 | 10800 |
A | 2018/09/21 14:00:00 | 2018/09/21 15:00:00 | 3 | 3600 |
※上記クエリでは、2018/09/21 15:00:00~2018/09/22 06:00:00までの残リソースが5という情報は出力できていません。
かなり長々としたクエリになり、且つ計画済トランザクションのデータが多いとパフォーマンスにも影響を与えると思い、
別の手段がないかと今回質問させていただきました次第です。
正直、Viewだけでは厳しいのかと思っており、ストアドで計画済トランザクションをカーソルで探索する方法の方が良いのか?とも思っています。
どうか知恵をお貸しいただけないでしょうか。
補足情報(FW/ツールのバージョンなど)
SQL Server 2014
回答1件
あなたの回答
tips
プレビュー