编辑代码

CREATE DATABASE test;
use test;
create table tmp_liujg_dau_based(
imp_date varchar(20) not null comment '⽇期',
qimei varchar(20) not null comment '⽤户唯⼀标识',
is_new varchar(10) comment '新⽤户表示，1表示新⽤户，0表示⽼⽤户',
primary key(imp_date,qimei));
ALTER TABLE tmp_liujg_dau_based COMMENT '⽤户活跃模型表';

create table tmp_liujg_packed_based 
(
imp_date varchar(20) comment '⽇期',
report_time varchar(20) comment '领取时间戳',
qimei varchar(20) not null comment '⽤户唯⼀标识',
add_money varchar(20) not null comment '领取⾦额，单位为分');
ALTER TABLE tmp_liujg_packed_based COMMENT '红包参与领取模型表';

insert into tmp_liujg_dau_based values('20190301','001','0')
;insert into tmp_liujg_dau_based values('20190301','002','0')
;insert into tmp_liujg_dau_based values('20190301','003','1')
;insert into tmp_liujg_dau_based values('20190301','004','1')
;insert into tmp_liujg_dau_based values('20190301','005','1')
;insert into tmp_liujg_dau_based values('20190301','006','1')
;insert into tmp_liujg_dau_based values('20190302','001','0')
;insert into tmp_liujg_dau_based values('20190302','002','0')
;insert into tmp_liujg_dau_based values('20190302','003','0')
;insert into tmp_liujg_dau_based values('20190302','005','0')
;insert into tmp_liujg_dau_based values('20190302','006','0')
;insert into tmp_liujg_dau_based values('20190302','007','1')
;insert into tmp_liujg_dau_based values('20190303','005','0')
;insert into tmp_liujg_dau_based values('20190303','006','0')
;insert into tmp_liujg_dau_based values('20190303','007','0')
;insert into tmp_liujg_dau_based values('20190303','008','1')
;insert into tmp_liujg_dau_based values('20190303','009','1')
;insert into tmp_liujg_dau_based values('20190303','010','1')
;insert into tmp_liujg_dau_based values('20190401','008','0')
;insert into tmp_liujg_dau_based values('20190401','009','0')
;insert into tmp_liujg_dau_based values('20190401','010','0')
;insert into tmp_liujg_dau_based values('20190401','011','1')
;insert into tmp_liujg_dau_based values('20190401','012','1')
;insert into tmp_liujg_dau_based values('20190402','009','0')
;insert into tmp_liujg_dau_based values('20190402','010','0')
;insert into tmp_liujg_dau_based values('20190402','011','0')
;insert into tmp_liujg_dau_based values('20190402','012','0')
;insert into tmp_liujg_dau_based values('20190402','013','1')
;insert into tmp_liujg_dau_based values('20190402','014','1')
;insert into tmp_liujg_dau_based values('20190501','001','0')
;insert into tmp_liujg_dau_based values('20190501','002','0')
;insert into tmp_liujg_dau_based values('20190501','008','0')
;insert into tmp_liujg_dau_based values('20190501','007','0')
;insert into tmp_liujg_dau_based values('20190501','015','1')
;insert into tmp_liujg_dau_based values('20190501','016','1')
;insert into tmp_liujg_dau_based values('20190501','017','1')
;insert into tmp_liujg_dau_based values('20190501','018','1')
;insert into tmp_liujg_dau_based values('20190601','008','0')
;insert into tmp_liujg_dau_based values('20190601','017','0')
;insert into tmp_liujg_dau_based values('20190601','018','0')
;insert into tmp_liujg_dau_based values('20190601','019','1')
;insert into tmp_liujg_dau_based values('20190601','020','1')
;insert into tmp_liujg_dau_based values('20190601','021','1')
;insert into tmp_liujg_dau_based values('20190601','022','1')
;insert into tmp_liujg_dau_based values('20190603','021','0')
;insert into tmp_liujg_dau_based values('20190603','022','0')
;insert into tmp_liujg_dau_based values('20190603','011','0')
;insert into tmp_liujg_dau_based values('20190603','012','0')
;insert into tmp_liujg_dau_based values('20190603','023','1')
;insert into tmp_liujg_dau_based values('20190701','023','0')
;insert into tmp_liujg_dau_based values('20190701','008','0')
;insert into tmp_liujg_dau_based values('20190701','011','0')
;insert into tmp_liujg_dau_based values('20190701','022','0')
;insert into tmp_liujg_dau_based values('20190701','012','0')
;insert into tmp_liujg_dau_based values('20190701','024','1')
;insert into tmp_liujg_dau_based values('20190701','025','1')
;insert into tmp_liujg_dau_based values('20190701','026','1')
;insert into tmp_liujg_dau_based values('20190701','027','1')
;insert into tmp_liujg_dau_based values('20190705','026','0')
;insert into tmp_liujg_dau_based values('20190705','027','0')
;insert into tmp_liujg_dau_based values('20190705','009','0')
;insert into tmp_liujg_dau_based values('20190705','010','0')
;insert into tmp_liujg_dau_based values('20190705','028','1')
;insert into tmp_liujg_dau_based values('20190705','029','1');

insert into tmp_liujg_packed_based values('20190301','2019/03/01
10:15:01','001','1.05');
insert into tmp_liujg_packed_based values('20190301','2019/03/01
13:15:01','001','2.30');
insert into tmp_liujg_packed_based values('20190301','2019/03/01
11:15:01','002','0.80');insert into tmp_liujg_packed_based values('20190301','2019/03/01
17:15:01','002','0.89');insert into tmp_liujg_packed_based values('20190301','2019/03/01
14:15:01','003','2.12');insert into tmp_liujg_packed_based values('20190301','2019/03/01
18:15:01','003','1.12');insert into tmp_liujg_packed_based values('20190301','2019/03/01
17:15:01','005','1.12');insert into tmp_liujg_packed_based values('20190301','2019/03/01
19:15:01','005','0.12');insert into tmp_liujg_packed_based values('20190301','2019/03/01
09:15:01','006','0.98');insert into tmp_liujg_packed_based values('20190301','2019/03/01
11:15:01','006','1.45');insert into tmp_liujg_packed_based values('20190302','2019/03/02
09:30:01','001','0.78');insert into tmp_liujg_packed_based values('20190302','2019/03/02
19:30:01','001','0.88');insert into tmp_liujg_packed_based values('20190302','2019/03/02
10:30:01','003','0.68');insert into tmp_liujg_packed_based values('20190302','2019/03/02
09:30:01','005','1.01');insert into tmp_liujg_packed_based values('20190302','2019/03/02
14:30:01','005','1.88');insert into tmp_liujg_packed_based values('20190302','2019/03/02
14:30:01','006','1.88');insert into tmp_liujg_packed_based values('20190302','2019/03/02
10:30:01','006','0.68');insert into tmp_liujg_packed_based values('20190302','2019/03/02
15:30:01','007','0.68');insert into tmp_liujg_packed_based values('20190302','2019/03/02
16:30:01','007','1.78');insert into tmp_liujg_packed_based values('20190303','2019/03/03
16:30:01','005','0.68');insert into tmp_liujg_packed_based values('20190303','2019/03/03
08:50:01','006','0.32');insert into tmp_liujg_packed_based values('20190303','2019/03/03
10:50:01','006','1.78');insert into tmp_liujg_packed_based values('20190303','2019/03/03
10:50:01','007','0.32');insert into tmp_liujg_packed_based values('20190303','2019/03/03
15:50:01','008','1.01');insert into tmp_liujg_packed_based values('20190303','2019/03/03
17:50:01','008','1.68');insert into tmp_liujg_packed_based values('20190303','2019/03/03
10:30:01','010','1.88');insert into tmp_liujg_packed_based values('20190303','2019/03/03
15:50:01','010','0.32');insert into tmp_liujg_packed_based values('20190401','2019/04/01
09:50:00','008','0.18');insert into tmp_liujg_packed_based values('20190401','2019/04/01
11:50:00','009','0.88');insert into tmp_liujg_packed_based values('20190401','2019/04/01
12:35:00','009','0.32');insert into tmp_liujg_packed_based values('20190401','2019/04/01
17:50:00','010','1.01');insert into tmp_liujg_packed_based values('20190401','2019/04/01
12:35:00','011','1.68');insert into tmp_liujg_packed_based values('20190401','2019/04/01
12:50:00','011','0.88');insert into tmp_liujg_packed_based values('20190401','2019/04/01
15:50:00','012','0.32');insert into tmp_liujg_packed_based values('20190401','2019/04/01
17:50:00','012','1.68');insert into tmp_liujg_packed_based values('20190401','2019/04/01
12:35:00','012','1.88');insert into tmp_liujg_packed_based values('20190402','2019/04/02
09:50:00','009','0.18');insert into tmp_liujg_packed_based values('20190402','2019/04/02
19:50:00','009','1.18');insert into tmp_liujg_packed_based values('20190402','2019/04/02
17:50:00','010','0.88');insert into tmp_liujg_packed_based values('20190402','2019/04/02
16:50:00','010','0.32');insert into tmp_liujg_packed_based values('20190402','2019/04/02
19:50:00','010','0.32');insert into tmp_liujg_packed_based values('20190402','2019/04/02
16:50:00','013','0.88');insert into tmp_liujg_packed_based values('20190402','2019/04/02
19:55:00','013','0.88');insert into tmp_liujg_packed_based values('20190402','2019/04/02
20:55:00','013','1.01');insert into tmp_liujg_packed_based values('20190402','2019/04/02
19:55:00','014','0.32');insert into tmp_liujg_packed_based values('20190402','2019/04/02
20:55:00','014','1.01');insert into tmp_liujg_packed_based values('20190501','2019/05/01
09:50:00','001','1.18');insert into tmp_liujg_packed_based values('20190501','2019/05/01
09:55:00','002','0.32');insert into tmp_liujg_packed_based values('20190501','2019/05/01
10:55:00','002','0.32');insert into tmp_liujg_packed_based values('20190501','2019/05/01
10:00:00','007','0.88');insert into tmp_liujg_packed_based values('20190501','2019/05/01
10:55:00','015','0.88');insert into tmp_liujg_packed_based values('20190501','2019/05/01
10:00:00','015','0.32');insert into tmp_liujg_packed_based values('20190501','2019/05/01
14:00:00','017','1.01');insert into tmp_liujg_packed_based values('20190501','2019/05/01
15:00:00','017','1.01');insert into tmp_liujg_packed_based values('20190501','2019/05/01
15:30:00','018','0.88');insert into tmp_liujg_packed_based values('20190501','2019/05/01
16:30:00','018','0.68');insert into tmp_liujg_packed_based values('20190601','2019/06/01
09:50:00','008','1.38');insert into tmp_liujg_packed_based values('20190601','2019/06/01
14:50:00','017','0.88');insert into tmp_liujg_packed_based values('20190601','2019/06/01
11:50:00','019','1.01');insert into tmp_liujg_packed_based values('20190601','2019/06/01
13:50:00','019','0.88');insert into tmp_liujg_packed_based values('20190601','2019/06/01
14:50:00','019','0.68');insert into tmp_liujg_packed_based values('20190602','2019/06/02
09:50:00','021','0.38');insert into tmp_liujg_packed_based values('20190602','2019/06/02
13:50:00','012','0.88');insert into tmp_liujg_packed_based values('20190602','2019/06/02
15:50:00','012','1.12');insert into tmp_liujg_packed_based values('20190602','2019/06/02
13:59:00','023','0.88');insert into tmp_liujg_packed_based values('20190602','2019/06/02
15:50:00','023','1.01');insert into tmp_liujg_packed_based values('20190701','2019/07/01
09:50:00','023','0.38');insert into tmp_liujg_packed_based values('20190701','2019/07/01
13:50:00','023','0.78');insert into tmp_liujg_packed_based values('20190701','2019/07/01
14:50:00','008','0.68');insert into tmp_liujg_packed_based values('20190701','2019/07/01
14:50:00','024','0.68');insert into tmp_liujg_packed_based values('20190701','2019/07/01
15:50:00','024','1.68');insert into tmp_liujg_packed_based values('20190701','2019/07/01
14:50:00','026','0.68');insert into tmp_liujg_packed_based values('20190701','2019/07/01
18:50:00','026','1.68');insert into tmp_liujg_packed_based values('20190701','2019/07/01
14:50:00','027','0.68');insert into tmp_liujg_packed_based values('20190701','2019/07/01
19:35:00','027','1.11');insert into tmp_liujg_packed_based values('20190702','2019/07/02
09:50:00','026','0.78');insert into tmp_liujg_packed_based values('20190702','2019/07/02
11:50:00','026','0.78');insert into tmp_liujg_packed_based values('20190702','2019/07/02
13:50:00','028','1.01');insert into tmp_liujg_packed_based values('20190702','2019/07/02
14:35:00','028','0.88');insert into tmp_liujg_packed_based values('20190702','2019/07/02
15:35:00','028','0.33');
/*
select imp_date as day, count(distinct qimei) as DAU from tmp_liujg_dau_based
group by imp_date
having date_format(imp_date,'%Y-%m-%d')>='2019-06-01';
*/
/*
select imp_date, user_type, sum(add_money)/count(distinct qimei) as avg_amount, 
count(*)/count(distinct qimei) as avg_cnt
from ( 
    select b.*,
    (case when is_new=1 then 'new_user'
    when is_new=0 then 'old_user' 
    else 'unknown_user' end) as user_type from 
    tmp_liujg_dau_based as a 
    right outer join tmp_liujg_packed_based as b
    on a.qimei = b.qimei and a.imp_date=b.imp_date)as c
group by imp_date, user_type
having date_format(imp_date,'%Y-%m-%d')>='2019-06-01';
*/

/*
select date_format(imp_date,'%Y-%m') as mon, count(distinct imp_date) as cnt,
count(distinct qimei) as user_cnt,
round(sum(add_money)/count(distinct qimei),2) as avg_amount,
round(count(*)/count(distinct qimei),2) as avg_cnt from tmp_liujg_packed_based
group by date_format(imp_date,'%Y-%m');
*/


/*
select left(imp_date,6) as 'month', user_type , count(distinct qimei) as user_cnt, 
round(count(*)/count(distinct qimei),2) as avg_ac_time from
(
    select a.imp_date, a.qimei,b.bmonth,b.qimei  hb_qimei, (
        case when b.qimei is not null then '红包用户'
        else '非红包用户' end
    ) as user_type 
    from tmp_liujg_dau_based as a 
    left join (
        select distinct date_format(imp_date,'%Y-%m') as bmonth, qimei from tmp_liujg_packed_based
        where date_format(imp_date,'%Y-%m')>='2016-03'
    ) as b
    on date_format(a.imp_date,'%Y-%m')=b.bmonth and a.qimei = b.qimei
    
)as cc
group by left(imp_date,6), user_type 
*/
/*
select day1, count(distinct user_day2)/count(distinct user_day1) as '次留',
count(distinct Hb_day2)/count(distinct Hb_day1) as '红包次留' from
(select aa.imp_date as day1, aa.qimei as user_day1,
bb.qimei as user_day2, 
aa.Hb_qimei as Hb_day1,
(case when aa.Hb_qimei is not null and bb.qimei is not null then aa.Hb_qimei end) as Hb_day2 from
(
    select a.*, c.qimei as Hb_qimei
    from tmp_liujg_dau_based as a left join (
    select imp_date, qimei from tmp_liujg_packed_based
    group by imp_date, qimei
    ) as c
    on a.qimei = c.qimei and a.imp_date=c.imp_date
)as aa
left outer join 
(
    select a.*, c.qimei as Hb_qimei
    from tmp_liujg_dau_based as a left join (
    select imp_date, qimei from tmp_liujg_packed_based
    group by imp_date, qimei
    ) as c
    on a.qimei = c.qimei and a.imp_date=c.imp_date
)as bb
on aa.qimei=bb.qimei and (aa.imp_date+1)=bb.imp_date) as cc
group by cc.day1
*/


/*
select a.imp_date, a.qimei, b.imp_date as report_day, b.add_money from
tmp_liujg_dau_based as a 
join 
(select imp_date ,qimei, add_money, @rank:=(
    case when @user=qimei then @rank+1 else 1 end
)as rn,
@user:=qimei
from tmp_liujg_packed_based, (
    select @user:=null, @rank:=0
)as c
order by qimei, imp_date) as b
on a.qimei = b.qimei
where a.is_new=1 and b.rn=1
*/


select a.imp_date, a.qimei, bb.date1 as report_day, 
round(bb.money2-bb.money1,2) as moneydiff,
TIMESTAMPDIFF(minute,bb.time2,bb.time1 ) as timediff from
tmp_liujg_dau_based as a 
join 
(   select b1.imp_date as date1, b2.imp_date as date2 ,b1.qimei, b1.rn,b1.add_money as money1,
    b2.add_money as money2,
    b1.report_time as time1, b2.report_time as time2 from
    (select imp_date ,qimei, add_money,report_time, @rank:=(
        case when @user=qimei then @rank+1 else 1 end
    )as rn,
    @user:=qimei
    from tmp_liujg_packed_based, (
        select @user:=null, @rank:=0
    )as c
    order by qimei, report_time) as b1
left join
    (select imp_date ,qimei, add_money,report_time, @rank:=(
        case when @user=qimei then @rank+1 else 1 end
    )as rn,
    @user:=qimei
    from tmp_liujg_packed_based, (
        select @user:=null, @rank:=0
    )as c
    order by qimei, report_time) as b2
on b1.qimei=b2.qimei and b1.rn=b2.rn-1
)as bb
on a.qimei = bb.qimei
where a.is_new=1 and bb.rn=1