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

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

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

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

Q&A

解決済

3回答

25529閲覧

SQLで1レコードを複数レコードとして抽出したい

yamakawa03

総合スコア20

SQL Server

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

0グッド

1クリップ

投稿2017/08/29 05:17

SQLで1レコードを複数レコードとして抽出したい

例えばスケジュールを管理しているテーブルとして

期間を保持しているテーブルA
(場所AAAは9:00から18:00時まで開いている)

日付場所CD開始時刻終了時刻
2017/09/01AAA9:0018:00

予定を保持しているテーブルB
(担当者001は場所AAAを10:00から12:00まで利用する。担当者002は場所AAAを14:00から17:00まで利用する。)
|日付|場所CD|担当者CD|開始時刻|終了時刻|
|:--|:--|:--|:--|
|2017/09/01|AAA|001|10:00|12:00|
|2017/09/01|AAA|002|14:00|17:00|

とあった場合
SQLのみで以下のような、場所が空いているデータを取得できますでしょうか?

日付場所CD開始時刻終了時刻
2017/09/01AAA9:0010:00
2017/09/01AAA12:0014:00
2017/09/01AAA17:0018:00

※DBはSQL Serverを使用していおります。

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

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

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

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

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

momon-ga

2017/08/29 07:13

予定の時間の刻みは固定ですか?(30分単位、1時間単位など)
yamakawa03

2017/08/29 07:20

設定ファイルで変更可能ですが、設定後は固定です。基本30分単位です。
guest

回答3

0

うーん、CTEを使ってがんばって出してみましたが、かなり冗長な気がします……。
もっと良いやり方がありそうです。
※日付と場所CDは無視しているので、適宜修正してください

sql

1/* データをとりあえずテーブル変数で用意 */ 2declare @tblA table ( 3 日付 date, 4 場所CD char(3), 5 開始時刻 time, 6 終了時刻 time 7) 8insert into @tblA values('2017-09-01','AAA','09:00:00','18:00:00') 9 10declare @tblB table ( 11 日付 date, 12 場所CD char(3), 13 担当者CD char(3), 14 開始時刻 time, 15 終了時刻 time 16) 17insert into @tblB values('2017-09-01','AAA','001','10:00:00','12:00:00') 18insert into @tblB values('2017-09-01','AAA','002','14:00:00','17:00:00') 19 20; 21/* opentime:利用可能時間帯を30分刻みで列挙 */ 22with opentime as ( 23 select 24 開始時刻 時刻 25 from 26 @tblA 27 union all 28 select 29 dateadd(minute,30,時刻) 時刻 30 from 31 opentime 32 where 33 時刻<(select dateadd(minute,-30,終了時刻) from @tblA) 34), 35/* opentime2:利用中の時間帯を除外した結果 */ 36opentime2 as ( 37 select 38 O.時刻, 39 case when lead(O.時刻,1) over(order by O.時刻)=dateadd(minute, 30,O.時刻) then 1 else 0 end f, 40 case when lag(O.時刻,1) over(order by O.時刻)=dateadd(minute,-30,O.時刻) then 1 else 0 end t 41 from 42 opentime O 43 where 44 not exists ( 45 select * from @tblB B 46 where 47 B.開始時刻<=O.時刻 and O.時刻<B.終了時刻 48 ) 49), 50/* opentime3:開始と終了のみを抽出する準備 */ 51opentime3 as ( 52 select 53 O2.時刻 開始時刻, 54 case when t=0 then lead(O2.時刻,1) over(order by O2.時刻) else null end 次開始時刻 55 from opentime2 O2 56 where O2.f=0 or O2.t=0 57) 58/* 最終結果 */ 59select 60 O3.開始時刻, 61 dateadd(minute,30,O3.次開始時刻) 終了時刻 62from opentime3 O3 63where 64 O3.次開始時刻 is not null

実行結果:

開始時刻終了時刻
09:00:00.000000010:00:00.0000000
12:00:00.000000014:00:00.0000000
17:00:00.000000018:00:00.0000000

投稿2017/08/29 10:13

編集2017/08/29 10:14
alg

総合スコア2019

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

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

maiko0318

2017/08/29 10:29

作るのはいいんですが、誰がメンテするんでしょう? メンテする人の頭の良さまで計算に入っていますか? 変更都度、ここに来ることになりますね。
sazi

2017/08/30 02:03 編集

条件として日付と場所は無視しちゃ駄目ですよ。 と言いつつ、私も最初はサンプルデータだけ見て漏らしていましたがw
yamakawa03

2017/08/30 01:59

algさん回答ありがとうございます。 こちらで動作確認したところ、期待した結果になりました。 少々難しく感じましたが、lead()やlag()などを利用しているのが勉強になります。
guest

0

ベストアンサー

こんな感じでどうでしょうか。
概略としては、
・テーブルAからその日の予定の開始~終了以外の両端の空き時間を求める。
・テーブルBから、lag()によって前回の終了時刻を求め、それを空の開始時刻、開始時刻を終了時刻とする。
(最初の空き時間はテーブルAから求めているので、NUll判定で除去)
・上記をUNIONでマージ(予定が無い場合はUNIONでマージされて、結果、テーブルAと同じになる)

SQL

1 select 日付, 場所CD 2 , 開始時刻 3 , coalesce((select min(開始時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付), 終了時刻) as 終了時刻 4 from tbla as ta 5 where 開始時刻<((select min(開始時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付)) 6union 7 select 日付, 場所CD, 前回終了時刻, 開始時刻 8 from (select *, lag(終了時刻,1) over(partition by 場所CD, 日付 order by, 開始時刻) as 前回終了時刻 from tblb) tmp 9 where 前回終了時刻 is not null 10union 11 select 日付, 場所CD 12 , coalesce((select max(終了時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付), 開始時刻) 13 , 終了時刻 14 from tbla as ta 15 where 終了時刻>((select max(終了時刻) from tblb where 場所CD=ta.場所CD and 日付=ta.日付)) 16order by 場所CD,日付,開始時刻

手っ取り早くunionにしていますが、時間がかかるようなら除外条件(予定が無い場合)をいれて、union allとすれば、unionマージの時間は省けます。

投稿2017/08/30 01:25

編集2017/08/30 04:05
sazi

総合スコア25138

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

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

yamakawa03

2017/08/30 02:02

saziさん回答ありがとうございます。 SQL一発で取得はあきらめていたのですが、こちらのSQLは分かりやすく、期待通りの結果となっております。 チューニングも考慮に入れて頂きありがとうございます。
sazi

2017/08/30 02:38

開始および終了が使用できる時間ぴったりの場合の考慮が漏れていたので、修正しています。
guest

0

2017/09/01 AAA 9:00 18:00
と入力されたら(場所や日付は複数可)30分(設定を見る)ごとにデータを作る。

2017/09/01 AAA 001 10:00 12:00
と入力されたら指定区間のデータに印(使用中フラグ)をつける。

で、使用中フラグの付いていないデータを抜き出せばOK。

画面は作らねばなりませんがデータの整合性の問題もありますし(データの手入力なら
10分単位でも開始>終了など何でもありになってしまう)この方がいいかと。

投稿2017/08/29 09:14

maiko0318

総合スコア876

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

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

yamakawa03

2017/08/30 01:53

maiko0318さん回答ありがとうございます。 確かに、別のコメントにある通り、保守のことを考えると、素直なソースの方が保守しやすいですね。 今後の参考にさせていただきます。
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

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

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

質問する

関連した質問