トップページに戻る    次の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 最大のリージョンを求める(境界なし)