【力扣】MySQL知识点整理回顾-聚合函数

聚合函数

在MySQL中，聚合函数用于对一组值执行计算并返回单个值。这些函数通常与GROUP BY子句一起使用，以便对结果集中的行进行分组，并对每个组应用聚合函数。以下是一些最常用的MySQL聚合函数：

COUNT()：返回指定列的值的数量。

COUNT(*) 返回行数，不考虑NULL值。
COUNT(column_name) 返回指定列中非NULL值的数量。
SUM()：返回指定列的数值之和。如果列中包含NULL值，它们将被忽略。

AVG()：返回指定列的平均值。如果列中包含NULL值，它们将被忽略。

MIN()：返回指定列的最小值。如果列中包含NULL值，它们将被忽略。

MAX()：返回指定列的最大值。如果列中包含NULL值，它们将被忽略。

例题

平均售价

原错误解法：

select p.product_id, round((sum(u.units*p.price))/sum(units),2) 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

错误原因在于没有把没有均价的产品考虑进去。在SQL中，当使用左连接时，如果右表中没有与左表相匹配的记录，则结果集中对应的右表列将显示为NULL。如果想要列出所有产品及其价格，但即使某些产品没有价格（即Prices表中没有对应的记录），也希望这些产品在结果集中出现，并且价格显示为0，需要使用左连接，并在查询中使用IFNULL或COALESCE函数将NULL值转换为0。
正确解法：

select p.product_id, ifnull(round((sum(u.units*p.price))/sum(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

IFNULL(Prices.Price, 0) 是一个条件函数，它检查Prices.Price的值。如果Prices.Price是NULL（即没有价格信息），则它返回0；否则，它返回Prices.Price的实际值。

各赛事的用户注册率

解题思路：
两个表虽然有共同的字段，但是并不需要连接。因为统计一门赛事注册的user_id在Register表中，统计user_id总数量在Users表中，两者都是可以独立查询出来的
做题步骤：
1.不同赛事进行分组
2.分别统计一门赛事中注册了的user_id数量、user_id总数量
3.保留两位小数
4.结果按percentage降序排序，相同按contest_id的升序排序
代码为：

select contest_id,
round(count(user_id)*100/(select count(*) from Users),2) percentage
from Register
group by contest_id
ORDER BY percentage DESC, contest_id ASC

每月交易

每月交易-力扣官方解题

预备知识

（1）DATE_FORMAT(date, format) ：用于以不同的格式显示日期/时间数据。date 参数是合法的日期，format 规定日期/时间的输出格式。
（2）根据某些条件对结果进行不同的处理时：CASE WHEN或IF函数
假设我们有一个名为orders的表，它包含订单信息，如order_id、customer_id、status和amount。我们想要选择所有订单，但想要添加一个额外的列status_label，如果订单状态是’shipped’，则显示’已发货’，否则显示’未发货’。

SELECT  
    order_id,  
    customer_id,  
    status,  
    amount,  
    CASE  
        WHEN status = 'shipped' THEN '已发货'  
        ELSE '未发货'  
    END AS status_label  
FROM  
    orders;

思路：DATE_FORMAT() 函数、GROUP BY

本题要求查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额，我们可以将这句话拆分成几个子任务：
（1）查找每个月和每个国家/地区。
数据表中的 trans_date 是精确到日，我们可以使用 DATE_FORMAT() 函数将日期按照年月 %Y-%m 输出。比如将 2019-01-02 转换成 2019-01 。

（2）DATE_FORMAT(trans_date, ‘%Y-%m’)
获取到所有的月份后，使用 GROUP BY 聚合每个月和每个国家的记录就完成了第一步。

（3）查找总的事务数。
第一步已经将数据按月和国家聚合，只需要使用 COUNT 函数就能获取到总的事务数。

（4）COUNT(*) AS trans_count
查找总金额。
使用 SUM 函数计算总金额。

（5）SUM(amount) AS trans_total_amount
查找已批准的事物数。
已批准的事物的 state 标记为 approved。首先使用 IF 函数将 state = ‘approved’ 的记录标记为 1，否则为 NULL。再使用 COUNT 计算总量。

（6）COUNT(IF(state = ‘approved’, 1, NULL)) AS approved_count
查找已批准的事物的总金额。
和第四步一样，先使用 IF 函数，再使用 SUM 函数。

（7）SUM(IF(state = ‘approved’, amount, 0)) AS approved_total_amount
代码：

select DATE_FORMAT(trans_date, '%Y-%m') as month,
country,
count(state) trans_count,
COUNT(IF(state = 'approved', 1, NULL)) 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

当然也可以使用CASE WHEN：

SELECT  
    DATE_FORMAT(trans_date, '%Y-%m') AS month,  
    country,  
    COUNT(state) AS trans_count,  
    COUNT(CASE WHEN state = 'approved' THEN 1 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;

这种写法和原来的使用IF函数的查询在功能上是完全等效的。