瑶池数据库SQL-问题二

简介: 简述问题二的分析思路及最后结果

为什么选问题二

个人没有详细的看三个题目的具体内容,只是看了三个题目的题目名称,

image.png

最后觉得问题二比较有意思,然后就选择了问题二进行解答。

问题二

首先来看一下阿里云数据库SQL挑战赛赛题二:游戏游玩情况的问题描述,首先有一张表,表名Activity

image.png

表中的字段就是以上四个字段,建表语句

CREATETABLE `Activity` (  `player_id` int(11)NOTNULL,  `device_id` int(11)NOTNULL,  `event_date` dateNOTNULL,  `games_played` int(11)NOTNULL,  PRIMARY KEY (`player_id`,`event_date`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这张表的业务就是记录某些游戏的玩家的活动情况。每一行是一个玩家在指定日期的游玩记录,包含了设备信息,以及总共玩了多少款游戏。那么问题二来了,问:【注册首周内至少有两次登录的玩家占总玩家的比例,四舍五入到小数点后两位

准备工作

个人的阿里云账号已经没有试用资格了,只能在我自己的本地数据库测试了,然后下载了为赛题准备的数据集,现在本地数据库建表

image.png

然后通过数据库连接工具navicat 导入数据,导入数据的具体步骤这里就不再演示了,直接看导入数据后的结果

image.png

问题解决

数据导入之后,就可以开始着手分析问题了,根据题目要求,查询注册首周内至少有两次登录的玩家占总玩家的比例,四舍五入到小数点后两位】,那么问题应该分为两步处理:

1.获取当前表中总玩家数;

2.获取当前表中注册首周内至少有两次登录的玩家数;

分析完成之后我们开始按步骤处理,查询对应的数据。

第一步

首先需要查询当前表中总玩家数,查询语句

SELECTCOUNT(DISTINCT player_id)FROM Activity;

执行结果

image.png

可以看到总玩家数量是1000;

第二步

首先我们先观察一下数据结构

image.png

可以看到相同player_id下event_date默认就是升序排列的,那么我们就不用再单独进行升序来获取注册时间了。下面我们只需要获取每一个player_id的前两条记录,并且比较这两条记录的event_date是否在一周内,那么这样统计出来的数据就是【注册首周内至少有两次登录记录的玩家数了】。

初步尝试

考虑到这里需要取每个player_id下的前面两条记录,那么我们可以写sql

SELECT t.player_id,t.event_dateFROM Activity t WHERE(SELECTCOUNT(*)FROM Activity t1 WHERE t1.player_id= t.player_idAND t1.event_date< t.event_date)<2ORDERBY t.player_id;

执行结果如图

image.png

根据执行结果可以看到我们是获取了每个player_id下面的前面两条记录,但是在此基础上再进行event_date日期的比较在一周内的话难以执行,那么又想了另外一种方案。

再次尝试

对于上面无法进行event_date日期比较的境况,后来又考虑了一种方向,既然要进行event_date日期字段的比较,那么首先要确保当前player_id下的两条记录是在一条记录上,那么后续通过比较event_date日期字段是否在一周内就可以直接判断当前player_id是满足条件的数据了。那么下面就按这个思路来写sql。

主表自关联

首先主表自关联,将后续的时间都挪到上一条记录的后面,方便后续进行event_date日期字段的比较

SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_id;

执行结果如图

image.png

这里可以看到我们想要的数据已经出现了,这个时候其实只要GROUP BY t.player_id就可以每个player_id 下获取一条数据,但是目前的sql获取的是第一条

12015-02-142015-02-14

这样的数据,并不是我们想要的第二条符合要求的数据,那么我们可以排除第一条数据就可以了,改写sql,同时直接加上GROUP BY t.player_id

SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_idAND t.event_date!= t1.event_dateGROUPBY t.player_id;

执行结果如图

image.png

这样我们就得到了按player_id分组,并且前两次登录的时间在同一行数据的结果了,下面只需要对当前数据按照event_date进行比较就可以得到【当前表中注册首周内至少有两次登录的玩家

查询满足条件数据

根据上面的分析我们继续改写sql

SELECT*FROM(SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_idAND t.event_date!= t1.event_dateGROUPBY t.player_id) t2 
WHERE TIMESTAMPDIFF(DAY,t2.event_date,t2.event_date2)<7;

执行结果如图

image.png

这里我们就可以看到所有满足【当前表中注册首周内至少有两次登录的玩家】条件的玩家了,下面统计数量的话把查询字段的*换成COUNT(*)即可

SELECTCOUNT(*)FROM(SELECT t.player_id,t.event_date,t1.event_date event_date2 FROM Activity t
LEFT JOIN Activity t1 ON t.player_id=t1.player_idAND t.event_date!= t1.event_dateGROUPBY t.player_id) t2 
WHERE TIMESTAMPDIFF(DAY,t2.event_date,t2.event_date2)<7;

执行结果如图

image.png

那么整个问题二到这里也就结束了,问题二的结果就是

-- 0.014SELECT14/1000;

结果如图

image.png

到这里整个问题二的解答就完工了。

解题感受

最初是因为对这个题的名称比较敢兴趣,后来点进去看了题目详细内容之后,就更有兴趣了,工作中由于工作方向的不同,不太容易遇到类似的场景,因此刚开始解题确实绕路了。这里就不再写出来了,毕竟绕路不是什么开心的事哈。

为什么觉得这个是经典SQL,过去的业务逻辑,写sql的话有时候一时想不起来的,基本上没多久也就写完了,这次写SQL,如果对数据结构没有分析到位的话,还是很容易绕路的,比较有误导性,当时当你绕到正路上的时候,你再看这个SQL又回觉得特别简单,没什么难度。其实这就是写SQL的乐趣,需要针对需求,结合数据结构深入分析,才能快速完成业务功能,完成之后回头再看又比较简单,哈哈。以上就是个人解题感悟,敬请指导。

相关文章
|
16天前
|
Cloud Native 关系型数据库 分布式数据库
《阿里云产品四月刊》—瑶池数据库云原生化和一体化产品能力升级
阿里云瑶池数据库云原生化和一体化产品能力升级,多款产品更新迭代
|
4天前
|
SQL 监控 安全
数据库安全:SQL注入防御实践
【7月更文挑战第11天】SQL注入攻击作为一种常见的网络攻击手段,对数据库的安全性和业务稳定构成了严重威胁。为了有效防御SQL注入攻击,开发者和数据库管理员应采取一系列实践措施,包括输入验证与过滤、使用参数化查询、限制数据库用户权限、使用Web应用程序防火墙、定期更新和打补丁、实施实时监控和审计以及使用HTTPS协议等。通过这些措施的实施,可以显著提升数据库的安全性,降低遭受SQL注入攻击的风险。同时,开发者和数据库管理员应持续关注新的安全威胁和防御技术,不断提升自身的安全防护能力。
|
5天前
|
SQL 存储 安全
数据库数据恢复—SQL Server数据库出现逻辑错误的数据恢复案例
SQL Server数据库数据恢复环境: 某品牌服务器存储中有两组raid5磁盘阵列。操作系统层面跑着SQL Server数据库,SQL Server数据库存放在D盘分区中。 SQL Server数据库故障: 存放SQL Server数据库的D盘分区容量不足,管理员在E盘中生成了一个.ndf的文件并且将数据库路径指向E盘继续使用。数据库继续运行一段时间后出现故障并报错,连接失效,SqlServer数据库无法附加查询。管理员多次尝试恢复数据库数据但是没有成功。
|
10天前
|
SQL 自然语言处理 网络协议
【Linux开发实战指南】基于TCP、进程数据结构与SQL数据库:构建在线云词典系统(含注册、登录、查询、历史记录管理功能及源码分享)
TCP(Transmission Control Protocol)连接是互联网上最常用的一种面向连接、可靠的、基于字节流的传输层通信协议。建立TCP连接需要经过著名的“三次握手”过程: 1. SYN(同步序列编号):客户端发送一个SYN包给服务器,并进入SYN_SEND状态,等待服务器确认。 2. SYN-ACK:服务器收到SYN包后,回应一个SYN-ACK(SYN+ACKnowledgment)包,告诉客户端其接收到了请求,并同意建立连接,此时服务器进入SYN_RECV状态。 3. ACK(确认字符):客户端收到服务器的SYN-ACK包后,发送一个ACK包给服务器,确认收到了服务器的确
|
11天前
|
SQL 存储 关系型数据库
关系型数据库SQL Server学习
【7月更文挑战第4天】
21 2
|
16天前
|
SQL 存储 Java
SQL数据库学习指南:从基础到高级
SQL数据库学习指南:从基础到高级
|
5天前
|
SQL Java 关系型数据库
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
Java面试题:描述JDBC的工作原理,包括连接数据库、执行SQL语句等步骤。
14 0
|
5天前
|
SQL 监控 Java
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
Java面试题:简述数据库性能优化的常见手段,如索引优化、SQL语句优化等。
11 0
|
13天前
|
SQL 存储 搜索推荐
SQL游标的原理与在数据库操作中的应用
SQL游标的原理与在数据库操作中的应用
|
13天前
|
SQL 存储 Java
SQL数据库学习指南:从基础到高级
SQL数据库学习指南:从基础到高级