如何为价格变动时重置的序列生成非唯一ID列？

原始数据表：

product price   date
banana  90  2022-01-01
banana  90  2022-01-02
banana  90  2022-01-03
banana  95  2022-01-04
banana  90  2022-01-05
banana  90  2022-01-06

需要为表添加非唯一ID列，要求每次价格变动时ID随之变更，最终结果如下：

id  product price   date
A   banana  90  2022-01-01
A   banana  90  2022-01-02
A   banana  90  2022-01-03
B   banana  95  2022-01-04
C   banana  90  2022-01-05
C   banana  90  2022-01-06

目前已通过查询生成my_seq列（序列在价格变动时重置），数据如下：

my_seq  rn1 rn2 product price   date
1   1   1   banana  90  2022-01-01
2   2   2   banana  90  2022-01-02
3   3   3   banana  90  2022-01-03
1   1   4   banana  95  2022-01-04
1   4   5   banana  90  2022-01-05
2   5   6   banana  90  2022-01-06 

现在需要将上述结果转换为符合要求的ID列。

核心思路是先识别价格连续的分组，给每个分组分配唯一编号，再将编号转换为字母ID：

方法一：直接基于原始表生成 #

通过窗口函数标记价格变动并分组，再转换为字母ID（以PostgreSQL为例）：

WITH grouped_data AS (
    SELECT 
        product,
        price,
        date,
        -- 标记价格变动：当前行价格与上一行不同则记为1
        CASE WHEN LAG(price) OVER (PARTITION BY product ORDER BY date) != price THEN 1 ELSE 0 END AS price_change,
        -- 累计求和得到每个连续价格组的组号
        SUM(CASE WHEN LAG(price) OVER (PARTITION BY product ORDER BY date) != price THEN 1 ELSE 0 END) OVER (PARTITION BY product ORDER BY date) + 1 AS group_id
    FROM your_table_name
)
SELECT 
    CHR(64 + group_id) AS id, -- 利用ASCII码转换：64对应A的前一位，加组号得到对应字母
    product,
    price,
    date
FROM grouped_data
ORDER BY date;

方法二：基于已生成的my_seq列转换 #

利用my_seq=1的行作为分组起始标记，累计生成组号后转换为字母：

WITH existing_data AS (
    -- 替换为你生成my_seq的查询语句
    SELECT 
        my_seq,
        product,
        price,
        date
    FROM your_existing_query_result
),
grouped AS (
    SELECT 
        *,
        -- 累计my_seq=1的次数得到组号
        SUM(CASE WHEN my_seq = 1 THEN 1 ELSE 0 END) OVER (ORDER BY date) AS group_id
    FROM existing_data
)
SELECT 
    CHR(64 + group_id) AS id,
    product,
    price,
    date
FROM grouped
ORDER BY date;

内容的提问来源于stack exchange，提问作者大朱雀