How to get previous calculated results of rows

Question

I have the following scenario that I need help with.

The rule is, sum of amount can not be greater than 7 for any 10 minutes (if greater the amount replace with zero). Can this be done using analytic functions?

Here's the example data and the expectied result.

with test_data as
   (         select 1 id , 0.2 amount,to_date('01.01.2010 18:42','DD.MM.YYYY HH24:MI') dt from dual
   union all select 2 id , 0.4 amount,to_date('01.01.2010 18:58','DD.MM.YYYY HH24:MI') dt from dual
   union all select 3 id , 3.0 amount,to_date('01.01.2010 18:59','DD.MM.YYYY HH24:MI') dt from dual
   union all select 4 id , 0.2 amount,to_date('01.01.2010 19:00','DD.MM.YYYY HH24:MI') dt from dual
   union all select 5 id , 0.2 amount,to_date('01.01.2010 19:01','DD.MM.YYYY HH24:MI') dt from dual
   union all select 6 id , 0.4 amount,to_date('01.01.2010 19:02','DD.MM.YYYY HH24:MI') dt from dual
   union all select 7 id , 2.6 amount,to_date('01.01.2010 19:04','DD.MM.YYYY HH24:MI') dt from dual
   union all select 8 id , 0.2 amount,to_date('01.01.2010 19:05','DD.MM.YYYY HH24:MI') dt from dual
   union all select 9 id , 11.2 amount,to_date('01.01.2010 19:06','DD.MM.YYYY HH24:MI') dt from dual
   union all select 10 id , 10.8 amount,to_date('01.01.2010 19:08','DD.MM.YYYY HH24:MI') dt from dual
   union all select 11 id , 11.4 amount,to_date('01.01.2010 19:09','DD.MM.YYYY HH24:MI') dt from dual
   union all select 12 id , 6.8 amount,to_date('01.01.2010 19:18','DD.MM.YYYY HH24:MI') dt from dual
   union all select 13 id , 1.8 amount,to_date('01.01.2010 19:19','DD.MM.YYYY HH24:MI') dt from dual
   union all select 14 id , 1.6 amount,to_date('01.01.2010 19:21','DD.MM.YYYY HH24:MI') dt from dual
   union all select 15 id , 11.4 amount,to_date('01.01.2010 19:23','DD.MM.YYYY HH24:MI') dt from dual
   )
   select
     *
    from
     test_data

Expected result is:

id   amount   dt                calculated_amount
1    0.2      1.1.2010 18:42    0.2
2    0.4      1.1.2010 18:58    0.4
3    3        1.1.2010 18:59    3
4    0.2      1.1.2010 19:00    0.2
5    0.2      1.1.2010 19:01    0.2
6    0.4      1.1.2010 19:02    0.4
7    2.6      1.1.2010 19:04    2.6
8    0.2      1.1.2010 19:05    0.2
9    11.2     1.1.2010 19:06    0
10   10.8     1.1.2010 19:08    0
11   11.4     1.1.2010 19:09    0.4
12   6.8      1.1.2010 19:18    6.6
13   1.8      1.1.2010 19:19    0
14   1.6      1.1.2010 19:21    0.4
15   11.4     1.1.2010 19:23    0

for id = 1 time 18:42 add 10 minutes to this time and you find 18:52. But there is no rows smaller than 18:52, so go on. For id = 2 time 18:58 add 10 minutes and you find 19:08. you can select and sum amount between 18:58 and 19:08. The total amount is 29. Our rule is broken, so we start to calculate sum of id between 2 and 10 row by row. When you achieved total is grater than 7, you can put zero. (0.4+3+0.2+0.2+0.4+2.6+0.2 = 7 so id =9 and 10 must be zero). And we must use this calculation for all rows with calculated rows before. For id = 3 time 18:59 add 10 minutes and you find 19:09. This time, we sum calculated values before (3+0.2+0.2+0.4+2.6+0.2+0+0 = 6.6 ) so, new calculated value is 7-6.6 = 0.4 (if amount greater than the difference)

you can see steps in this link

Answer 1

I haven't entirely given up on the possibility that this might be achievable in plain SQL, possibly via the model clause; but in the meantime here's a PL/SQL solution that seems to adhere to what I think the rules are supposed to be. It gets the right answer, anyway...

create type type42 as object (id number, amount number, dt date,
  calculated_amount number)
/

create type tab42 as table of type42
/

create function f42 return tab42 pipelined as
  l_tab tab42;
  l_sum number;
  l_last_sum number;
begin
  -- load the starting state into a collection; calculated amount is
  -- initially just the original amount
  select type42(id, amount, dt, amount)
  bulk collect into l_tab
  from test_data;

  -- iterate over the collection row by row, each time adjusting the
  -- following 10-minute's worth of rows. As the collection is being
  -- updated, each iteration sees the changes from earlier ones
  for i in 1..l_tab.count loop
    l_sum := 0;
    l_last_sum := 0;

    <<inner_loop>>
    -- look at all rows later than the current one
    for j in i..l_tab.count loop
      -- but we're only interested in the 10 minute period after the
      -- starting row for this iteration (inclusive; so 19:08:00 looks
      -- at rows up to 19:18:00, not 19:17:59), so exit if later
      if l_tab(j).dt > l_tab(i).dt + interval '10' minute then
        -- row is more than 10 minutes ahead, so stop this iteration
        exit inner_loop;
      end if;

      -- adjust the running total based on this row's calculated amount
      -- which may have been changed by previous iterations
      l_sum := l_sum + l_tab(j).calculated_amount;
      if l_sum >= 7 then
        -- adjust the calculated amount using the last_sum value;
        -- so this row gets whatever is left from the 7, regardless
        -- of its actual value (but this must always be <= its amount)
        l_tab(j).calculated_amount := greatest(7 - l_last_sum, 0);
      end if;
      -- for use on the next inner loop
      l_last_sum := l_sum;
    end loop;

    -- send the current row with its final calculated amount
    pipe row (l_tab(i));
  end loop;
  return;
end;
/

With your test data:

select * from table(f42);

        ID     AMOUNT DT                  CALCULATED_AMOUNT
---------- ---------- ------------------- -----------------
         1         .2 2010-01-01 18:42:00                .2 
         2         .4 2010-01-01 18:58:00                .4 
         3          3 2010-01-01 18:59:00                 3 
         4         .2 2010-01-01 19:00:00                .2 
         5         .2 2010-01-01 19:01:00                .2 
         6         .4 2010-01-01 19:02:00                .4 
         7        2.6 2010-01-01 19:04:00               2.6 
         8         .2 2010-01-01 19:05:00                .2 
         9       11.2 2010-01-01 19:06:00                 0 
        10       10.8 2010-01-01 19:08:00                 0 
        11       11.4 2010-01-01 19:09:00                .4 
        12        6.8 2010-01-01 19:18:00               6.6 
        13        1.8 2010-01-01 19:19:00                 0 
        14        1.6 2010-01-01 19:21:00                .4 
        15       11.4 2010-01-01 19:23:00                 0 

Answer 2

Here is my answer, which corresponds to the text of your description, but not the "expected result" you have. Either way, I think it's close:

WITH TEST_DATA AS
   (         SELECT 1 ID , 0.2 AMOUNT,TO_DATE('01.01.2010 18:42','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 2 ID , 0.4 AMOUNT,TO_DATE('01.01.2010 18:58','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 3 ID , 3.0 AMOUNT,TO_DATE('01.01.2010 18:59','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 4 ID , 0.2 AMOUNT,TO_DATE('01.01.2010 19:00','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 5 ID , 0.2 AMOUNT,TO_DATE('01.01.2010 19:01','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 6 ID , 0.4 AMOUNT,TO_DATE('01.01.2010 19:02','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 7 ID , 2.6 AMOUNT,TO_DATE('01.01.2010 19:04','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 8 ID , 0.2 AMOUNT,TO_DATE('01.01.2010 19:05','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 9 ID , 11.2 AMOUNT,TO_DATE('01.01.2010 19:06','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 10 ID , 10.8 AMOUNT,TO_DATE('01.01.2010 19:08','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 11 ID , 11.4 AMOUNT,TO_DATE('01.01.2010 19:09','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 12 ID , 6.8 AMOUNT,TO_DATE('01.01.2010 19:18','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 13 ID , 1.8 AMOUNT,TO_DATE('01.01.2010 19:19','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 14 ID , 1.6 AMOUNT,TO_DATE('01.01.2010 19:21','DD.MM.YYYY HH24:MI') DT FROM DUAL
   UNION ALL SELECT 15 ID , 11.4 AMOUNT,TO_DATE('01.01.2010 19:23','DD.MM.YYYY HH24:MI') DT FROM DUAL
   )
SELECT ID
    , SUM(AMOUNT)
    , SUM(CASE WHEN  NVL(FINAL_TOTAL, AMOUNT) != 0 THEN B_AMOUNT ELSE 0 END) AS CALC
    , CASE
        WHEN SUM(AMOUNT) > 7 THEN 7 -   SUM(CASE WHEN  NVL(FINAL_TOTAL, AMOUNT) != 0 THEN B_AMOUNT ELSE 0 END)
        ELSE SUM(AMOUNT)
       END AS ANSWER
FROM (
    SELECT A.ID
        , A.AMOUNT
        , A.DT
        , B.ID AS B_ID
        , NVL(B.AMOUNT, A.AMOUNT) AS B_AMOUNT
        , B.DT AS B_DT
        , SUM(B.AMOUNT) OVER(PARTITION BY A.ID ORDER BY B.DT RANGE BETWEEN UNBOUNDED PRECEDING AND 
            CURRENT ROW) AS RUNNING_TOTAL
         , CASE 
            WHEN SUM(B.AMOUNT) OVER(PARTITION BY A.ID ORDER BY B.DT RANGE BETWEEN UNBOUNDED PRECEDING AND 
            CURRENT ROW) > 7 THEN 0 ELSE
            SUM(B.AMOUNT) OVER(PARTITION BY A.ID ORDER BY B.DT RANGE BETWEEN UNBOUNDED PRECEDING AND 
            CURRENT ROW) END AS FINAL_TOTAL
    FROM TEST_DATA A
    LEFT JOIN TEST_DATA B
          ON B.DT <= A.DT  + INTERVAL '10' MINUTE
           AND B.DT >= A.DT
    ) X
WHERE NVL(FINAL_TOTAL, AMOUNT) != 0
GROUP BY ID
ORDER BY ID

Answer 3

I don't understand your logic, however this query could be a starting point:

WITH test_data AS
   (         SELECT 1 ID , 0.2 amount,TO_DATE('01.01.2010 18:42','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 2 ID , 0.4 amount,TO_DATE('01.01.2010 18:58','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 3 ID , 3.0 amount,TO_DATE('01.01.2010 18:59','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 4 ID , 0.2 amount,TO_DATE('01.01.2010 19:00','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 5 ID , 0.2 amount,TO_DATE('01.01.2010 19:01','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 6 ID , 0.4 amount,TO_DATE('01.01.2010 19:02','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 7 ID , 2.6 amount,TO_DATE('01.01.2010 19:04','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 8 ID , 0.2 amount,TO_DATE('01.01.2010 19:05','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 9 ID , 11.2 amount,TO_DATE('01.01.2010 19:06','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 10 ID , 10.8 amount,TO_DATE('01.01.2010 19:08','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 11 ID , 11.4 amount,TO_DATE('01.01.2010 19:09','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 12 ID , 6.8 amount,TO_DATE('01.01.2010 19:18','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 13 ID , 1.8 amount,TO_DATE('01.01.2010 19:19','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 14 ID , 1.6 amount,TO_DATE('01.01.2010 19:21','DD.MM.YYYY HH24:MI') dt FROM dual
   UNION ALL SELECT 15 ID , 11.4 amount,TO_DATE('01.01.2010 19:23','DD.MM.YYYY HH24:MI') dt FROM dual
   )
SELECT ID, amount, dt,
    COUNT(*) OVER (ORDER BY dt RANGE BETWEEN CURRENT ROW AND INTERVAL '10' MINUTE FOLLOWING) AS COUNT_ROWS_PER_10_MIN,
    SUM(amount) OVER (ORDER BY dt RANGE BETWEEN CURRENT ROW AND INTERVAL '10' MINUTE FOLLOWING) AS SUM_PER_10_MIN
FROM test_data
ORDER BY ID;