SQL回炉重造09_数据过滤_数据分组GROUP BY

数据过滤——分组数据

1. 数据分组

• GROUP BY子句：实现将数据分为多个逻辑分组，对每个分组进行聚合计算

  • GROUP BY子句可以包含任意数目的列，这使得其可以对分组进行嵌套，为数据分组提供更细致的控制；
  • 如果在GROUP BY子句中嵌套了分组，数据将会在最后规定的分组上进行汇总，也就是说，建立分组时指定的所有列都会在一起计算（不可以从个别列取回数据）
  • GROUP BY 子句列出的每个列都必须是检索列或是有效的表达式（但不可以是聚合函数），如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不可以使用别名
  • 出聚合计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出
  • 如果分组列有NULL值，则将NULL作为一个分组返回，如果有多个NULL值，则全部分为一组
  • 必须出现在WHERE之后，ORDER BY之前

• WITH ROLLUP关键字：可以得到每个分组以及分组汇总级别的值

SELECT vend_id, COUNT(*) AS prod_num
FROM products
GROUP BY vend_id;
+---------+----------+
| vend_id | prod_num |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
+---------+----------+

SELECT vend_id, COUNT(*) AS prod_num
FROM products
GROUP BY vend_id WITH ROLLUP;
+---------+----------+
| vend_id | prod_num |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
|    NULL |       14 |
+---------+----------+

2. 过滤分组

• HAVING子句：与WHERE子句基本一致，但HAVING子句用于过滤分组，而WHERE子句用于过滤行。之前提到的所有与WHERE相关的技术和选项都适用于HAVING。
• HAVING & WHERE：可以理解为，WHERE在数据分组之前进行过滤，HAVING在分组之后过滤，也就是说被WHERE过滤掉的行不会出现在分组之中

SELECT vend_id, COUNT(*) AS prod_num
FROM products
GROUP BY vend_id
HAVING COUNT(*) >= 2;
+---------+----------+
| vend_id | prod_num |
+---------+----------+
|    1001 |        3 |
|    1002 |        2 |
|    1003 |        7 |
|    1005 |        2 |
+---------+----------+

SELECT vend_id, COUNT(*) AS prod_num
FROM products
WHERE prod_price >= 10
GROUP BY vend_id
HAVING COUNT(*) >= 2;
+---------+----------+
| vend_id | prod_num |
+---------+----------+
|    1003 |        4 |
|    1005 |        2 |
+---------+----------+

3. 分组和排序

ORDER BY	GROUP BY
排序产生的输出	分组行，但输出可能不是分组的顺序
任意列都可以使用（甚至非选择的列也可以使用）	只可能使用选择列或表达式列，而且必须使用每个选择列表达式
不一定需要	如果与聚集函数一起使用列（或表达式），则必须使用

注意：一般在使用GROUP BY时，也应该给出ORDER BY 来指定排序顺序，这是保证数据正确排序的唯一方法。不能依赖鱼GROUP BY 排序数据

SELECT order_num, SUM(quantity*item_price) AS total_order
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50;
+-----------+-------------+
| order_num | total_order |
+-----------+-------------+
|     20005 |      149.87 |
|     20006 |       55.00 |
|     20007 |     1000.00 |
|     20008 |      125.00 |
+-----------+-------------+

SELECT order_num, SUM(quantity*item_price) AS total_order
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY total_order;
+-----------+-------------+
| order_num | total_order |
+-----------+-------------+
|     20006 |       55.00 |
|     20008 |      125.00 |
|     20005 |      149.87 |
|     20007 |     1000.00 |
+-----------+-------------+

4. SELECT 子句顺序

子句	说明	是否必须使用
SELECT	要返回的列或表达式	是
FROM	从中检索数据的表	要从表中选择数据时
WHERE	行级过滤	否
GROUP BY	分组说明	按组计算聚集时
HAVING	组级过滤	否
ORDER BY	输出排序顺序	否
LIMIT	要检索的行数	否