Нарастающий итог, но с нюансом

Недавно начала изучать аналитические функции, и вот возникла задача, которую, чувствую, можно решить с помощью них, но быстрого варианта не вижу :(.

Исходные данные:

with Test as (
  select Trunc(sysdate - 6) as dDate, 100 as nQty from dual
  union
  select Trunc(sysdate - 5) as dDate, 200 as nQty from dual
  union
  select Trunc(sysdate - 7) as dDate, 140 as nQty from dual
  union
  select Trunc(sysdate - 4) as dDate, 150 as nQty from dual
  union
  select Trunc(sysdate - 3) as dDate, 200 as nQty from dual)

Имеем некое цифровое значение - например, 400.

Нужен результат, который дает такой запрос:

select t.*, (nQty + 400 - nQtyFull) as nQtyDiff
  from (
    select *
      from (
        select dDate
            , nQty
            , sum(nQty) over(order by dDate desc) as nQtyFull
          from Test
        )
      where nQtyFull >= 400
      order by dDate desc 
    ) t
  where rownum < 2

Но, насколько я понимаю, такой запрос при большом количестве записей в таблице Test будет тормозить, и очень прилично.

Как бы условие nQtyFull >= 400 учесть раньше, чем будет посчитан нарастающий итог по всей таблице? Да и вторая сортировка мне не импонирует :(

Kateryne

Как бы условие nQtyFull >= 400 учесть раньше, чем будет посчитан нарастающий итог по всей таблице? Да и вторая сортировка мне не импонирует :(

А процедурный нарастающий итог проимпонирует? :)

Kateryne

Но, насколько я понимаю, такой запрос при большом количестве записей в таблице Test будет тормозить, и очень прилично.

Аналитика по огромной нефильтрованной таблице - это, мягко говоря, вещь в себе :)

Elic

А процедурный нарастающий итог проимпонирует? :)

В смысле, считать нарастающий итог через PL/SQL? Так сейчас и делаю, в цикле. Но была надежда, что есть пока непонятные мне фишки с аналитическими функциями :)

Kateryne

была надежда, что есть пока непонятные мне фишки с аналитическими функциями :)

Какие фишки? - Чтобы отфильтровать аналитику, её сперва нужно посчитать.

В цикле получается, что приходится в среднем четыре-пять (правда, простых) селекта делать - то есть, по данным 4-5 итераций.
А хотелось одним. Что, надежды не оправдались? :)

Может быть при больших объемах сможет помочь pipelined - функция.
Это так сказать pl/sql- решение для sql :-)

Kateryne

В цикле получается, что приходится в среднем четыре-пять (правда, простых) селекта делать - то есть, по данным 4-5 итераций.

"Северный варвар способен не только ..." (с)

Kateryne

А хотелось одним.

Ну так и делается это одним запросом с остановкой на искомой строке.

А вот еще ...
В качестве альтернативного варианта при уникальности ddate можно попробовать
вариант с агрегацией first/last ...

SQL> with Test as (
  2    select Trunc(sysdate - 6) as dDate, 100 as nQty from dual
  3    union
  4    select Trunc(sysdate - 5) as dDate, 200 as nQty from dual
  5    union
  6    select Trunc(sysdate - 7) as dDate, 140 as nQty from dual
  7    union
  8    select Trunc(sysdate - 4) as dDate, 150 as nQty from dual
  9    union
 10    select Trunc(sysdate - 3) as dDate, 200 as nQty from dual)
 11  select max(ddate) ddate,
 12         max(nqty) keep(dense_rank last order by ddate) nqty,
 13         max(nqtyfull) keep (dense_rank last order by ddate) nqtfull,
 14         max(nqty+ 400 - nqtyfull) keep(dense_rank last order by ddate) nqtydiff
 15    from (select t.*,
 16                 sum(nQty) over(order by dDate desc) nQtyfull,
 17                 case
 18                   when sum(nQty) over(order by dDate desc) >= 400 then
 19                    1
 20                   else
 21                    0
 22                 end flag
 23            from test t)
 24   where flag = 1
 25  /
 
DDATE             NQTY    NQTFULL   NQTYDIFF
----------- ---------- ---------- ----------
28.11.2007         200        550         50
 
SQL> 

dmidek

В качестве альтернативного варианта при уникальности ddate можно попробовать вариант с агрегацией first/last ...

Это почти то же самое. А может и хуже :)
А вот если флаг выставить только у искомой строки, то можно избавиться от второй сортировки.

Elic

dmidek В качестве альтернативного варианта при уникальности ddate можно попробовать вариант с агрегацией first/last ... Это почти то же самое. А может и хуже :) А вот если флаг выставить только у искомой строки, то можно избавиться от второй сортировки.

Elic, классически :-)
Не дожал :-)

SQL> with Test as (
  2    select Trunc(sysdate - 6) as dDate, 100 as nQty from dual
  3    union
  4    select Trunc(sysdate - 5) as dDate, 200 as nQty from dual
  5    union
  6    select Trunc(sysdate - 7) as dDate, 140 as nQty from dual
  7    union
  8    select Trunc(sysdate - 4) as dDate, 150 as nQty from dual
  9    union
 10    select Trunc(sysdate - 3) as dDate, 200 as nQty from dual)
 11  select q.*, nqty+ 400 - nqtyfull nqtydiff
 12    from (select t.*,
 13                 sum(nQty) over(order by dDate desc) nQtyfull,
 14                 case
 15                   when sum(nQty) over(order by dDate desc) >= 400
 16                   and sum(nQty) over(order by dDate desc range between unbounded preceding
 17                   and 1 preceding) < 400 then
 18                    1
 19                   else
 20                    0
 21                 end flag
 22            from test t) q
 23   where flag = 1
 24  /
 
DDATE             NQTY   NQTYFULL       FLAG   NQTYDIFF
----------- ---------- ---------- ---------- ----------
28.11.2007         200        550          1         50
 
SQL>