group by 分组函数详解

如何分组数据，以便能汇总表内容的子集。
这涉及两个新SELECT语句子句，分别是GROUP BY子句和HAVING子句。

1.分组数据

如何分组数据，示例：

1. 查找指定供应商的产品数量 用where vend_id=
   
   那么返回每个供应商提供的产品数目怎么做？
   或者返回只提供单项产品的供应商所提供的产品，
   或返回提供10个以上产品的供应商怎么做？

这时就进行分组。分组允许把数据分为多个逻辑组，以便能对每个组进行聚集计算。

2. 每个供应商提供的产品数目怎么办？
   这里指定了两个列，vend_id包含产品供应商ID，num_prods为count(*)函数建立的计算字段group by按vend_id排序并分组数组，这是对每个vend_id而不是整个表计算num_prods一次。系统会自动计算和估值每个组，group by指示MySQL分组数据，然后对每个组而不是结果集进行聚集。

注意：

1. group by子句必须出现在where子句之后，group by子句之前。
2. 如果分组列中具有null值，则null将作为一个分组返回。如果列中有多行null值，它们将分为一组。
3. group by子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制。
4. 如果在group by子句中嵌套了分组，数据将在最后规定的分组上进行汇总。换句话说，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）。
5. group by子句中列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在select中使用表达式，则必须在group by子句中指定相同的表达式。不能使用别名。
6. 除聚集计算语句外，select语句中的每个列都必须在group by子句中给出。

2.过滤分组

注意：

1. 除了能用group by分组数据外，MySQL还允许过滤分组，规定包括哪些分组，排除哪些分组。
2. 例如，列出至少有2个订单的所有顾客、返回只提供单项产品的供应商所提供的产品、返回提供10个以上产品的供应商、查询至少有5个学生的所有班级。为得出这种数据，必须基于完整的分组而不是个别的行进行过滤。
3. where过滤指定的是行而不是分组，where没有分组的概念。且where在数据分组前进行过滤，having在数据分组后进行过滤。这使得where排除的行不包括在分组中。这可能会改变计算值，从而影响having子句中基于这些值过滤掉的分组。
4. having支持所有where操作符。唯一的差别是where过滤行，而having过滤分组。

示例：

1. 过滤分组：过滤 count(*) >=2(两个以上的订单) 的那些分组。过滤是基于分组聚集值而不是特定行值的。
   
2. where和having同时过滤：进一步过滤上面的语句，使它返回过去12个月内具有2个以上订单的顾客。可增加一条where子句，过滤出过去12个月内下过的订单。然后再增加having子句过滤出具有两个以上订单的分组。
   列出具有2个及以上、价格为含10以上的产品的供应商：
   
   where子句过滤所有prod_price至少为10的行。然后按vend_id分组数据，having子句过滤计数为2或2以上的分组。

3.

4.分组练习题

1. 2356.计算每一位老师在大学里教授的不同科目的数量。[分组数据]

在 SQL 中，(subject_id, dept_id) 是该表的主键。
每一行都表示带有 teacher_id 的教师在系 dept_id 中教授科目subject_id。
得到：

根据教师表的教师id分组，统计每位教师教学的科目数量
	select teacher_id,count(distinct subject_id) as cnt
	from Teacher 
	group by teacher_id

2. 596.超过5名学生的课[过滤分组]

查询 至少有5个学生 的所有班级。
以 任意顺序 返回结果表。

1.使用子查询 [每个分区表都要有它自己的名字]
根据课程表中的课程分组，统计每个课程学生的数量，过滤找出数量大于5的课程
	select class 
	from(
	    select count(student)as num,class 
	    from Courses
	    group by class
	) stu
	where num >5
2.用having过滤分组
	select class 
	from Courses
	group by class
	having count(student)>5

仅分组数据不过滤结果值:

3. 只出现一次的最大数字

单一数字 是在 MyNumbers 表中只出现一次的数字。
找出最大的 单一数字 。如果不存在 单一数字 ，则返回 null 。

 以数字进行分组，统计每个数字出现的次数
 1.利用order by和limit函数，having筛选出出现次数=1的，再进行倒序排序，只取第一位，也就是最大的
 在这里，有可能没有一个数字出现次数=1，也就是子查询值为空，外面直接嵌套在select后，查找空值返回null；
 如果子查询放在from后，也就是select expr from 空，那么，当数据库中无法匹配当前条件时，输出的数据行是为空而非null的输出仍为空。
    select(
        select num 
        from MyNumbers 
        group by num
        having count(num)=1
        order by num desc
        limit 1
    )num
2.having筛选出出现次数=1的，再在外面用max函数找最大的，利用聚合函数转换空值为null，max(空)=null。
select max(num) as num
	from(
	    select num 
	    from MyNumbers 
	    group by num
	    having count(num)=1
	)t
本来想把max函数写里面不嵌套，这样写是错的，count(num)=1是个布尔表达式，判断为0、1
且报错 Invalid use of group function，非法使用group分组函数
    select  max(count(num)=1) 
    from MyNumbers 
    group by num
    having count(num)=1

题解https://leetcode.cn/problems/biggest-single-number/solutions/1/dang-biao-ge-wei-kong-shi-ru-he-fan-hui-6qpzg/?envType=study-plan-v2&envId=sql-free-50

4. 1045 买下所有产品的客户 [过滤分组]

报告 Customer 表中购买了 Product 表中所有产品的客户的 id。返回结果表 无顺序要求

根据顾客id分组，统计每个顾客消费了的不同的产品数量
统计商品表总的产品数量
当 某个顾客消费了的 不同的 产品数量 = 商品总数，该顾客购买了所有产品

	select customer_id
	from Customer
	group by customer_id
	having count(distinct(product_key)) = (select count(product_key) from Product )

5. 1141.查询近30天活跃用户数

统计截至 2019-07-27(包含2019-07-27)，近30天的每日活跃用户数 (当天只要有一条活动记录，即为活跃用户)。以 任意顺序 返回结果表。

按日期分组，统计每个日期的不同用户；再用时间函数过滤分组的日期
	select activity_date  as day, count(distinct user_id) as active_users 
	from Activity 
	group by activity_date 
	-- having activity_date between '2019-06-28' and '2019-07-27'  #460 ms
	-- having datediff("2019-7-27",activity_date) between 0 and 29   #472ms
	-- having activity_date between date_add("2019-7-27",interval -29 day) and '2019-07-27'   #629ms
	-- having DATEDIFF("2019-7-27",activity_date)<30 and DATEDIFF("2019-7-27",activity_date)>=0 #459ms
	
	having 0<= DATEDIFF("2019-7-27",activity_date)<=30错误写法，拆分成and

6. 1084.销售分析
   找到2019年春季 才售出的产品。即仅 在 2019-01-01(含) 至 2019-03-31(含)之间出售的商品。

以商品进行分组，找出每个商品销售的最大日期和最小日期，保证都在要求区间的商品
	select product_id ,product_name  
	from Sales natural join Product
	group by product_id  
	having  max(sale_date)<= '2019-03-31'and min(sale_date)>='2019-01-01'

自然连接

	select product_id ,product_name  
	from Sales natural join Product

等值连接

	select *
	from Sales ,Product
	where Sales.product_id = Product.product_id

内连接

	select *
	from Sales inner join Product 
	on Sales.product_id = Product.product_id