如何用Python或Snowflake对连续重复的序列数字去重

Python 实现 #

直接遍历分割后的序列，只保留和前一个元素不同的项即可，代码如下：

核心处理函数 #

def remove_consecutive_duplicates(s):
    items = s.split('->')
    if not items:
        return ''
    result = [items[0]]
    for item in items[1:]:
        if item != result[-1]:
            result.append(item)
    return '->'.join(result)

测试验证 #

把你的测试案例代入运行，就能得到预期结果：

test_cases = [
    "8->8->8->8->5->7",
    "8->5->5->5->7->8->7->7",
    "25->15->15->13->18",
    "25->15->15->13->18->15"
]

for case in test_cases:
    print(f"原序列: {case}")
    print(f"处理后: {remove_consecutive_duplicates(case)}\n")

Snowflake SQL 实现 #

在Snowflake中可以通过拆分字符串为行，结合窗口函数过滤重复项，再重新聚合为目标格式：

假设你的表名为your_table，目标列名为col_a，执行以下SQL即可：

WITH split_data AS (
    SELECT 
        col_a,
        value AS item,
        seq AS rn  -- 利用SPLIT_TO_TABLE自带的seq字段保持原始顺序
    FROM your_table,
    LATERAL SPLIT_TO_TABLE(col_a, '->')
),
filtered_data AS (
    SELECT 
        col_a,
        item,
        rn,
        LAG(item) OVER (PARTITION BY col_a ORDER BY rn) AS prev_item
    FROM split_data
),
unique_items AS (
    SELECT col_a, item, rn
    FROM filtered_data
    WHERE prev_item IS NULL OR item != prev_item
)
SELECT 
    col_a AS 原序列,
    LISTAGG(item, '->') WITHIN GROUP (ORDER BY rn) AS 处理后序列
FROM unique_items
GROUP BY col_a;

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