select code,changeSign,sum(changeSign) over(partition by code order by ddate) unRiseDays from
(
select
code,
ddate,
case when price>=lag(price) over(partition by code order by ddate)
then 0 else 1 end changeSign
from stock_record
)
)
group by code,unRiseDays
)
group by code
having max(risenum) > 5
复制代码
用开源的集算器去写则简单很多
A1=connect@l(“orcl”).query@x(“select * from stock_record order by ddate”)2=A1.group(code)3=A2.new(code,~.group@i(price < price[-1]).max(~.len())-1:maxrisedays)计算每只股票的连续上涨天数4=A3.select(maxrisedays>=5)选出符合条件的记录2 列出每一个用户最近一次登录间隔
SQL的大致写法
WITH TT AS
(SELECT RANK() OVER(PARTITION BY uid ORDER BY logtime DESC) rk, T.* FROM t_loginT)
SELECT uid,(SELECT TT.logtime FROM TT where TT.uid=TTT.uid and TT.rk=1)
-(SELET TT.logtim FROM TT WHERE TT.uid=TTT.uid and TT.rk=2) interval