每日刷力扣SQL题(二)

1211.查询结果的质量和占比

本题主要考察在MYSQL内做简单的计算操作，比如求和、平均值等，

计算质量 可以用AVG()函数求出

计算劣质查询百分比 可以用SUM和IF

最后不要忘记使用ROUND()函数将结果四舍五入到小数点后两位

 
select Queries.query_name,
ROUND(SUM(rating/position)/count(result),2 ) as quality ,
ROUND(SUM(IF(rating <3 ,1 ,0)) /COUNT(*) * 100 , 2) as poor_query_percentage
from Queries 
where query_name IS NOT NULL
group by Queries.query_name

官方解答：

SELECT 
    query_name, 
    ROUND(AVG(rating/position), 2) quality,
    ROUND(SUM(IF(rating < 3, 1, 0)) * 100 / COUNT(*), 2) poor_query_percentage
FROM Queries
Where query_name IS NOT NULL
GROUP BY query_name
 

1193 每月交易

本题要求 查找每个月和每个国家/地区的事务数及其总金额、已批准的事务数及其总金额，我们可以将这句话拆分成几个子任务：

1、查找每个月和每个国家/地区。
数据表中的 trans_date 是精确到日，我们可以使用 DATE_FORMAT() 函数将日期按照年月 %Y-%m 输出。比如将 2019-01-02 转换成 2019-01 。

DATE_FORMAT(trans_date, '%Y-%m')

 2、查找总的事务数。

COUNT(*) AS trans_count

3、 查找总金额。

SUM(amount) AS trans_total_amount

4、 查找已批准的事物数。

COUNT(IF(state = 'approved', 1, NULL)) AS approved_count

5、 查找已批准的事物的总金额

SUM(IF(state = 'approved', amount, 0)) AS approved_total_amount

官方解答：

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

 我的解答：

使用切割字符的方式

 select left(trans_date,7) as month ,
    country,
    count(state) as trans_count,
    sum(if(state="approved",1,0)) 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.即时食物配送 ||

本题需要求出即时订单在所有用户的首次订单中的比例，本题最重要的就是求每一个用户的首单数据：我们使用 group by 聚合每个用户的数据，再使用 min 函数求出首单的时间。将 (customer_id, order_date) 作为查询条件，使用 where in 便可查出具体的数据。

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

550.游戏玩法分析 IV

首先，求出所有用户首次登录的第二天的时间。方法是查询出 Activity 表中每个用户的第一天时间，并加上 1.

select player_id, DATE_ADD(MIN(event_date), INTERVAL 1 DAY) as second_date
from Activity
group by player_id
 

将此表命名为 Expected。随后我们要从 Activity 表中查询 event_date 与 Expected.sencond_date 重叠的部分，注意此判定要限定在用户相同的前提下。这部分用户即为在首次登录后第二天也登录了的用户：

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
 

将此表命名为 Result。随后我们只需要得到 Result 表中用户的数量，以及 Activity 表中用户的数量，相除并保留两位小数即可。

select IFNULL(round(count(distinct(Result.player_id)) / count(distinct(Activity.player_id)), 2), 0) as fraction

最终组合起来

# Write your MySQL query statement below
select 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 Excepted ,Activity
where Activity.player_id = Excepted.player_id and Excepted.second_date = Activity.event_date
)
as Result ,Activity

这是官方题解的组合查询 在评论中也有比较清晰的解法，运用了自连接：

SELECT ROUND(SUM(IF(DATEDIFF(event_date, first_date)= 1,1,0)) / COUNT(DISTINCT a.player_id),2) AS fraction
FROM Activity AS a LEFT JOIN(
    SELECT player_id, min(event_date) AS first_date
    FROM Activity
    GROUP BY player_id
) T
ON a.player_id = T.player_id;