主页 / dba / 问题 / 206030
Accepted
Vérace
Asked: 2018-05-08 04:41:21 +0800 CST

MySQL - 不同月份的最大总和与多年的联系

  • 772

这个问题的灵感来自这个[关闭]并且实际上与这个问题相同但使用不同的RDBMS（PostgreSQL vs. MySQL）。

假设我有一个肿瘤列表（这个数据是根据真实数据模拟的）：

CREATE table illness (nature_of_illness VARCHAR(25), created_at DATETIME);

INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Cervix', '2018-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2018-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2018-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2018-02-03 17:50:32');
-- 2017, with 1 Cervix and Lung each for the month of Jan - tie!
INSERT INTO illness VALUES ('Cervix', '2017-01-03 15:45:40');
INSERT INTO illness VALUES ('Lung',   '2017-01-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Lung',   '2017-02-03 17:50:32');
INSERT INTO illness VALUES ('Cervix', '2017-02-03 17:50:32');

您想找出给定月份中哪种特定肿瘤最常见 - 到目前为止一切顺利！

现在，您会注意到，对于 2017 年的第 1 个月，有一个平局 - 因此随机选择一个并给出它作为答案是没有任何意义的 - 所以必须包括平局 - 这使得问题更具挑战性。

正确答案是：

  Year    Month  Tumour count      Type
  2017        1             1    Cervix  -- note tie
  2017        1             1      Lung  --   "   "
  2017        2             3      Lung
  2018        1             5    Cervix
  2018        2             3      Lung

另一个好处是让月份名称显示为文本而不是整数。

我有一个解决方案，但它非常复杂 - 我想知道我的解决方案是否最优。MySQL 小提琴在这里！

mysql-5.6

2 个回答

  1. Best Answer

    我尝试解决这个问题如下。对于如何改进此查询的任何建议，我将不胜感激：

    SELECT 
  t3.c_year AS "Year",
  t3.c_month AS "Month", 
  t3.il_mc AS  "Tumour count", 
  t4.ill_nat AS "Type" FROM
(
  SELECT c_year, c_month, il_mc FROM
  (
    SELECT  
    c_year, 
    c_month,
    MAX(month_count) AS il_mc
  FROM
    (
      SELECT nature_of_illness as illness,
        EXTRACT(YEAR  FROM created_at) AS c_year,
        EXTRACT(MONTH FROM created_at) AS c_month,
        COUNT(EXTRACT(MONTH FROM created_at)) AS month_count
      FROM illness
      GROUP BY illness, c_year, c_month
      ORDER BY c_year, c_month
    ) AS t1
  GROUP BY c_year, c_month
  ) AS t2
) AS t3
JOIN
(
SELECT 
  EXTRACT(YEAR FROM created_at) AS t_year, 
  EXTRACT(MONTH FROM created_at) AS t_month,  
  nature_of_illness AS ill_nat, 
  COUNT(nature_of_illness) AS ill_cnt
FROM illness
GROUP BY t_year, t_month, nature_of_illness
ORDER BY t_year, t_month, nature_of_illness
) AS t4
ON t3.c_year = t4.t_year
AND t3.c_month = t4.t_month
AND t3.il_mc = t4.ill_cnt

    它确实给出了正确的结果，正如在这里的小提琴中所见！

    • 5

  2. 使用 MySQL-8.0 和 CTE，我们首先创建tmp按年/月/ 分组的聚合计数nature_of_illness，RANK()将相同的值分配给相同的值，c以便考虑重复的最大值：

     SELECT y as 'Year',mon as 'Month',c as 'Tumor Count', nature_of_illness as 'Type'
 FROM (
   WITH tmp AS ( 
    SELECT YEAR(created_at) as y, MONTH(created_at) as mon, COUNT(*) as c, nature_of_illness
    FROM illness
    GROUP BY y, mon, nature_of_illness
   )
   SELECT y, mon, c, nature_of_illness,
   RANK() OVER (PARTITION BY y, mon ORDER BY c DESC) as `rank`
   FROM tmp
 ) AS tmp2 
WHERE `rank` = 1
ORDER BY y, mon
    • 1

相关问题