Thoughts and solutions to a mysql query interview question

Long-winded (can be skipped)

I had an interview a few days ago and encountered this question during the written test. After reading the question several times, I was still confused, “Within a period of time. At least for N days, the sum of the scores for each day in these N days must be greater than M." It was so confusing that I didn't write it correctly in the end.

I thought of this question today, wrote the answer and verified the sql statement.

Recommended: "mysql video tutorial", "mysql interview questions2020"

questions

A certain game uses a mysql database. The data table scores records the user score history. uid represents the user ID, score represents the score, and date represents the date. Each user will generate multiple records every day.

The data structure and data rows are as follows:

# Now we need a list of users. During the 31 days of March 2017, there must be at least For 16 days, the total score every day is greater than 40 points. Expressed using a sql statement.

Thoughts

Reorganize the requirements and draw the key points.

Now we need a list of users who have a daily score greater than 40 points for at least 16 of the 31 days in March 2017. Expressed using a sql statement.

User list

Represents a unique uid list, which can be implemented using DISTINCT uid or GROUP BY uid.

In the 31 days of March 2017

Use the where statement to limit the time range.

There must be at least 16 days

It is necessary to aggregate the days date and use the aggregation function COUNT(*) > 15 to make the judgment.

(Per person) The sum of daily scores is greater than 40

It is necessary to aggregate the daily scores and use the aggregation function to judge SUM(score) > 40 .

There are two aggregation functions here, but they are for different dimensions (days and scores per day), so you need to use subqueries to place the two aggregations on the inner and outer sql statements respectively.

Based on the principle of "from inside to outside", we first aggregate the daily scores, that is, aggregate the days.

-- 在2017年3月份的31天中
select * from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39;;
-- (每人)每天得分总和大于40
-- 使用 group by uid,date 实现对分数进行聚合，使用 having  sum() 过滤结果
select uid,date from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39; group by uid, `date` having sum(score) > 40;
-- 至少要有16天
-- 以上条结果为基础，在对 group by uid 实现对天进行聚合，使用 having  count() 过滤结果
select uid from (
    select uid,date from scores where `date` >= &#39;2017-03-01&#39; and `date` <= &#39;2017-03-31&#39; group by uid, `date` having sum(score) > 40
) group by uid having count(*) > 15;

Answer

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

Verification

-- 结构
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` < &#39;2018-04-07&#39;
        GROUP BY
            uid,
            `date`
        HAVING
            total_score > 2
        ORDER BY
            uid,
            date
    ) AS a
GROUP BY
    uid
HAVING
    count(*) > 1;
-- 答案是：
uid : 1

The above is the detailed content of Thoughts and solutions to a mysql query interview question. For more information, please follow other related articles on the PHP Chinese website!