with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$'then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
max(raw_p1.user_id) as user_id
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.group_id
having count(raw_p1.group_id)>=3)
select
core.user_id
,if(active.user_id is null,0,1) as active_tag 
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
active
on
core.user_id=active.user_id

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$'then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3)
select
core.user_id
,if(active.user_id is null,0,1) as active_tag 
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
active
on
core.user_id=active.user_id

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$'then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3
order by raw_p1.user_id)
select
core.user_id
,if(active.user_id is null,0,1) as active_tag 
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
active
on
core.user_id=active.user_id

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$'then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通|新世界百货|方恒购物中心)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3
order by raw_p1.user_id)
select
core.user_id
,if(active.user_id is null,0,1) as active_tag 
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
active
on
core.user_id=active.user_id

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3
order by raw_p1.user_id)
select
core.user_id
,if(active.user_id is null,0,1) as active_tag 
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
active
on
core.user_id=active.user_id

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
,1 as active_tag
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3
order by raw_p1.user_id)
select
user_id
,active_tag
from active

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3
order by raw_p1.user_id)
select
active.user_id
,1 as active_tag
from active
union
(select
 b.user_id
 ,0 as active_tag
from hello_bike_riding_rcd b
 where not exists(
 select 1
 from active
 where active.user_id=b.user_id
 )
group by b.user_id)

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3
order by raw_p1.user_id)
select
active.user_id
,1 as active_tag
from active
union
(select
 b.user_id
 ,0 as active_tag
from hello_bike_riding_rcd b
 where not exists(
 select 1
 from active
 where b.user_id=b.user_id
 )
group by b.user_id)

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
,1 as active_tag
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3
order by raw_p1.user_id)
select
core.user_id
,if(active.user_id is null,0,1) as active_tag 
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
active
on
core.user_id=active.user_id

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
,1 as active_tag
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3)
select
active.user_id
,active.active_tag
from active
union all
(select
 a.user_id
 ,0 as active_tag
from hello_bike_riding_rcd a
where a.user_id not in (select user_id from active)
group by a.user_id);

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心|凯德广场'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
,1 as active_tag
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3)
select
active.user_id
,active.active_tag
from active
union all
(select
 a.user_id
 ,0 as active_tag
from hello_bike_riding_rcd a
where a.user_id not in (select user_id from active)
group by a.user_id);

with active as (
with raw as (
select
core.user_id
,core.month_index
,count(distinct core.day_index) as days
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
,(case
when a.start_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.start_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as start_loc_type
,(case
when a.end_loc regexp '^(将台西|望京|望京南|阜通)$' then '地铁站'
when a.end_loc not regexp '^(将台西|望京|望京南|阜通)$' then '写字楼'
end) as end_loc_type
from hello_bike_riding_rcd a
where concat(a.start_loc,a.end_loc) not regexp '新世界百货|方恒购物中心|凯德广场'
having concat(start_loc_type,end_loc_type) regexp '(地铁站写字楼)|(写字楼地铁站)'
order by a.user_id,a.start_time) core
group by core.user_id,core.month_index
having days>=5
order by core.user_id,core.month_index
)
select
distinct raw_p1.user_id
,1 as active_tag
from
(select
raw.user_id
,raw.month_index
,raw.days
,case 
when (
(@prev_id is null or @prev_id=raw.user_id) and (@prev_month is null or @prev_month+1=raw.month_index)
) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:=raw.user_id as lc_id
,@prev_month:=raw.month_index as lc_month
from raw
,(select @prev_id:=null, @prev_month:=null,@group_id:=0) init_vars) raw_p1
group by raw_p1.user_id, raw_p1.group_id
having count(raw_p1.group_id)>=3)
select
active.user_id
,active.active_tag
from active
union all
(select
 a.user_id
 ,0 as active_tag
from hello_bike_riding_rcd a
where a.user_id not	in (select user_id from active)
group by a.user_id);

with raw4 as(
with raw3 as(
with raw2 as(
with raw1 as(
select
r1.user_id
,r1.start_time
,timestampdiff(month,'2020-1-1',r1.start_time) as month_index
,day(r1.start_time) as day_inmon
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
from hello_bike_riding_rcd a) r1
left join
(select
 a.loc_nm
 ,if( a.loc_nm ='北京机床研究所', '写字楼',a.loc_type) as loc_type
from gd_loc_map a) loc_s
on
r1.start_loc=loc_s.loc_nm
left join
(select
 a.loc_nm
 ,if( a.loc_nm ='北京机床研究所', '写字楼',a.loc_type) as loc_type
from gd_loc_map a) loc_e
on
r1.end_loc=loc_e.loc_nm
where concat(loc_e.loc_type,loc_s.loc_type) regexp '写字楼' 
and	concat(loc_e.loc_type,loc_s.loc_type) regexp '地铁站'
order by r1.user_id,r1.start_time) 
select
raw1.*
from raw1
left join
(select
 raw1.user_id
 ,raw1.month_index
 ,count(distinct raw1.day_inmon) as days
from raw1
group by raw1.user_id,raw1.month_index) day_tag
on
raw1.user_id=day_tag.user_id
and	
raw1.month_index=day_tag.month_index
where day_tag.days>=5
order by raw1.user_id,raw1.start_time)
select
raw.user_id
,raw.start_time
,raw.month_index
,raw.group_id
from
(select
raw2.user_id
,raw2.start_time
,raw2.month_index
,case when
((@prev_id is null or @prev_id=raw2.user_id)
 and (@prev_mon is null or (@prev_mon=raw2.month_index or @prev_mon+1=raw2.month_index))
 ) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:= raw2.user_id
,@prev_mon:=raw2.month_index
from raw2,
(select @prev_mon:=null, @prev_id:=null,@group_id:=0) init_vars) raw)
select
core.user_id
from
(select
 raw3.user_id
 ,raw3.group_id
 ,count(distinct raw3.month_index) as duration
from raw3
group by raw3.user_id,raw3.group_id
having duration >=3) core
group by core.user_id
)
select
core.user_id
,if(raw4.user_id is null,0,1) as active_tag
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
raw4
on core.user_id=raw4.user_id

with raw4 as(
with raw3 as(
with raw2 as(
with raw1 as(
select
r1.user_id
,r1.start_time
,timestampdiff(month,'2020-1-1',r1.start_time) as month_index
,day(r1.start_time) as day_inmon
from
(select
a.user_id
,a.start_time
,a.start_loc
,a.end_loc
from hello_bike_riding_rcd a) r1
left join
(select
 a.loc_nm
 ,a.loc_type
from gd_loc_map a) loc_s
on
r1.start_loc=loc_s.loc_nm
left join
(select
 a.loc_nm
 ,a.loc_type
from gd_loc_map a) loc_e
on
r1.end_loc=loc_e.loc_nm
where concat(loc_e.loc_type,loc_s.loc_type) regexp '写字楼' 
and	concat(loc_e.loc_type,loc_s.loc_type) regexp '地铁站'
order by r1.user_id,r1.start_time) 
select
raw1.*
from raw1
left join
(select
 raw1.user_id
 ,raw1.month_index
 ,count(distinct raw1.day_inmon) as days
from raw1
group by raw1.user_id,raw1.month_index) day_tag
on
raw1.user_id=day_tag.user_id
and	
raw1.month_index=day_tag.month_index
where day_tag.days>=5
order by raw1.user_id,raw1.start_time)
select
raw.user_id
,raw.start_time
,raw.month_index
,raw.group_id
from
(select
raw2.user_id
,raw2.start_time
,raw2.month_index
,case when
((@prev_id is null or @prev_id=raw2.user_id)
 and (@prev_mon is null or (@prev_mon=raw2.month_index or @prev_mon+1=raw2.month_index))
 ) then @group_id
else @group_id:=@group_id+1
end as group_id
,@prev_id:= raw2.user_id
,@prev_mon:=raw2.month_index
from raw2,
(select @prev_mon:=null, @prev_id:=null,@group_id:=0) init_vars) raw)
select
core.user_id
from
(select
 raw3.user_id
 ,raw3.group_id
 ,count(distinct raw3.month_index) as duration
from raw3
group by raw3.user_id,raw3.group_id
having duration >=3) core
group by core.user_id
)
select
core.user_id
,if(raw4.user_id is null,0,1) as active_tag
from
(select
a.user_id
from hello_bike_riding_rcd a
group by a.user_id) core
left join
raw4
on core.user_id=raw4.user_id

with r1 as (
select
raw.user_id
,raw.start_loc
,raw.end_loc
,raw.loc_start
,raw.loc_end
,raw.month_index
,raw.day_index
,raw.days
,raw.group_id
from
(select
core_raw.user_id
,core_raw.start_loc
,core_raw.end_loc
,core_raw.loc_start
,core_raw.loc_end
,core_raw.month_index
,core_raw.day_index
,core_raw.days
,case when(
(@prev_month is null or (@prev_month+1=core_raw.month_index or @prev_month=core_raw.month_index))
and (@prev_id is null or @prev_id=core_raw.user_id)
) then @group_id
else @group_id:=@group_id +1
end as group_id,
@prev_id:=core_raw.user_id,
@prev_month:=core_raw.month_index
from
(select
core.user_id
,core.start_loc
,core.end_loc
,loc_s.loc_type as loc_start
,loc_e.loc_type as loc_end 
,core.month_index
,core.day_index
,days.days
from
(select
a.user_id
,a.start_loc
,a.end_loc
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,day(a.start_time) as day_index
from hello_bike_riding_rcd a
order by a.user_id ,a.start_time
) core
left join
(select
a.user_id
,TIMESTAMPDIFF(MONTH, '2020-1-1', a.start_time) as month_index
,count(distinct day(a.start_time)) as days
from hello_bike_riding_rcd a
group by a.user_id, month_index
order by a.user_id,month_index) days
on core.user_id=days.user_id
and core.month_index=days.month_index
left join 
(select
a.loc_nm
,a.loc_type
from gd_loc_map a) loc_s
on
core.start_loc=loc_s.loc_nm
left join 
(select
a.loc_nm
,a.loc_type
from gd_loc_map a) loc_e
on
core.end_loc=loc_e.loc_nm
where days.days>=5 and loc_s.loc_type <> '购物广场' and loc_e.loc_type <> '购物广场'
) core_raw,
(select @prev_id:=null, @prev_month:=null, @group_id:=0) init_vars) raw)
(select
raw_y.user_id
,1 as active_tag
from
(select
r1.user_id
,r1.group_id
,r1.days
,mons.mons
from r1
left join
(select
r1.user_id
,r1.group_id
,count(distinct r1.month_index) as mons
from r1
group by r1.user_id,r1.group_id) mons
on
r1.user_id=mons.user_id
and	r1.group_id=mons.group_id
where mons.mons>=3) raw_y
group by raw_y.user_id)
union
(select
 a.user_id
 ,0 as active_tag
from hello_bike_riding_rcd a
group by a.user_id)