with t1 as
(
select 'A' c1, '2017-1-1' c2 from dual union all
select 'A' c1, '2017-10-1' c2 from dual union all
select 'A' c1, '2017-3-1' c2 from dual union all
select 'B' c1, '2017-1-1' c2 from dual union all
select 'B' c1, '2017-3-1' c2 from dual union all
select 'B' c1, '2017-8-30' c2 from dual
)
, t2 as
(
select row_number() over(order by c1, to_date(c2, 'yyyy-mm-dd')) rn, c1, to_date(c2, 'yyyy-mm-dd') c2 from t1
)
select a.c1, sum(case when b.c1 is not null and months_between(b.c2, a.c2)<=6 then 1 else 0 end)
from t2 a
left join t2 b on a.c1=b.c1 and a.rn=b.rn-1
group by a.c1
with t1 as
(
select 'A' id1, date'2017-1-1' start_date from dual union all
select 'A' id1, date'2017-8-1' start_date from dual union all
select 'A' id1, date'2017-10-1' start_date from dual union all
select 'A' id1, date'2017-11-1' start_date from dual union all
select 'A' id1, date'2017-12-1' start_date from dual union all
select 'A' id1, date'2018-7-1' start_date from dual union all
select 'A' id1, date'2018-10-1' start_date from dual union all
select 'A' id1, date'2018-11-1' start_date from dual union all
select 'B' id1, date'2017-12-1' start_date from dual union all
select 'B' id1, date'2018-7-1' start_date from dual union all
select 'B' id1, date'2018-10-1' start_date from dual union all
select 'B' id1, date'2018-11-1' start_date from dual UNION ALL
select 'B' id1, date'2019-10-1' start_date from dual union all
select 'B' id1, date'2020-11-1' start_date from dual
)
, t2 as
(select id1
,start_date start_day
,nvl(lead(start_date) over(partition by id1 order by start_date),date'2999-12-31') end_day
,row_number() over(partition by id1 order by start_date) rn
from t1)
select
id1
,max(num1)+1 --最大連續次數
from (
SELECT ID1,TYPE1,COUNT(1) num1
FROM (select rn-rn1 TYPE1
,a1.*
from (
select a.*
,row_number() over(partition by id1 order by start_day) rn1
from (select t2.id1
,t2.rn
,t2.start_day
,t2.end_day
,case when add_months(t2.start_day,6) > t2.end_day then 1 else 0 end lx_rn
from t2
where case when add_months(t2.start_day,6) > t2.end_day then 1 else 0 end > 0
) a
) a1
) A2
GROUP BY ID1,TYPE1)
group by id1
;
看看這種
**桔妹導讀：**深耕人工智能領域，致力于探索AI讓出行更美好的滴滴AI Labs再次斬獲國際大獎，這次獲獎的專案是什么呢？一起來看看詳細報道吧！ 近日，由國際計算語言學協會ACL（The Association for Computational Linguistics）舉辦的世界最具影響力的機器 ......
我們經常在資料庫中使用 LIKE 運算子來完成對資料的模糊搜索，LIKE 運算子用于在 WHERE 子句中搜索列中的指定模式。 如果需要查找客戶表中所有姓氏是“張”的資料，可以使用下面的 SQL 陳述句： SELECT * FROM Customer WHERE Name LIKE '張%' 如果需要 ......
關于MySQL的二進制日志（binlog），我們都知道二進制日志（binlog）非常重要，尤其當你需要point to point災難恢復的時侯，所以我們要對其進行備份。關于二進制日志（binlog)的備份,可以基于flush logs方式先切換binlog，然后拷貝&壓縮到到遠程服務器或本地服務器 ......
，但是有個問題，這樣求的是所有連續報讀次之和，我這個需求想求的是最大連續次數