17,086
社区成员
发帖
与我相关
我的任务
分享
WITH tmp AS
(SELECT 'A' NAME
,500 score
,DATE '2020-1-1' da
FROM dual
UNION ALL
SELECT 'A' NAME
,1000 score
,DATE '2020-1-15' da
FROM dual
UNION ALL
SELECT 'A' NAME
,2000 score
,DATE '2020-2-1' da
FROM dual
UNION ALL
SELECT 'A' NAME
,1500 score
,DATE '2020-2-11' da
FROM dual
UNION ALL
SELECT 'A' NAME
,600 score
,DATE '2020-3-1' da
FROM dual
UNION ALL
SELECT 'A' NAME
,1600 score
,DATE '2020-3-11' da
FROM dual
UNION ALL
SELECT 'A' NAME
,2000 score
,DATE '2020-3-15' da
FROM dual
UNION ALL
SELECT 'B' NAME
,1500 score
,DATE '2020-1-1' da
FROM dual
UNION ALL
SELECT 'B' NAME
,1000 score
,DATE '2020-1-15' da
FROM dual
UNION ALL
SELECT 'B' NAME
,2000 score
,DATE '2020-2-1' da
FROM dual
UNION ALL
SELECT 'B' NAME
,1500 score
,DATE '2020-2-11' da
FROM dual
UNION ALL
SELECT 'B' NAME
,600 score
,DATE '2020-3-1' da
FROM dual
UNION ALL
SELECT 'B' NAME
,1600 score
,DATE '2020-3-11' da
FROM dual
UNION ALL
SELECT 'B' NAME
,500 score
,DATE '2020-4-1' da
FROM dual)
SELECT T1.NAME
,COUNT(DISTINCT da1)
FROM tmp t1
,(SELECT NAME
,add_months(S, RN - 1) da1
,add_months(S, RN) da2
FROM (SELECT LEVEL RN FROM DUAL CONNECT BY LEVEL <= 12)
,(SELECT NAME
,MIN(t.da) S
FROM tmp t
WHERE t.score >= 1000
GROUP BY NAME)) A
WHERE t1.score >= 1000
AND T1.NAME = A.NAME
AND t1.da BETWEEN da1 AND da2 - 0.00001
GROUP BY T1.NAME;
with tab1 as
(select 'a' name
,500 score
,date '2020-1-1' da
from dual
union all
select 'a' name
,1000 score
,date '2020-1-15' da
from dual
union all
select 'a' name
,2000 score
,date '2020-2-1' da
from dual
union all
select 'a' name
,1500 score
,date '2020-2-11' da
from dual
union all
select 'a' name
,600 score
,date '2020-3-1' da
from dual
union all
select 'a' name
,1600 score
,date '2020-5-11' da
from dual
union all
select 'a' name
,1600 score
,date '2020-5-11' da
from dual
union all
select 'a' name
,1600 score
,date '2020-5-12' da
from dual
union all
select 'a' name
,2000 score
,date '2020-3-15' da
from dual
union all
select 'b' name
,1500 score
,date '2020-1-1' da
from dual
union all
select 'b' name
,1000 score
,date '2020-1-15' da
from dual
union all
select 'b' name
,2000 score
,date '2020-2-1' da
from dual
union all
select 'b' name
,1500 score
,date '2020-2-11' da
from dual
union all
select 'b' name
,600 score
,date '2020-3-1' da
from dual
union all
select 'b' name
,1600 score
,date '2020-3-11' da
from dual
union all
select 'b' name
,500 score
,date '2020-4-1' da
from dual)
, tab2 as (
select t1.name, max(t1.score) score, t1.da, row_number() over(partition by t1.name order by t1.da) rn
from tab1 t1 group by t1.name, t1.da
)
, cur(name, score, da, rn) as(
select t1.name, t1.score, t1.da, t1.rn
from tab2 t1,
(select t2.name, min(t2.rn) rn from tab2 t2 where t2.score >= 1000 group by t2.name) t2
where t1.name = t2.name
and t1.rn = t2.rn
union all
select t1.name,
first_value(t1.score) over(partition by t1.name order by t1.rn),
first_value(t1.da) over(partition by t1.name order by t1.rn),
first_value(t1.rn) over(partition by t1.name order by t1.rn)
from tab2 t1, cur t2
where t1.score >= 1000
and t1.da > add_months(t2.da, 1)
and t1.name = t2.name
and t1.rn > t2.rn
)
select distinct t1.* from cur t1 order by t1.name, t1.da