【SQL基础】常用聚合函数、函数以及题目

聚合函数

聚合函数（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来进行分组。一般设计聚合函数都要分组。

更多题目详见：链接

补充

字符串函数

1. CONCAT():

   • 用于将多个字符串连接成一个字符串。
   • 例如：CONCAT('Hello, ', 'World!') 将返回 'Hello, World!'。

2. LEFT() / RIGHT():

   • LEFT() 函数返回字符串左侧的指定数量的字符。
   • RIGHT() 函数返回字符串右侧的指定数量的字符。
   • 例如：LEFT('Hello World', 5) 将返回 'Hello'，RIGHT('Hello World', 5) 将返回 'World'。

3. LENGTH() / CHAR_LENGTH():

   • LENGTH() 函数返回字符串的字节长度。
   • CHAR_LENGTH() 函数返回字符串的字符数量。
   • 例如：LENGTH('Hello World') 将返回 11，CHAR_LENGTH('Hello World') 将返回 11。

4. UPPER() / LOWER():

   • UPPER() 函数将字符串转换为大写。
   • LOWER() 函数将字符串转换为小写。
   • 例如：UPPER('hello world') 将返回 'HELLO WORLD'，LOWER('HELLO WORLD') 将返回 'hello world'。

5. REPLACE():

   • 用于替换字符串中的字符或子字符串。
   • 例如：REPLACE('Hello World', 'World', 'Everyone') 将返回 'Hello Everyone'。

6. SUBSTRING() / SUBSTR():

   • SUBSTRING() 或 SUBSTR() 函数用于从字符串中提取子字符串。
   • 例如：SUBSTRING('Hello World', 7) 将返回 'World'，SUBSTR('Hello World', 7) 也将返回 'World'。

7. REPEAT():

   • 用于重复字符串指定的次数。
   • 例如：REPEAT('abc', 3) 将返回 'abcabcabc'。