oracle如何查出连续上涨或连续下跌的股票数据

例如表结构是：

with as ashareeodprice as
(select '1234567' as wind_code,'20160806' as trade_date,1.1 as s_dq_pctchange from dual union all
select '1234567' as wind_code,'20160807' as trade_date,-1.2 as s_dq_pctchange from dual union all
select '1234567' as wind_code,'20160808' as trade_date,-1.3 as s_dq_pctchange from dual union all
select '1234567' as wind_code,'20160810' as trade_date,-1.1 as s_dq_pctchange from dual union all
select '1234567' as wind_code,'20160811' as trade_date,-1.1 as s_dq_pctchange from dual union all
select '444' as wind_code,'20160806' as trade_date,1.1 as s_dq_pctchange from dual union all
select '444' as wind_code,'20160807' as trade_date,-1.1 as s_dq_pctchange from dual union all
select '444' as wind_code,'20160808' as trade_date,-1.1 as s_dq_pctchange from dual 
)
select * from ashareeodprice

假如我想查询连续上涨或下跌三天或以上的数据则查出windcode = 1234567的数据，并知道他是涨还是跌，连续次数。如果我想查询连续上涨或下跌2天或以上的数据，则查出两条数据。类似

1234567 3 下跌
444 2 上涨
这样的数据。麻烦各位大佬帮帮忙。实在没什么思路呀。谢谢啦

--Sql Server脚本
;WITH ashareeodprice as
(
    select '1234567' as wind_code,'20160806' as trade_date,1.1 as s_dq_pctchange  union all
    select '1234567' as wind_code,'20160807' as trade_date,-1.2 as s_dq_pctchange  union all
    select '1234567' as wind_code,'20160808' as trade_date,-1.3 as s_dq_pctchange  union all
    select '1234567' as wind_code,'20160810' as trade_date,-1.1 as s_dq_pctchange  union all
    select '1234567' as wind_code,'20160811' as trade_date,-1.1 as s_dq_pctchange  union ALL
    select '1234567' as wind_code,'20160812' as trade_date,1.1 as s_dq_pctchange  union all
    select '444' as wind_code,'20160806' as trade_date,1.1 as s_dq_pctchange  union all
    select '444' as wind_code,'20160807' as trade_date,-1.1 as s_dq_pctchange  union all
    select '444' as wind_code,'20160808' as trade_date,-1.1 as s_dq_pctchange  
),cte AS (
    SELECT *,
        CASE  WHEN  s_dq_pctchange>0 THEN 1 ELSE 0 END AS col   --通过s_dq_pctchange正负数得到一个0/1的标识列
    FROM ashareeodprice
),cte2 AS (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY wind_code ORDER BY trade_date) AS rn,    --根据wind_code分组，trade_date排序得到一个rn排序列
        ROW_NUMBER() OVER(PARTITION BY wind_code,col ORDER BY trade_date) AS rn2, --根据wind_code,col标识列分组，trade_date排序得到一个rn2排序列
        ROW_NUMBER() OVER(PARTITION BY wind_code ORDER BY trade_date) - ROW_NUMBER() OVER(PARTITION BY wind_code,col ORDER BY trade_date) AS cnt    --以上两列相减的结果列
    FROM cte
),cte3 AS (
    SELECT wind_code,
        cnt,
        CASE col WHEN 0 THEN '下跌' WHEN 1 THEN '上涨'  END [上涨/下跌],
        COUNT(cnt) [连续天数] 
    FROM cte2
    GROUP BY wind_code,cnt,col      --按wind_code,cnt,col分组
    HAVING count(cnt)>=2    --连续的天数【你的条件】
)
SELECT wind_code,[上涨/下跌],[连续天数] 
FROM cte3;

wind_code 上涨/下跌 连续天数
--------- ----- -----------
1234567   下跌    4
444       下跌    2

如果你的上涨/下跌是通过正负数来判断的话，上面的脚本和思路你可以参考下。是sql server的脚本，不是Oracle的。Oracle上应该直接运行不了的。

先group by 然后加having ?? 能满足你吗
不太清楚你的需求。

不会Oracle,sql就不写了,反之什么关系型数据库思路都一样
查询连续涨思路是这样的:

表A:
(
查询全部数据和行号根据股票编码和日期排序 -表TBA
查询全部数据和行号根据股票编码和每股价格排序 -表TBB
其中日期排序和每股价格排序排序方向相同,相反的话是查连续跌用的
两表join TBA.行号=TBB.行号
生成一个列:equals
如果 TBA.日期=TBB.日期 and TBA.价格=TBB.价格 equals=0 反之为1
)

查询上面的表A,根据股票编码分组,查询sum(equals) 如果为0就是连续涨

连续跌思路相同


set @day=7;
set @lastDate=CURRENT_DATE();
set @startDate=DATE_SUB(@lastDate,interval @day day);
select @day,@startDate,@lastDate;

select wind_code from ashareeodprice
where s_dq_pctchange>0 and trade_date>=@lastDate and trade_date<=@startDate
GROUP BY wind_code
HAVING COUNT(0)=@day