如何在Snowflake SQL中为分组添加价格变更增量计数器

原始数据 #

product start       end         price
A       08/04/2022  12/04/2022  29
A       13/04/2022  12/05/2022  45      
A       13/05/2022  25/12/2022  45
A       26/12/2022  16/05/2023  29
A       17/05/2023  23/05/2023  29
A       24/05/2023  31/12/9999  49

需求说明 #

针对同一产品，若当前行价格与上一行不同则计数器加1，否则沿用上方计数器值，最终输出如下：

product start       end         price  counter
A       08/04/2022  12/04/2022  29     1
A       13/04/2022  12/05/2022  45     2      
A       13/05/2022  25/12/2022  45     2
A       26/12/2022  16/05/2023  29     3
A       17/05/2023  23/05/2023  29     3
A       24/05/2023  31/12/9999  49     4

1. SQL 解决方案 #

适用于MySQL 8.0+、PostgreSQL、SQL Server等支持窗口函数的数据库：

SELECT
    product,
    start,
    end,
    price,
    SUM(CASE WHEN price != LAG(price) OVER (PARTITION BY product ORDER BY start) THEN 1 ELSE 0 END) OVER (PARTITION BY product ORDER BY start) + 1 AS counter
FROM
    price_table;

• 逻辑：用LAG(price)获取同一产品的上一行价格，对比当前行价格，不同则标记为1，相同为0；对标记值累计求和后加1，得到连续的计数器值。

2. Python Pandas 解决方案 #

import pandas as pd

# 假设数据已加载为DataFrame
df = pd.DataFrame({
    'product': ['A']*6,
    'start': ['08/04/2022', '13/04/2022', '13/05/2022', '26/12/2022', '17/05/2023', '24/05/2023'],
    'end': ['12/04/2022', '12/05/2022', '25/12/2022', '16/05/2023', '23/05/2023', '31/12/9999'],
    'price': [29, 45, 45, 29, 29, 49]
})

# 生成计数器列
df['counter'] = df.groupby('product')['price'].apply(lambda x: (x.diff() != 0).cumsum() + 1)

# 打印结果
print(df.to_string(index=False))

• 逻辑：按产品分组后，用diff()计算价格变化，cumsum()累计变化次数，加1得到初始计数器值1。

3. Excel 解决方案 #

假设数据位于A2:D7区域：

• 在E2单元格输入1（第一行计数器初始值）
• 在E3单元格输入公式：=IF(D3=D2,E2,E2+1)，下拉填充至E7
• 逻辑：判断当前行价格与上一行是否相同，相同则沿用计数器值，不同则加1。

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