编辑代码

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';