【SQL】【Oracle】自己結合したテーブルからデータを取得した時に、一つしかないはずのデータが重複する。

Question

前提・実現したいこと

　お疲れ様です。

■人情報が入っているテーブルからその人名と、その人が休暇を取得した場合に、
休暇情報が入ったテーブルから休暇行使日を取得した休暇別に取得したいです。
加えて、上詰めで表示で来てたら尚うれしいです。
最悪上詰めで無くてもよいです。

【発生している問題点】
・本来は入っていない重複したデータが取得されてしまいます。
実際に取得されたイメージを下に掲載いたしました。

【テーブルの構成】
「person」

・人情報が入っているテーブル＝「person」のカラムはそれぞれ、
person_id int primary key
person_code varchar2(10) not null
person_name varchar2(50) not null
で、入っているデータは仮に、

person_id	person_code	person_name
1	001	A
2	002	B
3	003	C

とします。

「holiday」

・休暇の取得情報が入っているテーブル＝「holiday」のカラムはそれぞれ、
holiday_id int primary key
person_code varchar2(10) not null
holiday_code varchar2(4) not null
holiday_name varchar2(30) not null
used_date char(10 char)
で、

今回問題が発生したデータが以下、
|holiday_id|person_code|holiday_code|holiday_name|used_date|
|:--:|:--:|:--:|:--:|
|1|001|1000|代休|2020/10/01|
|1|001|1000|代休|2020/10/02|
|1|001|2000|有給|2020/10/03|
|1|001|1000|怪我病欠|2020/10/04|
|2|002|2000|有給|2020/10/01|
|2|002|1000|代休|2020/10/03|
|2|002|1000|代休|2020/10/05|
|3|003|3000|怪我病欠|2020/10/03|
|3|003|3000|怪我病欠|2020/10/04|
|3|003|3000|怪我病欠|2020/10/05|

とします。

これら二つのテーブルを、取得したい休暇情報分自己結合したのち、以下のようなイメージでデータを取得したいと考えております。

person_name	代休	有休	怪我病欠
A	2020/10/01	2020/10/03	2020/10/04
A	2020/10/02	null	null
B	2020/10/03	2020/10/01	null
B	2020/10/04	null	null
C	null	null	2020/10/06
C	null	null	2020/10/07
C	null	null	2020/10/08

しかし実際に出てきたデータがこちら

person_name	代休	有休	怪我病欠
A	2020/10/01	2020/10/03	2020/10/04
A	2020/10/02	2020/10/03	2020/10/04
B	2020/10/03	2020/10/01	null
B	2020/10/04	2020/10/01	null
C	null	null	2020/10/03
C	null	null	2020/10/04
C	null	null	2020/10/05

太字の個所のように、本来はない重複データが取得されてしまいます。
原因もさっぱりわからず、これを実現するためにどのようにネットでサーチしたらよいかもさっぱりでして、
また助けていただけるとありがたいです。

試したこと

前回の質問の時に教えていただいた、下のクエリで集計いたしました。

SELECT p.person_name
     , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠
FROM  PERSON p
      left JOIN HOLIDAY h1
      ON    h1.holiday_code='1000'
        and p.person_code = h1.person_code
      left JOIN HOLIDAY h2
      ON    h2.holiday_code='2000'
        and p.person_code = h2.person_code
      left JOIN HOLIDAY h3
      ON    h3.holiday_code='3000'
        and p.person_code = h3.person_code

その時に、クロス集計することになるだろうとはおっしゃっていただいたのですが、
調べた際に出てきたクロス集計では
「SELECT p.person_name
, h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠」
この部分をcase文（式？）で集計するというものは見かけたのですが、
システム上ここはいじることができない為、結合する部分でなんとか実現できないものかと思っております。

結合する際に、FULL OUTER JOINを用いまして、且つサブクエリで

        (SELECT 
               ROW_NUMBER() OVER(PARTITION BY person_code, holiday_code ORDER BY used_date ASC) NUM,
               person_code,
               used_date
FROM
        holiday where holiday_code ='1000') t1
ON
        person.person_code = t1.person_code
FULL OUTER JOIN
        (SELECT 
               ROW_NUMBER() OVER(PARTITION BY person_code, holiday_code ORDER BY used_date ASC) NUM,
               person_code,
               used_date
FROM
        holiday where holiday_code ='2000') t2
ON
        t1.NUM = t2.NUM
AND
        t1.person_code = t2.person_code
※以下同様にもう一テーブル結合

とやってみたところ、今回のケースの場合うまくいくのですが、

where holiday_code ='1000'

の部分が例えば

where holiday_code ='1001'

のように実際にないデータ、または休暇を取っている人がおらずにデータがない場合等、データを取得できない場合に結合できない為か、
その列だけすべてnullになればよいものが、データがぐちゃぐちゃになって集計されてしまいます。

データが問題が起きている現場のものと一緒でない為、もしかするとここに書いた情報だけでは特定できないということもあるかもしれませんが、可能性だけでもご教授いただけたら大変ありがたいです。

尚前回ご指摘いただいた修正依頼でデータをインサートする部分も記述せよとのことだったのですが、
私が現場に入った時からこの状態でしてどのようにクエリが発行されたかはわかりませんでした、、、

以上です。

Comments

[szk.]

自己結合した表のイメージではなく明文化してください。
特に何で集約されたものかは必要。
（イメージされたものに明確な決まりが無いように見えます。）

データを見た感覚ですが、代休／有給／怪我病欠を同じ日で登録できるシステムに問題があるように感じます。
person_codeとused_dateでUKを貼ればいい気がしますが、、、

[maru-omochi]

すみません取り急ぎデータを修正いたしました。
おっしゃる通りで重複してデータは登録できないのが正解です。
私の記述ミスでした。
尚今回ローカルDBにてテストできるように新たにテーブルを作成しましてそれに基づきデータを修正しなおしました。最初のものと比べると大幅に変更されています。

ほかにも修正しなければいけないことがあるのですが取り急ぎデータの修正を行いました。
ご指摘ありがとうございました。

Answer 1

実際結合する前のテーブルでは　A　の　有休　は１日しかなかったはずなのに、二つに増えてしまいました。

結合をperson_codeのみで行っているのだから、当然そうなります。
希望する形するには、結合する条件を作り出す必要があります。

内容を見ると、person_code, holiday_code毎にused_date順になっていれば良さそうなので、row_number()を用いて連番を作ります。

それを**person **と上記の連番を付加したものと結合する為に、連番付加したものを集計したものを用います。

冗長になるので、withを用いてますが、

「SELECT p.person_name, h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠」
この部分をcase文（式？）で集計するというものは見かけたのですが、
システム上ここはいじることができない為

良く分からない縛りがあるようですので、抵触するようならwith部分を展開して下さい。

with hseq as (
  select holiday.*
       , row_number() over(partition by person_code,holiday_code order by used_date) as seq
  from holiday 
), h0 as (
  select person_code, seq
  from  hseq
  group by person_code, seq
)
select p.person_name
     , h1.used_date 代休, h2.used_date 有給, h3.used_date 怪我病欠
from  person p
      left join h0 
      on    p.person_code = h0.person_code
      left join hseq h1
      on    h1.holiday_code='1000'
        and h0.person_code = h1.person_code
        and h0.seq = h1.seq
      left join hseq h2
      on    h2.holiday_code='2000'
        and h0.person_code = h2.person_code
        and h0.seq = h2.seq
      left join hseq h3
      on    h3.holiday_code='3000'
        and h0.person_code = h3.person_code
        and h0.seq = h3.seq
order by p.person_code, h0.seq

Comments

[maru-omochi]

前回同様今回も迅速に回答いただき誠にありがとうございます！
今回もおかげさまで希望通りに集計することができました。本当に感謝です！
まだいまいち内容を消化しきれていないのですが、いただいたコードを元にしっかりと勉強して吸収したいと思います。

[sazi]

縦を横にするのがクロス表で、それを集計したものがクロス集計です。
出力が最大や最小の日付なら、クロス表を経由せずに集計すれば良いだけでしたが、クロス表のままの出力というのが面倒な点です。

[maru-omochi]

そういうことなのですね。承知いたしました。
ようやくなんとなくわかってきたような気がしています。
いろいろテストをして落とし込んでいきたいと思います。いつもありがとうございます。感謝です。

[sazi]

連番で集計していますからクロス集計と言えなくもないですね。

[maru-omochi]

すみません通知が届いておらず気が付きませんでした。
だいぶわかってきたと思うのですが、まだまだ自在に使いこなせるという状態ではないのでもっと使い込んで落とし込んでいきたいと思います。