Блог

У меня есть две таблицы tab_a как

    SUB_ID AMOUNT
    1     10
    2     5
    3     7
    4     15
    5     4
  

2 таблица tab_b как

     slab_number slab_start slab_end 
    1             12          20
    2             21          25
    3             26          35
  

значение slab_start всегда будет на 1 больше, чем значение slab_end предыдущего номера сляба

Если я выполню текущий итог для tab_a, мой результат будет

  select sub_id , sum(amount)  OVER(ORDER BY sub_id) run_sum
from tab_a

sub_id run_sum
  1     10
  2     15
  3     22
  4     37
  5     41
  

Мне нужно выполнить SQL-запрос, чтобы проверить, какой slab_NUMBER, если run_sum меньше первого slab_number, тогда он должен быть равен нулю, если run_sum больше последнего номера slab, тогда пустым, кроме строки, которая пересекает предел.
Ожидаемый результат

   sub_id run_sum slab_number
      1     10    0
      2     15    1
      3     22    2
      4     37    3
      5     41    NULL
  

Я пробовал это .

Сначала найдите текущую сумму, которая пересекает предел, т.е. последний slab_end

         select min(  run_sum ) 
              from  (select sub_id , sum(amount)  OVER(ORDER BY sub_id) run_sum
from tab_a ) where run_sum>=35
  

затем используйте следующий запрос

 select sub_id,
       run_sum,
       case
         when run_sum <
              (select SLAB_START from tab_b where slab_number = '1') then
          0
         when run_sum = 37 then
          (select max(slab_number) from tab_b)
         when run_sum > 37 then
          NULL
         else
          (select slab_number
             from tab_b
            where run_sum between SLAB_START and slab_end)
       end slab_number
  from (select sub_id, sum(amount) OVER(ORDER BY sub_id) run_sum from tab_a)
  

есть ли какой-либо другой способ улучшить.

Несколько странное требование 🙂 Используйте некоторые аналитические функции и case when ‘ы. Row_number когда вам нужно сначала что-то найти max() over() и sum() over() когда вам нужна информация из более чем строк:

 with  
  a as (
    select sub_id, row_number() over (order by sub_id) rn, 
           sum(amount) over (order by sub_id) rs 
      from tab_a),
  b as (select tab_b.*, max(slab_number) over () msn from tab_b )
select sub_id, rs, 
       case when sn is null and row_number() over (partition by sn order by sub_id) = 1 
            then msn else sn 
       end sn
  from (
    select sub_id, rs, max(msn) over () msn,
           case when slab_number is null and rn = 1 then 0 else slab_number end sn
      from a left join b on rs between slab_start and slab_end)
  

_{демонстрация dbfiddle}

вы могли бы попробовать это:

 select a.sub_id , sum(a.amount)  OVER(ORDER BY a.sub_id) run_sum
 ,case when b.slab_number=1 then 0 else  lag(b.slab_number,1) over (order by a.sub_id)end slab_number
from tab_a a
left join tab_b b on a.SUB_ID = b.slab_number
  

Я думаю, что это в основном a left join со значением по умолчанию:

 select a.*,
       (case when a.run_sum < bb.min_slab_num then 0
             else b.slab_num
        end) as slab_num
from (select sub_id,
             sum(amount) over (order by sub_id) as run_sum
      from tab_a
     ) a left join
     tab_b b
     on a.run_sum between slab_start and slab_end cross join
     (select min(slab_start) as min_slab_start
      from tab_b
     ) bb;