背景：订单表a存放字段如下：dt，uid，order_id，create_time，year，month，day。用户基础信息表b存放字段如下：uid， reg_time，year，month，day。要求以2018年01月注册且完单的用户为对象，提取2019年1月-12月每个月连续n个月无完单用户数。输出字段：月份、持续无呼叫月份、人数
知识点
1、max的筛选意识
2、if函数
select mon,
(mon-max_mon) silent_days, --连续无呼叫月数
sum(1) pas_cnt
from
(
select a.mon,
b.pid,
max(if(c.mon<=a.mon,c.mon,'0')) max_mon --每个月之前最近的呼叫月份
from
--每个月份，可用临时表代替
(
select month mon,
1 as join_on
from gulfstream_ods.g_city
where concat(year,month) between '201601' and '201612'
group by month
) a
left outer join
--2015年12月注册且有呼叫的用户
(
select a.pid,a.join_on
from
(
select pid,
1 as join_on
from a
where concat(year,month,day)='20151231'
and month(reg_time)=12
and year(reg_time)=2015
group by pid
)b1
join
(
select pid
from b
where concat(year,month) ='201512'
group by pid
)b2
on b1.pid=b2.pid
) b
on (a.join_on = b.join_on)
left outer join
--每个月呼叫用户
(
select month mon,pid
from b
where concat(year,month) between '201601' and '201612'
group by month,pid
) c
on (b.pid = c.pid)
group by a.mon,b.pid
) a
group by mon,(mon-max_mon)
网友评论