with
CET_Table
as
(
select
'A'
name
,1
as
month
,0
as
value
union
all
select
'A'
,2,10
union
all
select
'A'
,3,10
union
all
select
'A'
,4,20
union
all
select
'A'
,5,20
union
all
select
'A'
,6,20
union
all
select
'A'
,7,15
union
all
select
'A'
,8,20
union
all
select
'A'
,9,20
union
all
select
'A'
,10,20
union
all
select
'A'
,11,1
union
all
select
'A'
,12,1
union
all
select
'B'
,1,20
union
all
select
'B'
,2,15
union
all
select
'B'
,3,15
union
all
select
'B'
,4,10
union
all
select
'B'
,5,10
union
all
select
'B'
,6,10
union
all
select
'B'
,7,15
union
all
select
'B'
,8,15
union
all
select
'B'
,9,15
union
all
select
'B'
,10,15
union
all
select
'B'
,11,20
union
all
select
'B'
,12,20
),t
as
(
select
*,
month
-ROW_NUMBER() over(partition
by
name
,value
order
by
month
)
as
辅助列
--这里是精妙之处
from
CET_Table
)
select
name
,value,
COUNT
(
month
) 连续数次
from
t
group
by
name
,value,辅助列
having
COUNT
(
month
) >=3
/*
name
value 连续数次
A 20 3
A 20 3
B 10 3
B 15 4
*/