查询结果要求.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_