oracle求指定时间连续天数

需求：得到指定时间开始往前数连续的天数，如id为a的数据对应时间：20200115，20200114，20200112，20200101，给定查询时间为20200115，则连续天数为2，一中断就终止

 

初始数据：

答案：

-- 部分数据

WITH T_AG_TOP_SYMBOLS AS (SELECT '20191024' AS UPDATE_DATE, '000158' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '常山北明' AS SYMBOL_NAME, '华为产业链概念，2天2板。华为发布折叠屏手机国行版；龙头诚迈科技11天9板；公司互动平台表示为华为鸿蒙系统的合作伙伴' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '000679' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '大连友谊' AS SYMBOL_NAME, '房地产概念。公司是专注于大连、沈阳、邯郸的商业地产开发商' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '002084' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '海鸥住工' AS SYMBOL_NAME, '业绩增长概念。公司主要从事高档水龙头零组件、排水器、温控阀等；三季度净利润5395万元，同比增长1086.09%' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '002591' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '恒大高新' AS SYMBOL_NAME, '业绩预增概念。公司预告年报净利润同比增长186%-272%；为国内少数几家掌握垃圾焚烧炉防护技术并有能力进行大规模施工的企业之一' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '300032' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '金龙机电' AS SYMBOL_NAME, '创业板壳股概念。上市公司重组新规修改，支持创业板重组上市；此前为创业板壳股，公司加工柔性AMOLED的产线目前有两条，预计上半年净利润同比增长93%-64.5%，因财务费用大幅减少，' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '300200' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '高盟新材' AS SYMBOL_NAME, '业绩预增概念。预告年报净利润同比增长118.2%-147.9%，因华森塑胶收入增长等' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '300492' AS TICKER_SYMBOL, 'XSHZ' AS EXCHANGE_CD, '山鼎设计' AS SYMBOL_NAME, '公告利好，2天2板。股权转让-华图投资受让上市公司30%股份表决权' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '600223' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '鲁商发展' AS SYMBOL_NAME, '医美概念，4天4板。龙头鲁商置业4连板；多家美妆平台10分钟预售超去年双11全天；公司拟收购透明质酸（玻尿酸）生产商山东焦点生物股权，完成后将获得控制权；焦点生物目前拥有玻尿酸产能120吨/年' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191023' AS UPDATE_DATE, '600223' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '鲁商发展' AS SYMBOL_NAME, '化妆品概念，3天3板。龙头鲁商置业三连板；多家美妆平台10分钟预售超去年双11全天；公司收购山东焦点生物，标的是一家专业生产透明质酸的企业；主营山东地区房地产开发' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191022' AS UPDATE_DATE, '600223' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '鲁商发展' AS SYMBOL_NAME, '公告利好，2天2板。公司主营山东地区房地产开发，此前收购山东焦点生物，标的是一家专业生产透明质酸的企业' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191023' AS UPDATE_DATE, '603477' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '振静股份' AS SYMBOL_NAME, '养猪概念，16天8板。生猪价格站上37元/公斤，单周上涨超10%；公司拟收购巨星农牧，标的涉及畜禽养殖和饲料生产两大业务' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20190926' AS UPDATE_DATE, '603477' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '振静股份' AS SYMBOL_NAME, '公告利好，2天2板。并购重组-拟收购巨星农牧100%股份，标的涉及畜禽养殖和饲料生产两大业务' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '603477' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '振静股份' AS SYMBOL_NAME, '养猪概念，17天9板。生猪价格站上37元/公斤，单周上涨超10%；公司拟收购巨星农牧，标的涉及畜禽养殖和饲料生产两大业务' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191022' AS UPDATE_DATE, '603477' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '振静股份' AS SYMBOL_NAME, '养猪概念，9天5板。生猪价格站上37元/公斤，单周上涨超10%；公司拟收购巨星农牧，标的涉及畜禽养殖和饲料生产两大业务' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191017' AS UPDATE_DATE, '603477' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '振静股份' AS SYMBOL_NAME, '养猪概念，6天3板。公司拟收购巨星农牧，标的涉及畜禽养殖和饲料生产两大业务' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191016' AS UPDATE_DATE, '603477' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '振静股份' AS SYMBOL_NAME, '养猪概念。公司拟收购巨星农牧100%股份，标的涉及畜禽养殖和饲料生产两大业务' AS CSL_SENTENCE FROM DUAL UNION ALL SELECT '20191024' AS UPDATE_DATE, '603880' AS TICKER_SYMBOL, 'XSHG' AS EXCHANGE_CD, '南卫股份' AS SYMBOL_NAME, '医药概念。公司实控人旗下左右生物主要研发产品为治疗系统性红斑狼疮的青蒿素衍生物马来酸蒿乙醚胺' AS CSL_SENTENCE FROM DUAL) ,

-- 首先得到给定时间所有数据，后面方便匹配

T AS

(SELECT *

FROM T_AG_TOP_SYMBOLS

WHERE UPDATE_DATE = '20191024'),

-- 根据T表匹配全部数据，并根据TICKER_SYMBOL字段分组排序，按时间倒序

T2 AS

(SELECT A.*,

ROW_NUMBER() OVER(PARTITION BY A.TICKER_SYMBOL, A.EXCHANGE_CD ORDER BY A.TICKER_SYMBOL, A.UPDATE_DATE DESC) AS RANK

FROM T_AG_TOP_SYMBOLS A, T

WHERE T.TICKER_SYMBOL = A.TICKER_SYMBOL

--这里需要加个时间限制，否则大于指定时间的数据也会查询出来

AND A.UPDATE_DATE <= T.UPDATE_DATE),

-- 拿到分组后的数据，通过rank做连续两条记录时间相减，结果为1则给1，其它给0（这里给0和1主要是为了用来累乘），累乘后通过乘积=1判断是否连续，为1连续，其它不连续

TT AS

(SELECT TICKER_SYMBOL,

-- 这里是根据我这边的业务需求处理了，可以直接count

(CASE

WHEN TO_DATE(MAX(UPDATE_DATE), 'YYYYMMDD') < TRUNC(SYSDATE) THEN

COUNT(1)

ELSE

COUNT(1) - 1

END) AS COUNT,

TO_CHAR(TO_DATE(MIN(UPDATE_DATE), 'YYYYMMDD'),

'YYYY-MM-DD HH24:MI:SS') AS DATE_TIME

FROM (SELECT A.*,

-- 累乘步骤，因为给0会报错，所以为0时给2，本来可以根据积不为0判断连续，现在可以根据乘积为1判断连续，同样可以得到结果

EXP(SUM(LN(DECODE(A.NUM, 0, 2, A.NUM)))

OVER(PARTITION BY A.TICKER_SYMBOL ORDER BY A.UPDATE_DATE DESC)) AS SUM

FROM (SELECT T2.*, 1 AS NUM

FROM T2

--这里rank为1需要单独提取，因为后面语句只会1之后的数据

WHERE RANK = 1

UNION ALL

SELECT B.*,

(CASE

WHEN (TO_DATE(A.UPDATE_DATE, 'YYYYMMDD') -

TO_DATE(B.UPDATE_DATE, 'YYYYMMDD')) = 1 THEN

1

ELSE

0

END) AS NUM

FROM T2 A, T2 B

WHERE A.RANK = B.RANK - 1

AND A.TICKER_SYMBOL = B.TICKER_SYMBOL) A

ORDER BY TICKER_SYMBOL, UPDATE_DATE DESC)

-- sum即为累乘的乘积

WHERE SUM = 1

GROUP BY TICKER_SYMBOL)

SELECT B.SYMBOL,

B.SYMBOL_NAME,

T.TICKER_SYMBOL AS SYMBOL_NUM,

TT.COUNT,

(CASE

WHEN SUBSTR(T.TICKER_SYMBOL, 0, 1) = 6 THEN

'SH'

ELSE

'SZ'

END) AS MARKET,

B.INDUSTRY,

TO_CHAR(B.MARKET_VALUE / 100000000, 'FM99999990.00') AS MARKET_VALUE,

T.CSL_SENTENCE,

T.UPDATE_DATE,

TT.DATE_TIME

FROM T

LEFT JOIN TT

ON T.TICKER_SYMBOL = TT.TICKER_SYMBOL

LEFT JOIN T_AG_SYMBOL_INFO B

ON T.TICKER_SYMBOL = SUBSTR(B.SYMBOL, 0, 6)

ORDER BY B.SYMBOL

 

执行SQL后结果，其中count为连续的天数：