C# と VB.NET の質問掲示板

ASP.NET、C++/CLI、Java 何でもどうぞ

C# と VB.NET の入門サイト

Re[3]: Oracleの検索SQLについて


(過去ログ 149 を表示中)

[トピック内 5 記事 (1 - 5 表示)]  << 0 >>

■86776 / inTopicNo.1)  Oracleの検索SQLについて
  
□投稿者/ TT_R25 (1回)-(2018/03/14(Wed) 17:13:40)

分類:[データベース全般] 

開発環境
OraceleServer 12c Release 12.1.0.1.0
OracleClient  11g Release 11.2.0.1.0
Windows7 32bit

以下のようなテーブルがあり、
同一の社員、作業日で開始時刻〜終了時刻の範囲が被るデータを検索したいのですが、
日付をまたいでいることもあり、方法が分かりませんでした。
どなたかアドバイスをお願い致します。

テーブル名(T_KINTAI)IDはNUMBER型、それ以外はVARCHAR2型となっています。
ID | 社員CODE | 作業日  |開始時間|終了時間
  1|A001      |20180301 |0100    |0800
  2|A001      |20180301 |2200    |0800
  3|A001      |20180301 |2300    |0100
  4|A002      |20180301 |0845    |1715
  5|A002      |20180301 |1710    |2200
  6|A003      |20180301 |0845    |1830
  7|A003      |20180301 |2200    |0400

検索結果
ID | 社員CODE | 作業日  |開始時間|終了時間
  2|A001      |20180301 |2200    |0800
  3|A001      |20180301 |2300    |0100
  4|A002      |20180301 |0845    |1715
  5|A002      |20180301 |1710    |2200

ID3の開始時間、終了時間共にID2の開始時間、終了時間の範囲内なので出力対象
ID5の開始時間がID4の開始時間、終了時間の範囲内なので出力対象

引用返信 編集キー/
■86777 / inTopicNo.2)  Re[1]: Oracleの検索SQLについて
□投稿者/ 魔界の仮面弁士 (1589回)-(2018/03/14(Wed) 18:23:21)
No86776 (TT_R25 さん) に返信
> ID | 社員CODE | 作業日  |開始時間|終了時間
>   1|A001      |20180301 |0100    |0800

0100 〜 0800 というデータが意味するところが
01:00:00〜08:00:59 なのか
01:00:00〜08:00:00 なのか
01:00:00〜07:59:59 なのか
いわゆる以上/超過、以下/未満の判断が曖昧ですが、
この辺は後で考えて頂くとして。


> 日付をまたいでいることもあり、方法が分かりませんでした。
開始終了いずれかが NULL となることがあるのか?
開始時間と終了時間が同一の場合に、0分扱いとするのか 24時間扱いとするのか?
そもそも48時間超過は無いのか?

…などなど、細かい仕様が曖昧だったりしますが、今回はとりあえず
「開始時間≧終了時間」なら翌日終了扱いということで考えてみました。


WITH KINTAI1 AS (
  SELECT
    T_KINTAI.*,
    TO_DATE(作業日 || 開始時間, 'YYYYMMDDHH24MISS') AS 開始日時,
    TO_DATE(作業日 || 終了時間, 'YYYYMMDDHH24MISS')
    + CASE WHEN 開始時間 >= 終了時間 THEN 1 ELSE 0 END AS 終了日時
  FROM
    T_KINTAI
), KINTAI2 AS (
  SELECT
    KINTAI1.*,
    LAG (終了日時, 1, NULL) OVER (PARTITION BY 社員CODE, 作業日 ORDER BY 開始時間, 終了時間, ID) AS 直前の終了日時,
    LEAD(開始日時, 1, NULL) OVER (PARTITION BY 社員CODE, 作業日 ORDER BY 開始時間, 終了時間, ID) AS 直後の開始日時
  FROM
    KINTAI1
) SELECT ID, 社員CODE, 作業日, 終了時間 FROM KINTAI2
WHERE 開始日時 < 直前の終了日時 OR 直後の開始日時 < 終了日時
ORDER BY ID

引用返信 編集キー/
■86782 / inTopicNo.3)  Re[2]: Oracleの検索SQLについて
□投稿者/ TT_R25 (2回)-(2018/03/15(Thu) 11:59:46)
魔界の仮面弁士様

数々の説明不足の中、回答いただきありがとうございました。
実行してみたところ、思い通りの結果となりました。

非常に助かりました。
ありがとうございました。


解決済み
引用返信 編集キー/
■86783 / inTopicNo.4)  Re[2]: Oracleの検索SQLについて
□投稿者/ furu (161回)-(2018/03/15(Thu) 12:47:40)
No86777 (魔界の仮面弁士 さん) に返信

魔界の仮面弁士さん、勉強になります。

TT_R25さん
解決済みですが、同じ日に3件以上あった場合にも
対応できるSQLを考えてみました。

ID | 社員CODE | 作業日  |開始時間|終了時間
 11|A011      |20180301 |1000    |1600
 12|A011      |20180301 |1100    |1200
 13|A011      |20180301 |1300    |1400

WITH KINTAI1 AS (
  SELECT
    T_KINTAI.*,
    TO_DATE(作業日 || 開始時間, 'YYYYMMDDHH24MISS') AS 開始日時,
    TO_DATE(作業日 || 終了時間, 'YYYYMMDDHH24MISS')
    + CASE WHEN 開始時間 >= 終了時間 THEN 1 ELSE 0 END AS 終了日時
  FROM
    T_KINTAI
)
SELECT ID, 社員CODE, 作業日, 開始時間, 終了時間
FROM KINTAI1 K
WHERE exists (
    select * from KINTAI1 M
    where K.ID <> M.ID and
          K.社員CODE = M.社員CODE and 
          K.作業日 = M.作業日 and
          K.開始日時 <= M.終了日時 and K.終了日時 >= M.開始日時)
order by ID

解決済み
引用返信 編集キー/
■86784 / inTopicNo.5)  Re[3]: Oracleの検索SQLについて
□投稿者/ 魔界の仮面弁士 (1590回)-(2018/03/15(Thu) 13:56:29)
先の SQL に問題が見つかったので、
あえて 解決済み フラグを外しておきます。


■No86782 (TT_R25 さん) に返信
> 数々の説明不足の中、回答いただきありがとうございました。

重複期間の検出にあたり、
 2018/03/01 05:00〜同12:00
 2018/03/01 12:00〜同15:30
のようなパターンを拾うべきかどうかが分からなかったです。



■No86783 (furu さん) に返信
> 解決済みですが、同じ日に3件以上あった場合にも
> 対応できるSQLを考えてみました。

おぉ、ご指摘感謝。

再考してみたところ、作業日 は繋ぐべきではない気がしてきました。
下記の ID=10 のような、日またぎの重複も検出するべきかと思うので。

ID|社員CODE| 作業日 |開始時間|終了時間
 1|A001    |20180301|0100    |0800
 2|A001    |20180301|2200    |0800
 3|A001    |20180301|2300    |0100
 4|A002    |20180301|0845    |1715
 5|A002    |20180301|1710    |2200
 6|A003    |20180301|0845    |1830
 7|A003    |20180301|2200    |0400
 8|A003    |20180302|0400    |0600
 9|A010    |20180302|0400    |0600
10|A011    |20180228|2000    |1030
11|A011    |20180301|1000    |1600
12|A011    |20180301|1100    |1200
13|A011    |20180301|1300    |1400
14|A011    |20180302|1100    |1500
15|A011    |20180302|1530    |1900


上記では、 ID=7 の 終了と ID=8 の開始が同時刻ですが
これは重複とみなさない方針として抽出しています。

これも重複と見做したい場合は、日時比較部の
< 演算子を <= 演算子に変更してみてください。


WITH KINTAI AS (
  SELECT
    T_KINTAI.*,
    TO_DATE(作業日 || 開始時間, 'YYYYMMDDHH24MISS') AS 開始日時,
    TO_DATE(作業日 || 終了時間, 'YYYYMMDDHH24MISS')
    + CASE WHEN 開始時間 >= 終了時間 THEN 1 ELSE 0 END AS 終了日時
  FROM
    T_KINTAI
)
SELECT ID, 社員CODE, 作業日, 開始時間, 終了時間
FROM KINTAI K
WHERE EXISTS (
    SELECT * FROM KINTAI M
    WHERE K.ID <> M.ID
      AND K.社員CODE = M.社員CODE
   /* AND K.作業日 = M.作業日 */
      AND K.開始日時 < M.終了日時
      AND M.開始日時 < K.終了日時
) ORDER BY ID

引用返信 編集キー/


トピック内ページ移動 / << 0 >>

このトピックに書きこむ

過去ログには書き込み不可

管理者用

- Child Tree -