代码之家  ›  专栏  ›  技术社区  ›  Renaud Pacalet

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

  •  0
  • Renaud Pacalet  · 技术社区  · 7 年前

    我的桌子 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 如果相等的话 瓦尔 最小化 date . 我想每个人 类型 这样的行存在并且是唯一的。最后,我想知道结果(有多少行不同 类型 值)排序依据 瓦尔 , 日期 .

    如果 小精灵 包含以下行:

    +------+---------------------+------+-------+
    | 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) )在这种简单的情况下有效,但在更复杂的情况下可能更困难。

    是否还有其他更通用的方案也能做到这一点?

    1 回复  |  直到 7 年前
        1
  •  1
  •   Strawberry    7 年前
    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 |
    +-----+---------------------+------+-------+