编程显示user表中所有记录_SQL实战 —— user_3、log_exit_3、payment_3和product_3表

1、查找各表的所有信息

select * from user_3;
select * from log_exit_3;
select * from payment_3;
select * from product_3;

user_3表：

log_exit_3表：

payment_3表：

product_3表：

2、对数据进行清洗，将user_3表中的性别转为0（男）、1（女）、2（保密），并将各表的时间数据由varchar格式转换为datetime格式

update user_3 set gender = 0 where user_3.gender = '男';    -- 将性别为男的改为0
update user_3 set gender = 1 where user_3.gender = '女';    -- 将性别为女的改为1
update user_3 set gender = 2 where user_3.gender = '保密';  -- 将性别为保密的改为2

update user_3 set reg_time = date_format(cast(user_3.reg_time as datetime),'%Y-%m-%d');  -- 使用cast函数进行修改，注意user_3表中是截止到天，所以还要用date_format
update log_exit_3 set log_time = cast(log_exit_3.log_time as datetime),
exit_time = cast(log_exit_3.exit_time as datetime);         -- 清洗log_exit_3表中的登录时间和退出时间
update payment set paytime = cast(payment.paytime as datetime);   -- 清洗payment表中的paytime

-- update user_3 set gender = '男' where user_3.gender = '0';    -- 将性别为0的改回男性（注意即使0是数值也要加引号）
-- update user_3 set gender = '女' where user_3.gender = '1';    -- 将性别为1的改回女性
-- update user_3 set gender = '保密' where user_3.gender = '2';  -- 将性别为2的改回保密

3、计算男性、女性和性别保密用户的占比，并根据性别输出所有在该性别下的name（组合在一个字段），最后按女、男、保密的顺序自定义排序

select count(*) from user_3;     -- 首先查看一下一共有多少个id，将输出结果用于接下来的查询构建
select gender as 性别, count(*) / 10 as 用户比例, group_concat(name) as 所有用户    -- 计算占比情况，用group_concat将该类别下所有用户的姓名进行拼接
from user_3 group by 1           -- 根据性别字段进行分组
order by find_in_set(性别,'女,男,保密');  -- 使用find_in_set函数来完成自定义排序

4、基于log_exit_3表，按总登录次数和总登录时间对不同id的用户进行排名（次数越多/时长越久，排名越高）

select id, 总登录次数, row_number() over (order by 总登录次数 desc) as 按登录次数排名,            -- 使用窗口函数来完成排名情况（注意，窗口函数在mySQL 8.0版本以上才支持）
总登录时间, dense_rank() over (order by 总登录时间 desc) as 按登录时间排名 from
(select id, count(*) as 总登录次数, sum(timestampdiff(second,log_time,exit_time)) as 总登录时间   -- 使用timestampdiff俩获取两个时间之间的精确到秒数的差（用datediff只能到天）
from log_exit_3
group by id) as t;     -- 将查询得到的结果作为t表传递给新的查询

这里由于我的mysql版本是5.7，所以没办法显示窗口函数的结果，这里给出t表的结果：

5、对每个id进行0-1类别划分（0表示不是、1表示是），分4个字段，第1个是登录不少于2次，第2个是购买不少于1次，第3个是登录不少于2次且购买也不少于1次

select id,(case when count(log_time)>=2 then 1 else 0 end) as 登录不少于2次,     -- 使用case when 语句来完成分布的处理
(case when count(paytime)>=1 then 1 else 0 end) as 购买不少于1次,
(case when count(log_time)>=2 and count(paytime)>=1 then 1 else 0 end) as 登录不少于2次且购买不少于1次
from
(select l.*, p.id as id1, p.product, p.pay, p.paytime            -- 需要先将两张表进行左连接，注意需要把log_exit_3表放在左边，因为登录不购买是可能的，但不登录就购买是不可能的
 from log_exit_3 l left join payment_3 p on l.id = p.id) as t    -- 在查询的时候注意对公共字段id进行别名（将后一个表的公共字段进行别名，避免出现查询错误）
group by id;         -- 最后根据id进行分组查询

6、根据payment_3表和product_3表计算每个用户每类道具的购买数量和带给企业的净利润

select p.id, p.product, (p.pay / pd.price) as 购买数量,    -- 购买数量是p.pay / pd.price
(sum((p.pay / pd.price)*(pd.price - pd.cost))) as 净利润   -- 净利润是购买数量 * （price-cost)
from payment_3 p, product_3 pd
where p.product = pd.product       -- 表的关联字段是product
group by p.id, p.product;          -- 最后根据p.id和p.product进行分组