Аналитические функции SQL

← →
k2 © (2005-01-27 10:43) [0]

SELECT table.*, SUM(common_val) OVER (ORDER BY code, pdate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum_n FROM table common_val pdate code sum_n 1 20 011,86 16.02.2004 92213 20 011,86 2 111 177,00 16.02.2004 92213 131 188,86 3 905 968,60 16.02.2004 92213 1 037 157,46 4 832 283,50 17.02.2004 92213 1 869 440,96 5 -2435 480,00 26.03.2004 92213 - 566 039,04 6 275 267,34 31.03.2004 92213 - 290 771,70 7 293 761,70 31.03.2004 92213 2 990,00 8 -3 990,00 31.05.2004 92213 - 1 000,00 ...

В поле sum_n накапливаются промежуточные суммы по полю сommon_val, необходимо вытащить ещё и дату последнего возникновения положительной промежуточной суммы (из строки 7 в примере) Можно ли это сделать с помощью какой-либо аналитической функции, и если да то подтолкните плиз в нужную сторону

← →
Sergey13 © (2005-01-27 10:48) [1]

А что это за сервер?

← →
k2 © (2005-01-27 10:51) [2]

сорри, значок Oracle вроде отмечала :(

← →
k2 © (2005-01-27 10:52) [3]

точнее 9.2.05

← →
Sergey13 © (2005-01-27 10:56) [4]

Слышал я про аналитику в девятке, но видеть не приходилось.
Над тем, что бы получить подобный результат, люди такие ли структуры наворачивают. А тут одним махом. А хочется еще и с винтом. Да, апетит приходит во время еды. 8-)
По сабжу - не знаю. 9 не юзал.

← →
Внук © (2005-01-27 14:03) [5]

>>k2 © (27.01.05 10:43)
Ну и задачку ты задала, Катерина :))
А нужно именно аналитическую функцию, или просто чтобы одним запросом без хранимых процедур? В любом случае я не раньше понедельника смогу этим заняться. Насколько терпит?

← →
Внук © (2005-01-31 12:06) [6]

-- Создал табличку с исходными полями и данными, назвал k2 :)) -- Предполагал, что есть поле ID с номерами записей по порядку. Если его нет - подсоединить к запросу не проблема -- Вывести результат в нужном виде SELECT RES_SET.ID,RES_SET.COMMON_VAL,RES_SET.PDATE,RES_SET.CODE,RES_SET.SUM_N,RES_SET.LAST_DATE FROM -- Отсортировать по флажку и взять первое значение даты (SELECT K2_WITH_FLAG.*,FIRST_VALUE(PDATE) OVER (ORDER BY FLAG DESC,ID DESC) LAST_DATE FROM ( -- Исходный запрос SELECT K2.*, SUM(COMMON_VAL) OVER (ORDER BY CODE,PDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM_N, -- Добавил поле-флажок, содержит 1 в случае положительной sum_n, и 0 - иначе DECODE(GREATEST(0,SUM(COMMON_VAL) OVER (ORDER BY CODE, PDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)),0,0,1 ) FLAG FROM K2 ) K2_WITH_FLAG ) RES_SET ORDER BY ID -- Вроде, все работает, как требовалось :)

← →
k2 © (2005-01-31 13:17) [7]

Спасибо Александр, я не очень хорошо объяснила как требуется, поэтому немножко не так работает. На том же примере если рассуждать: порядок id совпадает с порядком pdate поэтому пусть везде где у тебя сортировка по id сортировать по pdate(мне кажется это не важно), тогда после сортировки по флагу и дате строки выстроятся так примерно: 7,4,3,2,1,8,6,5 и first_value(pdate) дает нужное значение. Но если допустим в 8-й строке common_val=-990,00 тогда промежуточная сумма sum_n останется положительной, флаг для неё соотв.=1 и после сортировки first_value(pdate)=31.05.2004, а нужно 31.03.2004-дату последнего возникновения положительной промежуточной суммы. Извени за неудачный пример :-/

← →
DenK_vrtz © (2005-01-31 13:19) [8]

Катерина, если правильно понял задачу

select * from K2
where sum_n > 0
and pdate =
(select max(pdate) from k2 where sum_n >0)

← →
k2 © (2005-01-31 13:31) [9]

to DenK_vrtz © (31.01.05 13:19) [8]
К сожалению нет :) на примере: если бы не было 7-й строки то это была бы pdate из первой строки, если 9,10 строки будут содержать sum_n>0 тогда это будет pdate из 9-й
Я понимаю што хочется странного :) но ничего поделать с собой не могу :)

← →
DenK_vrtz © (2005-01-31 13:40) [10]

а так? :)

SELECT * from
(select ROWNUM, K2.* from K2
where sum_n > 0
and pdate_code =
(select max(pdate_code) from k2 where sum_n >0)
)
where ROWNUM=1

нет :) подзапрос select max(pdate_code) from k2 where sum_n >0 возвращает максимальную среди строк с положительной суммой, а искомая дата таковой может являться а может и не являться :) не запутывай меня пожалуйста :)

>>k2 © (31.01.05 13:17) [7]
Дату последнего возниковения положительного значения в common_val? Если так, то я просто неправильно понял условие. Но это не принципиально, просто в подзапросе, где выставляется флаг, нужно вместо
DECODE(GREATEST(0,SUM(COMMON_VAL) OVER (ORDER BY CODE, PDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)),0,0,1 ) FLAG
писать
DECODE(GREATEST(0,COMMON_VAL),0,0,1) FLAG
Здесь насчет эквивалентности сортировки по номеру записи и по дате не уверен, но опять же добавить rownum в подзапрос несложно.
Или опять не так понял? :)

предполагаю, что
запрос отсортирован по датам
"возникновение" - это сумма положтельная, а предыдущая - отрицательная

тогда

select
case when (sum_n > 0) and (lag(sum_n, 1) over (order by pdate)) < 0) then pdate else null end -- это дата возникновения

а потом ее максимальную берем

не проверял, м.б. что-нить нехорошо со скобками :), но идея такая

После окончательного выяснения условия задачи вот это должно подойти.
SELECT K2_WITH_TWO_FLAGS.ID,K2_WITH_TWO_FLAGS.COMMON_VAL,K2_WITH_TWO_FLAGS.PDATE,K2_WITH_TWO_FLAGS.CODE, K2_WITH_TWO_FLAGS.SUM_N, -- Вывод поля, в котором последний раз встречается комбинация флагов FLAG, PREV_FLAG, -- равная 1,0 - сответствует смене знака с "-" на "+" FIRST_VALUE(PDATE) OVER(ORDER BY FLAG DESC, PREV_FLAG ASC,ID DESC) AS LAST_DATE FROM ( SELECT K2_WITH_FLAG.*, -- Поле, содержащее значение sum_n из предыдущей строки LAG(SUM_N,1,0) OVER (ORDER BY ID) AS PREV_VAL, -- Аналогично полю FLAG, но для столбца PREV_VAL DECODE(GREATEST(0,LAG(SUM_N,1,0) OVER (ORDER BY ID)),0,0,1) AS PREV_FLAG FROM ( -- Исходный запрос SELECT K2.*, SUM(COMMON_VAL) OVER (ORDER BY CODE,PDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SUM_N, -- Добавил поле-флажок, содержит 1 в случае положительной sum_n, и 0 - иначе DECODE(GREATEST(0,SUM(COMMON_VAL) OVER (ORDER BY CODE, PDATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)),0,0,1 ) FLAG FROM K2 ) K2_WITH_FLAG ) K2_WITH_TWO_FLAGS ORDER BY ID

Внук и Petr V. Abramov - Большое спасибо :) с lag все получилось :)) можно я вас чмокну - очень уж достала эта задачка kiss kiss

Аналитические функции SQL Найти похожие ветки