I have a situation as below:
a 1 0.1
a 2 0
a 3 0
b 1 0.1
b 2 0.2
b 3 0
Data is partitioned by Col1, sorted by Col2
I want to create column 4 with the rules below:
So at the end the outcome should look like this:
a 1 0.1 0.1
a 2 0 0.05
a 3 0 0.025
b 1 0.1 0.1
b 2 0.2 0.2
b 3 0 0.1
Updated Rules (to make it clearer):
you probably need recursion for this:
WITH RECURSIVE prev AS
SELECT tab.*, col3 AS col4
WHERE col2 = 1
CASE WHEN t.col3 < prev.col4 THEN (t.col3 + prev.col4)/2 ELSE t.col3 END
FROM tab AS t
ON t.col1= prev.col1
AND t.col2 = prev.col2+1
Assuming you simplified the actual data, if col2 is not a sequence you must materialize a ROW_NUMBER in a Volatile Table:
CREATE VOLATILE TABLE vt AS
( SELECT ...
OVER (PARTITION BY col1
ORDER BY col2) AS rn
PRIMARY INDEX (col1)
ON COMMIT PRESERVE ROWS
and use rn instead of col2.
Can you add more details, how many rows per col1 and actual data for col3?
Maybe it's possible with OLAP-functions, too.
Col2 is simplified but they are dates in numeric format (ie 20150215) and they increase on a yearly basis (next input will be 20160215)
By that definition, Number of rows per col1 will be increasing every year.
I'll give the code a try. Thanks!
You could use col2 + 10000, but you will need a FIRST_VALUE or ROW_NUMBER in your seed query, so materializing your data should be the most efficient.