10-8 累計が100以上になるまで出力(負数あり)

SQLパズル

GetPointテーブル
ID  Point
--  -----
 1     30
 2    -30
 3     90
 4     20
 5   -900
 6    900
 7     50
 8    120
 9    150

GetPointテーブルからIDの昇順にPointの合計が100以上になるまで
IDとPointを出力する。
Pointには負数が存在する(Pointの累計は、IDに対して単調増加ではない)

出力例
ID  Point
--  -----
 1     30
 2    -30
 3     90
 4     20

データ作成スクリプト

create table GetPoint(
ID    number(1),
Point number(3),
Primary key(id));

insert into GetPoint values(1,  30);
insert into GetPoint values(2, -30);
insert into GetPoint values(3,  90);
insert into GetPoint values(4,  20);
insert into GetPoint values(5,-900);
insert into GetPoint values(6, 900);
insert into GetPoint values(7,  50);
insert into GetPoint values(8, 120);
insert into GetPoint values(9, 150);
commit;

SQL

--■■■分析関数を使う方法1■■■
select ID,Point
from (select ID,Point,
      min(case when 累計 >= 100 then ID end) over() as MinID
      from (select ID,Point,sum(Point) over(order by ID) as 累計
              from GetPoint))
where ID <= MinID
order by ID;

--■■■分析関数を使う方法2■■■
select ID,Point from GetPoint a
where ID <= (select min(b.ID)
               from (select c.ID,sum(c.Point) over(order by c.ID) as 累計
                       from GetPoint c) b
              where b.累計 >= 100)
order by ID;

--■■■相関サブクエリを使う方法1■■■
select ID,Point from GetPoint a
where ID <= (select min(b.ID)
               from GetPoint b
              where (select sum(c.Point)
                       from GetPoint c
                      where b.ID >= c.ID) >=100)
order by ID;

--■■■相関サブクエリを使う方法2■■■
select ID,Point from GetPoint a
where ID <= all(select b.ID
                  from GetPoint b,GetPoint c
                 where b.ID >= c.ID
                 group by b.ID
                having sum(c.Point) >=100)
order by ID;

--■■■相関サブクエリを使う方法3■■■
select ID,Point from GetPoint a
where ID <= (select min(min(b.ID))
               from GetPoint b,GetPoint c
              where b.ID >= c.ID
              group by b.ID
             having sum(c.Point) >=100)
order by ID;

解説

IDが、累計が100以上のIDの中で最小のID以下かを、
where句でチェックしてます。

---