案例分析：SQL基于MYSQL数据库的用户充值问题

一、建表

1、用户注册表：结构（ 1 user id 2 注册日期时间 3 性别 ）

create table t_usereg(
  user_id int primary key auto_increment,--用户id主键、自增
  registration_date datetime,--注册日期时间
  sex char(1)
);

2、登录表: 结构（1 user id 2 登录日期时间 3 充值金额 4 登出日期时间 ）

用户可多次重复登录，所以主键设为联合主键较为合适。

create table t_userlog(
  user_id int ,
  login_date datetime,--登录日期时间
  recharge_amount int,--充值金额
  logout_date datetime,--登出日期时间
  primary key(user_id,login_date,recharge_amount,logout_date)
);

二、模拟数据用于自测

注册表

 登录表

三、问题概览

1 今天登录人数 
2 今天和昨天都登录的 id 
3 单笔充值金额大于 500 的 id 
4 累计充值金额大于 5000 的 id 
5 累计充值金额大于 5000 注册时间大于 1 年的 id 
6 男女充值金额差值 
7 总注册用户数量和总充值用户数量 
8 近期 30 天平均在线时长 2 小时的用户数量 
9 近期 30 天没登录用户数量 
10 连续两天充值大于 100 元的 id 

四、解决问题 
1、 今天登录人数

select
  login_date,
  count( user_id)
from
  t_userlog
where
  date(login_date)=curdate();

结果输出

+---------------------+-----------------+
| login_date          | count( user_id) |
+---------------------+-----------------+
| 2022-05-03 14:24:21 |               2 |
+---------------------+-----------------+
1 row in set (0.01 sec)

思路：

• 登录日期=系统当前日期（注意建表时登录日期类型设置的是带时分秒的，这块得转换一下）
• curdate()函数返回的是年月日，不带时分秒

2、 今天和昨天都登录的 id 

select
  a.user_id
from (
  select
      distinct(user_id)
  from
      t_userlog 
  where curdate()-date(login_date)=1
         ) as a
join (
   select distinct(user_id)
   from
      t_userlog 
   where date(login_date)=curdate()
       ) as b
on
   a.user_id=b.user_id;

结果输出

+---------+
| user_id |
+---------+
|       2 |
|       4 |
+---------+
2 rows in set (0.01 sec)

思路：

• 把昨天登录的用户id查出来，再把今天登录的用户查出来，然后做一个连接，此时会自动过滤掉只在其中一天登录的id。（注意id去重）
• curdate()-date()得出来的是天数。

3 、单笔充值金额大于 500 的 id 

select
  user_id,
  recharge_amount 
from
  t_userlog 
where
  recharge_amount>500 
group by
  user_id;

结果输出

+---------+-----------------+
| user_id | recharge_amount |
+---------+-----------------+
|       1 |             600