SELECT с SUM, ограниченным по значению.

Kareglazka
Дата: 18.11.2008 17:06:57
Помогите, пожалуйста, реализовать такую задачу SELECT-ом:
Строки:
NN fff
1 20
2 20
3 20
4 20
5 25
6 15
7 40
8 45
9 30
10 80
Нужно получить суммы с нарастающим итогом, но чтобы этот самый итог не превышал 100.
Из приведённых данных должен получится такой результат:
NN SUM(ff) OVER(?????????)
1 20
2 40
3 60
4 80
5 25
6 40
7 80
8 45
9 75
10 80
...
Dionis M
Дата: 18.11.2008 17:47:33
Kareglazka,
with T as (
select 1 NN, 20 FFF from dual union all
select 2 NN, 20 FFF from dual union all
select 3 NN, 20 FFF from dual union all
select 4 NN, 20 FFF from dual union all
select 5 NN, 25 FFF from dual union all
select 6 NN, 15 FFF from dual union all
select 7 NN, 40 FFF from dual union all
select 8 NN, 45 FFF from dual union all
select 9 NN, 30 FFF from dual union all
select 10 NN, 80 FFF from dual
)
select NN, FFF, sum( FFF ) over( partition by Flag order by NN )
from (
select NN, FFF, trunc( sum( FFF ) over (partition by 1 order by nn) / 100 ) Flag
from T );
Vint
Дата: 18.11.2008 17:49:52
Kareglazka,

топорно и влоб

WITH t AS(
SELECT 1 a, 20 b FROM dual UNION all select 2, 20 FROM dual UNION all select 3, 20 FROM dual UNION all select 4, 20 FROM dual
UNION all select 5, 25 FROM dual UNION all select 6, 15 FROM dual UNION all select 7, 40 FROM dual UNION all select 8, 45 FROM dual
UNION all select 9, 30 FROM dual UNION all select 10, 80 FROM dual),
z AS (SELECT trunc((SUM(b) OVER (ORDER BY a))/100) del,b,a FROM t)
SELECT SUM(b) OVER (PARTITION BY del ORDER BY a),b,a FROM z
orawish
Дата: 18.11.2008 18:04:34
Dionis M,

with T as (
select 1 NN, 20 FFF from dual union all
select 2 NN, 20 FFF from dual union all
select 3 NN, 20 FFF from dual union all
select 4 NN, 20 FFF from dual union all
 select 5 NN, 55 FFF from dual union all
 select 6 NN, 55 FFF from dual union all
select 7 NN, 40 FFF from dual union all
select 8 NN, 45 FFF from dual union all
select 9 NN, 30 FFF from dual union all
select 10 NN, 80 FFF from dual
)
select NN, FFF, sum( FFF ) over( partition by Flag order by NN )
from (
select NN, FFF, trunc( sum( FFF ) over (partition by 1 order by nn) / 100 ) Flag
from T );
Vint
Дата: 18.11.2008 18:42:06
orawish,
мда если прекратить тупить на работе в Джаву и вспомнить ораклу то все получается как нильзя просто)))
WITH t AS(
SELECT 1 a, 20 b FROM dual UNION all select 2, 20 FROM dual UNION all select 3, 20 FROM dual UNION all select 4, 20 FROM dual
UNION all select 5, 55 FROM dual UNION all select 6, 60 FROM dual UNION all select 7, 40 FROM dual UNION all select 8, 45 FROM dual
UNION all select 9, 30 FROM dual UNION all select 10, 80 FROM dual)
SELECT  floor((SUM(b) OVER (ORDER BY a desc RANGE 100-b PRECEDING))/100) del,b,a FROM t
Elic
Дата: 18.11.2008 19:04:26
Vint
то все получается как нильзя просто)))
Осталась малость: понять, что ж за хрень получилась
P.S. С ручника так быстро не слезть :)
Elic
Дата: 18.11.2008 19:42:21
Kareglazka
Нужно получить суммы с нарастающим итогом, но чтобы этот самый итог не превышал 100.
Для идей STFF SQL задачка (аналитическая функция ?) вопрос
Elic
Дата: 18.11.2008 20:49:46
+
with T as (
select 1 NN, 20 FFF from dual union all
select 2 NN, 20 FFF from dual union all
select 3 NN, 20 FFF from dual union all
select 4 NN, 20 FFF from dual union all
 select 5 NN, 55 FFF from dual union all
 select 6 NN, 55 FFF from dual union all
select 7 NN, 40 FFF from dual union all
select 8 NN, 45 FFF from dual union all
select 9 NN, 30 FFF from dual union all
select 10 NN, 80 FFF from dual
),
t2 as (select nn, fff, row_number() over (order by nn) as rn, sum(fff) over (order by nn) as s from t),
t3 as (select t2.*, count(*) over (order by s range between current row and 100 - fff following) as cnt from t2),
t4 as (select rn, s - fff as s from t3 start with rn = 1 connect by rn = prior rn + prior cnt)
--
select t2.nn, t2.fff, t2.s - max(t4.s) over (order by t2.rn) as s from t4, t2 where t4.rn(+) = t2.rn
;

           NN           FFF             S
------------- ------------- -------------
            1            20            20
            2            20            40
            3            20            60
            4            20            80
            5            55            55
            6            55            55
            7            40            95
            8            45            45
            9            30            75
           10            80            80

10 rows selected.
Бррр... :)
Vint
Дата: 19.11.2008 12:13:27
Elic

голову пеплом посыпал. чукча пошел читать дальше. и много думать)))
мысль двигалась в правильном направлении, но как получить пороговые значения t4.s не знал... пасибо за пример))
Kareglazka
Дата: 19.11.2008 16:26:23
Eric, спасибо огромное, то, что надо. *THUMPS_UP**THUMPS_UP**THUMPS_UP*