大数据量分类统计每个类别的排名前三的数据

ゞ浴缸里的玫瑰 2022-12-31 08:26 159阅读 0赞

## **需求：** ##

**统计每个类别某项数据排名前几的数据**

## 方案： ##

1、sql统计

2、sql查询出所有的数据，代码 group 分组取出每个类别的前3个数据

3、sql查询每个类别前3数据，union连接起来

4、sql查询查询出来所有的类型，根据类型查询出每个类型的前3个数据

## 测试： ##

**表结构：**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70][]

**添加15万条测试数据：**

@Before
        public void  getList() {
            long start = System.currentTimeMillis();
            Ranking user;
            for (int i = 1; i <= 3; i++) {
                for (int j = 1; j <=50000 ; j++) {
                    user = new Ranking();
                    user.setType(i);
                    user.setNum(j);
                    list.add(user);
                }
    
            }
            System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start));
            System.out.println(list.size());
        }
    
        @Test
        public void batchInsert() {
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
            RankMapper mapper = sqlSession.getMapper(RankMapper.class);
            System.out.println("batchInsert 插入开始========");
            long start = System.currentTimeMillis();
            for (int i = 0; i < list.size(); i++) {
                mapper.insert(list.get(i));
                if (i%5000==4999) {
                    sqlSession.flushStatements();
    
                }
            }
            sqlSession.flushStatements();
            System.out.println("SqlSession 批量插入耗时："+(System.currentTimeMillis()-start));
        }

### **方案1：** ###

SELECT
    	a.type,a.num
    FROM
    	ranking AS a
    WHERE
    	(
    		SELECT
    			COUNT(*)
    		FROM
    			ranking AS b
    		WHERE
    			b.type = a.type
    		AND b.num >= a.num
    	) <= 3
    ORDER BY
    	a.type ASC,
    	a.num DESC;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 1][]

因为涉及子查询，查询效率太低，几分钟后sql还是没有执行完毕。。

### **方案2：** ###

**先使用mybatis-plus 查询出所有的数据，使用分组统计出每个类别前三的数据。**

@Test
        public void testRankSelect() {
            long start = System.currentTimeMillis();
            List<Ranking> rankings = rankMapper.selectList(null);
            long end = System.currentTimeMillis();
            System.out.println("查询15万数据查询时间："+(end-start));
            System.out.println("分组开始================");
            List<Ranking> rankingresult = new ArrayList<>();
            Map<Integer, List<Ranking>> typeGroup = rankings.stream().collect(Collectors.groupingBy(Ranking::getType));
            typeGroup.forEach((k,v)->{
                List<Ranking> v1 = v;
                v1.sort(Comparator.comparing(Ranking::getNum).reversed());
                v1 = v1.subList(0,3);
                rankingresult.addAll(v1);
            });
            long groupEnd = System.currentTimeMillis();
            System.out.println("分组统计前三花费时间："+(groupEnd-end));
            System.out.println(rankingresult);
        }

**效果：15万条数据，查询加上分组统计大约花费2秒；用sql时间应该在1秒之内**

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 2][]

### **方案3：** ###

**分别查询出3个类别前3的数据，然后union起来**

(SELECT type,num from ranking where type = 1 order by num desc limit 3)
    union
    (SELECT type,num from ranking where type = 2 order by num desc limit 3)
    union
    (SELECT type,num from ranking where type = 3 order by num desc limit 3)

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 3][]

**在类别不多的情况下，使用此种方式，3个类别，15万条数据 执行时间0.12秒**

### **方案4：** ###

**先sql查询出来所有的类型，根据类型遍历查询每个类型的前3条数据，将查询出来的数据放入list中**

@Test
        public void queryWrapperRankSelect() {
            long start = System.currentTimeMillis();
            QueryWrapper<Ranking> queryWrapper = new QueryWrapper<>();
            queryWrapper.select("distinct type");
            List<Ranking> rankTypes = rankMapper.selectList(queryWrapper);
    
            List<Ranking> rankingresult = new ArrayList<>();
            rankTypes.forEach(e->{
                LambdaQueryWrapper<Ranking> lambdaQueryWrapper = new LambdaQueryWrapper<>();
                lambdaQueryWrapper.eq(Ranking::getType,e.getType());
                lambdaQueryWrapper.orderByDesc(Ranking::getNum);
                lambdaQueryWrapper.last("limit 3");
                List<Ranking> rankings = rankMapper.selectList(lambdaQueryWrapper);
                rankingresult.addAll(rankings);
            });
            long end = System.currentTimeMillis();
            System.out.println("分组统计前三花费时间："+(end-start));
            System.out.println(rankingresult);
        }

效果：查询处理数据大约花费0.7秒

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 4][]

## 总结： ##

若是需求允许，并且类别固定且数量少的情况下，使用方案3效率最高（灵活性低，**类别固定情况下可以使用**）；

其次就是方案4，先sql查询出来所有的类型，根据类型遍历查询每个类型的前3条数据，将查询出来的数据放入list中，效率高（**建议使用**）。

然后就是使用方案2，先查询出来所有的数据，在分组求每个类别的前3的数据，效率也挺高的（**建议使用**）。

最后就是方案1，使用sql直接计算出结果，效率最低，如果数据量大的情况下程序可能会崩溃（**不建议使用**）。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70]: /images/20221120/f340d79abad145699ea205efd9033a73.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20201229115322458.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20201229115747554.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/2020122911595631.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20201231094259931.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw==,size_16,color_FFFFFF,t_70