【Ryo】牛客SQL新题感受—查漏补缺随笔

佛系随缘投一波寒假，牛客上了新sql继续开刷，最近没有好好看书所以记录一下sql的查漏补缺。

研究内容	语言	日期
数据库	sql	2021年12月2日

水平有限 欢迎斧正！

一、sql开窗函数-移动窗口

前排感谢参考博客：点我看原贴！

1. 最原始的开窗：按照商品类型分组，按照销售价格排序，获得每组的价格排名。rank是跳序重复的排序，dense_rank是并序重复，row_number是顺序不重复，不再赘述。当然partition不是必须的~

select product_name, product_type, sale_price,
       rank () over (order by sale_price) as ranking,
	   dense_rank () over (order by sale_price) as dense_ranking,
	   row_number () over (order by sale_price) as row_num
from Product;

2. 移动平均的计算方法：
①窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。指定最靠近的3行做为汇总对象：

select product_id, product_name, sale_price,
       avg (sale_price) over (order by product_id
	                          rows 2 preceding) as moving_avg
from Product;

指定框架（汇总范围）：这里使用的rows（行）和preceding（之前）两个关键字，将框架指定为截止到之前？行，因此rows 2 preceding就是将框架指定为截止到之前2行，也就是将作为汇总对象的记录限定为如下的最靠近3行：

1.自身（当前记录）

2.之前1行的记录

3.之前2行的记录
结果为：

②使用关键字following（之后）替换preceding，就可以将框架改为截止到之后X行。
将当前记录的前后行作为汇总对象：

select product_id, product_name, sale_price,
       avg(sale_price) over (order by product_id
	                          rows between 1 preceding and 1 following) as moving_avg--使用between规划范围，语句意思为rows 1 preceding
																				     --到rows 1 following
from Product;

语句意思：
1.之前1行的记录
2.自身（当前记录）
3.之后1行的记录

整的框架就是这样：

重点总结： 移动窗口无论取平均还是其他的聚合函数，核心语句的部分是：

rows between 1 preceding and 1 following

首先搞清楚是向前取还是向后取，然后如果是求业务类的需要注意天数，譬如牛客某音第七题，统计每类视频每天的近一周总点赞量和一周内最大单天转发量。代码↓，其中rows between 6 preceding and current row就是指今年和前七天！

select
*
from 
(
    select 
    t.tag
    ,t.dt
    ,sum(t.like_cnt) over(partition by t.tag order by t.dt rows between 6 preceding and current row) sum_like_cnt_7d
    ,max(t.retweet_cnt) over(partition by t.tag order by t.dt rows between 6 preceding and current row) max_retweet_cnt_7d
    from 
    (
        select 
        b.tag
        ,date(a.start_time) dt
        ,sum(a.if_like) like_cnt
        ,sum(a.if_retweet) retweet_cnt
        from 
        tb_user_video_log a 
        join 
        tb_video_info b 
        using(video_id)
        where 
        date(a.start_time) between '2021-09-25' and '2021-10-03'
        group by 
        b.tag,date(a.start_time)
    ) t
    order by 
    t.tag desc,t.dt
) e 
where 
e.dt between '2021-10-01' and '2021-10-03'

二、牛客第一个双百： 求2021年国庆在北京接单3次及以上的司机统计信息

题目连接
趁人少分享一下自己的第一个牛客双百嘿嘿！（新题打卡）
分享一下解题：
内部一个筛选 先选取符合条件的司机的订单量，总收入，外面再套一个求avg就行了。

select t.city,round(avg(avg_order_num),3),round(avg(avg_income),3)
from (
select driver_id,city,count(o.order_id) as avg_order_num, sum(fare) as avg_income
from tb_get_car_record as r
join tb_get_car_order as o
on r.order_id = o.order_id
where city = '北京' and event_time between '2021-10-01' and '2021-10-07'
group by driver_id
having count(*) > 2
) as t
group by city

三、 WITH ROLLUP的用法

用法是跟在group by 字段之后 ，可以在分组字段的基础上做一个汇总统计。例如：
可以看到按照name分组后对money求和统计了。但是新生成的最后一行的名字会是null，如何给他命名呢？可以把第一个name改成如下：【相当厉害又简单的一个做法】

select ifnull(name,"总计") as name ......

写在最后，最近摆烂大摆特摆，每天读小说惶惶不可终日，还被同学夸奖实在惭愧，呜呜呜一定好好学习！