一道mysql查询面试题的思考解决过程

xiaowugui666 发布于2019-07-31 18:00 / 1848人阅读

摘要：现在需要一份用户列表，这些用户在年月份的天中，至少要有天，每天得分总和大于分。在年月份的天中使用语句限定时间范围。

啰嗦（可跳过）

前几天面试，笔试时遇到这道题，读了几遍题目都是懵懵懂懂，“一个段时间内至少N天，这N天中每天的分数总和要大于M”，好绕，最后没有写对。

今天想起来这道题，写出了答案并进行了sql语句的验证。

问题

某游戏使用mysql数据库，数据表 scores 记录用户得分历史，uid 代表用户ID， score 表示分数， date 表示日期，每个用户每天都会产生多条记录。

数据结构以及数据行如下：

uid int(11)	score int(11)	date date
1	2	2017-02-28
1	3	2017-03-02
3	2	2017-03-17
3	1	2017-03-17
3	2	2017-03-17
4	3	2017-03-25
3	5	2017-03-27
...	...	...

现在需要一份用户列表，这些用户在2017年3月份的31天中，至少要有16天，每天得分总和大于40分。使用一条sql语句表示。

思路

重新梳理需求,画出重点。

现在需要一份用户列表，这些用户在2017年3月份的31天中，至少要有16天，每天得分总和大于40分。使用一条sql语句表示。

用户列表
代表一个不重复的 uid 列表，可使用 DISTINCT uid 或 GROUP BY uid 来实现。

在2017年3月份的31天中
使用 where 语句限定时间范围。

至少要有16天
需要对天 date 进行聚合，使用聚合函数 COUNT(*) > 15来进行判断。

(每人)每天得分总和大于40
需要对每天分数 score 分数进行聚合，使用聚合函数对 SUM(score) > 40来进行判断。

此处有2处聚合函数，但是是针对不同维度的（天和每天里的分数），所以需要使用子查询，将2处聚合分别放置在内外层的sql语句上。

由“从内到外”的原则，我们先对每天的得分进行聚合，那就是对天进行聚合。

-- 在2017年3月份的31天中
select * from scores where `date` >= "2017-03-01" and `date` <= "2017-03-31";


-- (每人)每天得分总和大于40
-- 使用 group by uid,date 实现对分数进行聚合，使用 having  sum() 过滤结果
select uid,date from scores where `date` >= "2017-03-01" and `date` <= "2017-03-31" group by uid, `date` having sum(score) > 40;

-- 至少要有16天
-- 以上条结果为基础，在对 group by uid 实现对天进行聚合，使用 having  count() 过滤结果
select uid from (
    select uid,date from scores where `date` >= "2017-03-01" and `date` <= "2017-03-31" group by uid, `date` having sum(score) > 40
) group by uid having count(*) > 15;

答案

SELECT uid FROM (
    SELECT uid,date FROM WHERE `date` >= "2017-03-01" AND `date` <= "2017-03-31" GROUP BY uid,`date` HAVING SUM(score) > 40
) WHERE GROUP BY uid HAVING count(*) > 15;

验证

-- 结构
CREATE TABLE `scores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  `date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- 数据
INSERT INTO `scores` VALUES ("1", "1", "1", "2018-04-03");
INSERT INTO `scores` VALUES ("2", "1", "2", "2018-04-03");
INSERT INTO `scores` VALUES ("3", "1", "1", "2018-04-04");
INSERT INTO `scores` VALUES ("11", "1", "4", "2018-04-04");
INSERT INTO `scores` VALUES ("12", "1", "3", "2018-04-06");
INSERT INTO `scores` VALUES ("4", "1", "3", "2018-04-07");
INSERT INTO `scores` VALUES ("5", "2", "2", "2018-04-04");
INSERT INTO `scores` VALUES ("6", "2", "4", "2018-04-04");
INSERT INTO `scores` VALUES ("7", "2", "1", "2018-04-03");
INSERT INTO `scores` VALUES ("8", "3", "3", "2018-04-06");
INSERT INTO `scores` VALUES ("9", "3", "1", "2018-04-05");
INSERT INTO `scores` VALUES ("10", "3", "2", "2018-04-04");

-- 因为数据录入量有限，我们将结果改为修改改为：
-- 获取一个用户列表，时间范围是4号到6号，至少要有2天，每天分数总和大于2。

-- 查询
-- 非最精简语句，包含调试语句，可分段运行查看各个语句部分的效果。
SELECT
    uid
FROM
    (
        SELECT
            uid,
            `date`,
            sum(score) AS total_score
        FROM
            scores
        WHERE
            `date` > "2018-04-03"
        AND `date` < "2018-04-07"
        GROUP BY
            uid,
            `date`
        HAVING
            total_score > 2
        ORDER BY
            uid,
            date
    ) AS a
GROUP BY
    uid
HAVING
    count(*) > 1;

-- 答案是：
uid : 1

云数据库MySQL 存储过程分组查询 java存储过程查询第一道 mysql 存储过程

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/47707.html

链式调用与事件循环--一道JavaScript面试题的思考

摘要：最后画几张粗糙的图，简单描述一下这个执行的过程因为是链式调用，所以在链上的都会入栈然后执行，额，执行栈少画了和。。。前言：昨天在群里讨(jin)论(chui)技(niu)术(pi)，有位老铁发了一道他面的某公司面试题，顺手保存了。今早花了一点时间把这题做了出来，发现挺有意思的，决定在今天认真工（hua)作(shui)前，与大家分享我的解题方案和思考过程。题目如下（可以自己先思考一会...

wow_worktile 2019-08-23 15:04 评论0 收藏0
对一道【脉脉】上头条算法面试题的思考

摘要：偶然间在脉脉上看到了一道头条的算法面试题按照题目的理解，简单的写了一个网页开始学的不仅是技术，更是梦想得到了如下效果图得到如题可以进行开关的示例在最后一个灯特殊处理，链接第一个灯，形成环经过测试发现只要从序号开始，如偶然间在脉脉上看到了一道头条的算法面试题 showImg(https://segmentfault.com/img/bVboxvT?w=1148&h=1080); 按照题...

tyheist 2019-08-23 15:18 评论0 收藏0
对一道【脉脉】上头条算法面试题的思考

摘要：偶然间在脉脉上看到了一道头条的算法面试题按照题目的理解，简单的写了一个网页开始学的不仅是技术，更是梦想得到了如下效果图得到如题可以进行开关的示例在最后一个灯特殊处理，链接第一个灯，形成环经过测试发现只要从序号开始，如偶然间在脉脉上看到了一道头条的算法面试题 showImg(https://segmentfault.com/img/bVboxvT?w=1148&h=1080); 按照题...

xuxueli 2019-07-31 10:05 评论0 收藏0
尾调用优化——记一道面试题的思考

摘要：如果函数内部还调用函数，那就还有一个的调用帧，依次类推。等同于等同于如果所有函数都是尾调用，那么完全可以做到每次执行时，调用帧只有一项，这将大大节省内存。这就是尾调用优化。尾递归函数调用自身，称为递归。前言面某东，有一道题目是实现一个斐波拉契数列，已知第一项为0，第二项为1，第三项为1，后一项是前两项之和，即f(n) = f(n - 1) + f(n -2)。拿到这个题目，二...

awkj 2019-08-22 17:00 评论0 收藏0
[Java] 关于一道面试题的思考

摘要：对于这种会退出的情况，数组显然不能像链表一样直接断开，因此采用标记法先生成一个长度为的布尔型数组，用填充。中对整个进行遍历才能得到此时数组中的数量。文中的速度测试部分，时间是通过简单的 System.currentTimeMillis() 计算得到的，又由于 Java 的特性，每次测试的结果都不一定相同，对于低数量级的情况有 ± 20 的浮动，对于高数量级的情况有的能有 ± 10...

rozbo 2019-08-15 14:50 评论0 收藏0