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

Question

例如表结构是：

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 上涨
  这样的数据。麻烦各位大佬帮帮忙。实在没什么思路呀。谢谢啦

Answer 1

--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上应该直接运行不了的。