トップページに戻る
次のSQLパズルへ
前のSQLパズルへ
10-206 連続範囲の累計と非連続範囲の累計
SQLパズル
SeqValTable
Seq Val
--- ---
1 -1
2 -1
3 4
4 2
5 -4
6 -6
7 -2
8 -2
9 2
10 2
11 4
12 1
13 2
14 -1
15 -3
16 -9
17 9
18 -8
Valが負数だったら、
Seqの昇順で、Valが連続した負数な範囲の累計をSum1、
Seqの昇順で、Valが負数な範囲の累計をSum2として求める。
Valが負数でなかったら、nullとする。
出力結果
Seq Val Sum1 Sum2
--- --- ---- ----
1 -1 -1 -1
2 -1 -2 -2
3 4 null null
4 2 null null
5 -4 -6 -4
6 -6 -12 -10
7 -2 -14 -12
8 -2 -16 -14
9 2 null null
10 2 null null
11 4 null null
12 1 null null
13 2 null null
14 -1 -17 -1
15 -3 -20 -4
16 -9 -29 -13
17 9 null null
18 -8 -37 -8
データ作成スクリプト
create table SeqValTable(Seq,Val) as
select 1,-1 from dual union
select 2,-1 from dual union
select 3, 4 from dual union
select 4, 2 from dual union
select 5,-4 from dual union
select 6,-6 from dual union
select 7,-2 from dual union
select 8,-2 from dual union
select 9, 2 from dual union
select 10, 2 from dual union
select 11, 4 from dual union
select 12, 1 from dual union
select 13, 2 from dual union
select 14,-1 from dual union
select 15,-3 from dual union
select 16,-9 from dual union
select 17, 9 from dual union
select 18,-8 from dual;
SQL
--■■■旅人算の感覚を使う方法1■■■
select Seq,Val,
case when sign(Val) = -1
then sum(Least(Val,0)) over(order by Seq) end as Sum1,
case when sign(Val) = -1
then sum(Least(Val,0)) over(partition by partID order by Seq) end as Sum2
from (select Seq,Val,
Seq - Row_Number() over(partition by sign(Val) order by Seq) as partID
from SeqValTable)
order by Seq;
--■■■旅人算の感覚を使う方法2■■■
select Seq,Val,
case when sign(Val) = -1
then sum(Least(Val,0)) over(order by Seq) end as Sum1,
case when sign(Val) = -1
then sum(Val) over(partition by sign(Val),partID order by Seq) end as Sum2
from (select Seq,Val,
Seq - Row_Number() over(partition by sign(Val) order by Seq) as partID
from SeqValTable)
order by Seq;
--■■■旅人算の感覚を使わない方法■■■
select Seq,Val,
case when sign(Val) = -1
then sum(Least(Val,0)) over(order by Seq) end as Sum1,
case when sign(Val) = -1
then sum(Least(Val,0)) over(partition by groupID order by Seq) end as Sum2
from (select Seq,Val,sum(willSum) over(order by Seq) as groupID
from (select Seq,Val,
case when (Lag(Val) over(order by Seq) is null or
Lag(Val) over(order by Seq) >= 0) and Val < 0 then 1
else 0 end as willSum
from SeqValTable))
order by Seq;
解説
旅人算の感覚を使う方法2では、4人の旅人をイメージしてます。
Seqが必ず1進む旅人
Row_Number() over(partition by sign(Val) order by Seq)が
Valが正の時にだけ1進む旅人
Valが 0の時にだけ1進む旅人
Valが負の時にだけ1進む旅人
そして、旅人の種類と、必ず進む旅人との距離でパーティションを切ってます。
なお、
必ず進む旅人との距離だけでグループ化すると、
旅人が混ざることがあるので間違いとなります。
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
Seq - Row_Number() over(partition by sign(Val) order by Seq) as partID
の列別名は、
partIDではなくdistance
にしたほうが、分かりやすいかもしれませんね。
9-52 最大のリージョンを求める(境界なし)