高频 SQL 50 题 之 聚合函数篇 620. 有趣的电影 1075. 项目员工 I 1633. 各赛事的用户注册率 1211. 查询结果的质量和占比 1193 1174 550

620. 有趣的电影1251. 平均售价​​​​​​1075. 项目员工 I1633. 各赛事的用户注册率1211. 查询结果的质量和占比1193. 每月交易 I1174. 即时食物配送 II550. 游戏玩法分析 IV620. 有趣的电影SELECT id, movie, description, rating FROM cinema WHERE description ! boring AND id % 2 1 ORDER BY rating DESC;SELECT id, movie, description, rating按题目要求返回所有 4 个字段完整展示符合条件的电影信息。FROM cinema指定从cinema表中查询数据。WHERE description ! boring AND id % 2 1description ! boring筛选出描述不是 boring 的电影id % 2 1判断id为奇数取模运算余数为 1 即奇数AND逻辑运算符确保两个条件同时满足ORDER BY rating DESC按rating字段降序排列符合题目排序要求。select * from cinema where mod(id, 2) 1 and description ! boring order by rating DESC ;1251. 平均售价SELECT p.product_id, IFNULL(ROUND(SUM(u.units * p.price) / SUM(u.units), 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;关联两张表通过product_id和日期范围purchase_date介于start_date和end_date之间关联Prices和UnitsSold表匹配销售记录对应的价格。计算加权平均平均售价 总销售额price * units求和 / 总销量units求和。处理无销售产品若产品无任何售出记录平均售价设为 0。四舍五入结果保留小数点后两位。SELECT p.product_id按产品 ID 分组返回每个产品的 ID。IFNULL(ROUND(SUM(u.units * p.price) / SUM(u.units), 2), 0)SUM(u.units * p.price)计算该产品的总销售额单价 × 销量 求和。SUM(u.units)计算该产品的总销量。两者相除得到加权平均售价。ROUND(..., 2)将结果四舍五入到小数点后两位。IFNULL(..., 0)处理无销售的产品总销量为 0 时除法结果为NULL替换为 0。FROM Prices p LEFT JOIN UnitsSold u以Prices表为左表确保所有产品都出现在结果中即使无销售记录。LEFT JOIN是关键避免遗漏无销售的产品。ON p.product_id u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date关联条件 1产品 ID 匹配。关联条件 2销售日期在该产品的价格有效期内确保匹配正确的单价。GROUP BY p.product_id按产品 ID 分组计算每个产品的平均售价。SELECT product_id, IFNULL(Round(SUM(sales) / SUM(units), 2), 0) AS average_price FROM ( SELECT Prices.product_id AS product_id, Prices.price * UnitsSold.units AS sales, UnitsSold.units AS units FROM Prices LEFT JOIN UnitsSold ON Prices.product_id UnitsSold.product_id AND (UnitsSold.purchase_date BETWEEN Prices.start_date AND Prices.end_date) ) T GROUP BY product_id 作者力扣官方题解 链接https://leetcode.cn/problems/average-selling-price/solutions/305361/ping-jun-shou-jie-by-leetcode-solution/ 来源力扣LeetCode 著作权归作者所有。商业转载请联系作者获得授权非商业转载请注明出处。1075. 项目员工 ISELECT p.project_id, ROUND(AVG(e.experience_years), 2) AS average_years FROM Project p JOIN Employee e ON p.employee_id e.employee_id GROUP BY p.project_id;关联两张表通过employee_id关联Project表和Employee表获取每个项目下员工的工作年限。分组计算平均值按project_id分组计算每组experience_years的平均值。保留两位小数将结果四舍五入到小数点后两位满足格式要求。SELECT p.project_id按项目 ID 分组返回每个项目的 ID。ROUND(AVG(e.experience_years), 2) AS average_yearsAVG(e.experience_years)计算该项目下所有员工的平均工作年限。ROUND(..., 2)将平均值四舍五入到小数点后两位符合题目精度要求。AS average_years为计算结果指定列名匹配输出格式。FROM Project p JOIN Employee e ON p.employee_id e.employee_id内连接两张表关联项目与员工信息确保只统计有对应员工的项目。GROUP BY p.project_id按项目 ID 分组为每个项目单独计算平均值。1633. 各赛事的用户注册率SELECT r.contest_id, ROUND(COUNT(DISTINCT r.user_id) * 100.0 / (SELECT COUNT(*) FROM Users), 2) AS percentage FROM Register r GROUP BY r.contest_id ORDER BY percentage DESC, r.contest_id ASC;计算总用户数从Users表获取总用户数量本题示例中为 3。统计各赛事注册人数从Register表按contest_id分组统计每个赛事的注册用户数。计算注册率注册率 (赛事注册人数 / 总用户数) × 100%结果保留两位小数。排序规则按percentage降序排列若百分比相同则按contest_id升序排列。SELECT r.contest_id按赛事 ID 分组返回每个赛事的 ID。ROUND(COUNT(DISTINCT r.user_id) * 100.0 / (SELECT COUNT(*) FROM Users), 2) AS percentageCOUNT(DISTINCT r.user_id)统计该赛事的注册用户数DISTINCT避免重复计数符合主键唯一性要求。(SELECT COUNT(*) FROM Users)子查询获取总用户数。100.0将比例转为百分比同时确保浮点数运算避免整数除法截断。ROUND(..., 2)将结果四舍五入到小数点后两位满足精度要求。AS percentage为计算结果指定列名匹配输出格式。FROM Register r GROUP BY r.contest_id按赛事 ID 分组为每个赛事单独计算注册率。ORDER BY percentage DESC, r.contest_id ASC先按注册率降序排列注册率高的赛事在前。若注册率相同按赛事 ID 升序排列符合题目排序要求。1211. 查询结果的质量和占比SELECT query_name, ROUND(AVG(rating / position), 2) AS quality, ROUND(100.0 * SUM(CASE WHEN rating 3 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_query_percentage FROM Queries WHERE query_name IS NOT NULL GROUP BY query_name;质量 (quality)该查询下所有结果的rating / position比率的平均值四舍五入到小数点后两位。劣质查询百分比 (poor_query_percentage)该查询下rating 3的结果数占总结果数的百分比四舍五入到小数点后两位。按query_name分组计算最终返回每个查询的对应指标。SELECT query_name按查询名称分组返回每个查询的名称。ROUND(AVG(rating / position), 2) AS qualityrating / position计算每条结果的评分与位置的比率。AVG(...)对该查询下所有结果的比率求平均值得到质量。ROUND(..., 2)四舍五入到小数点后两位符合精度要求。ROUND(100.0 * SUM(CASE WHEN rating 3 THEN 1 ELSE 0 END) / COUNT(*), 2) AS poor_query_percentageCASE WHEN rating 3 THEN 1 ELSE 0 END将评分小于 3 的结果标记为 1否则为 0。SUM(...)统计该查询下劣质结果的总数量。COUNT(*)统计该查询下的总结果数。100.0 * ... / ...将比例转换为百分比100.0确保浮点数运算避免整数除法截断。ROUND(..., 2)四舍五入到小数点后两位符合精度要求。FROM Queries WHERE query_name IS NOT NULL过滤掉查询名称为空的无效数据可选避免分组异常。GROUP BY query_name按查询名称分组为每个查询单独计算两个指标。1193. 每月交易 ISELECT DATE_FORMAT(trans_date, %Y-%m) AS month, country, COUNT(id) AS trans_count, SUM(CASE WHEN state approved THEN 1 ELSE 0 END) AS approved_count, SUM(amount) AS trans_total_amount, SUM(CASE WHEN state approved THEN amount ELSE 0 END) AS approved_total_amount FROM Transactions GROUP BY month, country;trans_count该分组下的总交易数approved_count该分组下状态为approved的交易数trans_total_amount该分组下的总交易金额approved_total_amount该分组下approved状态的总交易金额月份格式要求为YYYY-MM如2018-12DATE_FORMAT(trans_date, %Y-%m) AS month将trans_date日期格式化为YYYY-MM的月份字符串作为分组维度%Y代表 4 位年份%m代表 2 位月份补前导零如01代表 1 月country第二个分组维度按国家 / 地区统计COUNT(id) AS trans_count统计该分组下的总交易数用主键id计数避免空值影响SUM(CASE WHEN state approved THEN 1 ELSE 0 END) AS approved_count用CASE语句将approved状态标记为 1其他为 0求和得到批准交易数SUM(amount) AS trans_total_amount统计该分组下的总交易金额SUM(CASE WHEN state approved THEN amount ELSE 0 END) AS approved_total_amount仅对approved状态的金额求和得到批准交易总金额GROUP BY month, country按月份 国家分组确保每个分组唯一对应输出的一行数据1174. 即时食物配送 IIselect round ( sum(order_date customer_pref_delivery_date) * 100 / count(*), 2 ) as immediate_percentage from Delivery where (customer_id, order_date) in ( select customer_id, min(order_date) from delivery group by customer_id )SELECT customer_id, MIN(order_date)FROM delivery GROUP BY customer_id按用户分组取出每个用户最早的订单日期结果就是(用户ID, 首次订单日期)外层 WHERE只保留【首次订单】的数据WHERE (customer_id, order_date) IN ( ...子查询... )这行是核心技巧只筛选出真正是首次订单的那些记录其他订单全部丢掉最后表里只剩下每个用户 1 条首次订单计算即时订单比例最妙的一行SUM(order_date customer_pref_delivery_date)在 MySQL 里条件成立 1条件不成立 0所以SUM(条件) 即时订单的数量最终计算百分比SUM(...) * 100 / COUNT(*)即时订单数 ÷ 总首次订单数 × 100 百分比ROUND(...,2)保留两位小数550. 游戏玩法分析 IVselect IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction from ( select Activity.player_id as player_id from ( select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date from Activity group by player_id ) as Expected, Activity where Activity.event_date Expected.second_date and Activity.player_id Expected.player_id ) as Result, Activity第一层最内层子查询 Expected作用给每个玩家算出「首次登录的第二天」select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date from Activity group by player_idMIN(event_date)→ 首次登录日期DATE_ADD(..., 1 DAY)→ 首次登录第二天结果(player_id, 应该再次登录的日期)第二层中间子查询 Result作用找出「真的在第二天登录了」的玩家select Activity.player_id from Expected, Activity where Activity.event_date Expected.second_date -- 日期匹配第二天and Activity.player_id Expected.player_id -- 玩家匹配把原表和 Expected 表关联只保留首次登录后第二天确实回来登录的玩家结果符合条件的玩家列表第三层最外层计算比例作用求 次日留存率round(count(distinct Result.player_id) -- 分子次日登录的玩家数/ count(distinct Activity.player_id), -- 分母所有玩家数2)比例 次日登录玩家数 ÷ 总玩家数round(...,2)→ 保留两位小数IFNULL(..., 0)→ 防止分母为 0 报错总结本文总结了7道SQL编程题的解法涉及电影筛选、销售统计、项目分析、赛事注册、查询质量评估、交易统计和游戏行为分析。题目涵盖常见SQL操作条件筛选如奇数ID电影、关联查询如价格与销售匹配、分组聚合如平均工作年限、子查询如次日留存率计算、日期处理如按月统计交易和特殊函数如CASE WHEN、IFNULL。每道题都提供了清晰的解题思路和SQL语句解析重点讲解了关键语法和计算逻辑如加权平均售价、注册率百分比计算等适合SQL学习者快速掌握复杂查询技巧。