mysqlクエリの面接の質問に対する考えと解決策

長文 (読み飛ばしても構いません)

数日前に面接を受け、筆記試験中にこの質問に遭遇しました。何度も質問を読んだ後、私はまだ疑問を感じていました。混乱して、「一定期間内。少なくとも N 日間、この N 日間の毎日のスコアの合計が M より大きくなければなりません。」 混乱しすぎて、結局正しく書けませんでした。

今日この質問を思いつき、答えを書き、SQL ステートメントを検証しました。

推奨: 「mysql ビデオ チュートリアル 」、「mysql 面接の質問 2020」

質問

# #あるゲームではmysqlデータベースを使用しています。データテーブルscoresにはユーザーのスコア履歴が記録されています。uidはユーザーID、scoreはスコア、dateは日付を表します。各ユーザーは毎日複数のレコードを生成します。

データ構造とデータ行は次のとおりです:

# 次に、ユーザーのリストが必要です。2017 年 3 月の 31 日間に、ユーザーのリストが必要です。少なくとも 16 日間、毎日の合計スコアが 40 ポイントを超えていること。 SQL文を使って表現します。

感想

要件を整理し、要点を導き出す。

ここで、2017 年 3 月の 31 日間のうち少なくとも 16 日間、毎日のスコアが 40 ポイントを超えたユーザーのリストが必要になります。 SQL文を使って表現します。

ユーザー リスト

一意の uid リストを表します。これは、DISTINCT uid または GROUP BY uid を使用して実装できます。

2017 年 3 月 31 日

where ステートメントを使用して、時間範囲を制限します。

少なくとも 16 日ある必要があります

日数の日付を集計し、集計関数 COUNT(*) > 15 を使用して判定する必要があります。

(1人あたり) 毎日のスコアの合計が40を超えています

毎日のスコアを集計し、集計関数を使用してSUM(スコア)を判定する必要があります> 40 。

ここには 2 つの集計関数がありますが、ディメンション (日数と 1 日あたりのスコア) が異なるため、サブクエリを使用して 2 つの集計をそれぞれ内部 SQL ステートメントと外部 SQL ステートメントに配置する必要があります。

「内から外へ」の原則に基づき、まず毎日のスコア、つまり日数を集計します。

-- 在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;

回答

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;

検証

-- 结构
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

以上がmysqlクエリの面接の質問に対する考えと解決策の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。