聚合函数
聚合函数(Aggregate Function)是一类SQL函数,它对一组数据(通常是表中的多行)进行计算,并返回单个值作为结果。聚合函数通常用于数据分析和报告,它们可以执行计算,如求和、计算平均值、统计数量、找出最大值或最小值等。
尤其要注意,聚合函数返回的是单个值。例如:
Example表
id | country | state | amount | trans_date |
---|---|---|---|---|
121 | US | approved | 1000 | 2018-12-18 |
122 | US | declined | 2000 | 2018-12-19 |
123 | US | approved | 2000 | 2019-01-01 |
124 | DE | approved | 2000 | 2019-01-07 |
有如下SQL语句:
SELECT COUNT(country)
FROM Example
得到的结果只有4. 这在较为简单的情况下是没问题的,但如果涉及到分组查询,那就会得不到正确的结果,因为聚合函数返回的值永远是单值。如果我们想查询每个国家的交易数目(Transaction):
SELECT COUNT(id)
FROM Example
GROUP BY country
这样得到的才是每个国家的交易订单数量,也就是US3个,DE1个。这点会在联表查询或者较为复杂的查询时给你带来很大麻烦。所以务必牢记住,聚合函数返回的是单值。也因此,往往聚合函数与GROUP BY
同时使用。
(1)SUM
SUM函数用于计算一列数值的总和。
SELECT SUM(column_name) FROM table_name
[WHERE condition];
以上面的表为例,如果想查询每个国家amount的值的和,则可以如下写:
SELECT SUM(amount) as total_amount
FROM Example
GROUP BY country
所得即为每个国家amount的总和。
(2)COUNT
COUNT函数用于计算表中行的数量或特定列中值的数量。
SELECT COUNT(column_name) FROM table_name
[WHERE condition];
例子在前文有过。
(3)AVG
AVG函数用于计算一列数值的平均值。
SELECT AVG(column_name) FROM table_amount
[WHERE condition];
例如想要计算各个国家的amount的平均值:
SELECT AVG(amount) as avg_amount
FROM Example
GROUP BY country
(4)MAX和MIN
用于找到最大值、最小值。
SELECT MAX(column_name) FROM table_name
[WHERE condition];
例如想找每个国家的amount的最大值:
SELECT MAX(amount) as max_amount
FROM Example
GROUP BY country
常用函数
COALESCE
用法:
SELECT COALESCE(value1, value2, ..., value_n);
返回参数列表中的第一个非NULL值。
示例代码:
SELECT COALESCE(NULL, NULL, 'Hello', NULL) AS first_non_null;
这将返回 ‘Hello’,因为它是第一个非NULL的值。
ROUND
用法:
SELECT ROUND(column_name, decimal_places);
将数值四舍五入到指定的小数位数。
示例代码:
SELECT ROUND(123.456, 2) AS rounded_value;
这将返回 123.46,因为123.456四舍五入到两位小数是123.46。
IF (MySQL)
用法:
SELECT IF(condition, value_if_true, value_if_false);
如果条件为真,则返回value_if_true,否则返回value_if_false。
示例代码:
SELECT IF(10 > 5, 'Yes', 'No') AS result;
这将返回 ‘Yes’,因为10 > 5为真。
上述函数都可以和聚合函数一起使用,例如有条件的计算和,就可以使用
SUM(if(amount>1000, amount, 0))
# 计算amount>1000的和
例题
平均售价
SELECT
p.product_id,
IFNULL(ROUND(COALESCE(SUM(p.price * u.units), 0) / IFNULL(SUM(u.units), 0), 2), 0) AS average_price
FROM
Prices p
LEFT JOIN
UnitsSold u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY
p.product_id;
联表查询将两个表组合成一个,用时间约束价格对应的数量,然后就是如何把每列的价格和数量相乘,再将每种产品的值加和除以总数量。
对于剩下的操作,就是使用聚合函数进行计算。IFNULL
可以处理null
值。
项目员工
SELECT project_id, ROUND((SUM(experience_years)/COUNT(e.employee_id)), 2) as average_years
FROM Project p, Employee e
WHERE p.employee_id=e.employee_id
GROUP BY project_id
思路和上一题类似,联表后计算数值,用GROUP BY
来进行分组。一般设计聚合函数都要分组。
更多题目详见:链接
补充
字符串函数
-
CONCAT():
- 用于将多个字符串连接成一个字符串。
- 例如:
CONCAT('Hello, ', 'World!')
将返回'Hello, World!'
。
-
LEFT() / RIGHT():
- LEFT() 函数返回字符串左侧的指定数量的字符。
- RIGHT() 函数返回字符串右侧的指定数量的字符。
- 例如:
LEFT('Hello World', 5)
将返回'Hello'
,RIGHT('Hello World', 5)
将返回'World'
。
-
LENGTH() / CHAR_LENGTH():
- LENGTH() 函数返回字符串的字节长度。
- CHAR_LENGTH() 函数返回字符串的字符数量。
- 例如:
LENGTH('Hello World')
将返回11
,CHAR_LENGTH('Hello World')
将返回11
。
-
UPPER() / LOWER():
- UPPER() 函数将字符串转换为大写。
- LOWER() 函数将字符串转换为小写。
- 例如:
UPPER('hello world')
将返回'HELLO WORLD'
,LOWER('HELLO WORLD')
将返回'hello world'
。
-
REPLACE():
- 用于替换字符串中的字符或子字符串。
- 例如:
REPLACE('Hello World', 'World', 'Everyone')
将返回'Hello Everyone'
。
-
SUBSTRING() / SUBSTR():
- SUBSTRING() 或 SUBSTR() 函数用于从字符串中提取子字符串。
- 例如:
SUBSTRING('Hello World', 7)
将返回'World'
,SUBSTR('Hello World', 7)
也将返回'World'
。
-
REPEAT():
- 用于重复字符串指定的次数。
- 例如:
REPEAT('abc', 3)
将返回'abcabcabc'
。