2024年4月14日星期日

关于Mysql部分聚合函数和分组的使用杂谈

四月 14, 2024    No comments

Blog1

关于Mysql部分聚合函数和分组的使用杂谈

关键字：Mysql, 子查询, exists, any, group by, count, having

Long time no C

接上文python两种方法实现异步的mysql ~ 绯境之外~Outside of Scarlet (scarletborders.top)，这里再次整理一些常用的关键字以及解决一些谬论。

Note

使用了人工智能生成了部分内容。

 

exists关键字

Reference:

MySQL中EXISTS的用法 - QiaoZhi - 博客园 (cnblogs.com)

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

EXISTS 指定一个子查询，检测 行 的存在。

虽然子查询的select后跟的key如何不影响结果，但是实际使用时的子查询还是写成

select 1 from tb_b where tb_b.somekey = tb_a.somekey

可能会节省一些性能。

any关键字(in, some all)

Reference

mysql之any，in，some，all的区别_mysql all in-CSDN博客

在本部分中将通过一些样例说明这些关键字的使用场景。

in

例如在工作名单中查询家乡属于特定集合的情况下，这里假设staff表有id,name,home，detail表有id,home,gdp，

使用类似select name from staff where home in ('Anhui','Beijing')的sql语句来查询已指定的特定集合。

现在想选择staff表中所有home的gdp大于50的name。使用子查询的情况下select name from staff where home in (select home from detail where gdp > 50)

另外使用join操作效率更高，推荐使用join

SELECT s.name
FROM staff s
JOIN detail d ON s.home = d.home
WHERE d.gdp > 50;

这里引入问题，是否需要使用表名或别名（如 detail.home）来前缀列名（如 home）主要取决于查询的上下文和是否存在歧义。

Important

当查询涉及多个表，并且这些表中有一列或多列具有相同的名称时，你需要使用表名或别名来指明你引用的是哪个表的列。

例如，如果staff和detail表都有一个名为home的列，那么在涉及这两个表的查询中，就必须明确指出是使用staff.home还是detail.home。

但是在select name from staff where home in (select home from detail where gdp > 50)查询中，

  • 子查询SELECT home FROM detail WHERE gdp > 50只涉及detail表，所以这里的home没有歧义，不必非要使用detail.home。
  • 主查询SELECT name FROM staff WHERE home IN (...)只涉及staff表，所以这里的home同样没有歧义，不必非要使用staff.home。

这里特意构造一个存在歧义的情况演示

SELECT s.name, d.home
FROM staff s
JOIN detail d ON s.home = d.home
WHERE d.gdp > 50;

在这个查询中：

  • 必须使用s.home和d.home来消除歧义，因为home列同时存在于staff和detail两个表中。

 

all 和 any

Important

any,all关键字必须与一个比较操作符一起使用。

any关键词可以理解为“对于子查询返回的列中的任一数值，如果比较结果为true，则返回true”。

all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”

Tip

更多例子可以查看 mysql-any，in，some，all - 简书 (jianshu.com)

not in 是 <>all的别名，用法相同。

语句in 与=any是相同的。

语句some是any的别名，用法相同。

group by

Reference: MySQL GROUP BY 语句 | 菜鸟教程 (runoob.com)

这部分人工智能生成的质量不错，把我能想到的aspects都想到了就直接用了

这里说明一些必须使用group by子句的情况

1. 使用聚合函数统计数据

当需要使用聚合函数（如SUM(), AVG(), MAX(), MIN(), COUNT()等）来计算分组数据的统计信息时，必须使用GROUP BY子句。例如：

  • 计算每个部门的平均工资

    SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

  • 统计每个分类的产品数量

    SELECT category, COUNT(*)
FROM products
GROUP BY category;

2. 对分组的结果进行过滤

如果你想对分组后的聚合结果进行过滤，这通常通过在GROUP BY后使用HAVING子句来实现（HAVING可以视为聚合后的WHERE）。例如：

  • 找出平均工资超过50000的部门

    SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

3. 结合多个聚合计算

当你需要在同一个查询中对多个列使用不同的聚合函数时，也需要使用GROUP BY。例如：

  • 计算每个部门的最高工资和最低工资

    SELECT department_id, MAX(salary), MIN(salary)
FROM employees
GROUP BY department_id;

4. 配合窗口函数使用（不总是必需）

虽然窗口函数（如ROW_NUMBER(), RANK()等）本身不一定需要GROUP BY，但如果要在聚合级别上使用窗口函数，可能会结合GROUP BY使用。例如：

  • 分部门计算工资排名

    SELECT department_id, salary, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM employees
GROUP BY department_id, salary;

注意事项

  • 如果在一个包含聚合函数的查询中没有使用GROUP BY，那么整个结果集将被视为一个单一的组。
  • GROUP BY后的每个列必须在SELECT语句的非聚合列中明确出现，除非它们是聚合函数的一部分。

having

reference:

MySQL having子句 - MySQL教程 (yiibai.com)

MySQL HAVING用法 - 简书 (jianshu.com)

Note

写在前面，关于having单独使用的情况

HAVING子句通常与GROUP BY子句一起使用，以根据指定的条件过滤分组。如果省略GROUP BY子句，则HAVING子句的行为与WHERE子句类似。

HAVING可以在不使用GROUP BY的情况下独立使用，但这种情况较为少见，并且通常是在全表聚合上进行过滤。

HAVING子句将过滤条件应用于每组分行，而WHERE子句将过滤条件应用于每个单独的行。

having主要用于在使用GROUP BY子句进行分组后，对这些分组的聚合结果进行过滤。HAVING可以看作是对分组后的数据进行条件查询

WHERE子句在GROUP BY分组前对原始数据进行过滤。

使用HAVING 子句时SELECT 语句的顺序 SELECT → FROM → WHERE → GROUP BY → HAVING

HAVING子句在SQL中的使用是非常具体的，主要用于在使用GROUP BY子句进行分组后，对这些分组的聚合结果进行过滤。HAVING可以看作是对分组后的数据进行条件查询，这与WHERE子句在分组前对原始数据进行过滤是相区分的。

HAVING子句的使用场景

1. 过滤聚合结果

HAVING最常见的用途是过滤使用聚合函数（如SUM(), AVG(), COUNT(), MAX(), MIN()等）后的结果。这是HAVING子句与GROUP BY一起使用的典型场景。

  • 示例

    ：选择平均工资超过50000的部门。

    SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 50000;

    在这个查询中，HAVING子句用于过滤那些平均工资超过50000的部门。

2. 结合聚合函数但不使用GROUP BY

虽然HAVING通常与GROUP BY一起使用，但也可以在不分组的情况下使用HAVING来过滤聚合操作的结果。这种情况较少见，且通常不推荐因为它可能导致混淆。

  • 示例

    ：查询整个公司的总工资是否超过某个值。

    SELECT SUM(salary)
FROM employees
HAVING SUM(salary) > 1000000;

    这里没有使用GROUP BY，但使用了HAVING来确保只有总工资超过1000000时才返回结果。

使用HAVING而不是WHERE

  • 聚合过滤：WHERE不能用于聚合函数的结果，因为WHERE在数据分组和聚合之前执行。如果你需要根据聚合函数的结果来过滤数据，应该使用HAVING。

  • 示例：错误使用WHERE尝试过滤聚合结果。

    -- 错误示例
SELECT department_id, AVG(salary)
FROM employees
WHERE AVG(salary) > 30000  -- 错误！WHERE不能用于聚合函数
GROUP BY department_id;

HAVING和WHERE同时使用

假设有一个名为employees的表，其中包含department_id, employee_name, salary, 和join_date字段。目标是找出在2020年之后加入并且部门平均薪资超过50,000的所有部门。

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE join_date > '2020-01-01'
GROUP BY department_id
HAVING AVG(salary) > 50000;

执行顺序

  • Step 1：执行WHERE子句过滤出符合条件的原始数据（加入日期在2020年之后的员工）。
  • Step 2：对上一步的结果按department_id进行分组。
  • Step 3：在每个分组内计算平均薪资（AVG(salary)）。
  • Step 4：执行HAVING子句，进一步过滤出那些平均薪资超过50,000的部门。

后记

这篇博文是第一次使用typora导出html后直接上传至blogger的attempt。对代码显示的效果良好之后会再次尝试。

0 评论:

发表评论