SQL 条件函数 日期函数 思维使用训练 13

MySQl 专栏持续更新 不说晦涩难懂的东西 尽量输出容易理解 和 使用的SQL技巧 和 初中级开发不是很常用的但很有用的知识

欢迎查看👉🏻👉🏻👉🏻SQL 专栏 查漏补缺 指教一二

知识点

条件函数、日期函数(datediff）

这些是算是比较高阶的函数 我说的高阶 意思是大部分初中级 甚至高级开发都不是很常用的 但是比较有用的 我们先了解一下这些知识点

条件函数

这个算是比较强大一个功能 但是我目前水平有限工作中很少接触这个使用 看一下语句结构

这个看起来的意思有点像 java 中的 Switch

第一种使用格式 也是比较简单的使用格式

第二种算是增加判断 有点像 加上 if 判断

Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。 Case when 相当于一个自定义的数据透视表，group by 是行名，case when 负责列名。

使用实例： 比较常见的就是行列转换 与GROUP BY 结合，自定义列联表统计

实现效果：

不说过多的例子 知道这个可以进行条件判断就行了

日期函数

这个算是最近我比较常用的 在进行思维练习的时候 使用的比较多 工作现在也尝试使用这个东西了 比较常用的就是 TIMESTAMPDIFF 和 datediff 可以比较时间的差值 使用格式如下： datediff： datediff函数返回两个日期之间的天数

语法：DATEDIFF(date1,date2)

示例：

TIMESTAMPDIFF： 使用格式： TIMESTAMPDIFF(interval，datetime1，datetime2)，比较的单位interval可以为以下数值

示例：

接下来进行思维训练 主要是使用这两个知识点进行训练

创建表

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (109, 9001, '2021-08-31 10:00:00', '2021-08-31 10:00:09', 0),
  (109, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0),
  (108, 9001, '2021-09-01 10:00:01', '2021-09-01 10:01:50', 0),
  (108, 9001, '2021-11-03 10:00:01', '2021-11-03 10:01:50', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (104, 9003, '2021-09-03 11:00:45', '2021-09-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (102, 9001, '2021-10-30 10:00:00', '2021-10-30 10:00:09', 0),
  (103, 9001, '2021-10-21 10:00:00', '2021-10-21 10:00:09', 0),
  (101, 0, '2021-10-01 10:00:00', '2021-10-01 10:00:42', 1);

问题

问题：统计活跃间隔对用户分级后，各活跃等级用户占比，结果保留两位小数，且按占比降序排序。

补充解释：

• 用户等级标准简化为：忠实用户(近7天活跃过且非新晋用户)、新晋用户(近7天新增)、沉睡用户(近7天未活跃但更早前活跃过)、流失用户(近30天未活跃但更早前活跃过)。
• 假设今 天就是数据中所有日期的最大值。
• 近7天表示包含当天T的近7天，即闭区间[T-6, T]。

分析思路

首先分析问题：

把用户分成4个等级，并分别统计各个等级的用户占总用户的比，结果按照占比降序排列。4个等级的划分规则如下：

• 忠实用户(近7天活跃过且非新晋用户)
• 新晋用户(近7天新增)
• 沉睡用户(近7天未活跃但更早前活跃过)
• 流失用户(近30天未活跃但更早前活跃过)。

今天就是数据中所有日期的最大值，近7天表示包含当天T的近7天，即闭区间[T-6, T]。 在进行编写的过程中 还有几个比较注意问题：

• 日期1—最后一次活跃日期：MAX(DATE(out_time)) AS max_dt

• 日期2—成为新用户的日期：MIN(DATE(in_time))  AS new_dt

• 日期3—今天：MIN(DATE(in_time))  AS new_dt

难点：

沉睡用户(近7天未活跃但更早前活跃过)，根据定义它是包含流失用户(近30天未活跃但更早前活跃过)，也就是说只要是流失用户就是沉睡用户！实际上答案并不包括。所以沉睡用户应该这样定义：近7天未活跃但更早前活跃过且非流失用户。 2.case when 判断使每个类型的用户不会有重合。而且先判断哪个后判断哪个尤其重要，顺序不能换！

了解的新思维方式：

1.用户最近一次活跃时间：max(date)；一直没有想到，其实在之前是知道求用户第一次活跃时间是用：min(date)。如果使用这个会省很多力气。

因为最后一次活跃的时间可以用来确定 近几天未活跃但更早前活跃过

2.select里面可以套用select，尤其是从另外一个表用group by查询，因为group by又不好放在总的框架里面。

（1）统计用户第一次活跃的时间；用户最后一次活跃的时间

如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过

• [使用]：min()确定用户第一次活跃时间，max()确定用户最后一次活跃时间

（2）近7天和近30天，涉及两个不同的活跃条件

一种方法是求出第7天是什么时候：日期减去天数得到日期；另一种是日期减去日期得到天数

• [使用]：date_sub()、datediff('2021-11-04',dt_max) <=6、timestampdiff(day,expr1,expr2)都可以

（3）对用户分类且求比例

case when 判断就使每个类型的用户不会有重合，所以‘新晋用户’在’忠实用户‘的前面，’流失用户‘在’沉睡用户‘的前面。

• [使用]：case when

编写 SQL：

#case when 判断就使每个类型的用户不会有重合。
select
    (case
        when timestampdiff(day,min_date,'2021-11-04') <= 6
        then '新晋用户'
        when timestampdiff(day,max_date,'2021-11-04') <= 6
        then '忠实用户'
        when timestampdiff(day,max_date,'2021-11-04') >= 30
        then '流失用户'
        else '沉睡用户'
    end) as user_grade,
    round(count(distinct uid)/(select count(distinct uid) from tb_user_log),2) as ratio
from(
    #用户第一次活跃的时间,用户最后活跃的时间
    select
        uid,
        min(date(in_time)) as min_date,
        max(date(in_time)) as max_date
    from tb_user_log
    group by uid
    ) main
group by user_grade
order by ratio desc