オラクルで取得したいデータのSQL文がわかりません

Question

Access2019 VBAでオラクルDB（11g?）に接続しています。
取得したいデータのSQL文がわからず質問させてください。

・テーブルAにカラム「ID」、「区分」、「日付」があります。
・カラム「区分」は「開始」、「終了」があります。

テーブルA
ID　区分　日付
1　　開始　2020/01/01
1　　終了　2020/02/01
1　　開始　2020/03/01
1　　終了　2020/04/01
2　　開始　2020/01/01
2　　終了　2020/03/01
2　　開始　2020/04/01

結果
ID　開始日　　　　終了日
1　　2020/01/01　2020/02/01
1　　2020/03/01　2020/04/01
2　　2020/01/01　2020/03/01
2　　2020/04/01

※↑オラクルDBを参照する環境しかないのでSQL文は控させてください。

と「ID」「開始日」に対し直近の「終了日」を取得するSQL文はどうなるのでしょうか？

どうググるかわからず質問することにしました。。

↓なんとなく考えたSQL文です。最適かはわからなですが。。

SELECT
　ID,
　日付 開始日,
　(SELECT
　　 MIN(日付)
　 FROM
　　 テーブルA
　 WHERE
　　 区分 = '終了' AND A.日付 =< 日付 AND A.ID = ID
　) 終了日
FROM
 テーブルA A
WHERE
 区分 = '開始'

よろしくお願いいたします。

Comments

[Orlofsky]

質問のテーブル定義はCREATE TABLE文に、テーブル中のデータはINSERT文に修正して https://teratail.com/help/question-tips#questionTips3-7 の [コード] で提示できると、動作確認し易くなるでの適切なコメントが付き易いです。Markdownの機能は覚えましょう。

[m.ts10806]

まず、自身が思ったように書いてみてください。

[aaaccc]

失礼しました。
明日、改めたいと思います。

Answer 1

リンクテーブル経由ですか？それともパススルーですか？
検索のキーワードは「SQL 直近」で。
それに加え、リンクテーブルなら「Access」を、パススルーなら「oracle」をキーワードに加えて下さい。

access/oracleどちらでも大丈夫ですが、リンクテーブルで遅いようならパススリークエリーに変更した方が良いかと思います。

select 開始.ID, 開始.日付 as 開始日, min(終了.日付) as 終了日
from  (
        select * from テーブルA where 区分='開始'
      ) as 開始
      left join (
        select * from テーブルA where 区分='終了'
      ) as 終了
      on    開始.ID = 終了.ID and 開始.日付 < 終了.日付
group by 開始.ID, 開始.日付

oracleだと分析関数が使用できるので、lag()を使用すると以下の様な記述にもできます。

select ID, 日付 as 開始日
     , lag(日付) over(partition by ID order by 日付) as 終了日
from テーブルA
where 日付='開始'

Comments

[aaaccc]

解決しました。
個人的にわかりやすかったのでベストアンサーとさせていただきました。

Answer 2

あまり美しくないですが・・・。

SELECT
    ID
,   START_DATE AS "開始日"
,   CASE WHEN MAX(END_DATE) < START_DATE THEN NULL ELSE MAX(END_DATE) END AS "終了日"
FROM
    (
    SELECT
        ID
    ,   MAX(DECODE("区分",'開始',"日付")) OVER (PARTITION BY ID ORDER BY "日付") START_DATE
    ,   MAX(DECODE("区分",'終了',"日付")) OVER (PARTITION BY ID ORDER BY "日付") END_DATE
    FROM
        "テーブルA"
    ) SUB
GROUP BY
    ID
,   START_DATE
ORDER BY
    ID
,   START_DATE

Comments

[aaaccc]

OVER句のあたりもっと勉強したいと思います。
素早い回答ありがとうございました。