5-44 パーティションを切って分析関数

SQLパズル

顧客テーブル
顧客ID  名前   内線
------  ----   ----
   001  佐藤    001  ※同じ（これだけ残す）
   002  鈴木    002
   003  田中    003
   004  佐藤    001  ※同じ（削除）
   005  佐藤    004
   006  鈴木    005
   007  佐藤    001  ※同じ（削除）

伝票テーブル
伝票ID  顧客ID  購入物
------  ------  ------
   001     001  ガム    ※対応する顧客は残る
   002     003  チョコ
   003     004  洋菓子  ※対応する顧客が残らない
   004     006  和菓子
   005     002  お茶
   006     007  マンガ  ※対応する顧客が残らない

顧客テーブルの佐藤4人中3人は、名前も内線も同じなので
顧客IDが一番小さいレコード以外のレコード削除し、
伝票テーブルの顧客IDは、一番小さい顧客IDに更新する。

データ作成スクリプト

create Table 顧客(
顧客ID  char(3),
名前    char(4),
内線    char(3));

create Table 伝票(
伝票ID  char(3),
顧客ID  char(3),
購入物  char(6));

insert into 顧客(顧客ID,名前,内線) values('001','佐藤','001');
insert into 顧客(顧客ID,名前,内線) values('002','鈴木','002');
insert into 顧客(顧客ID,名前,内線) values('003','田中','003');
insert into 顧客(顧客ID,名前,内線) values('004','佐藤','001');
insert into 顧客(顧客ID,名前,内線) values('005','佐藤','004');
insert into 顧客(顧客ID,名前,内線) values('006','鈴木','005');
insert into 顧客(顧客ID,名前,内線) values('007','佐藤','001');
insert into 伝票(伝票ID,顧客ID,購入物) values('001','001','ガム');
insert into 伝票(伝票ID,顧客ID,購入物) values('002','003','チョコ');
insert into 伝票(伝票ID,顧客ID,購入物) values('003','004','洋菓子');
insert into 伝票(伝票ID,顧客ID,購入物) values('004','006','和菓子');
insert into 伝票(伝票ID,顧客ID,購入物) values('005','002','お茶');
insert into 伝票(伝票ID,顧客ID,購入物) values('006','007','マンガ');
commit;

SQL

--■■■分析関数を使用■■■
begin
    update 伝票 a set
    顧客ID = (select min顧客ID
              from(select b.顧客ID,
                   min(b.顧客ID) over(partition by 名前,内線) as min顧客ID
                     from 顧客 b) c
              where c.顧客ID=a.顧客ID)
    where (select min顧客ID
              from(select b.顧客ID,
                   min(b.顧客ID) over(partition by 名前,内線) as min顧客ID
                     from 顧客 b) c
              where c.顧客ID=a.顧客ID) != a.顧客ID;

    delete from 顧客 a
    where 顧客ID not in
    (select min(b.顧客ID) over(partition by b.名前,b.内線) from 顧客 b);
    commit;
end;
/

--■■■相関サブクエリを使用■■■
begin
    update 伝票 a set
    顧客ID = (select min(b.顧客ID) from 顧客 b
               where (b.名前,b.内線) = (select c.名前,c.内線 from 顧客 c
                                         where c.顧客ID=a.顧客ID))
      where  (select min(b.顧客ID) from 顧客 b
               where (b.名前,b.内線) = (select c.名前,c.内線 from 顧客 c
                                         where c.顧客ID=a.顧客ID)) != a.顧客ID;

    delete from 顧客 a
    where exists(select 1 from 顧客 b
                  where b.名前 = a.名前
                    and b.内線 = a.内線
                    and b.顧客ID < a.顧客ID);
    commit;
end;
/

解説

分析関数を使う方法では、
内線でパーテーションを切った中で、
min関数で、最小の顧客IDを取得してます。