mysql group by 查询非聚集列

本文为博主原创，转载请注明出处：

　　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='域名用量热门排行表';

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

 

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

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

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

使用如下：

 

 

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

select clock, band, total,domain from (
select MAX(band)as band1,domain from fucdn_hot_rank_domain
where domain 
in(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
)t join fucdn_hot_rank_domain fhrd on t.band1=fhrd.band and t.domain= fhrd.domain ORDER BY domain

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

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

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

@Override
    public List<HotRankDomainResult> getHotRankDomainList(DomainCodeHelper helper) throws FucdnException {
        List<HotRankDomainResult> resultList = new ArrayList<>();
        System.out.println("<<<<<<<<<<<<<<selectTime::"+new Date());
        List<HotRankHelper> helperList = hotRankDao.getHotRankDomainList(helper);
        System.out.println(">>>>>>>>>>>>>>selectEndTime::"+new Date());

        // 计算出域名带宽峰值，峰值时间，总流量，总请求数等
        // 1.获取域名列表
        List<String> domainList = helperList.stream().map(HotRankHelper::getDomain).collect(Collectors.toList());

        // 2.对域名列表去重
        domainList = domainList.stream().distinct().collect(Collectors.toList());
        for (String domain : domainList) {
            HotRankDomainResult resultDetail = new HotRankDomainResult();
            // 获取域名对应的列表数据
            List<HotRankHelper> 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::"+new Date());
        return resultList;
    }