Hello everybody!
Given a table with transactions
create table trans as
select t2.*, t1.*
from (select level transaction_id, abs(trunc(1e1 * sin(level * level))) quantity, abs(trunc(1e1 * sin(level) * (1 + level / 2e1))) price
from dual
connect by level <= 2e1) t1
,(select level product_id
from dual
connect by level <= 1) t2
order by 1, 2;
Our task is to calculate total for the last N transactions if prices were as first N transactions. In particular for N = 4:
SQL> select *
2 from trans
3 match_recognize
4 (
5 partition by product_id
6 order by transaction_id
7 measures
8 final first(price, 0) * nvl(prev(quantity, 3), 0) +
9 final first(price, 1) * nvl(prev(quantity, 2), 0) +
10 final first(price, 2) * nvl(prev(quantity, 1), 0) +
11 final first(price, 3) * prev(quantity, 0) result
12 all rows per match
13 pattern (x+) define x as 1 > 0
14 );
PRODUCT_ID TRANSACTION_ID RESULT QUANTITY PRICE
---------- -------------- ---------- ---------- ----------
1 1 72 8 8
1 2 71 7 10
1 3 123 4 1
1 4 156 2 9
1 5 107 1 11
1 6 134 9 3
1 7 116 9 8
1 8 188 9 13
1 9 225 6 5
1 10 213 5 8
1 11 218 9 15
1 12 143 4 8
1 13 188 6 6
1 14 199 9 16
1 15 182 9 11
1 16 228 9 5
1 17 171 0 17
1 18 198 4 14
1 19 94 2 2
1 20 114 8 18
20 rows selected.
So for example, for transaction 6 the result is 134 = 9*9 + 1*1 + 2*10 + 4*8.
The same can be done with window functions.
select
t.*,
nth_value(price, 1) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following)
* lag(quantity, 3, 0) over (partition by product_id order by transaction_id) +
nth_value(price, 2) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following)
* lag(quantity, 2, 0) over (partition by product_id order by transaction_id) +
nth_value(price, 3) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following)
* lag(quantity, 1, 0) over (partition by product_id order by transaction_id) +
nth_value(price, 4) over (partition by product_id order by transaction_id rows between unbounded preceding and unbounded following)
* lag(quantity, 0, 0) over (partition by product_id order by transaction_id) result
from trans t;
Everything is fast and trivial when N is fixed.
Now the goal is to write the query which can do the job for any arbitrary N.
Let's compare performance for attached data and N = 20 but as mentioned it should for any N without code changes.
This is not super challenging problem by any means but it covers a few interesting points (imo) and similarly to recent puzzle by James this can be solved with/without joins.
floating_window.sql