如何编写Presto Query获取商品零库存后连续3天有库存的最新日期

现有表结构 #

我们有一张名为item_inventory的表，存储商品每日库存数据，结构及数据如下：

需求说明 #

需要获取商品库存为0，且后续至少连续3天库存不为0的最新日期，示例输出如下：

原因：Item1在2021年8月1日库存为0，之后的9、10、11日连续3天库存均为1，符合条件，且这是符合条件的最新日期。

解决方案（SQL） #

可以使用窗口函数实现，以下是适配多数SQL数据库的写法：

WITH inventory_with_next AS (
    SELECT 
        City,
        invDate,
        inventory,
        -- 获取后续3天的库存值
        LEAD(inventory, 1) OVER (PARTITION BY City ORDER BY invDate) AS next1_inv,
        LEAD(inventory, 2) OVER (PARTITION BY City ORDER BY invDate) AS next2_inv,
        LEAD(inventory, 3) OVER (PARTITION BY City ORDER BY invDate) AS next3_inv
    FROM item_inventory
)
SELECT 
    City,
    MAX(invDate) AS lst_soldout
FROM inventory_with_next
WHERE 
    inventory = 0
    AND next1_inv = 1
    AND next2_inv = 1
    AND next3_inv = 1
GROUP BY City;

逻辑解释 #

1. 用LEAD()窗口函数按商品分组、日期排序，分别提取当前日期后第1、2、3天的库存值；
2. 筛选出当前库存为0，且后续3天库存均为1的记录；
3. 按商品分组，取符合条件的最大日期，即为最新的目标售罄日期。

内容的提问来源于stack exchange，提问作者Harpreet Singh