Gedanken und Lösungen zu einer MySQL-Query-Interviewfrage

Upy (kann übersprungen werden)

Vor ein paar Tagen, während des Interviews, stieß ich beim schriftlichen Test auf diese Frage, nachdem ich die Frage mehrmals gelesen hatte, war ich immer noch verwirrt „Innerhalb eines Zeitraums muss die Summe der Punkte für jeden Tag in diesen N Tagen größer als M sein.“ Das war so verwirrend, dass ich es am Ende nicht richtig geschrieben habe.

Ich habe heute über diese Frage nachgedacht, die Antwort geschrieben und die SQL-Anweisung überprüft.

Empfohlen: „MySQL-Video-Tutorial“, „MySQL-Interviewfragen2020“

Fragen

Ein bestimmtes Spiel verwendet eine MySQL-Datenbank. Die Benutzer-Score-Historie stellt die Benutzer-ID dar, und das Datum stellt das Datum dar. Jeder Benutzer generiert mehrere Datensätze.

Die Datenstruktur und Datenzeilen sind wie folgt:

Jetzt benötigen wir eine Liste von Benutzern in der 31 Tage im März 2017. An 16 Tagen beträgt die Gesamtpunktzahl jeden Tag mehr als 40 Punkte. Wird mit einer SQL-Anweisung ausgedrückt.

Dinge

Anforderungen neu ordnen und die wichtigsten Punkte zeichnen.

Jetzt benötigen wir eine Liste von Benutzern, die an mindestens 16 der 31 Tage im März 2017 einen Tageswert von mehr als 40 Punkten haben. Wird mit einer SQL-Anweisung ausgedrückt.

Benutzerliste

stellt eine eindeutige UID-Liste dar, die mit DISTINCT uid oder GROUP BY uid implementiert werden kann.

In den 31 Tagen im März 2017

Verwenden Sie die Where-Anweisung, um den Zeitbereich einzuschränken.

Es müssen mindestens 16 Tage vorhanden sein

Es ist notwendig, die Tage zu aggregieren und zur Beurteilung die Aggregationsfunktion COUNT(*) > zu verwenden.

Die Summe der Tagespunktzahlen (pro Person) ist größer als 40

Es ist notwendig, die Tagespunktzahlen zu aggregieren und die Aggregatfunktion zu verwenden, um SUM(Punktzahl) zu beurteilen. > 40 .

Hier gibt es zwei Aggregatfunktionen, die jedoch für unterschiedliche Dimensionen (Tage und Punkte pro Tag) gelten. Daher müssen Sie Unterabfragen verwenden, um die beiden Aggregationen jeweils in der inneren und äußeren SQL-Anweisung zu platzieren.

Nach dem Prinzip „von innen nach außen“ aggregieren wir zunächst die Tageswerte, also die Tage.

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

Antwort

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;

Verifizierung

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

Das obige ist der detaillierte Inhalt vonGedanken und Lösungen zu einer MySQL-Query-Interviewfrage. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!