質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.53%

時間帯が重複していれば、数値が少なくなるようなViewを作成したい

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 858

locoJr.

score 6

 前提・実現したいこと

現在、スケジューラのようなシステムを作成しています。
そのシステムでは、大まかに以下のような要件があります。

  • チームが存在する。
  • チームはリソース(人数)の上限まで、複数の作業を並行で実施することができる。
  • 作業には、作業するのに必要なリソース(人数)を持つ。
  • 作業のタスクを登録できるのは、チームのリソースに余裕があり時間内に収まる場合のみ。
  • チームのリソース(人数)、作業の必要リソース(人数)は固定。
  • 作業に必要な時間(作業量)は変動する。

現在は、上記の4点目
「作業のタスクを登録できるのは、チームのリソースに余裕があり時間内に収まる場合のみ。」
をどのように判断するかで悩んでいます。

テーブル構成は以下のようになります。
イメージ説明
今回新たにチームという概念が加わったため、チームマスタ及びチーム⇔作業紐付けマスタを追加しております。
※チーム名がない点は、気にしないでください。

 検討内容

上記テーブルのデータはViewで以下の形式で持つことで、楽に問合せができると考えています。
※タスク登録時には、既存の機能で他にも多くのテーブルにデータを追加しているため、実データで持つのは避けられたら・・・と思っています。
イメージ説明

問い合わせの方法ですが、クライアントアプリから以下を引数としたストアドファンクションを実行します。

  • 作業ID
  • 作業時間
  • 作業開始可能日時

ストアドファンクションでは、上記テーブルのデータより以下の結果を返します。

  • 開始可能日時
    ※開始日時が一番早い1件のみ。(TOP 1かつORDER BY 開始日時 DESC)

実データのイメージは、以下になります。
イメージ説明

なお、引数で作業開始可能日時を渡すため、残リソースViewの開始日時が作業開始可能日時を超えている場合、
作業時間も作業開始可能日時~終了日時の分で算出し、返す開始日時は作業開始可能日時とする必要があります。

 ソース

2018/09/22:追記しました。

  • チームマスタ
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)
  • 作業マスタ
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)
  • チーム⇔作業紐付けマスタ
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')
  • 計画済タスクトランザクション
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の多段使用で近いところまではいったのですが、実際のソースは会社にありメモできていませんが、おおまかに以下のような流れで考えました。
2018/09/22:誤記等もありましたので、修正しました。

  • 一旦、日時情報を1カラムにまとめる
CREATE VIEW tmp1
  (種別, チームID, 作業ID, 所有リソース, 必要リソース, 日時)
AS
SELECT
  1 AS 種別
  ,Team_WorkMaster.チームID
  ,PlanedTaskTran.作業ID
  ,TeamMaster.所有リソース
  ,WorkMaster.必要リソース
  ,PlanedTaskTran.開始日時 AS 日時
FROM PlanedTaskTran
JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業ID
JOIN TeamMaster ON TeamMaster.チームID = Team_WorkMaster.チームID
UNION ALL --データは重複することはないため、UNION ALLに修正しました。
SELECT
  -1
  ,Team_WorkMaster.チームID
  ,PlanedTaskTran.作業ID
  ,TeamMaster.所有リソース
  ,WorkMaster.必要リソース
  ,PlanedTaskTran.開始日時
FROM PlanedTaskTran
JOIN Team_WorkMaster ON Team_WorkMaster.作業ID = PlanedTaskTran.作業ID
JOIN WorkMaster ON WorkMaster.作業ID = PlanedTaskTran.作業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
  • 日時毎の残リソースを算出する
CREATE VIEW tmp2
  (チームID, 日時, 残リソース)
AS
SELECT
  tmp1.チームID
  ,tmp1.日時
  ,(SELECT
      MAX(t.所有リソース) - (SUM(t.種別) * MAX(t.必要リソース))
    FROM tmp1 AS t
    WHERE t.日時 < tmp1.日時
    OR (t.日時 = tmp1.日時 AND t.種別 <= tmp1.種別)) AS 残リソース
FROM 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
  • 時間帯毎の残リソースを算出する
CREATE VIEW RemainResource
  (チームID, 開始日時, 終了日時, 残リソース, 作業可能時間)
AS
SELECT
  tmp3.*
  ,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') --6時の情報は、別テーブルに定義した値をSELECTします。
      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という情報は出力できていません。

かなり長々としたクエリになり、且つ計画済トランザクションのデータが多いとパフォーマンスにも影響を与えると思い、
別の手段がないかと今回質問させていただきました次第です。

正直、Viewだけでは厳しいのかと思っており、ストアドで計画済トランザクションをカーソルで探索する方法の方が良いのか?とも思っています。
どうか知恵をお貸しいただけないでしょうか。

 補足情報(FW/ツールのバージョンなど)

SQL Server 2014

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • Orlofsky

    2018/09/21 03:12

    SQLで使われるテーブルはCREATE TABLE しておく必要があります。質問の[テーブル構成]はCREATE TABLEで、[実データのイメージ]はINSERT文で提示した方が適切なコメントが付き易いかと。

    キャンセル

  • Orlofsky

    2018/09/21 03:13

    UNION と UNION ALL の違いはググって調べておきましょう。

    キャンセル

  • locoJr.

    2018/09/23 08:45

    なるほど、初めてでしたのでそこまで気が回りませんでした。ご指摘ありがとうございます。UNIONに関しては、データの重複がないことからUNION ALLに修正しました。

    キャンセル

回答 1

checkベストアンサー

+1

具体的な内容での回答にするには時間が掛かりそうなので、アイデアだけ。

「計画済タスクトランザクション」の行間や起点や終点の間が、「残リソース」と言えると思います。
window関数のlag()lead()を使用すれば行間などの残リソースが作り出せるのではないかと思います。

ただ、込み入った仕様のようですので、考え方は同じですが、結局は「計画済タスクトランザクション」のフェッチループ内で色んな判定を行うストアドになりそうな気はします。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/09/22 10:17

    ご回答ありがとうございます。

    window関数はほとんど無知でした。(知らずにROW_NUMBER()を使用していた程度)
    提示いただいた関数を調べてみましたが、前後の行のデータも参照できるとは思いませんでした。
    参考にさせていただき、Viewで可能か引き続き検討いたします。

    ご察しのとおり、ストアドで残リソースや作業可能時間を算出していき、引数から渡された作業時間等から取得した必要リソースと作業時間よりも大きくなったときだけ結果を返すようなやり方の方が単純にできるのか?と思案しているところです。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.53%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる