SQL刷题记录-牛客SQL177（某滴打车）

题目链接：国庆期间近7日日均取消订单量_牛客题霸_牛客网 (nowcoder.com)

1. 描述

现有用户打车记录表tb_get_car_record

id	uid	city	event_time	end_time	order_id
1	101	北京	2021-09-25 08:28:10	2021-09-25 08:30:00	9011
2	102	北京	2021-09-25 09:00:30	2021-09-25 09:01:00	9012

（uid-用户ID, city-城市, event_time-打车时间, end_time-打车结束时间, order_id-订单号）

打车订单表tb_get_car_order

id	order_id	uid	driver_id	order_time	start_time	finish_time	mileage	fare	grade
1	9011	101	211	2021-09-25 08:30:00	2021-09-25 08:31:00	2021-09-25 08:54:00	10	35	5
2	9012	102	211	2021-09-25 09:01:00	2021-09-25 09:01:50	2021-09-25 09:28:00	11	32	5
3	9013	103	212	2021-09-26 08:01:00	2021-09-26 08:03:00	2021-09-26 08:27:00	12	31	4
4	9023	104	213	2021-09-26 08:01:00	NULL	2021-09-26 08:27:00	NULL	NULL	NULL

（order_id-订单号, uid-用户ID, driver_id-司机ID, order_time-接单时间, start_time-开始计费的上车时间,  finish_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分）

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

2. 思路：

先筛选出'2021-09-25'-'2021-10-03'时间段内的信息；

计算“每天的近7天” ---> 滑动窗口函数

3. SQL代码：

select
dt
,finish_num_7d
,cancel_num_7d
from(
#滑动窗口函数
select
    dt
    ,round(sum(finish_cnt) over(order by dt rows 6 preceding)/7,2) as finish_num_7d
    ,round(sum(cancel_cnt) over(order by dt rows 6 preceding)/7,2) as cancel_num_7d
from(
select 
        date(order_time) dt
        ,sum(if(mileage is not null,1,0)) as finish_cnt
        ,sum(if(mileage in null,1,0)) as cancel_cnt
    from tb_get_car_order
    where date(order_time) between '2021-09-25' and '2021-10-03'
    group by date(order_time) 
    )t1
)t2
where dt in ('2021-10-01','2021-10-02','2021-10-03')
order by dt;

此处注意最后一层'where'的筛选，建议在最外一层查询时进行筛选，不然会报错。

4. 知识补充 -- 滑动窗口函数

 滑动窗口函数可以拆解为“窗口”和“滑动”两个部分。

“窗口”        

首先，

定义方式：

• PARTITION BY: 定义分区的方式，将数据划分为独立的组，每个组独立应用窗口函数。
• ORDER BY: 窗口内的排序方式，通常用于指定窗口的顺序。

常用的窗口函数如下。

ROW_NUMBER()

• 作用： 为每一行分配一个唯一的行号，从 1 开始递增。
• 使用场景： 用于对结果集中的每一行进行编号，常用于在分页查询或去重时标记行号。

RANK()

• 作用： 根据指定的排序，为结果集中的每一行分配一个排名。如果两行具有相同的值，它们将获得相同的排名，但接下来的行的排名会跳过（即，排名中会出现“空位”）。
• 使用场景： 当需要计算排名并允许排名存在“空位”时，如比赛中相同成绩的选手获得相同名次。

DENSE_RANK()

• 作用： 与 RANK() 类似，但不会在排名中留下“空位”。如果两行具有相同的值，它们将获得相同的排名，接下来的行的排名是连续的。
• 使用场景： 需要计算没有“空位”的排名时使用，如在奖金分配中，不希望出现跳过的排名。

NTILE(n)

• 作用： 将结果集划分为 n 个桶（或组），并为每一行分配一个桶号。结果集尽量均匀地分配到每个桶中。
• 使用场景： 当需要将数据集分成几部分时使用，如将销售数据按百分位划分为四组（即四分位数）。

LAG()

• 作用： 返回当前行的前一行的值。如果前一行不存在，则返回 NULL。
• 使用场景： 当需要计算时间序列中当前行与前一行之间的差异时使用，如计算股票价格的日变化。

LEAD()

• 作用： 返回当前行的下一行的值。如果下一行不存在，则返回 NULL。
• 使用场景： 与 LAG() 相似，但用于获取下一行的值，如预测下一天的销售量。

SUM()

• 作用： 计算当前行及之前的行的累积和（可配置窗口大小）。
• 使用场景： 用于累积求和，如计算每个月的累计销售额或计算某一股票的滚动总收益。

AVG()

• 作用： 计算当前行及之前的行的平均值（可配置窗口大小）。
• 使用场景： 用于计算移动平均值，如计算股票价格的5日均线。

MIN() / MAX()

• 作用： 计算当前行及之前的行的最小值或最大值（可配置窗口大小）。

FIRST_VALUE() / LAST_VALUE()

• 作用： 返回窗口内的第一行或最后一行的值。
• 使用场景： 当需要比较当前值与窗口内的首尾值时使用，如比较月初与月末的库存水平。

 "滑动”

 滑动 描述了窗口如何在结果集中移动。随着每一行的计算，窗口的位置会发生变化，通常是从上到下逐行滑动。

在 SQL 中，OVER() 子句通常与窗口函数一起使用来定义一个窗口或范围，在这个范围内进行聚合或排序操作。

经典案例

1. 某个时间点到今天

使用 WHERE 子句来过滤数据，然后使用 OVER() 来应用窗口函数。

SELECT 
    sale_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date) AS cumulative_sales
FROM 
    sales
WHERE 
    sale_date >= '2024-01-01'
ORDER BY 
    sale_date;

#相似表达
#从 2024-08-01 到今天的销售额累积（某个指定时间点到今天的累积）
SELECT 
    sale_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM 
    sales
WHERE 
    sale_date BETWEEN '2024-08-01' AND CURRENT_DATE
ORDER BY 
    sale_date;

2.某段时间的聚合

你可以通过 WHERE 子句定义一个时间段，然后对这个时间段内的数据应用窗口函数。

#某一周内每天的累计销售额
SELECT 
    sale_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS weekly_sales
FROM 
    sales
WHERE 
    sale_date BETWEEN '2024-07-01' AND '2024-07-07'
ORDER BY 
    sale_date;

 3. 每天的近 n 天数据

#每一天的近7天销售额平均
SELECT 
    sale_date,
    sales_amount,
    AVG(sales_amount) OVER (ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg
FROM 
    sales
ORDER BY 
    sale_date;

 4. 时间范围的滑动窗口

#计算每一天过去一周的销售额总和
SELECT 
    sale_date,
    sales_amount,
    SUM(sales_amount) OVER (ORDER BY sale_date RANGE BETWEEN INTERVAL '6' DAY PRECEDING AND CURRENT ROW) AS weekly_sum
FROM 
    sales
ORDER BY 
    sale_date;