16
08月
2024
SQL 的聚合函数是用来对一组值执行计算并返回单一值的函数。这些函数通常与 GROUP BY 子句一起使用,以对数据进行分组和汇总。以下是一些常用的 SQL 聚合函数及其使用案例:
1. COUNT()
用途:计算表中的行数或某列中非空值的数量。
语法:
COUNT(*): 计算所有行的数量。
COUNT(column_name): 计算指定列中非空值的数量。
案例:
SELECT COUNT(*) AS total_rows, COUNT(customer_id) AS non_null_customers
FROM customers;
2. SUM()
用途:计算数值列中所有值的总和。
语法:SUM(column_name)
案例:
SELECT SUM(sales) AS total_sales
FROM sales;
3. AVG()
用途:计算数值列的平均值。
语法:AVG(column_name)
案例:
SELECT AVG(price) AS average_price
FROM products;
4. MAX()
用途:找出一列中的最大值。
语法:MAX(column_name)
案例
SELECT MAX(salary) AS highest_salary
FROM employees;
5. MIN()
用途:找出一列中的最小值。
语法:MIN(column_name)
案例:
SELECT MIN(salary) AS lowest_salary
FROM employees;
6. 使用 GROUP BY 进行分组
用途:与聚合函数一起使用,可以对数据进行分组和汇总。
案例:
SELECT department, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;
7. 使用 HAVING 过滤分组
用途:在 GROUP BY 后过滤分组的结果。
案例
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;
8. 开窗函数
用途:在不改变原始数据行的情况下计算聚合值。
案例:
SELECT employee_id, salary,
COUNT(*) OVER (PARTITION BY department) AS dept_employee_count,
AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees;
9. GROUP_CONCAT():将多行数据合并成一行,使用逗号或其他分隔符连接。
案例
SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employee_names
FROM employees
GROUP BY department;
10. STDDEV() 或 STDEV():计算数值列的标准差。
案例
SELECT department, GROUP_CONCAT(employee_name SEPARATOR ', ') AS employee_names
FROM employees
GROUP BY department;
11. VARIANCE():计算数值列的方差。
案例
SELECT VARIANCE(salary) AS salary_variance
FROM employees;
这些聚合函数可以单独使用,也可以组合使用,以满足不同的业务需求。在实际应用中,可以根据具体情况选择合适的聚合函数和组合方式。