1

#yyds干货盘点#SQL聚合查询方法总结

 1 year ago
source link: https://blog.51cto.com/u_11365839/5434563
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

#yyds干货盘点#SQL聚合查询方法总结

原创

尼羲 2022-07-01 16:06:39 ©著作权

文章标签 字段 聚合函数 sql 文章分类 软件设计 软件研发 yyds干货盘点 阅读数182

SQL 为什么要支持聚合查询呢？

这看上去是个幼稚的问题，但我们还是一步步思考一下。数据以行为粒度存储，最简单的 SQL 语句是 select * from test，拿到的是整个二维表明细，但仅做到这一点远远不够，出于以下两个目的，需要 SQL 提供聚合函数：

1.明细数据没有统计意义，比如我想知道今天的营业额一共有多少，而不太关心某桌客人消费了多少。

2.虽然可以先把数据查到内存中再聚合，但在数据量非常大的情况下很容易把内存撑爆，可能一张表一天的数据量就有 10TB，而 10TB 数据就算能读到内存里，聚合计算可能也会慢到难以接受。

另外聚合本身也有一定逻辑复杂度，而 SQL 提供了聚合函数与分组聚合能力，可以方便快速的统计出有业务价值的聚合数据，这奠定了 SQL 语言的分析价值，因此大部分分析软件直接采用 SQL 作为直接面向用户的表达式。

常见的聚合函数有：

  • COUNT：计数。
  • SUM：求和。
  • AVG：求平均值。
  • MAX：求最大值。
  • MIN：求最小值。

COUNT

COUNT 用来计算有多少条数据，比如我们看 id 这一列有多少条：

SELECT COUNT(id) FROM test



但我们发现其实查任何一列的 COUNT 都是一样的，那传入 id 有什么意义呢？没必要特殊找一个具体列指代呀，所以也可以写成：



SELECT COUNT(*) FROM test



但这两者存在微妙差异。SQL 存在一种很特殊的值类型 ​​NULL​​​，如果 COUNT 指定了具体列，则统计时会跳过此列值为 ​​NULL​​​ 的行，而 ​​COUNT(*)​​​ 由于未指定具体列，所以就算包含了 ​​NULL​​​，甚至某一行所有列都为 ​​NULL​​​，也都会包含进来。所以 ​​COUNT(*)​​​ 查出的结果一定大于等于 ​​COUNT(c1)​​。



当然任何聚合函数都可以跟随查询条件 WHERE，比如：



SELECT COUNT(*) FROM test
WHERE is_gray = 1



SUM 求和所有项，因此必须作用于数值字段，而不能用于字符串。



SELECT SUM(cost) FROM test



SUM 遇到 NULL 值时当 0 处理，因为这等价于忽略。



AVG 求所有项均值，因此必须作用于数值字段，而不能用于字符串。



SELECT AVG(cost) FROM test



AVG 遇到 NULL 值时采用了最彻底的忽略方式，即 NULL 完全不参与分子与分母的计算，就像这一行数据不存在一样。



MAX、MIN



MAX、MIN 分别求最大与最小值，上面不同的时，也可以作用于字符串上，因此可以根据字母判断大小，从大到小依次对应 ​​a-z​​，但即便能算，也没有实际意义且不好理解，因此不建议对字符串求极值。



SELECT MAX(cost) FROM test



多个聚合字段



虽然都是聚合函数，但 MAX、MIN 严格意义上不算是聚合函数，因为它们只是寻找了满足条件的行。可以看看下面两段查询结果的对比：



SELECT MAX(cost), id FROM test -- id: 100
SELECT SUM(cost), id FROM test -- id: 1



第一条查询可以找到最大值那一行的 id，而第二条查询的 id 是无意义的，因为不知道归属在哪一行，所以只返回了第一条数据的 id。



当然，如果同时计算 MAX、MIN，那么此时 id 也只返回第一条数据的值，因为这个查询结果对应了复数行：



SELECT MAX(cost), MIN(cost), id FROM test -- id: 1



基于这些特性，最好不要混用聚合与非聚合，也就是一条查询一旦有一个字段是聚合的，那么所有字段都要聚合。



现在很多 BI 引擎的自定义字段都有这条限制，因为混用聚合与非聚合在自定义内存计算时处理起来边界情况很多，虽然 SQL 能支持，但业务自定义的函数可能不支持。



分组聚合就是 GROUP BY，其实可以把它当作一种高级的条件语句。



举个例子，查询每个国家的 GDP 总量：



SELECT COUNT(GDP) FROM amazing_table
GROUP BY country



返回的结果就会按照国家进行分组，这时，聚合函数就变成了在组内聚合。



其实如果我们只想看中、美的 GDP，用非分组也可以查，只是要分成两条 SQL：



SELECT COUNT(GDP) FROM amazing_table
WHERE country = '中国'

SELECT COUNT(GDP) FROM amazing_table
WHERE country = '美国'



所以 GROUP BY 也可理解为，将某个字段的所有可枚举的情况都查了出来，并整合成一张表，每一行代表了一种枚举情况，不需要分解为一个个 WHERE 查询了。



多字段分组聚合



GROUP BY 可以对多个维度使用，含义等价于表格查询时行/列拖入多个维度。



上面是 BI 查询工具视角，如果没有上下文，可以看下面这个递进描述：



  • 按照多个字段进行分组聚合。
  • 多字段组合起来成为唯一 Key，即​​GROUP BY a,b​​ 表示 a,b 合在一起描述一个组。
  • ​​GROUP BY a,b,c​​ 查询结果第一列可能看到许多重复的 a 行，第二列看到重复 b 行，但在同一个 a 值内不会重复，c 在 b 行中同理。



下面是一个例子：



SELECT SUM(GDP) FROM amazing_table
GROUP BY province, city, area



GROUP BY + WHERE



WHERE 是根据行进行条件筛选的。因此 GROUP BY + WHERE 并不是在组内做筛选，而是对整体做筛选。



但由于按行筛选，其实组内或非组内结果都完全一样，所以我们几乎无法感知这种差异：



SELECT SUM(GDP) FROM amazing_table
GROUP BY province, city, area
WHERE industry = 'internet'



然而，忽略这个差异会导致我们在聚合筛选时碰壁。



比如要筛选出平均分大于 60 学生的成绩总和，如果不使用子查询，是无法在普通查询中在 WHERE 加聚合函数实现的，比如下面就是一个语法错误的例子：



SELECT SUM(score) FROM amazing_table
WHERE AVG(score) > 60



不要幻想上面的 SQL 可以执行成功，不要在 WHERE 里使用聚合函数。



GROUP BY + HAVING



HAVING 是根据组进行条件筛选的。因此可以在 HAVING 使用聚合函数：



SELECT SUM(score) FROM amazing_table
GROUP BY class_name
HAVING AVG(score) > 60



上面的例子中可以正常查询，表示按照班级分组看总分，且仅筛选出平均分大于 60 的班级。



所以为什么 HAVING 可以使用聚合条件呢？因为 HAVING 筛选的是组，所以可以对组聚合后过滤掉不满足条件的组，这样是有意义的。而 WHERE 是针对行粒度的，聚合后全表就只有一条数据，无论过滤与否都没有意义。



但要注意的是，GROUP BY 生成派生表是无法利用索引筛选的，所以 WHERE 可以利用给字段建立索引优化性能，而 HAVING 针对索引字段不起作用。

        

        

            

                report
            

        

    







        
Recommend

        



                

    
About Joyk

    
 





Aggregate valuable and interesting links.

Joyk means Joy of geeK