MySQL 分组之后如何取Top(N)?

最近碰到一个有意思的问题，因为MySQL里没有top n的用法，所以如果要实现取数据的前几操作只能通过排序之后加limit限制数量，但是这种用法又跟group 冲突。这篇文章就是来分析下分组取topN的解题思路。

现在创建一个测试表。用户的商品消费数据（测试表就不建立索引了）

CREATE TABLE `tb_user_consume` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned DEFAULT NULL,
  `goods_id` int(11) unsigned DEFAULT NULL,
  `goods_name` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL,
  `price` decimal(10,2) unsigned DEFAULT NULL,
  `num` int(10) unsigned DEFAULT NULL,
  `total` decimal(10,2) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tb_user_consume`(`user_id`,`goods_id`,`goods_name`,`price`,`num`,`total`)
VALUES
(100,1,"1号商品",1.3,10, price*num),
(100,2,"2号商品",1.15,12, price*num),
(100,3,"3号商品",3.4,5, price*num),
(100,4,"4号商品",18.99,2, price*num),
(100,5,"5号商品",7.4,9, price*num),'
(101,1,"1号商品",1.3,13, price*num),
(101,2,"2号商品",1.15,12, price*num),
(101,3,"3号商品",3.4,20, price*num),
(101,4,"4号商品",18.99,8, price*num),
(101,5,"5号商品",7.4,7, price*num),'
(102,1,"1号商品",1.3,21, price*num),
(102,2,"2号商品",1.15,3, price*num),
(102,3,"3号商品",3.4,51, price*num),
(102,4,"4号商品",18.99,23, price*num),
(102,5,"5号商品",7.4,22, price*num),'
(103,1,"1号商品",1.3,2, price*num),
(103,2,"2号商品",1.15,7, price*num),
(103,3,"3号商品",3.4,9, price*num),
(103,4,"4号商品",18.99,22, price*num),
(103,5,"5号商品",7.4,99, price*num),'
(104,1,"1号商品",1.3,77, price*num),
(104,2,"2号商品",1.15,54, price*num),
(104,3,"3号商品",3.4,23, price*num),
(104,4,"4号商品",18.99,23, price*num),
(104,5,"5号商品",7.4,44, price*num)
;

假如现在有一个需求是，筛选出用户消费商品总价最高的前三个商品。

粗一看，这个需求也没有什么实现上的难度，就是根据用户分组，取出表里total最高的三行记录就可以了。
对没有错，需求就是这么简单，解题思路也不难，那么我们开始着手编码了。

第一步，做一个子查询，

取出表里total最高的三行记录

sql写起来也很简单，如下所示

SELECT * FROM `tb_user_consume` WHERE user_id = 100 ORDER BY total DESC LIMIT 3;

第二步，按照用户分组
取出所有用户

SELECT * FROM `tb_user_consume` ORDER BY total DESC LIMIT 3 GROUP BY user_id;

看这个好像是满足了需求，别急，我们运行一下。

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘GROUP BY user_id’ at line 1

报错了，很明显上面的sql有语法错误。limit 只能用在查询语句的最后面。
那么我们要怎么去实现这个需求呢？用单一的子查询好像都没法直接的按照用户分组来取数据。
一般到这种时候，我们很可能就直接用代码来解决了。

先取出所有用户列表。 SELECT DISTINCT(user_id) AS uid FROM tb_user_consume;

然后遍历用户列表，按照上面的查询语句查出所有的用户前三total信息 SELECT * FROM tb_user_consume WHERE user_id = 100 ORDER BY total DESC LIMIT 3;

这种方法不是不可取，在表里的数据不多的时候，用这个也能完成需求，抛去执行效率不说，我们就说开发效率，又是写代码，又是写sql。还要去联调，是不是很费时费力？
那么到底能不能通过sql语句直接查询出来呢？

首先我们要想，上面不能实现的痛点在哪里？没有办法先limit 3，对不对？那我们能不能通过排序筛选的方式来实现，排序后达到三个的数量我们就停止。
按照机器的思维应该是，先order by user_id, 然后 order by total desc。

SELECT * FROM tb_user_consume ORDER BY user_id ,total DESC;

在这个结果集里当user_id 输出3个记录行就停止。 本文的重点来了 怎么实现这个呢？
通过谷歌（其实是百度）发现mysql里有一个 case when的条件判断。正好满足我们的需求。【在这个结果集里当user_id 输出3个记录行就停止】 美哉！开撸。

SELECT @rnd :=
    CASE
    WHEN @userid = `user_id` THEN
    @rnd := @rnd+1
    ELSE 1
    END rnd, @userid := `user_id`, user_id,total,goods_id,goods_name,price
FROM `tb_user_consume`,
    (SELECT @rnd := 1,
         @userid:=0) b
    ORDER BY  `user_id` ,`total` DESC

像这样我们就可以以rnd变量来标记我们的结果集排序结果了。这样我们把它作为一个子查询在外面加上限制条件就拿到指定的行数。
最终的sql如下：

SELECT *
FROM 
    (SELECT @rnd :=
        CASE
        WHEN @userid = `user_id` THEN
        @rnd := @rnd+1
        ELSE 1
        END rnd, @userid := `user_id`, user_id,total,goods_id,goods_name,price
    FROM `tb_user_consume`,
        (SELECT @rnd := 1,
         @userid:=0) b
        ORDER BY  `user_id` ,`total` DESC) aa
    WHERE rnd <=3;

最终的查询展示结果：

+------+----------------------+---------+--------+----------+------------+-------+
| rnd  | @userid := `user_id` | user_id | total  | goods_id | goods_name | price |
+------+----------------------+---------+--------+----------+------------+-------+
|    1 |                  100 |     100 |  66.60 |        5 | 5号商品    |  7.40 |
|    2 |                  100 |     100 |  37.98 |        4 | 4号商品    | 18.99 |
|    3 |                  100 |     100 |  17.00 |        3 | 3号商品    |  3.40 |
|    1 |                  101 |     101 | 151.92 |        4 | 4号商品    | 18.99 |
|    2 |                  101 |     101 |  68.00 |        3 | 3号商品    |  3.40 |
|    3 |                  101 |     101 |  51.80 |        5 | 5号商品    |  7.40 |
|    1 |                  102 |     102 | 436.77 |        4 | 4号商品    | 18.99 |
|    2 |                  102 |     102 | 173.40 |        3 | 3号商品    |  3.40 |
|    3 |                  102 |     102 | 162.80 |        5 | 5号商品    |  7.40 |
|    1 |                  103 |     103 | 732.60 |        5 | 5号商品    |  7.40 |
|    2 |                  103 |     103 | 417.78 |        4 | 4号商品    | 18.99 |
|    3 |                  103 |     103 |  30.60 |        3 | 3号商品    |  3.40 |
|    1 |                  104 |     104 | 436.77 |        4 | 4号商品    | 18.99 |
|    2 |                  104 |     104 | 325.60 |        5 | 5号商品    |  7.40 |
|    3 |                  104 |     104 | 100.10 |        1 | 1号商品    |  1.30 |
+------+----------------------+---------+--------+----------+------------+-------+

参考文章: 我的mysql如何分组取top10？

<?php
$host = '127.0.0.1';
$dbname = 'yang';
$port = 3306;

$db = new PDO("mysql:host=$host;dbname=$dbname;port=$port", 'root', '12345');

$goods_info = [
    ['id' => 1, 'name' => '1号商品', 'price' => 1.30],
    ['id' => 2, 'name' => '2号商品', 'price' => 1.15],
    ['id' => 3, 'name' => '3号商品', 'price' => 3.40],
    ['id' => 4, 'name' => '4号商品', 'price' => 18.99],
    ['id' => 5, 'name' => '5号商品', 'price' => 7.40],
];

function insert($goods_info, PDO &$db, $start)
{
    $sql = 'insert into tb_user_consume(user_id,goods_id,goods_name,price,num,total) values';
    for ($i = $start; $i < $start + 50000; $i++) {
        foreach ($goods_info as $info) {
            $num = mt_rand(0, 1000);
            $total = $num * $info['price'];
            $sql .= sprintf("(%d,%d,\"%s\",%f,%d,%f),", $i, $info['id'], $info['name'], $info['price'], $num, $total);
        }
    }
    $sql = substr($sql, 0, -1);
    //echo $sql;

    $db->prepare($sql)->execute();
}

// 批量添加测试数据
//for ($j = 1000000; $j < 2000000; $j += 50000) {
//    insert($goods_info,$db,$j);
//}

// 执行时间
$start = time();
select($db);
echo "cost:".(time()-$start)."\n";

function select(PDO &$db){
    for ($i = 1000000; $i < 2000000; $i++) {
        $sql = 'SELECT * FROM `tb_user_consume` WHERE user_id = '.$i.' ORDER BY total DESC LIMIT 3; ';
        $ret = $db->query($sql)->fetchAll();
        //var_dump($ret);
    }
}

MySQL php

发表回复 取消回复

发表回复取消回复