MYSQL 刷题笔记（一）

目录

SQL21  每个城市中评分最高的司机信息

经典题型1: 某段时间的近几天

SQL22 国庆期间近7日日均取消订单量

典型题型2：新生成时间段，进行统计计算

SQL23 工作日各时段叫车量、等待接单时间和调度时间

典型题型3：有增有减，计算瞬时最大值

SQL24 各城市最大同时等车人数

典型题型4：连续登陆问题

---

SQL21  每个城市中评分最高的司机信息

问题：请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。

注：有多个司机评分并列最高时，都输出。

平均评分和日均接单量保留1位小数，

日均行驶里程数保留3位小数，按日均接单数升序排序。

SELECT city,driver_id,avg_grade,avg_order_num,avg_mileage
FROM (
    SELECT city,driver_id,avg_grade,avg_order_num,avg_mileage,
    RANK() over(PARTITION BY city ORDER BY avg_grade DESC) as rk
    FROM (
        SELECT city,
        driver_id,
        ROUND(SUM(grade)/ count(grade),1) AS avg_grade,
        ROUND(COUNT(driver_id)/ COUNT(DISTINCT DATE(order_time)) ,1) AS avg_order_num,
        ROUND(SUM(mileage)/ COUNT(DISTINCT DATE(order_time)),3) AS avg_mileage
        FROM tb_get_car_order t1
        JOIN tb_get_car_record t2 USING(order_id)
        GROUP BY city,driver_id
    )t1
)t2
WHERE rk=1
ORDER BY avg_order_num

笔记：

1）“有多个司机评分并列最高时，都输出”，并列输出，需要排序。先搜素，再排序，然后筛选。

2）统计多少天：COUNT(DISTINCT DATE(order_time)) 

经典题型1: 某段时间的近几天

SQL22 国庆期间近7日日均取消订单量

select *
from(select dt,
       round(sum(finish_num)over(order by dt rows 6 preceding)/7,2) as finish_num_7d,
       round(sum(cancel_num)over(order by dt rows 6 preceding)/7,2) as cancel_num_7d
     from(select date(order_time) dt,
            sum(case when start_time is not null then 1 else 0 end) as finish_num,
            sum(case when start_time is null then 1 else 0 end) as cancel_num
          from tb_get_car_order
          group by date(order_time)
          order by dt) t #统计每天
    ) a #统计7天
where dt between '2021-10-01' and '2021-10-03' #最后限制

问题：请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。

思路：

第一步：统计每天的订单完成量和取消量

第二步：统计7天的订单完成量和取消量【rows 6 preceding】

第三步：限制日期

典型题型2：新生成时间段，进行统计计算

SQL23 工作日各时段叫车量、等待接单时间和调度时间

问题：统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。

SELECT period, COUNT(*) AS get_car_num,
ROUND(AVG(wait_time),1) AS avg_wait_time,
ROUND(AVG(dispatch_time),1) AS avg_dispatch_time
FROM(
    SELECT a.order_id,
    TIMESTAMPDIFF(SECOND,event_time,order_time)/60 AS wait_time,
    TIMESTAMPDIFF(SECOND,order_time,start_time)/60 AS dispatch_time,
    CASE WHEN DATE_FORMAT(event_time,'%H-%i-%s')>='07-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s')<'09-00-00' THEN '早高峰'
    WHEN DATE_FORMAT(event_time,'%H-%i-%s')>='09-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s')<'17-00-00' THEN '工作时间'
    WHEN DATE_FORMAT(event_time,'%H-%i-%s')>='17-00-00' AND DATE_FORMAT(event_time,'%H-%i-%s')<'20-00-00' THEN '晚高峰'
    ELSE '休息时间' END AS period
    FROM tb_get_car_record a
    JOIN tb_get_car_order b ON a.order_id=b.order_id
    WHERE DATE_FORMAT(event_time,'%W') NOT IN ('saturday','sunday')
)t
GROUP BY period
ORDER BY get_car_num

问题拆解：

1. 指标：
   • 叫车辆
   • 平均等待接单时间=总的等待接单时间（打车时间-接单时间）/订单数目
   • 平均调度时间=总的调度时间（上车时间-接单时间）/订单数目
2. 筛选条件：周一到周五 -- WHERE DATE_FORMAT(event_time,'%W') NOT IN ( 'Saturday' ,'Sunday')
3. 新生成的时段字段 -- case when

典型题型3：有增有减，计算瞬时最大值

SQL24 各城市最大同时等车人数

问题：请统计各个城市在2021年10月期间，单日中最大的同时等车人数。

注:   等车指从开始打车起，直到取消打车、取消等待或上车前的这段时间里用户的状态。 

如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少。 

结果按各城市最大等车人数升序排序，相同时按城市升序排序。

经典解法：利用SUM窗口函数找到同一时刻内的最大计数_牛客博客

题目要求在瞬时统计时遵循【先进后出】：如果同一时刻有进入也有离开时，先记录用户数增加，再记录减少。因此在ORDER BY层面，在遵循dt升序的同时，还要遵循先+1，再-1的原则，即diff DESC：

SUM(diff) OVER(PARTITION BY artical_id ORDER BY dt, diff DESC)

WITH t AS(
SELECT city, event_time dt, order_id, 1 diff #开始等待，人数+1
FROM tb_get_car_record
WHERE LEFT(event_time, 7) = '2021-10'
UNION 
SELECT city, o.start_time dt, r.order_id, -1 diff #顺利上车，人数-1
FROM tb_get_car_record r
LEFT JOIN tb_get_car_order o USING(order_id)
WHERE LEFT(o.start_time, 7) = '2021-10' AND start_time IS NOT NULL
UNION
SELECT city, o.finish_time dt, r.order_id, -1 diff #上车前取消，人数-1
FROM tb_get_car_record r
LEFT JOIN tb_get_car_order o USING(order_id)
WHERE LEFT(o.finish_time, 7) = '2021-10' AND start_time IS NULL
)

SELECT 
  city,
  MAX(instant_wait_cnt) max_wait_uv
FROM (
  SELECT 
    city,
    DATE(dt) dt,
    SUM(diff) OVER (PARTITION BY city, DATE(dt) ORDER BY dt, diff DESC) instant_wait_cnt
  FROM t
) a
GROUP BY 1
ORDER BY 2, 1

步骤拆解：

1. 对原表编码并联立；
2. 按city, DATE(dt) 维度，dt升序 ，diff降序，对diff进行SUM开窗统计，得到每个city, DATE(dt) 的瞬时在等车人数instant_viewer_cnt；
3. 最外层SELECT按city 聚合，通过MAX（instant_viewer_cnt）取出瞬时在等车人数max_uv，并排序。

典型题型4：连续登陆问题

现有某乎问答创作者信息表author_tb如下(其中author_id表示创作者编号、author_level表示创作者级别，共1-6六个级别、sex表示创作者性别)。

创作者回答情况表answer_tb如下（其中answer_date表示创作日期、author_id指创作者编号、issue_id指回答问题编号、char_len表示回答字数）。

请你统计最大连续回答问题的天数大于等于3天的用户及其等级（若有多条符合条件的数据，按author_id升序排序），以上例子的输出结果如下：

select t2.author_id,author_level,t2.day_cnt
from 
    (select author_id,count(*) day_cnt
    from 
        (select answer_date,author_id,
                dense_rank()over(partition by author_id order by answer_date) as cnt
        from answer_tb
        group by answer_date,author_id
        ) t1
    group by author_id,date_sub(answer_date,interval cnt day) #从日期减去指定时间间隔
    having count(*)>=3
    ) t2
join author_tb
on t2.author_id=author_tb.author_id
order by t2.author_id;

SELECT q.author_id,author_level,q.days_cnt
FROM(
    select b.author_id,count(*) days_cnt
    FROM(
        select a.author_id,
        a.answer_date,
        row_number() over(partition by a.author_id order by a.answer_date) rk
        from(
            select distinct answer_date,author_id
            from answer_tb 
            ) a
        )b
    group by  b.author_id,(b.answer_date-rk)
    having days_cnt>=3
    )q
JOIN author_tb USING(author_id)
ORDER BY author_id

参考：题解 | #某乎问答最大连续回答问题天数大于等于3天的用户及其对应等级#_牛客博客

思路：

（1）首先，排序

（2）其次，原日期 减 排序序号； 依据差值分类；统计总行数。举个例子，dense_rank排序，第一次登陆时间为2月3日，排序为1，第二次登陆时间为2月3日，排序为1，第三次登陆时间为2月4日排序为2，第四次登陆时间为2月5日，排序序号为3，第五次登陆时间为2月8日，排序为4，则，时间减去排序序号，依次为2月2日，2月2日，2月2日，2月2日，2月5日，依据该差值分组，则有两个组，统计行数分别为4，1，找出行数大于3的，即完成了连续3次。

（3）个人觉得第一个代码不太正确，第2个代码正确。第一个代码没有考虑到一个日期登陆两次，第二个代码考虑了这个情况，所以它先取不同的日期，再排序。所以总结：先取不同的日期，然后再排序，然后再依据日期与排序序号的差值进行分组，统计行数就可以获得行数大于3个，最后连接另一个表，即可获得登陆时间连续三天的信息。

参考：

利用SUM窗口函数找到同一时刻内的最大计数_牛客博客

解题关键：搞清用户停止等待的所有情况_牛客博客