mysql 取非集_mysql group by 查询非聚集列

本文介绍 MySQL 中使用 Group By 和聚合函数时的注意事项及优化方法,包括如何避免 only_full_group_by 错误,通过嵌套查询提升性能,并提供了一个实际案例说明优化前后效率的变化。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

本文为博主原创,转载请注明出处:

mysql使用group by可以使用一些聚合函数,可以计算最大值(max(column)),最小值(min(column)),总和(sum(column)),平均数(avg(column()))等等,

在使用聚合函数的函数的时候,我们只可以查询聚合函数相关的列,其余的列则不能进行查询。示例如下:

表结构如下:

CREATE TABLE `fucdn_hot_rank_domain` (

`id`int(12) NOT NULL AUTO_INCREMENT COMMENT '主键',

`domain`int(11) NOT NULL COMMENT '域名id',

`band`double NOT NULL COMMENT '带宽,单位为B',

`total`int(16) NOT NULL COMMENT '请求数',

`clock` varchar(255) NOT NULL COMMENT '时间',

PRIMARY KEY (`id`),

UNIQUE KEY `domain` (`domain`,`clock`),

KEY `domain_id` (`domain`)

) ENGINE=InnoDB AUTO_INCREMENT=21961394 DEFAULT CHARSET=utf8 COMMENT='域名用量热门排行表';

使用聚合函数查询非聚合列:

fd3e19b7a83ec67c3ff48e7eccc02f31.png

查询非聚合列的时候异常提示:

which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by,不在聚合函数之内,查询异常。

如果要查询非聚合函数的列,可以再嵌套一遍进行关联查询,

使用如下:

f9e9694e51cb16501414f6bdcf019f46.png

这种查询的方便之处在于它可以避免写多个sql进行多次查询,但如果需要查询的时候有多条,则需要手动过滤一下。项目中用到的sql如下:

select clock, band, total,domain from(select MAX(band)as band1,domain fromfucdn_hot_rank_domainwheredomainin(11612,11613,11614,11615,11616,11617,11618,11619,11620)and clock BETWEEN '2019-10-20 00:00:00.0' and '2019-10-20 23:59:59.0' GROUP BY domain ORDER BY band1 desc limit 20)tjoin fucdn_hot_rank_domain fhrd on t.band1=fhrd.band and t.domain= fhrd.domain ORDER BY domain

之前代码中要查询这些数据的时候是分批次查询的,先查询所有数据,再根据过滤出的所有域名,计算出对应的峰值以及峰值时间。

代码都是用的流式计算,由于数据量大,计算相对还是比较慢的,后来优化为上面的sql后,性能比之前快了两倍多。

原来实现的代码如下,以供警醒,哈哈

@Overridepublic List getHotRankDomainList(DomainCodeHelper helper) throwsFucdnException {

List resultList = new ArrayList<>();

System.out.println("<<<<<<<<<<<<<<:>

List helperList =hotRankDao.getHotRankDomainList(helper);

System.out.println(">>>>>>>>>>>>>>selectEndTime::"+newDate());//计算出域名带宽峰值,峰值时间,总流量,总请求数等//1.获取域名列表

List domainList =helperList.stream().map(HotRankHelper::getDomain).collect(Collectors.toList());//2.对域名列表去重

domainList =domainList.stream().distinct().collect(Collectors.toList());for(String domain : domainList) {

HotRankDomainResult resultDetail= newHotRankDomainResult();//获取域名对应的列表数据

List domainResultList =helperList.stream()

.filter((HotRankHelper flow)->domain.equals(flow.getDomain())).collect(Collectors.toList());//计算峰值

domainResultList =domainResultList.stream().sorted(Comparator.comparing(HotRankHelper::getBand).reversed())

.collect(Collectors.toList());//计算总流量和请求数

double totalFlow =domainResultList.stream().collect(Collectors.summingDouble(HotRankHelper::getBand));int totalRequest =domainResultList.stream().collect(Collectors.summingInt(HotRankHelper::getTotal));//获取峰值数据

HotRankHelper topHelper = domainResultList.get(0);

resultDetail.setMaxBand(topHelper.getBand());

resultDetail.setMaxBandTime(topHelper.getClock());

resultDetail.setDomain(topHelper.getDomain());

resultDetail.setTotalFlow(totalFlow);

resultDetail.setTotalRequest(Integer.toString(totalRequest));

resultList.add(resultDetail);

}//对集合进行排序:降序

resultList =resultList.stream().sorted(Comparator.comparing(HotRankDomainResult::getMaxBand).reversed())

.collect(Collectors.toList());if (resultList.size() > 20) {//获取前20

resultList = resultList.subList(0, 20);

}

System.out.println(">>>>>>>>>>>>>>sortEndTime::"+newDate());returnresultList;

}

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值