聚合函数

聚合函数是数据分析的核心工具。它们将多行数据"聚合"成一个单一的汇总值，如求和、计数、平均值、最大值、最小值等。掌握聚合函数的使用，是从"查询数据"到"分析数据"的关键一步。

核心概念

什么是聚合函数？

聚合函数对一组值执行计算，返回单个值。与普通函数不同，聚合函数会"压缩"多行数据。

常用聚合函数

函数	说明	示例
COUNT(*)	统计所有行数	SELECT COUNT(*) FROM users
COUNT(column)	统计非 NULL 值数量	SELECT COUNT(bonus) FROM employees
COUNT(DISTINCT column)	统计不同值数量	SELECT COUNT(DISTINCT department) FROM employees
SUM(column)	求和	SELECT SUM(amount) FROM orders
AVG(column)	求平均值	SELECT AVG(salary) FROM employees
MAX(column)	最大值	SELECT MAX(price) FROM products
MIN(column)	最小值	SELECT MIN(created_at) FROM logs

聚合函数与 NULL

NULL 值的处理是聚合函数的重要细节：

函数	NULL 处理方式
COUNT(*)	统计所有行，包括 NULL
COUNT(column)	忽略 NULL 值
SUM(column)	忽略 NULL 值
AVG(column)	忽略 NULL 值（只计算非 NULL 的平均值）
MAX/MIN(column)	忽略 NULL 值

示例：

-- 假设 bonus 列有值 [100, NULL, 200, NULL, 300]
SELECT COUNT(*) as total,      -- 5
       COUNT(bonus) as cnt,    -- 3（忽略 NULL）
       AVG(bonus) as avg,      -- 200（(100+200+300)/3，不是 5）
       SUM(bonus) as sum       -- 600
FROM employees;

条件聚合

条件聚合是高级技巧，可以在聚合时根据条件分别计算：

-- 统计不同状态的数量
SELECT 
    COUNT(*) AS total,
    SUM(CASE WHEN status = 'approved' THEN 1 ELSE 0 END) AS approved,
    SUM(CASE WHEN status = 'rejected' THEN 1 ELSE 0 END) AS rejected
FROM orders;

-- MySQL 简写：布尔表达式直接转为 1 或 0
SELECT 
    COUNT(*) AS total,
    SUM(status = 'approved') AS approved,  -- MySQL 特有
    SUM(status = 'rejected') AS rejected
FROM orders;

---

620. 有趣的电影 (Not Boring Movies)

题目描述

cinema 表包含电影的 ID、名称、描述和评分：

• id: 电影 ID，主键。
• movie: 电影名称。
• description: 电影描述。
• rating: 评分（浮点数）。

请编写一个解决方案，找出所有 ID 为奇数 且 描述不是 "boring" 的电影。结果需按 rating 降序排列。

解题思路

这道题考查 多条件过滤 + 排序，虽然放在聚合函数章节，但实际是基础查询题。

判断奇偶数的方法：

-- 方法一：取模运算
WHERE id % 2 = 1    -- 奇数
WHERE id % 2 = 0    -- 偶数

-- 方法二：MOD 函数
WHERE MOD(id, 2) = 1

-- 方法三：位运算
WHERE id & 1 = 1    -- 奇数（二进制末位为 1）

不等于的写法：

WHERE description <> 'boring'  -- 标准写法
WHERE description != 'boring'  -- 也可接受

SQL 实现 (MySQL)

SELECT * 
FROM cinema
WHERE id % 2 = 1 
  AND description <> 'boring'
ORDER BY rating DESC;

---

1251. 平均售价 (Average Selling Price)

题目描述

你有两张表：

• Prices: 记录产品在不同时间段的售价。包含 product_id, start_date, end_date, price。
• UnitsSold: 记录产品的销售情况。包含 product_id, purchase_date, units。

请编写一个解决方案，计算每种产品的 平均售价。 平均售价 = 总销售额 / 总销售数量。结果保留两位小数。如果某产品没有销售记录，其平均售价应视为 0。

解题思路

这道题考查 LEFT JOIN + 范围连接 + 条件聚合。

关键点：

1. 范围连接：销售日期需要在价格的有效期内
2. 平均售价计算：总销售额 / 总销量
3. 处理无销售的产品：使用 IFNULL

销售额计算：price * units

范围连接条件：

ON p.product_id = s.product_id 
AND s.purchase_date BETWEEN p.start_date AND p.end_date

SQL 实现 (MySQL)

SELECT p.product_id, 
       ROUND(IFNULL(SUM(price * units) / SUM(units), 0), 2) AS average_price 
FROM Prices p 
LEFT JOIN UnitsSold s 
  ON p.product_id = s.product_id 
  AND s.purchase_date BETWEEN p.start_date AND p.end_date 
GROUP BY p.product_id;

代码解析：

• LEFT JOIN：保证没有销售的产品也在结果中
• BETWEEN：确保销售日期在价格有效期内
• SUM(price * units)：总销售额
• SUM(units)：总销量
• IFNULL(..., 0)：无销售时返回 0

---

1075. 项目员工 I (Project Employees I)

题目描述

你有两张表：

• Project: 包含 project_id 和 employee_id。记录了每个项目分配的员工。
• Employee: 包含 employee_id, name, experience_years。记录员工的工作经验年限。

请编写一个解决方案，查询 每个项目 中所有员工的 平均工作年限，并将结果保留两位小数。

解题思路

基础的 JOIN + 分组聚合。

SQL 实现 (MySQL)

SELECT project_id, 
       ROUND(AVG(experience_years), 2) AS average_years 
FROM Project p
JOIN Employee e ON p.employee_id = e.employee_id
GROUP BY project_id;

---

1633. 各赛事的用户注册率 (Percentage of Users Registered in a Tournament)

题目描述

Users 表包含用户信息，Register 记录了用户注册参加不同赛事的情况。 请编写一个解决方案，查询 每场赛事 的 注册百分比。 注册百分比 = (注册该赛事的总人数 / 总用户数) * 100。结果保留两位小数，并按百分比降序、赛事 ID 升序排列。

解题思路

这道题考查 聚合与标量子查询结合。

核心公式：

$$\text{注册百分比} = \frac{\text{赛事注册人数}}{\text{总用户数}} \times 100$$

标量子查询获取总用户数：

SELECT COUNT(*) FROM Users

这个子查询返回单个值，可以直接在表达式中使用。

SQL 实现 (MySQL)

SELECT contest_id, 
       ROUND(COUNT(user_id) * 100 / (SELECT COUNT(*) FROM Users), 2) AS percentage 
FROM Register 
GROUP BY contest_id 
ORDER BY percentage DESC, contest_id ASC;

---

1211. 查询结果的质量和占比 (Queries Quality and Percentage)

题目描述

Queries 表记录了查询结果的评分信息：

• query_name: 查询名称。
• result: 结果。
• rating: 评分 (1-5)。
• position: 在结果中的位置。

请编写一个解决方案，查询每种 query_name 的：

1. 质量 (Quality)：rating / position 的平均值。
2. 劣质查询占比 (Poor Query Percentage)：评分 小于 3 的查询所占的百分比。 结果保留两位小数。

解题思路

这道题考查 复杂聚合表达式 + 条件比例计算。

质量计算：

AVG(rating / position)

劣质查询占比计算：

劣质查询 = rating < 3 的查询 占比 = 劣质查询数 / 总查询数 * 100

在 MySQL 中，可以用布尔表达式来简化：

AVG(rating < 3) * 100  -- rating < 3 为 TRUE 时返回 1，FALSE 返回 0

SQL 实现 (MySQL)

SELECT query_name, 
       ROUND(AVG(rating / position), 2) AS quality, 
       ROUND(AVG(rating < 3) * 100, 2) AS poor_query_percentage 
FROM Queries 
WHERE query_name IS NOT NULL 
GROUP BY query_name;

代码解析：

• AVG(rating / position)：每个查询的质量平均值
• AVG(rating < 3)：布尔值转为数值后的平均值，即比例
• * 100：转为百分比
• WHERE query_name IS NOT NULL：排除无名称的记录

---

1193. 每月交易 I (Monthly Transactions I)

题目描述

Transactions 表包含银行交易记录：

• id: 交易 ID。
• country: 国家。
• state: 状态 ('approved', 'declined')。
• amount: 金额。
• trans_date: 日期。

编写解决方案，按 月份 和 国家 统计：

• 交易总数。
• 已批准的交易总数。
• 交易总金额。
• 已批准的交易总金额。

解题思路

这道题考查 日期提取 + 多维度分组 + 条件聚合。

日期格式化：

DATE_FORMAT(trans_date, '%Y-%m')  -- 将日期转为月份格式，如 '2024-01'

条件聚合：

• 已批准交易数：SUM(state = 'approved')
• 已批准交易金额：SUM(IF(state = 'approved', amount, 0))

SQL 实现 (MySQL)

SELECT 
    DATE_FORMAT(trans_date, '%Y-%m') AS month, 
    country, 
    COUNT(*) AS trans_count, 
    SUM(state = 'approved') AS approved_count, 
    SUM(amount) AS trans_total_amount, 
    SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount 
FROM Transactions 
GROUP BY month, country;

---

1174. 即时食物配送 II (Immediate Food Delivery II)

题目描述

Delivery 表记录了食物配送订单：

• customer_id: 顾客 ID。
• order_date: 下单日期。
• customer_pref_delivery_date: 顾客期望的配送日期。

如果订单的期望配送日期等于下单日期，则该订单称为 即时订单 (Immediate)，否则称为 计划订单 (Scheduled)。 "首次订单"是某位顾客最早下单的日期。 请编写一个解决方案，查询所有用户的 首次订单 中，即时订单所占的百分比。结果保留两位小数。

解题思路

这道题考查 子查询 + 条件聚合。

分析过程：

1. 找出每个用户的首单（最早的 order_date）
2. 在首单中，统计即时订单的比例

找首单的方法：

SELECT customer_id, MIN(order_date) 
FROM Delivery 
GROUP BY customer_id

行子查询：

WHERE (customer_id, order_date) IN (子查询)

这种写法可以同时匹配多个列。

SQL 实现 (MySQL)

SELECT ROUND(AVG(order_date = customer_pref_delivery_date) * 100, 2) AS immediate_percentage 
FROM Delivery 
WHERE (customer_id, order_date) IN (
    SELECT customer_id, MIN(order_date) 
    FROM Delivery 
    GROUP BY customer_id
);

代码解析：

• 子查询找出每个用户的首单（customer_id + order_date）
• WHERE (customer_id, order_date) IN (...) 筛选首单
• order_date = customer_pref_delivery_date 判断是否即时订单
• AVG(...) 计算比例

---

550. 游戏玩法分析 IV (Game Play Analysis IV)

题目描述

Activity 表包含玩家的登录统计记录：

• player_id: 玩家 ID。
• device_id: 设备 ID。
• event_date: 登录日期。
• games_played: 玩游戏数。

请编写一个解决方案，查询 在首次登录的第二天再次登录 的玩家比例。结果保留两位小数。

解题思路

这道题考查 日期计算 + 连接查询。

分析过程：

1. 找出每个玩家的首次登录日期
2. 判断玩家是否在首次登录后的第二天也登录了
3. 计算比例

日期差计算：

DATEDIFF(date1, date2) = 1  -- date1 比 date2 晚 1 天

SQL 实现 (MySQL)

SELECT ROUND(COUNT(DISTINCT a.player_id) / (SELECT COUNT(DISTINCT player_id) FROM Activity), 2) AS fraction 
FROM Activity a 
JOIN (
    SELECT player_id, MIN(event_date) AS first_date 
    FROM Activity 
    GROUP BY player_id
) b ON a.player_id = b.player_id 
   AND DATEDIFF(a.event_date, b.first_date) = 1;

代码解析：

• 子查询 b：找出每个玩家的首次登录日期
• JOIN：连接原表，找首次登录后第二天有登录记录的玩家
• DATEDIFF(a.event_date, b.first_date) = 1：日期相差 1 天
• COUNT(DISTINCT a.player_id)：满足条件的玩家数
• (SELECT COUNT(DISTINCT player_id) FROM Activity)：总玩家数

---

知识点总结

聚合函数要点

1. COUNT(*) vs COUNT(column)：前者统计所有行，后者忽略 NULL
2. AVG 忽略 NULL：只计算非 NULL 值的平均
3. 条件聚合：SUM(CASE WHEN ... END) 或 SUM(条件表达式)

常用模式

-- 1. 基础统计
SELECT COUNT(*), SUM(amount), AVG(price), MAX(score), MIN(score)
FROM table_name;

-- 2. 分组统计
SELECT category, COUNT(*), SUM(amount)
FROM table_name
GROUP BY category;

-- 3. 条件计数
SELECT 
    COUNT(*) AS total,
    SUM(status = 'approved') AS approved,
    SUM(status = 'rejected') AS rejected
FROM orders;

-- 4. 条件求和
SELECT 
    SUM(amount) AS total,
    SUM(IF(status = 'approved', amount, 0)) AS approved_amount
FROM orders;

-- 5. 比例计算
SELECT ROUND(AVG(condition) * 100, 2) AS percentage
FROM table_name;

注意事项

1. 聚合函数会忽略 NULL 值（除了 COUNT(*)）
2. 使用 IFNULL 或 COALESCE 处理可能为 NULL 的聚合结果
3. ROUND 函数保留小数位数
4. 日期提取用 DATE_FORMAT 或 YEAR/MONTH 函数