Hi experts,
I need an help on the task below.
INPUT: values separated by the tab,first row is the header
[DATE MARKET SIDE PRICE]
20110609 AS A 300.5000
20110609 AS R 200.5000
20110609 BR A 111.5000
20110609 BR R 222.5000
20110610 AS A 100.5500
20110610 AS R 150.5500
20110610 BR A 199.5000
20110610 BR R 288.5000
20110611 AS A 50.0000
20110611 AS R 50.0000
20110611 BR A 98.0000
20110611 BR R 97.0000
20110612 AS A 90.0000
20110612 AS R 80.0000
20110612 CO A 12.0000
20110612 CO R 13.0000
20110612 DE A 112.0000
20110612 DE R 113.0000
20110613 DE A 38012.6920
20110613 DE A 38012.6920
etc.
OUTPUT:
I need to calculate a three day AVERAGE PRICE for each day where the SIDE and the MARKET are the same (three day is just example - it should be CUSTOMIZABLE) :
Example:
For date 20110612 and side A and market AS - we will sum prices for (0612 +0611+0610) on the A side on the AS market,
so the summary will be (90+50+100,55 = 240,55)=SUM_SIDE_A then calculate the average which is SUM_SIDE_A/3 = 80,1833
for R side it will be (80+50+150,55=280,55=SUM_SIDE_R)/3 = 93.51
etc. for each day/market/side.
For days 20110609 or 20110610 when there is no record 3 days past, we will simple use the all existing records before.
Example : 20110609 : A side, market AS -> 300.5/ 1 =300.5 , 20110610 : 401.05/2 =200.525
OUTPUT should look like this: tab separated values, instead of 'xxx' should be similar summaries for other markets.
output should be sorted by date ascending , market alphabetically
[DAE MARKET AVERAGE_SIDE_A AVERAGE_SIDE_R]
20110609 AS 300.5000 200.5000
20110609 BR xxx xxx
20110610 AS 200.525 175.525
20110610 BR xxx xxx
20110611 AS 150.3500 133.6833
20110611 BR xxx xxx
20110612 AS 80.1330 93.5156
20110612 BR xxx xxx
20110612 CO xxx xxx
20110612 DE xxx xxx
20110613 AS xxx xxx
20110613 DE xxx xxx
etc.
In my real input there are of course hundreds of date and dozen of markets.
Thanks a lot for all replies