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

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

新規登録して質問してみよう
ただいま回答率
85.50%
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の機能拡張版です。

Q&A

解決済

1回答

759閲覧

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

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の機能拡張版です。

0グッド

0クリップ

投稿2018/09/20 17:04

編集2018/09/22 01:07

前提・実現したいこと

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

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

現在は、上記の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所有リソース必要リソース日時
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

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日時残リソース
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

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開始日時終了日時残リソース作業可能時間
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

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

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に修正しました。
guest

回答1

0

ベストアンサー

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

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

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

投稿2018/09/21 05:23

編集2018/09/21 05:30
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

locoJr.

2018/09/22 01:17

ご回答ありがとうございます。 window関数はほとんど無知でした。(知らずにROW_NUMBER()を使用していた程度) 提示いただいた関数を調べてみましたが、前後の行のデータも参照できるとは思いませんでした。 参考にさせていただき、Viewで可能か引き続き検討いたします。 ご察しのとおり、ストアドで残リソースや作業可能時間を算出していき、引数から渡された作業時間等から取得した必要リソースと作業時間よりも大きくなったときだけ結果を返すようなやり方の方が単純にできるのか?と思案しているところです。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問