背景 : 最近有一个需求要求对数据底表先进行分组, 再对分组内的数据进行排序, 最后限制每个分组的最大数量; 业务使用了 mysql , 这个问题属于分组 top n 问题;

业务场景如下:

取数逻辑：按日期，每日获取【合作方内容维度数据】报表当日数据，并限定以下条件：以【日期】-【累积曝光量】按从大到小排序，取每天最大的top 1000 条内容进行展示

为了简化数据模型, 我重新创建一张表用来做测试, 表结构如下

CREATE TABLE `stu_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `subject` varchar(255) DEFAULT NULL,
  `score` int(11) DEFAULT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

创建一批初始数据, 用来验证结果 : 2个分组, score字段递增, 数学组出现数据重复

每个分组取score最高的前2名, 预期的结果是:

问题点 : 如何取数?

一开始我也没有思路, 之前并没有接触过类似的场景; 查资料之后发现两种通用写法, 如下

方法一: 左连接

缺点 : 边界值重复不会输出任何边界值

SELECT
    a.`subject`,a.score
FROM
    stu_score AS a
LEFT JOIN stu_score AS b ON a.`subject` = b.`subject`
    AND a.score < b.score
GROUP BY
    a.`subject`,a.`score`
HAVING
    count(1) < 2
ORDER BY
    a.`subject` ASC,
    a.score DESC;

查询结果 :

语文 5
语文 4

可以看到, 数学组没有显示在结果; 具体原因需要先分析以上sql 的思路:

1. stu_score和自己左连接, 这样会产生一次笛卡尔积的操作, 得到 3 * 3 * 2 = 18 条记录
2. 比较第一步的结果, 把同一行中两个score进行比较, 只保留左边score比右边score大的结果; 如果没有符合条件的情况, 至少出现一次, 右边score的值是null
3. 以subject和score为维度, 将这两项相同的看成一个整体, 统计每个整体出现的结果; 产生一组计数值
4. 对计数值进行条件筛选, 只取计数值小于2的行记录

实践一下上面的逻辑:

第1步: 18条笛卡尔积的结果通过执行以下语句产生

SELECT
    a.`subject`,a.score,b.`subject`,b.score
FROM
    stu_score AS a
LEFT JOIN stu_score AS b ON a.`subject` = b.`subject`;

第2步 :

SELECT
    a.`subject`,a.score,b.`subject`,b.score
FROM
    stu_score AS a
LEFT JOIN stu_score AS b ON a.`subject` = b.`subject`
    AND a.score < b.score 
ORDER BY  a.`subject` desc, a.score desc;

第3步 :

SELECT
    a.`subject`,a.score,b.`subject`,b.score,count(1)
FROM
    stu_score AS a
LEFT JOIN stu_score AS b ON a.`subject` = b.`subject`
    AND a.score < b.score
GROUP BY
    a.`subject`,a.`score`;

执行结果 :

第4步 :

SELECT
    a.`subject`,a.score,b.`subject`,b.score
FROM
    stu_score AS a
LEFT JOIN stu_score AS b ON a.`subject` = b.`subject`
    AND a.score < b.score
GROUP BY
    a.`subject`,a.`score`
HAVING
    count(1) < 2;

按照这个步骤一步步操作, 会让人产生一个疑问 : a.score < b.score 这个语句是什么作用, 为什么加了这句就使语文组成功返回正确结果?

我执行了以下语句用于分析语文组的计数过程 :

SELECT *, sum(result) from (
    SELECT
    a.`subject`,
    a.score AS a_score,
    b.score AS b_score,
    IF( a.score < b.score, 1, 0 ) AS result
FROM
    stu_score AS a
    LEFT JOIN stu_score AS b ON a.`subject` = b.`subject` 
WHERE
    a.`subject` = "语文" 
) as t
GROUP BY
    t.`subject`,t.a_score;

观察语文组可以发现, 计数的目的是为了给分组内每个不同的值产生一个唯一的递增序号, 越符合条件的数据序号越小, 使得后续能够在聚合函数having中过滤掉多出来的记录

但是这个做法存在一个缺陷: 如果同一个分组内存在多个重复的值, 结果就一定会出现错误, 因此以上写法只适用于排序字段不重复的情况

执行这个 sql , 观察数学组 :

SELECT *,sum(result) from (
    SELECT
    a.`subject`,
    a.score AS a_score,
    b.score AS b_score,
    IF( a.score <= b.score , 1, 0 ) AS result
FROM
    stu_score AS a
    LEFT JOIN stu_score AS b ON a.`subject` = b.`subject` 
WHERE
    a.`subject` = "数学" 
) as t
GROUP BY
    t.`subject`,t.a_score;

从上面的例子可以得到一个思路, 分组top n的问题可以转化为分组内数据唯一的问题; 我们需要实现在一个分组中按预定的排序规则排序, 并且在数据重复的时候也要使用第二排序规则使对重复的数据做出唯一标识; 只有把重复的数据区分开, 才能精确地控制我们需要取出的行数

这时候我考虑到数据表是存在唯一字段id的, 能否同时使用score和id字段对分组内数据进行count计算?

方案二 : 使用多字段标识分组数据

思路 : 同时使用score和id对同组数据进行唯一标识; 当a.score<b.score的时候, 算1次计数 ; 当a.score=b.score时, 用id比较; 其他情况都不计数

SELECT
    a.`subject`,a.score
FROM
    stu_score AS a
LEFT JOIN stu_score AS b ON a.`subject` = b.`subject`
    AND (( (a.score = b.score) and (a.id < b.id) ) or (a.score < b.score))
GROUP BY
    a.`subject`,a.`score`,a.`id`
HAVING
    count(1) < 2
ORDER BY
    a.`subject` ASC,
    a.score DESC;

尝试打乱id 的顺序, 执行结果依然是正确的, 这个方案是可以实现, 但是 sql 比较复杂

方法三 : 子查询

缺点 : 边界值重复会把全部重复的值取出来

SELECT
    a.`subject`,a.score
FROM
    stu_score AS a
WHERE
    (
        SELECT
            count(1)
        FROM
            stu_score AS b
        WHERE
            a.`subject` = b.`subject`
        AND a.score < b.score
    ) < 2
ORDER BY
    a.`subject` DESC,
    a.score DESC;

这个和方案一存在一样的问题, 取数不精准

方法四 : 比较边界值

思路 : 先分组, 再获取分组中的分界值, 再比较分界值和每行的大小; 通过比较界限值避免界限值之前的数据多取; 相比方案二降低了错误概率

缺点 : 无法避免界限值相等的数据被多取, 仍然是取数不精确

说明 : default_score的意义在于处理界限值不存在的情况, 如果界限值不存在说明整组都可以取出, 所以给0就行了

SELECT a.`subject`,a.score,a.aim_score,a.default_score
FROM (
    SELECT * ,
        ( SELECT score FROM stu_score as b WHERE c.`subject`=b.`subject` ORDER BY score DESC LIMIT 1, 1 ) as aim_score,
        ( SELECT 0 ) AS default_score
    FROM stu_score AS c 
) as a 
WHERE a.score >= IFNULL(a.aim_score,a.default_score)
ORDER BY a.`subject` DESC , a.score DESC;

执行结果 :

优化 : 如果界限值aim_score存在重复的话, 会把这几个重复的都取出来; 假如按分数降序排序, 取前5名, 实际上从第4名开始都是0分, 那么这个语句会把整个分组都输出; 假设同一个分组数据量有1千万条呢? 大量数据重复的列不应该使用这个方案, 针对少量重复的降序数据列最好对加一个限制 a.score > 0; 但是总体上看, 这个方案的局限性还是很大的

# 这样只是避开出现概率最高的边界值, 但是同样不能保证精准输出想要的行
SELECT a.`subject`,a.score,a.aim_score,a.default_score
FROM (
    SELECT * ,
        ( SELECT score FROM stu_score as b WHERE c.`subject`=b.`subject` ORDER BY score DESC LIMIT 2, 1 ) as aim_score,
        ( SELECT 0 ) AS default_score
    FROM stu_score AS c 
) as a 
WHERE a.score >= IFNULL(a.aim_score,a.default_score) and a.score > 0
ORDER BY a.`subject` DESC , a.score DESC;

方法五 : 使用行号标识分组数据

分析 : 既然要求分组内数据有序且唯一, 那我自己创造一个唯一的字段不就可以了吗? 行号是一个不错的选择, 可惜mysql不支持这个关键字 (oracle支持 rownum 关键字, mysql不支持; 需要自己实现)

思路 : 先分组, 分组内排序, 对分组内数据添加行号, 取出行号前n位的数据

SELECT c.subject,c.score from (
    SELECT
        a.*,
        @rw  := IF(@sj = a.`subject`,@rw  + 1,1)  AS rowNum,
        @sj := a.`subject`
    FROM stu_score a ,( SELECT @rw  := 0, @sj := '' ) b 
    ORDER BY a.`subject` DESC, a.score * 1 DESC
) as c where c.rowNum <= 2

在mysql中, 支持变量写法, 支持IF语法, 且用户变量在数据库实例整个过程中都有效;

初始化两个变量 @rw = 0 ; @sj = '' ; 遍历每一行时赋值, @sj 存subject 的值; 如果当前行的subject等于上一次的subject, 说明同一组, @rw加1; 否则说明是分组第一行, @rw = 1

执行结果 :

这个方案是目前为止最简洁的方案, 取数精准; 对性能也没有太大消耗; 整体看下只有方案二和方案五能完全实现产品的要求, 我最终选择了方案五实现需求