searchresponse 分组查询分组查询用什么语句

转载

编程梦想实现家 2024-04-29 17:35:28

文章标签 searchresponse 分组查询字段聚合函数数据 文章分类 架构后端开发

文章目录

1、分组查询

①GROUP BY
②HAVING
总结

2、聚合查询-- max, min, sum, avg,count

1、分组查询

分组运行把数据分为多个逻辑组，以便能对每个组进行聚集计算。
聚合键中包含NULL时，在结果中会以“不确定”行的形式表现出来
使用聚合函数&GROUP BY子句时需要注意

只能写在SELECT子句中
GROUP BY 子句中不能使用SELECT子句中的列
GROUP BY子句的聚合结果是无序的。
WHERE子句中不能使用聚合函数。

①GROUP BY

-- 每个部门的人数 【执行计划一样】
select dept_no, count(dept_no) from dept_emp group by dept_no ;

select dept_no, count(dept_no) from dept_emp group by dept_no with ROLLUP ;

可以用逗号分隔指定多列。

* Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'userinfo.t_long.user_name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
* 出错原因：mysql和up实现了对功能依赖的检测，如果启用了only_full_group_by：默认的sql-mode里的ONLY_FULL_GROUP_BY字段导致不能直接查询group_by包裹的之外的字段，也就是说select的列都要在group中或者本身是聚合列，也是就说GROUP BY的用法：
	* SELECT row_name1, row_name2 FROM table_name GROUP BY row_name1,row_name2; 顺序要一一对应
	* SELECT AVG(row_name2) FROM table_name GROUP BY row_name1;
	* SELECT AVG(row_name2), row_name1 FROM table_name GROUP BY row_name1;
* 查看SQL是什么模式：
	* SELECT @@sql_mode;
* 解决方法参考： https://www.jianshu.com/p/9e53216db6aa

SELECT vend_id AS vi, COUNT(*) AS num_prods FROM products GROUP BY vi;
不建议在GROUP BY子句中使用SELECT子句中定义的别名的别名，虽然MYSQL、PostgreSQL支持。因为SQL语句在DBMS内部的执行顺序：SELECT子句在GROUP BY 子句之后执行

除了MySQL以外，使用GROUP BY子句时，SELECT子句中不能出现聚合键之外的列名

SELECT子句的执行结果顺序是随机的

②HAVING

HAVING子句可以对分组之后的结果再进行过滤

>#人数大于50000的人
select dept_no, count(dept_no) from dept_emp group by dept_no HAVING count(dept_no) > 50000 ;

只有SELECT子句和HAVING子句以及ORDER BY 子句中能够使用聚合函数

HAVING子句必须要写在GROUP BY子句之后
使用COUNT()函数等对表中数据进行汇总操作时，为其指定条件的不是WHERE子句，而是HAVING子句。where子句用来指定数据行的条件，HAVING子句用来指定分组的条件

WHERE子句和HAVING子句的执行速度

在WHERE子句和HAVING子句都可以使用的条件，最好写在WHERE子句中，因此执行速度更快。以及WHERE子句可以使用索引
使用COUNT(*)等对表中的数据进行聚合操作时，DBMS内部会进行排序处理。排序处理会大大增加机器负担[虽然Oracel等用hash处理来代替排序，但是也会增加负担]。因此，之哟与尽可能减少排序的行数，才能提高处理速度。使用WHERE子句指定条件时，由于排序之前就会数据进行了过滤，因此能够减少排序的数据。但是HAVING子句是先排序然后对数据分组的。

总结

语法：
select 分组函数, 分组后的字段
FROM 表
【where 筛选条件】
group by 分组的字段
【having 分组后的筛选】
【order by 子句】
特点：
1、分组查询中的筛选条件分为两类

数据源              位置           关键字
 		分组前筛选   原始表              group by前面   where
 		分组后筛选   分组后的结果集      group by后面   having
 		
 		分组函数做条件肯定时放在having子句中
 	    能用分组前筛选的就优先考虑分组前筛选

2、group by 子句支持单个字段，多个字段[多个字段之间用逗号隔开没有顺序要求]，表达式或者函数(用的较小)

GROUP BY子句中列出的每一个列都必修是索引列或者有效的表达式，但是不能是聚集函数。如果select中使用表达式，则必须在GROUP BY子句中指定相同的表达式，不能使用别名
除了聚集计算语句外，select语句中每个列必须在GROUP BY子句中给出
如果分组列中具有NULL值，则NULL值将作为一个分组返回。如果列中有多行NULL值，它们将分为一组。
GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。
HAVING可以支持所有的WHERE子句

2、聚合查询-- max, min, sum, avg,count

通常，聚合函数会对NULL之外的对象进行汇总。但是只有COUNT函数例外：使用COUNT()可以查出包含NULL再内的全部数据的行数。也就是聚合函数会将NULL排除在外，但是COUNT()除外，并不会排除NULL。
使用聚合函数对表中的列进行计算合计值或者平均值等汇总操作，常用于进行分析和生成报表。

确定表中函数或者满足某个条件或包含某个特定值的行数。
获取表中行组的和
找出某些列的最大值、最小值和平均值

max, min, sum, avg,count

功能：做统计使用，又称为统计函数
语法：select max(【distinct】字段) from 表名
支持： sum和avg一般用于处理数值型，max、min、count可以处理任何数据类型。以上分组函数都可以支持null

这五个聚集函数都可以使用：

对所有行执行计算，指定ALL，ALL时默认的，可以忽略
只包含不同的值，指定DISTINCT参数[DISTINCT只能放在字段前面]

四则运算中如果存在NULL，结果一定是NULL。聚合函数中列中包含NULL结果不是NULL的原因是在计算之前就已经把NULL的行排除在外了。也就是先选择不为NULL的行，然后再进行计算。
使用SUM()函数是，将NULL除外与等同于0的结果相同，但是AVG函数时这两种情况就完全不同了。
MAX/MIN VS SUM/AVG：SUM/AVG函数只能对数值类型的列使用，而MAX、MIN函数原则上可以适用任何数据类型的列。

max, min, sum, avg 均忽略NULL值

SELECT AVG(item_price) FROM orderitems;
SELECT MAX(item_price), MIN(item_price) FROM orderitems;
SELECT SUM(quantity) AS qun_sum, SUM(item_price*quantity) AS zonghe FROM orderitems WHERE order_num = 20005;
SELECT AVG(DISTINCT item_price) FROM orderitems;