在Snowflake中按日期连续区间对Quantity分组编号

你原来的SQL使用PARTITION BY "Quantity"会将所有相同Quantity的记录划入同一分区，这会导致不连续的同Quantity记录（比如4月1-3的75和4月6-9的75）被分配连续的RN，无法满足"仅连续日期且Quantity相同的记录共享同一RN"的需求。

这是典型的**岛屿和缝隙（Islands and Gaps）**问题，可通过以下SQL实现需求：

WITH group_ids AS (
    SELECT 
        "Quantity",
        "Usage date",
        -- 生成每个连续同Quantity组的唯一标识
        SUM(
            CASE WHEN "Quantity" != LAG("Quantity") OVER (ORDER BY "Usage date") 
                 THEN 1 ELSE 0 
            END
        ) OVER (ORDER BY "Usage date") AS group_id
    FROM your_table_name
)
SELECT 
    DENSE_RANK() OVER (ORDER BY group_id) AS RN,
    "Quantity",
    "Usage date"
FROM group_ids
ORDER BY "Usage date";

逻辑说明： #

1. LAG()函数：按Usage date排序后，获取当前行的前一行Quantity值，判断当前行与前一行的Quantity是否一致。
2. 分组标记累加：当当前行Quantity与前一行不同时标记为1，否则为0；对标记累加后，每个连续同Quantity的组会得到相同的group_id。
3. 生成RN：用DENSE_RANK()基于group_id生成连续的RN编号，确保每个连续同Quantity组对应唯一RN。

执行该SQL后，输出结果将完全匹配你预期的格式。

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