如何获取所有休息行(包括当前行)按类别获取最小数字

Question

i有一个数据表，如以下数据表，该数据表由数据，Route_Number和sequence分类。

Delivery Date   Order_ID    Route_Number    Stop #  Sequence    Min Stop#   Formula 
12/11/2017         Z11         100201         2        1           1    MIN(D2:$D$6)
12/11/2017         Z12         100201         1        2           1    MIN(D3:$D$6)
12/11/2017         Z13         100201         3        3           3    MIN(D4:$D$6)
12/11/2017         Z14         100201         5        4           4    MIN(D5:$D$6)
12/11/2017         Z15         100201         4        5           4    MIN(D6:$D$6)

我要做的是在我的SQL查询中，如何在Excel中获得列 min停止＃。

逻辑是：给我最小的停止＃从当前行到同一Route_number的所有休息行，以及交货日期，我在想诸如dovelion_date，route_number的分区之类的东西。

有人有一些想法吗？

谢谢

Answer 1

使用min窗口函数。

select t.*,min(stop) over(partition by route_number,delivery_date
                          order by sequence rows between current row 
                          and unbounded following) as min_stop
from tbl t

Answer 2

min(stop) over (partition by route_number, delivery_date
order by sequence rows between current row and unbounded following)

或

min(stop) over (partition by route_number, delivery_date
order by sequence desc rows between unbounded preceding and current row)

可以简化为

min(stop) over (partition by route_number, delivery_date
order by sequence desc) m2

因为rows between unbounded preceding and current row是使用" Over of Offer"子句的订购时的默认窗口。