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

最新推荐文章于 2024-03-20 10:09:10 发布

龙池小生

最新推荐文章于 2024-03-20 10:09:10 发布

阅读量3.9w

点赞数 1

分类专栏： sql

本文链接：https://blog.csdn.net/chenping1993/article/details/111885783

版权

sql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

需求：

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

方案：

1、sql统计

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

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

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

测试：

表结构：

添加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;

因为涉及子查询，查询效率太低，几分钟后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秒之内

方案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)

在类别不多的情况下，使用此种方式，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秒