5-10 データのグループ分け

SQLパズル

データをNoでソートした後、分割して、
その分割した塊の2番目だけを残して削除する。

No
-------
1008012
1008055
1008061
1000072

例：1000件のデータを100分割して、その中の2番目だけ取り出す。
つまり、11番目から20番目のデータが残る。

データ作成スクリプト

create table テーブル1(No number);

begin
    for i in 1..1000 loop
        insert into テーブル1(No) values(i*10);
    end loop;
    commit;
end;
/

SQL

--■■■Ntile関数を使用■■■
delete from テーブル1
where RowID in (select RowID
                from (select RowID,NTILE(100) over(order by No) as bucket
                      from テーブル1)
                 where bucket !=2);

--■■■インラインビュー内で、Row_Number関数でナンバリング■■■
delete from テーブル1
where RowID in (select RowID
                from (select RowID,Row_Number() over (order by No) as Rank
                      from テーブル1)
where Rank not between 1000/100+1 and 1000/100*2);

--■■■インラインビュー内でソートし、RowNumでナンバリング■■■
delete from テーブル1
where RowID in (select Row_ID
                from (select RowID as Row_ID,RowNum as Rank
                       from (select RowID from テーブル1 order by No))
where Rank not between 1000/100+1 and 1000/100*2);

--■■■count関数で順位を取得■■■
delete from テーブル1 a
where (select count(No)+1 from テーブル1 b
        where b.No < a.No) not between 1000/100+1 and 1000/100*2;

解説

in述語の引数に、プライマリキーを返すselect文を使用する方法もありますが、

delete文のfrom句で使用するテーブルと
delete文のwhere句のin述語の引数の、select文のfrom句で使用するテーブルが同じ場合は、
in述語の引数に、RowIDを返すselect文を使用する方法もあります。

---