求连续日期的有关问题
希望查询A股每只股票2014年以来连续涨停的最大天数。
例如F4代表股票代码，F2代表日期
F1 F2
0004 20140916
0004 20140917
0004 20141008
0004 20150709
0004 20150710
0004 20150713
0004 20150714
0005 20141009
0005 20141010
0005 20141011
所以F1为0004的最大连续涨停天数应该是 7月9 10 13 14号四天
所以F1为0005的最大连续涨停天数应该是三天
------解决思路----------------------
楼主，你应该有一个交易日的列表，就是哪天开盘交易的表。
------解决思路----------------------
下面是一个简单的例子，table1是涨停的日期，table2是交易日期。最终结果是连续涨停超过两天的日期。
with table1 as
(
select '20150707' dt from dual union all
select '20150709' dt from dual union all
select '20150710' dt from dual union all
select '20150713' dt from dual union all
select '20150714' dt from dual union all
select '20150716' dt from dual
)
, table2 as
(
select '20150707' dt from dual union all
select '20150708' dt from dual union all
select '20150709' dt from dual union all
select '20150710' dt from dual union all
select '20150713' dt from dual union all
select '20150714' dt from dual union all
select '20150715' dt from dual union all
select '20150716' dt from dual
)
, table1_rn as
(
select rownum rn, dt from table1
)
, table2_rn as
(
select rownum rn, dt from table2
)
, table_all as
(
select a.dt, to_number(a.rn)-to_number(b.rn) rn from table2_rn a left join table1_rn b on a.dt=b.dt order by a.dt
)
select dt from table_all where rn in (select rn from table_all group by rn having count(*)>1) order by dt
------解决思路----------------------
with t as(select '0004' f1,to_date('20140916','yyyymmdd') f2 from dual union all
select '0004',to_date('20140917','yyyymmdd') from dual union all
select '0004',to_date('20141018','yyyymmdd') from dual union all
select '0004',to_date('20150709','yyyymmdd') from dual union all
select '0004',to_date('20150710','yyyymmdd') from dual union all
select '0004',to_date('20150713','yyyymmdd') from dual union all
select '0004',to_date('20150714','yyyymmdd') from dual union all
select '0005',to_date('20141009','yyyymmdd') from dual union all
select '0005',to_date('20141010','yyyymmdd') from dual union all
select '0005',to_date('20141011','yyyymmdd') from dual),
tt as(
select f1,f2,f2-row_number()over(partition by f1 order by f2) fn
from t)
select f1,min(f2) as "开始连续",max(f2) as "结束连续",
max(f2)-min(f2)+1 as "连续天数"
from tt group by f1,fn order by 1,2
按f1分组给出了开始连续日期，结束日期，连续天数
你可以根据结果集再加条件