update scott.daily_market_01 a
set (
Z1_line05 ,
Z1_line10 ,
Z1_line20 ,
Z1_line40 ,
z1_va_ratio
)=
(
select
line05,
line10,
line20,
line40,
va_ratio
from(
select scott.daily_market.code,scott.daily_market.trading_day,scott.daily_market.voturnover,scott.daily_market.pchg,scott.daily_market.vaturnover,scott.daily_market.turnover,
avg(nvl(tclose,0)) over (partition by code order by trading_day desc ROWS BETWEEN CURRENT ROW AND 4 following) as line05,
avg(nvl(tclose,0)) over (partition by code order by trading_day desc ROWS BETWEEN CURRENT ROW AND 9 following) as line10,
avg(nvl(tclose,0)) over (partition by code order by trading_day desc ROWS BETWEEN CURRENT ROW AND 19 following) as line20,
avg(nvl(tclose,0)) over (partition by code order by trading_day desc ROWS BETWEEN CURRENT ROW AND 39 following) as line40,
nvl(vaturnover/(lag(vaturnover,1) over (partition by code order by trading_day asc)),0) as va_ratio
from scott.daily_market
) b
where a.trading_day=b.trading_day and a.code = b.code
)
–where customer_id=‘0’ and start_time>sysdate-30