如何在SQL/DuckDB中按滚动窗口Frame而非Partition生成行号

需求是在SQL/DuckDB的滚动窗口中，按**Frame（而非Partition）**生成行号——即针对每行，只在其对应的滚动窗口（当前行至之后1周内的行）范围内生成序号，而非基于整个Partition的全局排序编号。

原始数据 #

customer_id,date
ca,2024-04-03
ca,2024-04-04
ca,2024-04-04
ca,2024-04-11
cb,2024-04-02
cb,2024-04-02
cb,2024-04-03
cb,2024-05-13

原查询及问题 #

原查询尝试使用带RANGE窗口的row_number()，但row_number()的序号是基于整个Partition排序后的全局编号，并非Frame内的局部序号：

SELECT
    customer_id,
    date,
    row_number() OVER win AS row_by_partition
FROM 'example.csv'
WINDOW win AS (
    PARTITION BY customer_id
    ORDER BY date ASC
    RANGE BETWEEN CURRENT ROW
      AND INTERVAL 1 WEEK FOLLOWING)

原查询结果 #

┌─────────────┬────────────┬──────────────────┐
│ customer_id │    date    │ row_by_partition │
│   varchar   │    date    │      int64       │
├─────────────┼────────────┼──────────────────┤
│ ca          │ 2024-04-03 │                1 │
│ ca          │ 2024-04-04 │                2 │
│ ca          │ 2024-04-04 │                3 │
│ ca          │ 2024-04-11 │                4 │
│ cb          │ 2024-04-02 │                1 │
│ cb          │ 2024-04-02 │                2 │
│ cb          │ 2024-04-03 │                3 │
│ cb          │ 2024-05-13 │                4 │
└─────────────┴────────────┴──────────────────┘

期望结果 #

需要每个Frame内的局部行号，即同一customer_id下，与当前行日期在1周窗口内的行，在该范围内重新编号：

┌─────────────┬────────────┬──────────────┐
│ customer_id │    date    │ row_by_frame │
│   varchar   │    date    │      int64   │
├─────────────┼────────────┼──────────────┤
│ ca          │ 2024-04-03 │            1 │
│ ca          │ 2024-04-04 │            1 │
│ ca          │ 2024-04-04 │            2 │
│ ca          │ 2024-04-11 │            1 │
│ cb          │ 2024-04-02 │            1 │
│ cb          │ 2024-04-02 │            2 │
│ cb          │ 2024-04-03 │            1 │
│ cb          │ 2024-05-13 │            1 │
└─────────────┴────────────┴──────────────┘

解决方案 #

利用DuckDB的窗口函数能力，先标记每行所在Frame的唯一标识（窗口起始日期），再基于该标识和customer_id分组生成局部行号：

SELECT
    customer_id,
    date,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id, 
                     -- 获取当前行所在Frame的最小日期（窗口起始点）
                     MIN(date) OVER (
                         PARTITION BY customer_id
                         ORDER BY date
                         RANGE BETWEEN CURRENT ROW AND INTERVAL 1 WEEK FOLLOWING
                     )
        ORDER BY date
    ) AS row_by_frame
FROM 'example.csv'
ORDER BY customer_id, date;

逻辑说明 #

1. MIN(date) OVER (...) 窗口函数会为每行返回其所在滚动窗口（当前行至之后1周）内的最小日期，这个日期就是该Frame的唯一标识；
2. 基于customer_id和这个Frame标识分组，再用ROW_NUMBER()在分组内生成序号，即可得到每个Frame内的局部行号，完全符合需求。

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