MySQL 查询查找每组的*最佳*行，其中*最佳*是一个复杂的指标

Question

我的表 foobar 有以下列:


val:tinyint NOT NULL
日期:时间戳不为空
类型:枚举('A'，'B'，'C')NOT NULL
额外:tinyint NOT NULL


对于每个类型，我想找到与列上的任意条件匹配的行(例如extra > 12 AND val > 0)，从而最大限度地减少val，如果val相等，则最小化date。我假设对于每个type，这样的行都存在并且是唯一的。最后，我希望结果(有不同 type 值的行数)按 val、date 排序。

如果foobar包含以下行:

+------+---------------------+------+-------+
| val  | date                | type | extra |
+------+---------------------+------+-------+
| -1   | 2014-04-10 00:00:00 | A    | 40    |
|  1   | 2014-04-15 00:00:00 | A    | 15    |
|  2   | 2014-04-12 00:00:00 | A    | 77    |
|  1   | 2014-04-11 00:00:00 | A    |  2    |
|  1   | 2014-04-14 00:00:00 | A    | 22    |
|  1   | 2014-04-10 00:00:00 | B    | 40    |
|  1   | 2014-04-15 00:00:00 | B    | 15    |
|  1   | 2014-04-12 00:00:00 | B    | 77    |
|  1   | 2014-04-11 00:00:00 | B    |  2    |
|  1   | 2014-04-14 00:00:00 | B    | 22    |
|  4   | 2014-04-10 00:00:00 | C    | 40    |
|  3   | 2014-04-15 00:00:00 | C    | 15    |
|  3   | 2014-04-12 00:00:00 | C    | 77    |
|  1   | 2014-04-11 00:00:00 | C    |  2    |
|  3   | 2014-04-14 00:00:00 | C    | 22    |
+------+---------------------+------+-------+

查询应返回:

+------+---------------------+------+-------+
| val  | date                | type | extra |
+------+---------------------+------+-------+
|  1   | 2014-04-10 00:00:00 | B    | 40    |
|  1   | 2014-04-14 00:00:00 | A    | 22    |
|  3   | 2014-04-12 00:00:00 | C    | 77    |
+------+---------------------+------+-------+

这似乎有效:

SELECT a.* FROM (
    SELECT MIN(val * 4294967296 + UNIX_TIMESTAMP(date)) AS score
        FROM foobar WHERE extra > 12 AND val > 0
        GROUP BY type
    ) AS b
INNER JOIN foobar AS a
    ON a.val * 4294967296 + UNIX_TIMESTAMP(a.date) = b.score
ORDER BY val, date;

但我发现它过于复杂，我怀疑一定有更好的方法。此外，在这种简单的情况下，将多列条件转换为单个数值 (val * 4294967296 + UNIX_TIMESTAMP(date)) 是可行的，但在更复杂的情况下可能会更加困难。

还有其他更通用的方案可以实现相同的功能吗？

Answer 1

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(val INT SIGNED NOT NULL
,date TIMESTAMP NOT NULL
,type CHAR(1) NOT NULL 
,extra TINYINT NOT NULL
,PRIMARY KEY(val,date,type)
);

INSERT INTO my_table VALUES
(-1,'2014-04-10 00:00:00','A',40),
( 1,'2014-04-15 00:00:00','A',15),
( 2,'2014-04-12 00:00:00','A',77),
( 1,'2014-04-11 00:00:00','A', 2),
( 1,'2014-04-14 00:00:00','A',22),
( 1,'2014-04-10 00:00:00','B',40),
( 1,'2014-04-15 00:00:00','B',15),
( 1,'2014-04-12 00:00:00','B',77),
( 1,'2014-04-11 00:00:00','B', 2),
( 1,'2014-04-14 00:00:00','B',22),
( 4,'2014-04-10 00:00:00','C',40),
( 3,'2014-04-15 00:00:00','C',15),
( 3,'2014-04-12 00:00:00','C',77),
( 1,'2014-04-11 00:00:00','C', 2),
( 3,'2014-04-14 00:00:00','C',22);

SELECT a.* 
  FROM my_table a 
  JOIN 
     ( SELECT x.val
            , x.type
            , MIN(x.date) date 
         FROM my_table x 
         JOIN 
            ( SELECT MIN(val) val
                   , type
                FROM my_table 
               WHERE extra > 12 
                 AND val > 0 
               GROUP 
                  BY type
            ) y 
           ON y.type = x.type 
          AND y.val = x.val 
        WHERE x.extra > 12
        GROUP 
           BY val
            , type
     ) b 
    ON b.val = a.val 
   AND b.type = a.type 
   AND b.date = a.date;

+-----+---------------------+------+-------+
| val | date                | type | extra |
+-----+---------------------+------+-------+
|   1 | 2014-04-14 00:00:00 | A    |    22 |
|   1 | 2014-04-10 00:00:00 | B    |    40 |
|   3 | 2014-04-12 00:00:00 | C    |    77 |
+-----+---------------------+------+-------+