質問編集履歴

文法の修正

2018/09/22 01:07

投稿

locoJr.

スコア24

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -186,7 +186,7 @@
     MAX(tmp2.チームID) AS チームID
     ,CASE
       WHEN MAX(t.日時) IS NULL THEN
-        CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00')
+        CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00') --6時の情報は、別テーブルに定義した値をSELECTします。
       ELSE
         MAX(t.日時)
     END AS 開始日時

書式の改善

2018/09/22 01:07

投稿

locoJr.

スコア24

title CHANGED Viewed

File without changes

body CHANGED Viewed

@@ -20,19 +20,19 @@
 ※チーム名がない点は、気にしないでください。
 ### 検討内容
+上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せができると考えています。
+※タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
+![イメージ説明](333663d3d2e755ca781ac0b53fa72ee3.png)
-クライアントアプリからは、以下を引数としたストアドファンクションを実行します。
+問い合わせの方法ですが、クライアントアプリから以下を引数としたストアドファンクションを実行します。
 - 作業ID
 - 作業時間
 - 作業開始可能日時
 ストアドファンクションでは、上記テーブルのデータより以下の結果を返します。
 - 開始可能日時
+　※開始日時が一番早い1件のみ。（TOP 1かつORDER BY 開始日時 DESC）
-上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せができると考えています。
-チームの時間帯毎の残リソース及び作業時間があれば、可能と考えました。
-タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
-![イメージ説明](333663d3d2e755ca781ac0b53fa72ee3.png)
 実データのイメージは、以下になります。
 ![イメージ説明](4c6c5f0cc96dbbef811d5c7cefffe1f8.png)
@@ -40,39 +40,173 @@
 なお、引数で作業開始可能日時を渡すため、残リソースViewの開始日時が作業開始可能日時を超えている場合、
 作業時間も作業開始可能日時～終了日時の分で算出し、返す開始日時は作業開始可能日時とする必要があります。
+### ソース
+2018/09/22：追記しました。
+- チームマスタ
+```lang-SQL
+CREATE TABLE TeamMaster
+(
+  チームID varchar(10) NOT NULL,
+  所有リソース decimal(5,2) NOT NULL
+)
+ON PRIMARY
+ALTER TABLE TeamMaster ADD CONSTRAINT PK_TeamMaster
+PRIMARY KEY (チームID)
+ON PRIMARY
+INSERT INTO TeamMaster (チームID, 所有リソース) VALUES ('A', 5)
+```
+- 作業マスタ
+```lang-SQL
+CREATE TABLE WorkMaster
+(
+  作業ID varchar(10) NOT NULL,
+  作業名 varchar(20) NOT NULL,
+  必要リソース decimal(4,2) NOT NULL
+)
+ON PRIMARY
+ALTER TABLE WorkMaster ADD CONSTRAINT PK_WorkMaster
+PRIMARY KEY (作業ID)
+ON PRIMARY
+INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W1', 'Work1', 2)
+INSERT INTO WorkMaster (作業ID, 作業名, 必要リソース) VALUES ('W2', 'Work2', 2)
+```
+- チーム⇔作業紐付けマスタ
+```lang-SQL
+CREATE TABLE Team_WorkMaster
+(
+  チームID varchar(10) NOT NULL,
+  作業ID varchar(10) NOT NULL
+)
+ON PRIMARY
+ALTER TABLE Team_WorkMaster ADD CONSTRAINT PK_Team_WorkMaster
+PRIMARY KEY (チームID, 作業ID)
+ON PRIMARY
+INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W1')
+INSERT INTO Team_WorkMaster (チームID, 作業ID) VALUES ('A', 'W2')
+```
+- 計画済タスクトランザクション
+```lang-SQL
+CREATE TABLE PlanedTaskTran
+(
+  タスクID decimal(10,0) NOT NULL, --実際には自動採番を設定している
+  作業ID varchar(10) NOT NULL,
+  開始日時 datetime NOT NULL,
+  終了日時 datetime NOT NULL
+)
+ON PRIMARY
+ALTER TABLE PlanedTaskTran ADD CONSTRAINT PK_PlanedTaskTran
+PRIMARY KEY (タスクID)
+ON PRIMARY
+INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (1, 'W1', '2018/09/21 10:00:00', '2018/09/21 15:00:00')
+INSERT INTO PlanedTaskTran (タスクID, 作業ID, 開始日時, 終了日時) VALUES (2, 'W2', '2018/09/21 11:00:00', '2018/09/21 14:00:00')
+```
 ### 試したこと
-Viewの多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。
+~~Viewの多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。~~
-```
+2018/09/22：誤記等もありましたので、修正しました。
---1.一旦、日時情報を1カラムにまとめる
+- 一旦、日時情報を1カラムにまとめる
+```SQL
+CREATE VIEW tmp1
+  (種別, チームID, 作業ID, 所有リソース, 必要リソース, 日時)
+AS
 SELECT
   1 AS 種別
-  ,T.チームID
+  ,Team_WorkMaster.チームID
+  ,PlanedTaskTran.作業ID
-  ,T.所有リソース
+  ,TeamMaster.所有リソース
-  ,W.必要リソース
+  ,WorkMaster.必要リソース
-  ,P.開始日時 AS 日時
+  ,PlanedTaskTran.開始日時 AS 日時
-FROM 計画済タスクトランザクション AS P
+FROM PlanedTaskTran
-JOIN チーム⇔作業紐付けマスタ AS TW ON TW.作業ID = P.作業ID
+JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
-JOIN 作業マスタ AS W ON W.作業ID = P.作業ID
+JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID
-JOIN チームマスタ AS T ON T.チームID = TW.チームID
+JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID
-UNION
+UNION ALL --データは重複することはないため、UNION ALLに修正しました。
 SELECT
   -1
-  ,T.チームID
+  ,Team_WorkMaster.チームID
+  ,PlanedTaskTran.作業ID
-  ,T.所有リソース
+  ,TeamMaster.所有リソース
-  ,W.必要リソース
+  ,WorkMaster.必要リソース
-  ,P.終了日時
+  ,PlanedTaskTran.開始日時
-FROM 計画済タスクトランザクション AS P
+FROM PlanedTaskTran
-JOIN チーム⇔作業紐付けマスタ AS TW ON TW.作業ID = P.作業ID
+JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
-JOIN 作業マスタ AS W ON W.作業ID = P.作業ID
+JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID
-JOIN チームマスタ AS T ON T.チームID = TW.チームID
+JOIN 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|
---2.種別の情報から、・・・すみませｎ、忘れました。
+- 日時毎の残リソースを算出する
+```lang-SQL
+CREATE VIEW tmp2
+  (チームID, 日時, 残リソース)
+AS
+SELECT
+  tmp1.チームID
+  ,tmp1.日時
+  ,(SELECT
+      MAX(t.所有リソース) - (SUM(t.種別) * MAX(t.必要リソース))
+    FROM tmp1 AS t
+    WHERE t.日時 < tmp1.日時
---3.2で作成した情報を1レコードずらしてJOIN(FROM 2 JOIN 2 AS 2V ON 2V.日時 < 2.日時)し、
+    OR (t.日時 = tmp1.日時 AND t.種別 <= tmp1.種別)) AS 残リソース
+FROM tmp1
+```
+|チームID|日時|残リソース|
+|:--|:--|:--|
---  2V.日時を開始日時、2.日時を終了日時として情報を算出する。
+|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-SQL
+CREATE VIEW RemainResource
+  (チームID, 開始日時, 終了日時, 残リソース, 作業可能時間)
+AS
+SELECT
+  tmp3.*
---4.3で作成した情報から、DATEDIFF(ss,開始日時,終了日時)で作業時間を算出する。
+  ,DATEDIFF(ss,tmp3.開始日時,tmp3.終了日時) AS 作業可能時間
+FROM
+  (SELECT
+    MAX(tmp2.チームID) AS チームID
+    ,CASE
+      WHEN MAX(t.日時) IS NULL THEN
+        CONVERT(datetime, CONVERT(char(8), MAX(tmp2.日時), 112) + '06:00:00')
+      ELSE
+        MAX(t.日時)
+    END AS 開始日時
+    ,MAX(tmp2.日時) AS 終了日時
+    ,MAX(tmp2.残リソース) AS 残リソース
+  FROM tmp2
+  LEFT OUTER JOIN tmp2 AS t
+    ON t.日時 < tmp2.日時
+  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という情報は出力できていません。
 かなり長々としたクエリになり、且つ計画済トランザクションのデータが多いとパフォーマンスにも影響を与えると思い、
 別の手段がないかと今回質問させていただきました次第です。

文法の修正

2018/09/22 01:04

投稿

locoJr.

スコア24

title CHANGED Viewed

	@@ -1,1 +1,1 @@
1	- ~~チームの~~時間帯~~毎の使用リソースを出力す~~るViewを作成したい
1	+ 時間帯が重複していれば、数値が少なくなるようなViewを作成したい

body CHANGED Viewed

File without changes