在线定制logo_免费浏览器加速器_互联网营销平台有哪些_软文形式推广产品

一、题目描述

司机累计收入首次接近目标值的订单定位，滴滴平台计划优化司机奖励策略的触发机制，需精准识别司机在接单过程中累计收入首次接近特定目标值1000元的订单节点。该分析用于动态调整奖励发放规则，提升司机接单积极性。

样例数据

假设表 t_sales 结构如下：

driver_id	order_id	income	order_time
1	101	200	2025-02-19 09:00:00
1	102	300	2025-02-19 10:00:00
1	103	500	2025-02-19 11:00:00
2	104	400	2025-02-19 09:30:00
2	105	600	2025-02-19 10:30:00

二、SQL逻辑

1. 计算每位司机的累计收入

select   driver_id,order_id,income,order_time,sum(income) over (partition by driver_id order by order_time) as cumulative_income
from    t_sales;

执行结果：

driver_id	order_id	income	order_time	cumulative_income
1	101	200	2025-02-19 09:00:00	200
1	102	300	2025-02-19 10:00:00	500
1	103	500	2025-02-19 11:00:00	1000
2	104	400	2025-02-19 09:30:00	400
2	105	600	2025-02-19 10:30:00	1000

2. 计算累计收入与目标值的差值

 select   driver_id,order_id,income,order_time,cumulative_income,abs(cumulative_income - 1000) as diff
from    (select   driver_id,order_id,income,order_time,sum(income) over (partition by driver_id order by order_time) as cumulative_incomefrom    t_sales) t;

执行结果：

driver_id	order_id	income	order_time	cumulative_income	diff
1	101	200	2025-02-19 09:00:00	200	800
1	102	300	2025-02-19 10:00:00	500	500
1	103	500	2025-02-19 11:00:00	1000	0
2	104	400	2025-02-19 09:30:00	400	600
2	105	600	2025-02-19 10:30:00	1000	0

3. 筛选差值最小的记录

   select   driver_id,order_id,income,order_time,cumulative_income,diff
from    (select   driver_id,order_id,income,order_time,cumulative_income,diff,row_number() over (partition by driver_id order by diff) as rnfrom    (select   driver_id,order_id,income,order_time,sum(income) over (partition by driver_id order by order_time)             as cumulative_income,abs(sum(income) over (partition by driver_id order by order_time) - 1000) as difffrom    t_sales) t1) t2
where   rn = 1;

执行结果：

driver_id	order_id	income	order_time	cumulative_income	diff
1	103	500	2025-02-19 11:00:00	1000	0
2	105	600	2025-02-19 10:30:00	1000	0