数据库相关算法题 V3

订单最多的客户

在考虑多个最多订单客户的情况下可以采用dense_rank()函数，最多则由group by customer_number以及order count(*)得到

select customer_number from (select customer_number,dense_rank() over (order by count(*) desc) as rk from Orders group by customer_number) t where rk = 1

https://leetcode.cn/problems/customer-placing-the-largest-number-of-orders/description/

买下所有产品的客户

买下所有产品意味着该客户买的所有不重复产品总数等于总产品数

select customer_id from Customer group by customer_id having count(distinct(product_key))=(select count(*) from Product)

https://leetcode.cn/problems/customers-who-bought-all-products/description/

计算首单为即时订单的用户数

最自然判断首单为即时订单应该就是先找到首单，然后判断首单是不是即时的。但这样就太麻烦了。更好的办法，已知期望配送日期一定不早于下单日期，那么只要用户的首单日期与最小的期望配送日期相等，那么这个首单就是即时订单

select round(avg(a)*100,2) as immediate_percentage from
(select customer_id, 	if(min(order_date)=min(customer_pref_delivery_date),1,0) 
as a from Delivery group by customer_id ) t

重新格式化部门表

select id,
    avg(case month when 'Jan' then revenue end) as Jan_Revenue,
    avg(case month when 'Feb' then revenue end) as Feb_Revenue,
    avg(case month when 'Mar' then revenue end) as Mar_Revenue,
    avg(case month when 'Apr' then revenue end) as Apr_Revenue,
    avg(case month when 'May' then revenue end) as May_Revenue,
    avg(case month when 'Jun' then revenue end) as Jun_Revenue,
    avg(case month when 'Jul' then revenue end) as Jul_Revenue,
    avg(case month when 'Aug' then revenue end) as Aug_Revenue,
    avg(case month when 'Sep' then revenue end) as Sep_Revenue,
    avg(case month when 'Oct' then revenue end) as Oct_Revenue,
    avg(case month when 'Nov' then revenue end) as Nov_Revenue,
    avg(case month when 'Dec' then revenue end) as Dec_Revenue
from Department
group by id

avg函数在本题中并没有特殊含义，只是用于聚合，防止本应该获取到数值，但得到了null

case when相当于编程语言的switch

https://leetcode.cn/problems/reformat-department-table/description/

每月交易1

我最初的方案原本如下，就是根据国家区域和月份分组聚合，但是忽略了在没有匹配数据的情况下sum()、count()会返回null

select 
DATE_FORMAT(trans_date, '%Y-%m') as month,
country, 
count(*) as trans_count, 
sum(case state when 'approved' then 1 end) as approved_count, 
sum(amount) as trans_total_amount, 
sum(case state when 'approved' then amount end) as approved_total_amount
from Transactions
group by concat(country, DATE_FORMAT(trans_date, '%Y-%m'))

那么只需要对count()、sum()做些小小改变，count()会忽略null，那么如果都是null，返回值也就是0了；sum()如果都是null，才会返回null，那么只要在无值的时候返回0就可以了

select 
DATE_FORMAT(trans_date, '%Y-%m') as month,
country, 
count(*) as 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 concat(country, DATE_FORMAT(trans_date, '%Y-%m'))

此外也可以根据month、group分组

SELECT DATE_FORMAT(trans_date, '%Y-%m') AS month,
    country,
    COUNT(*) AS 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

https://leetcode.cn/problems/monthly-transactions-i/