SQL窗口函数：如何在满足指定条件的行后终止计算？

大家好！

我有一张表，此前已为每条采购记录添加了rang字段，数据如下：

我希望在遇到title字段包含“BUY”的行后，让后续行重新开始窗口函数的计数，但尝试以下语句后未得到预期效果：

RANK() OVER (PARTITION BY id ORDER BY time ASC RESET WHEN title LIKE '%BUY%')

执行后所有数据均在首次出现“BUY”的行处终止计数，结果如下：

标准SQL中并没有RESET WHEN这类窗口函数语法，你可以通过构造分组标识实现需求：

实现代码 #

SELECT 
    id,
    date,
    time,
    title,
    ROW_NUMBER() OVER (
        PARTITION BY id, buy_group 
        ORDER BY time ASC
    ) AS rang
FROM (
    SELECT 
        *,
        SUM(CASE WHEN title LIKE '%BUY%' THEN 1 ELSE 0 END) 
        OVER (PARTITION BY id ORDER BY time ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS buy_group
    FROM your_table
) t

逻辑说明 #

1. 内层查询通过SUM()窗口函数，为每个id分区内的行计算截至当前行的“BUY”累计数量，这个值作为新的分组标识buy_group——每遇到一个含“BUY”的行，分组标识就会+1。
2. 外层查询基于id和buy_group双重分区，用ROW_NUMBER()重新计算rang，这样每个“BUY”行之后的记录会进入新分组，重新开始计数。

预期结果 #

内容的提问来源于stack exchange，提问作者Mike Silver