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