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

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

新規登録して質問してみよう
ただいま回答率
86.12%
SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Transact-SQL

Transact-SQLはSybase ASEとMIcrosoft SQLサーバで対応されているSQLの機能拡張版です。

解決済

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

locoJr.
locoJr.

総合スコア15

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Transact-SQL

Transact-SQLはSybase ASEとMIcrosoft SQLサーバで対応されているSQLの機能拡張版です。

1回答

0リアクション

0クリップ

416閲覧

投稿2018/09/20 17:04

編集2018/09/22 01:07

前提・実現したいこと

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

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

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

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

検討内容

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

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

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

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

  • 開始可能日時

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

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

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

ソース

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

  • チームマスタ

lang

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

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

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

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カラムにまとめる

SQL

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所有リソース必要リソース日時
1AW1522018/09/21 10:00:00
1AW2522018/09/21 11:00:00
-1AW2522018/09/21 14:00:00
-1AW1522018/09/21 15:00:00
  • 日時毎の残リソースを算出する

lang

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日時残リソース
A2018/09/21 10:00:003
A2018/09/21 11:00:001
A2018/09/21 14:00:003
A2018/09/21 15:00:005
  • 時間帯毎の残リソースを算出する

lang

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開始日時終了日時残リソース作業可能時間
A2018/09/21 06:00:002018/09/21 10:00:00514400
A2018/09/21 10:00:002018/09/21 11:00:0033600
A2018/09/21 11:00:002018/09/21 14:00:00110800
A2018/09/21 14:00:002018/09/21 15:00:0033600

※上記クエリでは、2018/09/21 15:00:00~2018/09/22 06:00:00までの残リソースが5という情報は出力できていません。

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

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

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

SQL Server 2014

以下のような質問にはリアクションをつけましょう

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

リアクションが多い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

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

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

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

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

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

適切な質問に修正を依頼しましょう。

Orlofsky

2018/09/20 18:12

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

2018/09/20 18:13

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

2018/09/22 23:45

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

まだ回答がついていません

会員登録して回答してみよう

アカウントをお持ちの方は

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

ただいまの回答率
86.12%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問

同じタグがついた質問を見る

SQL Server

SQL Serverはマイクロソフトのリレーショナルデータベース管理システムです。データマイニングや多次元解析など、ビジネスインテリジェンスのための機能が備わっています。

SQL

SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

Transact-SQL

Transact-SQLはSybase ASEとMIcrosoft SQLサーバで対応されているSQLの機能拡張版です。