MySQL:复杂的 GROUP BY 查询

Question

我们正试图找到一个问题的解决方案，我已经能够用一个说明性的表格来复制这个问题。以下是说明性表格的创建方式:

create table illustrativeTable
(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  label VARCHAR(4),
  reportingDate DATE,
  attr_1 INT,
  attr_2 INT,
  attr_3 INT,

  PRIMARY KEY(id)
);

我已按如下方式填充说明性表格:

INSERT INTO illustrativeTable(label, reportingDate, attr_1, attr_2, attr_3) VALUES('A', '2018-01-01', '1', '3', '100'),
('A', '2018-01-05', '2', '4', '125'), ('A', '2018-01-07', '2', '5', '125'),
('A', '2018-01-08', '3', '6', '150'), ('A', '2018-01-11', '4', '7', NULL),
('B', '2018-01-02', '1', '3', '50'), ('B', '2018-01-05', '2', '5', '75'),
('B', '2018-01-06', '3', '6', '200'), ('B', '2018-01-16', '3', '5', '200'),
('C', '2018-01-05', '6', '9', '175'),('C', '2018-01-08', '7', '9', '225'),
('D', '2018-01-01', '2', '5', '55'), ('D', '2018-01-15', '3', '6', 85),
('D', '2018-01-21', '4', '7', '75'), ('E', '2018-01-25', '2', '4', '65'),
('E', '2018-01-28', '2', '5', NULL);

查询

SELECT * FROM illustrativeTable;

产生以下输出:

+----+-------+---------------+--------+--------+--------+
| id | label | reportingDate | attr_1 | attr_2 | attr_3 |
+----+-------+---------------+--------+--------+--------+
|  1 | A     | 2018-01-01    |      1 |      3 |    100 |
|  2 | A     | 2018-01-05    |      2 |      4 |    125 |
|  3 | A     | 2018-01-07    |      2 |      5 |    125 |
|  4 | A     | 2018-01-08    |      3 |      6 |    150 |
|  5 | A     | 2018-01-11    |      4 |      7 |   NULL |
|  6 | B     | 2018-01-02    |      1 |      3 |     50 |
|  7 | B     | 2018-01-05    |      2 |      5 |     75 |
|  8 | B     | 2018-01-06    |      3 |      6 |    200 |
|  9 | B     | 2018-01-16    |      3 |      5 |    200 |
| 10 | C     | 2018-01-05    |      6 |      9 |    175 |
| 11 | C     | 2018-01-08    |      7 |      9 |    225 |
| 12 | D     | 2018-01-01    |      2 |      5 |     55 |
| 13 | D     | 2018-01-15    |      3 |      6 |     85 |
| 14 | D     | 2018-01-21    |      4 |      7 |     75 |
| 15 | E     | 2018-01-25    |      2 |      4 |     65 |
| 16 | E     | 2018-01-28    |      2 |      5 |   NULL |
+----+-------+---------------+--------+--------+--------+

我们的问题是我们想要检索标签、reportingDate 和 attr_3，从选定的元组，具有以下约束:
1) attr_2 - attr_1 = 3
2) attr_3 不为空
3) 在多次命中的情况下，选择具有报告日期最高值的值

简单的查询:

SELECT label, reportingDate, attr_3 FROM illustrativeTable
WHERE label IN ('A', 'B', 'C', 'E') AND (attr_2-attr_1=3)
AND attr_3 IS NOT NULL GROUP BY label;

产生以下结果:

+-------+---------------+--------+
| label | reportingDate | attr_3 |
+-------+---------------+--------+
| A     | 2018-01-07    |    125 |
| B     | 2018-01-05    |     75 |
| C     | 2018-01-05    |    175 |
+-------+---------------+--------+

此结果的问题在于，对于标签“A”，满足所有约束的最高报告日期是 2018-01-08。同样，对于标签“B”，满足所有约束的最高报告日期是 2018-01-06。

我们想调整查询，使输出如下所示:

+-------+---------------+--------+
| label | reportingDate | attr_3 |
+-------+---------------+--------+
| A     | 2018-01-08    |    150 |
| B     | 2018-01-06    |    200 |
| C     | 2018-01-05    |    175 |
+-------+---------------+--------+

我确实尝试了一些来自 https://paulund.co.uk/get-last-record-in-each-mysql-group 但我得不到我想要的结果。

Answer 1

要获得最高 reportingDate 的输出，您只需将其作为约束添加到查询中。请注意，除非给定的 reportingDate 有多个数据值，否则您不需要 GROUP BY 子句:

SELECT label, reportingDate, attr_3 
FROM illustrativeTable it1
WHERE label IN ('A', 'B', 'C', 'E') AND 
      (attr_2-attr_1=3) AND
      attr_3 IS NOT NULL AND
      reportingDate = (SELECT MAX(reportingDate) 
                       FROM illustrativeTable 
                       WHERE label = it1.label AND 
                             attr_2-attr_1=3 AND 
                             attr_3 IS NOT NULL)

输出:

label   reportingDate   attr_3  
A       2018-01-08      150
B       2018-01-06      200
C       2018-01-05      175

如果给定的 reportingDate 确实有多个值，您将需要GROUP BY label，并且您还必须决定是想要最小值还是最大值attr_3 的值，在这种情况下，您可以将查询中的 attr_3 更改为 MIN(attr_3) 或 MAX(attr_3)分别。

更新

根据 OP 在下面的评论中指定的附加条件，这可能是获得所需结果的最有效查询。它将初始表的子选择(应用非聚合条件)连接到另外两个表，这两个表通过 label 给出最大 reportingDate 和 attr2分别通过reportingDate和label，使用JOIN条件过滤掉所有不符合的条目>MAX(reportingDate) 和 MAX(attr_2)。

SELECT it1.label, it1.reportingDate, it1.attr_3 
FROM (SELECT * 
      FROM illustrativeTable
      WHERE label IN ('A', 'B', 'C', 'E') AND 
            (attr_2-attr_1=3) AND
             attr_3 IS NOT NULL) it1
JOIN (SELECT label, MAX(reportingDate) AS max_reportingDate
      FROM illustrativeTable it1
      WHERE attr_2-attr_1=3 AND attr_3 IS NOT NULL
      GROUP BY label) it2
  ON it2.label = it1.label AND it2.max_reportingDate = it1.reportingDate
JOIN (SELECT label, reportingDate, MAX(attr_2) AS max_attr_2
      FROM illustrativeTable it1
      WHERE attr_2-attr_1=3 AND attr_3 IS NOT NULL
      GROUP BY label, reportingDate) it3
  ON it3.label = it1.label AND it3.reportingDate = it1.reportingDate AND it3.max_attr_2 = it1.attr_2
ORDER BY it1.label

对于样本数据，输出保持不变，但我已经用触发 MAX(attr_2) 条件的数据对其进行了测试 rextester .