查询结果要求.png
表结构.png
一、需求分析
统计2018年12月，【每日】的【新增用户在新增当天的充值笔数】、【每日充值的人员当中属于当日新增用户的人数】和【每日新增用户充值的总金额】，新增用户在新增日后的充值均不在统计范围内
二、测试分析：
1. 数据表选择：输出字段要求包含充值日期DATE、充值次数JYBS（count(A.trade_no)）、充值人数CZRS（count(DISTINCT A.user_id)）、充值金额CZJE（sum(A.trade_price)），所以选取le_user_deposit表 AS A；
2. 约束条件：业务发生时间是在2018年12月内，且必须是新增用户，而对于新增用户的选择，需要关联le_user AS B 表进行筛选
所以查询语句的Where条件必须限定业务发生的时间(A.gmt_create BETWEEN '2018-01-01 00:00:00' AND '2018-12-31 23:59:59')，并且排除掉在12.01之前已注册用户 B.gmt_create >= '2018-12-01 00:00:00'的业务
3. 场景设计：只统计新增用户在新增日当天的充值业务，即5种情况须考虑
①用户A在12.01当天注册并充值1笔，该笔数据统计；
②用户A在12.02/12.03/12.05这几天均有充值，该部分数据不统计，所以查询语句中必须要排除非注册当天的业务数据；
③用户B在12.01当天注册，但当天未充值，而是在12.03号充值，则该数据不统计；
④用户C在12.01当天注册，且充值多笔，则充值次数JYBS统计多次，充值人数CZRS只统计1次，充值金额CZJE累计多笔
⑤用户D已注册用户B在12.01当天充值，该部分数据不统计，所以where条件必须判断
三、创建数据，验证sql语句正确性
-- 1.1 创建用户表
CREATE TABLE `le_user` (
`user_id` varchar(20) DEFAULT NULL COMMENT '用户ID',
`phone_num` varchar(20) DEFAULT NULL COMMENT '用户手机号码',
`gmt_create` datetime DEFAULT NULL COMMENT '用户数据生成时间',
`gmt_modify` datetime DEFAULT NULL COMMENT '用户数据修改时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 1.2 创建用户充值记录表
CREATE TABLE `le_user_deposit` (
`deposit_id` varchar(20) DEFAULT NULL COMMENT '用户主键ID',
`user_id` varchar(20) DEFAULT NULL COMMENT '用户ID',
`trade_no` varchar(128) DEFAULT NULL COMMENT '交易订单号',
`trade_price` varchar(16) DEFAULT NULL COMMENT '交易金额',
`gmt_create` datetime DEFAULT NULL COMMENT '订单生成时间',
`gmt_modify` datetime DEFAULT NULL COMMENT '订单修改时间'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 2.1 注册用户
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0001', '13100010001','2018-12-01 12:00:09');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0002', '13100010002','2018-12-01 12:00:19');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0003', '13100010003','2018-12-01 12:03:09');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0004', '13100010004','2018-12-01 12:40:09');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0000', '13100010000','2018-11-01 12:00:09');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0005', '13100010005','2018-12-11 12:00:09');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0006', '13100010006','2018-12-21 12:00:19');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0007', '13100010007','2018-12-12 12:03:09');
insert INTO le_user (user_id, phone_num, gmt_create) VALUES ('LE0008', '13100010008','2018-12-21 12:40:09');
-- 2.2 添加用户充值记录
-- （1）. 用户A（LE0001）在12.01当日注册，并在当日和隔日充值多笔
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812011209','LE0001','TN20181201120009','10.00','2018-12-01 12:10:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812011219','LE0001','TN20181201121909','10.00','2018-12-01 12:19:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812011240','LE0001','TN20181201124009','10.00','2018-12-01 12:40:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812011310','LE0001','TN20181201131009','10.00','2018-12-01 13:10:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812011340','LE0001','TN20181201124009','10.00','2018-12-01 13:40:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812021209','LE0001','TN20181202120009','15.00','2018-12-02 12:10:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812021219','LE0001','TN20181202121909','20.00','2018-12-02 12:19:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812021240','LE0001','TN20181202124009','30.00','2018-12-02 12:40:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812021310','LE0001','TN20181202131009','10.00','2018-12-02 13:10:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812021340','LE0001','TN20181202124009','10.00','2018-12-02 13:40:09');
-- （2）用户B（LE0002）在12.01当天注册，隔日12.03充值
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI20181203141009','LE0002','TN20181203141009','15.00','2018-12-03 14:10:09');
-- （3）用户C（LE0003）在12.01当天注册，且充值1笔
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812011809','LE0003','TN20181201180009','25.00','2018-12-01 18:10:09');
-- （4） 用户D（LE0000）在上月已注册当天充值，并在12月充值多笔
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201811011209','LE0000','TN20181101120009','10.00','2018-11-01 12:10:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI201812016209','LE0000','TN20181201160009','10.00','2018-12-01 16:10:09');
INSERT INTO le_user_deposit (deposit_id, user_id, trade_no, trade_price, gmt_create) VALUES ('DI20181202111009','LE0000','TN20181202111009','30.00','2018-12-02 11:10:09');
-- 3.1 查询所有数据
SELECT user_id, phone_num, date_format(gmt_create, '%Y-%m-%d') AS DATE_RE FROM le_user ORDER BY gmt_create ASC;
SELECT deposit_id, user_id, trade_no, trade_price, date_format(A.gmt_create, '%Y-%m-%d') AS DATE_user_create FROM le_user_deposit A ORDER BY A.gmt_create ASC;
-- 3.2 确定le_user_deposit表中每日充值的新增用户
select user_id, min(date_format(gmt_create, '%Y-%m-%d')) as date_cz from le_user_deposit group by user_id;
四、 查询语句
SELECT
date_format(A.gmt_create, '%Y-%m-%d') AS DATE_NEW,
count(A.deposit_id) AS CZBS,
count(DISTINCT A.user_id) AS CZRS,
sum(A.trade_price) CZJE
FROM le_user_deposit A
WHERE(A.gmt_create BETWEEN '2018-12-01 00:00:00' AND '2018-12-31 23:59:59')
AND A.user_id NOT IN (SELECT user_id FROM le_user WHERE gmt_create < '2018-12-01 00:00:00')
AND date_format(A.gmt_create, '%Y-%m-%d') IN (SELECT min(date_format(B.gmt_create, '%Y-%m-%d')) AS date_cz FROM le_user_deposit B WHERE B.gmt_create GROUP BY B.user_id)
GROUP BY DATE_NEW;
以上仅个人理解，场景考虑可能不全面，期待大家补充。
