本篇文章主要介绍了" 用户频次分布、新增用户留存、活跃用户留存"，主要涉及到方面的内容，对于其他数据库感兴趣的同学可以参考一下：
select * from a WHERE a.field1 NOT IN (select field1 from b)select * from a WHER...
select * from a WHERE a.field1 NOT IN (select field1 from b)
select * from a WHERE NOT EXISTS (select 1 from b where a.field1 = b.field1)
表a的条件加在最后，表b的条件加在括弧中。
select id from aa left join bb on aa.id=bb.id and bb.id is null
select count(uid) as onl,date_format(time,'%H') as hour from d_user_login201704 where id in (select * from ((select min(id) from d_user_login201704 where type=0 and time>= '2017-04-09 00:00:00' and time<='2017-04-09 23:59:59' group by(uid)) as tmptable)) group by hour;
--用户频次分布
select a.次数，count（a.userid）
from （select userid，count（userid）as 次数 from t_orderid where dt='10月29日’ order by userid）a
group by a.次数
--新增用户留存、活跃用户留存不会
select cityid,dt,count(a.userid)as cou
from (select userid from t_user where dt='2016年10月1日')a inner join(select cityid,dt,userid from t_order where dt>='2016年10月1日' and dt<='2016年12月1日')b
on a.userid=b.userid
group by city,dt
select * from t_user where dt between '2016-10-01' and '2016-12-01' ----新增用户
要看相对于哪个时间段的存留，比如，跟这个月（2017-06-01-2017-06-30）比
select cityid,count(distinct userid) from t_order
where userid iN (select userid from t_user where dt between '2016-10-01' and '2016-12-01' )
and dt between 2017-06-01 and 2017-06-30
group by cityid
活跃用户
select userid fromt_order
where dt between '2016-10-01' and '2016-12-01'
and userid in (select userid from t_user where dt< '2016-10-01' )
---2016.10之前注册的，并且在-这个时间段的有订单的用户是活跃的，然后 对比 哪个区间 他还在不在
select cityid,count(distinct userid) from t_order
where userid iN (select userid fromt_order
where dt between '2016-10-01' and '2016-12-01'
