5873


新人SEの初投稿のため、拙い文章お許しください。

### テーブル定義

担当案件ではDBにOracle11gR2を使っています。

マスタとして下記の2テーブルを持っております。

  * 項目は全てvarchar2で定義されています。
  * どちらも"ユーザ"、"期間コード"が主キーです。

アカウント情報

[code]

    ユーザ   期間コード   開始日   　　　　  　　終了日
    A00001  bbbbbbbbb   2014/04/01|00:00:00 9999/12/31|23:59:59
    A00002  cccccccc    2012/04/01|00:00:00 9999/12/31|23:59:59
    A00003  ddddddddd   2011/01/01|00:00:00 9999/12/31|23:59:59
    A00004  eeeeeeee    2014/11/18|00:00:00 9999/12/31|23:59:59
    
[/code]

所属情報

[code]

    ユーザ   期間コード 組織コード   開始日 　　　　      終了日
    A00001  xxxxxxxxxx  A100    2014/04/01|00:00:00 2014/11/19|00:00:00
    A00002  yyyyyyyyyy  B200    2014/01/01|00:00:00 9999/12/31|23:59:59
    A00002  zzzzzzzzzz  C300    2012/04/01|00:00:00 2014/01/01|00:00:00
    A00003  aaaaaaaaaa  D400    2014/04/01|00:00:00 9999/12/31|23:59:59
    
[/code]

アカウント情報にはシステムに登録済のユーザとその有効期間が入っています。

所属情報にはいつからいつまでどの組織にいたという情報が入っています。

  * ユーザは組織に所属していない期間もあります。
  * 1ユーザが同じ組織に期間が分断して所属していることもあります。
  * 2012年、2014年は所属組織があるが2013年だけないというユーザも存在します。

### 実現したいこと

上記の情報から、所属組織が設定されていないユーザ＋その設定されていない期間を一覧形式で取得したいです。

理想的にはPL/SQL、ストアドプロシージャあるいはテーブルをExcelに出力し、VBA等で実行させたいです。

### 出力例

テーブルの内容が上記例だった場合には以下のような一覧を取得したいです。

[code]

    ユーザ　所属なし期間
    A00001　2014/11/19～9999/12/31
    A00003　2011/1/1～2014/3/31
    A00004　2014/11/18～9999/12/31
    
[/code]

A00001の場合

[code]

    A00001（2014/4/1～9999/12/31）アカウントON
    A100に　2014/4/1～2014/11/18　所属
    　⇒2014/11/19以降が所属なしとして抽出対象
    
[/code]

A00002の場合

[code]

    A00002（2012/4/1～9999/12/31）アカウントON
    B200に　2012/4/1～2013/12/31　所属
    C300に　2014/1/1～　所属
    　⇒アカウントON期間に全て所属組織が設定されているため、抽出対象にならない
    
[/code]

A00003の場合

[code]

    A00003（2011/1/1～9999/12/31）アカウントON
    D400に　2014/4/1～　所属
    　⇒2014/4/1以前が所属なしとして抽出対象
    
[/code]

A00004の場合

[code]

    A00004（2014/11/18～9999/12/31）アカウントON
    所属組織設定なし（所属情報テーブルにレコードなし）
    　⇒2014/11/18～9999/12/31が所属なしで抽出される
    
[/code]

実現方法をご教示いただけると大変助かります。  
よろしくお願いします。

2015/2/4 追記

編集、ご回答頂きありがとうございます。

パターンとして下記が漏れておりました。  
・所属情報が歯抜けになっているパターン  
⇒所属情報テーブルの3行目が

A00002 zzzzzzzzzz C300 2012/04/01|00:00:00 2014/01/01|00:00:00

ではなく

A00002 zzzzzzzzzz C300 2012/04/01|00:00:00 2013/01/01|00:00:00

のような場合  
・1ユーザに対するアカウント情報が複数レコードあるパターン

A00004 eeeeeeee1 2014/01/01|00:00:00 2014/11/17|23:59:59  
A00004 eeeeeeee2 2014/11/18|00:00:00 9999/12/31|23:59:59


CC BY-SA 3.0
2015-02-03T08:08:50.817
5841
2015-02-04T08:01:51.613
2015-02-04T08:01:51.613
8056
8056
question
sql oracle
所属情報を持たないユーザとその期間を抽出する方法


データ的には以下の4パターンですかね。

  * 所属情報が全くないユーザ
  * アカウント情報の開始日と所属情報の開始日が一致しないユーザ(終了日も同じ)
  * 所属情報の2レコードの隙間が空いているレコード

所属情報が無い場合は`NOT IN`で抽出できます。

[code]

    SELECT t1.ユーザ
         , t1.開始日
         , t1.終了日
      FROM アカウント情報 t1
     WHERE t1.ユーザ NOT IN (SELECT t2.ユーザ FROM 所属情報 t2)
    
[/code]

開始日が一致しているかどうかはグループ化で所属情報の最小値と比較すればよいと思います。

[code]

    SELECT t1.ユーザ
         , t1.開始日
         , MIN(t2.開始日) 所属開始日
      FROM アカウント情報 t1
     INNER JOIN 所属情報 t2
        ON t1.ユーザ = t2.ユーザ
     GROUP BY
           t1.ユーザ
         , t1.開始日
    HAVING t1.開始日 < MIN(t2.開始日)
    
[/code]

所属情報の隣り合うレコードを比較するには分析関数で隣接レコードの値を確認できます。

[code]

    SELECT ユーザ
         , 終了日
         , 次の開始日
      FROM
         (
        SELECT ユーザ
             , TO_DATE(終了日, 'yyyy/mm/dd|hh24:mi:ss') 終了日
             , TO_DATE(LEAD(開始日) OVER(PARTITION BY ユーザ ORDER BY 開始日), 'yyyy/mm/dd|hh24:mi:ss') 次の開始日
          FROM 所属情報 t1
         )
     WHERE 終了日 + (1 / 24 / 60 / 60) < 次の開始日
    
[/code]

最後のSQLのみ日付型になっていますのでご注意ください


CC BY-SA 3.0
2015-02-03T11:08:48.040
5869
2015-02-03T11:08:48.040
5750
5841
answer


SQL Fiddleで実際に動かしながらSQLを作ってみました。

<http://sqlfiddle.com/#!4/fbec5/2>

### DDL

[code]

    CREATE TABLE GROUPS 
      ( 
         user_cd  VARCHAR2(100), 
         term_cd  VARCHAR2(100), 
         group_cd VARCHAR2(100), 
         start_dt TIMESTAMP, 
         end_dt   TIMESTAMP 
      ); 
    
    INSERT INTO GROUPS 
    VALUES     ('A00001', 
                'xxxxxxxxxx', 
                'A100', 
                To_timestamp('2014/04/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('2014/11/19 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ); 
    
    INSERT INTO GROUPS 
    VALUES     ('A00002', 
                'yyyyyyyyyy', 
                'B200', 
                To_timestamp('2014/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('9999/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS') ); 
    
    INSERT INTO GROUPS 
    VALUES     ('A00002', 
                'zzzzzzzzzz', 
                'C300', 
                To_timestamp('2012/04/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('2014/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS') ); 
    
    INSERT INTO GROUPS 
    VALUES     ('A00003', 
                'aaaaaaaaaa', 
                'D400', 
                To_timestamp('2014/04/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('9999/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS') ); 
    
    CREATE TABLE users 
      ( 
         user_cd  VARCHAR2(100), 
         term_cd  VARCHAR2(100), 
         start_dt TIMESTAMP, 
         end_dt   TIMESTAMP 
      ); 
    
    INSERT INTO users 
    VALUES     ('A00001', 
                'bbbbbbbbb', 
                To_timestamp('2014/04/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('9999/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS') ); 
    
    INSERT INTO users 
    VALUES     ('A00002', 
                'cccccccc', 
                To_timestamp('2012/04/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('9999/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS') ); 
    
    INSERT INTO users 
    VALUES     ('A00003', 
                'ddddddddd', 
                To_timestamp('2011/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('9999/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS') ); 
    
    INSERT INTO users 
    VALUES     ('A00004', 
                'eeeeeeee', 
                To_timestamp('2014/11/18 00:00:00', 'YYYY/MM/DD HH24:MI:SS'), 
                To_timestamp('9999/12/31 23:59:59', 'YYYY/MM/DD HH24:MI:SS') ); 
    
[/code]

### SELECT文

[code]

    WITH view_1 
    AS (SELECT u.user_cd, 
               Max(u.start_dt) AS user_start_dt, 
               Max(u.end_dt)   AS user_end_dt, 
               Min(g.start_dt) AS min_group_start_dt, 
               Max(g.end_dt)   AS max_group_end_dt 
        FROM   users u 
               left outer join GROUPS g 
                            ON g.user_cd = u.user_cd 
        GROUP  BY u.user_cd), 
    view_2 
    AS (SELECT user_cd, 
               CASE 
                 WHEN max_group_end_dt < user_end_dt THEN max_group_end_dt 
                 WHEN user_start_dt < min_group_start_dt THEN user_start_dt 
                 WHEN min_group_start_dt IS NULL THEN user_start_dt 
                 ELSE NULL 
               END AS free_start_dt, 
               CASE 
                 WHEN max_group_end_dt < user_end_dt THEN user_end_dt 
                 WHEN user_start_dt < min_group_start_dt THEN 
                 min_group_start_dt - 
                 interval '1' day 
                 WHEN max_group_end_dt IS NULL THEN user_end_dt 
                 ELSE NULL 
               END AS free_end_dt 
        FROM   view_1) 
    SELECT user_cd, 
           To_char(free_start_dt, 'YYYY/MM/DD') AS free_start_dt, 
           To_char(free_end_dt, 'YYYY/MM/DD')   AS free_end_dt 
    FROM   view_2 
    WHERE  free_start_dt IS NOT NULL 
           AND free_end_dt IS NOT NULL 
    ORDER  BY user_cd 
    
[/code]

WITH句はCTEと呼ばれたりするものです。  
SELECT文の実行結果を違うSELECT文から別名で参照できるので結構便利です。

<http://www.shift-the-oracle.com/sql/with.html>

一応、例題の通りの結果が出ていますが、もっと複雑なパターンが来ると期待した結果にならないかもしれません。（たとえば無所属の期間がユーザーごとに複数回発生したりすると期待した結果にならないかも。ただし、解答パターンが提示されていないので推測でさらに凝ったSQLを考えるのはやめておきます）

とりあえず、ご参考までに。

## 追記

pgrhoさんの使っていたLEAD関数が面白そうだったので、解答例その2を作ってみました。  
その1と比べてわかりやすいかどうかはちょっと微妙ですが。。

<http://sqlfiddle.com/#!4/fbec5/36>

### SELECT文

[code]

    -- 期間の情報を並列に並べる
    WITH histories 
    AS (SELECT user_cd, 
               start_dt, 
               end_dt, 
               NULL AS group_cd 
        FROM   users 
        UNION ALL 
        SELECT user_cd, 
               start_dt, 
               end_dt, 
               group_cd 
        FROM   GROUPS), 
    -- 前後の所属情報を取得する
    view_1 
    AS (SELECT user_cd, 
               start_dt, 
               end_dt, 
               group_cd, 
               Lag(end_dt) over(PARTITION BY user_cd ORDER BY start_dt, end_dt) AS prev_end_dt, 
               Lag(group_cd) over(PARTITION BY user_cd ORDER BY start_dt) AS prev_group_cd, 
               Lead(start_dt) over(PARTITION BY user_cd ORDER BY start_dt, end_dt) AS next_start_dt, 
               Lead(group_cd) over(PARTITION BY user_cd ORDER BY start_dt, end_dt) AS next_group_cd 
        FROM   histories), 
    -- 無所属期間を割り出す
    view_2 
    AS (SELECT user_cd, 
               CASE 
                 WHEN prev_group_cd IS NOT NULL THEN prev_end_dt 
                 ELSE start_dt 
               END AS free_start_dt, 
               CASE 
                 WHEN next_group_cd IS NOT NULL THEN next_start_dt - interval '1' day 
                 ELSE end_dt 
               END AS free_end_dt 
        FROM   view_1 
        WHERE  group_cd IS NULL 
               AND (prev_group_cd IS NULL 
                    OR next_group_cd IS NULL)) 
    -- 見やすく整形する
    SELECT user_cd, 
           To_char(free_start_dt, 'YYYY/MM/DD') AS start_dt, 
           To_char(free_end_dt, 'YYYY/MM/DD')   AS end_dt 
    FROM   view_2 
    ORDER  BY user_cd, 
              free_start_dt 
    
[/code]

LEAD関数やLAG関数についてはこちらのサイトの説明が役立ちそうです。

<http://www.atmarkit.co.jp/ait/articles/0511/26/news010_2.html>

> 理想的にはPL/SQL、ストアドプロシージャあるいはテーブルをExcelに出力し、VBA等で実行させたいです。

質問がざっくりしすぎているので具体的なコードは思いつきませんが、このサイトに載っている情報が参考になるかもしれません。（2005年の記事なので今でも有効か若干不安なところもありますが）

<http://www.atmarkit.co.jp/ait/articles/0508/06/news016.html>

あと、SQLが大きくなってしまうので、ストアドにしておいた方がVBAがスッキリしますし、VBA以外の場所でもクエリを再利用しやすくなります。

PL/SQLとExcel VBAを連携させる方法はこのあたりの記事が参考になりそうです。

  * <http://www.brynthomas.com/examples/ADODemo.html>
  * <https://stackoverflow.com/questions/4558714/how-do-i-call-an-oracle-stored-procedure-from-an-excel-vba-script>

ストアドを作る方法以外だと、このSQLからVIEWを作っておくとVBAからシンプルなSQLで結果を取り出せるようになりますね。


CC BY-SA 3.0
2015-02-03T11:26:39.047
5873
2015-02-03T23:22:18.203
2017-05-23T12:38:55.250
-1
85
5841
answer