mysql查询查找每个组的*best*行,其中*best*是一个复杂的度量

vltsax25  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(261)

我的table foobar 包含以下列: val: tinyint NOT NULL date: timestamp NOT NULL type: enum('A', 'B', 'C') NOT NULL extra: tinyint NOT NULL 对于每个 type 我想找到与列上的任意条件匹配的行(例如。 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) )在这种简单的情况下有效,但在更复杂的情况下可能更困难。
有没有其他更通用的方案也会这样做?

ghhkc1vu

ghhkc1vu1#

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 |
+-----+---------------------+------+-------+

相关问题