跳到主要内容

聚合函数

聚合函数包括 COUNT, SUM, AVG, MIN, MAX 以及 HAVING 指令，用于对数据集进行统计分析。

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

题目描述​

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

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

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

解题思路​

基础逻辑过滤。

  1. id % 2 = 1：判断 ID 是否为奇数（也可以使用 MOD(id, 2) = 1）。
  2. description <> 'boring'：过滤掉描述为 boring 的电影。
  3. ORDER BY rating DESC：按评分降序。

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. 将 Prices 与 UnitsSold 左连接。
  2. 连接条件：product_id 相等且 purchase_date 处于 start_date 和 end_date 之间。
  3. 统计：按 product_id 分组，计算 SUM(price * units) / SUM(units)。
  4. 处理 0 值：使用 IFNULL(..., 0) 处理没卖出去的产品。

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;

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

题目描述​

你有两张表：

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

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

解题思路​

关联聚合。

  1. 连接两表获取员工的经验年限。
  2. 按 project_id 分组并使用 AVG 函数。

SQL 实现 (MySQL)​

SELECT project_id, ROUND(AVG(experience_years), 2) AS average_years 
FROM Project JOIN Employee USING(empId) 
GROUP BY project_id;

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

题目描述​

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

解题思路​

聚合与标量子查询。

  1. 注册数由 COUNT(user_id) 统计。
  2. 总用户数通过独立子查询 (SELECT COUNT(*) FROM Users) 获得。
  3. 注意在排序中使用聚合结果。

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 的查询所占的百分比。 结果保留两位小数。

解题思路​

精细聚合计算。

  1. Quality：直接对每条记录的 rating/position 求 AVG。
  2. Poor Query %：使用条件判断 rating < 3。在 MySQL 中满足条件为 1，不满足为 0。对这个布尔值求平均值并乘以 100 即可得到比例。
  3. 务必过滤掉 query_name 为名的记录（若存在）。

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;

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

题目描述​

Transactions 表包含银行交易记录：

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

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

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

解题思路​

日期提取与条件汇总。

  1. 时间提取：使用 DATE_FORMAT(trans_date, '%Y-%m') 将日期转化为月份字符串。
  2. 状态统计：使用 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. 锁定首单：使用子查询（或 IN 逻辑）找出每个 customer_id 对应的 MIN(order_date)。
  2. 比例计算：在首单集合中，计算满足 order_date = customer_pref_delivery_date 的行数占比。

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
);

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

题目描述​

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

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

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

解题思路​

时间偏移量匹配。

  1. 标记首日：找出每个玩家的 MIN(event_date)。
  2. 关联次日：连接原表，寻找日期恰好相差 1 天的记录。
  3. 比例统计：满足连表条件的去重玩家数 除以 全体去重玩家数。

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 FROM Activity GROUP BY player_id
) b ON a.player_id = b.player_id AND DATEDIFF(a.event_date, b.first) = 1;