sql查询连续增长天数 这里写了一个查询净值连续增长天数的方法,跟大家分享 表结构示例:行情表t_hangqingbiao,存放 产品号,日期,累计净值 三个字段 查询每个产品号的累计净值连续增长的天数
with tmp_grow as(
select a.c_fundcode, a.d_date,
case when lag(a.f_totalnetvalue,1,1) over(partition by a.c_fundcode order by a.d_date) < a.f_totalnetvalue then 1 else 0 end is_grow
from t_hangqingbiao a
),
tmp_grow2 as( select a.c_fundcode, a.d_date,
row_number() over(partition by a.c_fundcode order by a.d_date) - sum(is_grow) over(partition by a.c_fundcode order by a.d_date) grow_group
from tmp_grow a)
select distinct a.c_fundcode,count(1)over(partition by a.c_fundcode,a.grow_group) - 1 grow_days,
min(a.d_date) over(partition by a.c_fundcode,a.grow_group) grow_begin_date,
max(a.d_date) over(partition by a.c_fundcode,a.grow_group) grow_end_date
from tmp_grow2 a
结果示例:产品号,连续增长天数,增长开始日期,增长结束日期
|