11-29 08:55 阅读 78

关注

SQL 思维训练八 + 知识点补充

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

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

前言

通过思路解析 分析SQL书写 拆分逻辑 简单易懂  跟着学习 等系列更新完 SQL编写能力 和 SQL思维都会有提升 欢迎关注专栏 如果有更简单的接替方法 可以发在评论区会补充完善

为什么 进行SQL 思维训练 真正做过实战项目的人明白 其实开发思维很重要 开发思维是大于 开发能力的 SQL思维能力也一样 因为SQL是和数据直接打交道的 需要去训练自己的编写能力和编写思路 能够最快的找到最优解

表结构在上一篇文章 需要的请前往复制

困难

难度：

问题

现在运营想要查看用户在某天刷题后第二天还会再来刷题的平均概率。请你取出相应数据（也就是第一天访问 第二天又访问的平均概率）

示例

分析

这个问题 如果联系过前面文章的东西 就算不上困难模式 先分析一下问题

1. 需要知道两天都上线的人数

2. 需要知道上线的人数 可以是每一天 机器编号 + 时间唯一

我们通过两种方法进行编写

思路一

示例图已经很明确的表示了 这个只需要用到一张表 question_practice_detail

首先我们需要在这一张表判断 是不是连续两天进行访问 同时 device_id 是相同的

因为最近练习SQL比较多 所以分析到这里我第一个想到的就是自连接 自连接这里再稍微说一下 一张表数据进行数据比较的时候可以通过自连接实现 生成一张新表 里面的数据是两张表的集合 表1 X 表2

我们先进行表的自连接

生成 256 条数据 我们再根据上面条件进行筛选

• 连续两天访问

• qpd.date = qpe.date - 1

• device_id 相同

• qpd.device_id = qpe.device_id

拼接之后生成以下SQL：

这个时候就还剩下 九条数据

我们这个时候 需要通过 机器Id + 日期 进行去重 生成如下SQL：

这个时候我们第一步就完成了 找到了连续两天登陆的人

第二步是找到所有登陆过的人去重后的数据 生成SQL如下：

最后一步就是 通过连续登陆的/总数 生成如下SQL：

SELECT count(DISTINCT qpd.device_id, qpd.date) / (     SELECT count(DISTINCT qpd.device_id, qpd.date)     FROM question_practice_detail qpd ) AS avg_ret FROM question_practice_detail qpd          JOIN question_practice_detail qpe WHERE qpd.device_id = qpe.device_id   AND qpd.date = qpe.date - 1 复制代码

知识点

• 自连接

再一次体会到自连接的妙用

思路二

在说这个思路之前先明白一个补充一个小知识点

DATEDIFF函数用于返回两个日期的天数

语法格式

DATEDIFF(date1,date2)

参数说明

• date1: 比较日期1

• date2: 比较日期2

我们就用这道题为例子讲解使用方法

1、先来实现第一个 连续两天登陆的人

上一个写法有一个不好的点 就是 我要重新查一遍 question_practice_detail 中的数据 也就是下面的这点

我这个是想查question_practice_detail 表中的数据 因为上面的过滤导致我要重新查一遍 怎么去解决这个

使用Left join 保留一张表的数据 就可以完美解决 多一个查询的问题 加上上面的条件可以生成如下SQL：

SELECT * FROM question_practice_detail AS qpd          LEFT JOIN question_practice_detail AS qpe ON qpd.device_id = qpe.device_id     AND DATEDIFF(qpe.date, qpd.date) = 1 复制代码

我们可以看到 qpd 表是完整的  qpe 是不完整的 这里 qpd 就是所有登陆的用户 qpe 就是连续登陆的用户 最终SQL：

SELECT    COUNT( DISTINCT qpe.device_id, qpe.date )/ count( DISTINCT qpd.device_id, qpd.date ) AS avg_ret  FROM    question_practice_detail AS qpd    LEFT JOIN question_practice_detail AS qpe ON qpd.device_id = qpe.device_id     AND DATEDIFF( qpe.date, qpd.date )=1 复制代码

知识点

• 左链接

• 时间比较函数 DATEDIFF

结语

主要的知识点是时间比较和连接的灵活使用DATEDIFF 还有一些不同的思路 可以看出SQL还是挺有扩展空间的

作者：变成派大星
链接：https://juejin.cn/post/7171343028148240421