Snowflake SQL实操：按字符串匹配规则删除符合条件的表数据行

现有Snowflake中的业务表，需按照指定规则过滤数据行，相关信息如下：

样例表结构与原始数据 #

ID      TIMESTAMP                  GROUP
001     2021-04-01 12:51:12.063    AppleA    
001     2021-04-04 12:51:12.063    Apple-Direct
001     2021-04-14 10:47:03.022    AppleA
002     2021-01-13 09:46:23.012    BananaA
003     2021-09-10 03:32:53.043    Banana-Direct
004     2021-04-13 01:12:54.056    Grape-Direct
004     2021-04-13 11:12:26.054    AppleA
004     2021-04-13 21:53:36.023    GrapeA
005     2021-04-01 13:53:13.023    BananaO
005     2021-04-11 13:53:13.023    Banana-Direct
003     2022-04-13 20:32:11.011    Banana-Direct
006     2021-08-13 20:32:11.011    GrapeO
006     2021-08-13 20:32:11.011    GrapeA
007     2021-08-13 20:32:11.011    Grape-Direct
007     2021-08-13 20:32:11.011    BananaA

过滤规则 #

需删除满足以下全部条件的行：

• 同一ID下GROUP字段的去重取值数量恰好为2
• 两个取值中一个带-Direct后缀，另一个为同前缀（前缀仅包含Apple、Banana、Grape三类）的非Direct取值

其余场景所有行全部保留，明细规则如下：

1. 同ID下GROUP去重取值为1个：保留全部行
2. 同ID下GROUP去重取值大于2个：保留全部行
3. 同ID下GROUP去重取值为2个时：
   • 若其中1个带-Direct后缀，且两个取值前缀相同：删除带-Direct的行
   • 若其中1个带-Direct后缀但前缀不相同：保留全部行
   • 若没有带-Direct后缀的取值：保留全部行

符合删除条件的GROUP匹配组合仅为以下6类：

AppleA & Apple-Direct
AppleO & Apple-Direct
GrapeA & Grape-Direct
GrapeO & Grape-Direct
BananaA & Banana-Direct
BananaO & Banana-Direct

预期输出结果 #

ID      TIMESTAMP                  GROUP
001     2021-04-01 12:51:12.063    AppleA    
001     2021-04-14 10:47:03.022    AppleA
002     2021-01-13 09:46:23.012    BananaA
003     2021-09-10 03:32:53.043    Banana-Direct
004     2021-04-13 01:12:54.056    Grape-Direct
004     2021-04-13 11:12:26.054    AppleA
004     2021-04-13 21:53:36.023    GrapeA
005     2021-04-01 13:53:13.023    BananaO
003     2022-04-13 20:32:11.011    Banana-Direct
006     2021-08-13 20:32:11.011    GrapeO
006     2021-08-13 20:32:11.011    GrapeA
007     2021-08-13 20:32:11.011    Grape-Direct
007     2021-08-13 20:32:11.011    BananaA

查询过滤结果 #

如果仅需查询符合要求的结果，可使用如下SQL：

WITH group_info AS (
    SELECT 
        *,
        COUNT(DISTINCT "GROUP") OVER (PARTITION BY ID) AS group_distinct_cnt,
        CASE 
            WHEN "GROUP" LIKE '%-Direct' THEN SPLIT_PART("GROUP", '-', 1)
            ELSE REGEXP_REPLACE("GROUP", '[AO]$', '')
        END AS group_prefix,
        "GROUP" LIKE '%-Direct' AS is_direct
    FROM your_table_name
),
prefix_match AS (
    SELECT 
        *,
        COUNT(DISTINCT group_prefix) OVER (PARTITION BY ID) AS prefix_distinct_cnt,
        BOOLOR_AGG(is_direct) OVER (PARTITION BY ID) AS has_direct
    FROM group_info
)
SELECT ID, "TIMESTAMP", "GROUP"
FROM prefix_match
WHERE NOT (
    group_distinct_cnt = 2 
    AND prefix_distinct_cnt = 1 
    AND has_direct = TRUE 
    AND is_direct = TRUE
);

直接删除表中不符合要求的行 #

如果需要直接对原表执行删除操作，可使用如下SQL：

DELETE FROM your_table_name t1
USING (
    SELECT 
        ID, "TIMESTAMP", "GROUP"
    FROM (
        SELECT 
            *,
            COUNT(DISTINCT "GROUP") OVER (PARTITION BY ID) AS group_distinct_cnt,
            CASE 
                WHEN "GROUP" LIKE '%-Direct' THEN SPLIT_PART("GROUP", '-', 1)
                ELSE REGEXP_REPLACE("GROUP", '[AO]$', '')
            END AS group_prefix,
            "GROUP" LIKE '%-Direct' AS is_direct,
            COUNT(DISTINCT group_prefix) OVER (PARTITION BY ID) AS prefix_distinct_cnt,
            BOOLOR_AGG(is_direct) OVER (PARTITION BY ID) AS has_direct
        FROM your_table_name
    )
    WHERE group_distinct_cnt = 2 
      AND prefix_distinct_cnt = 1 
      AND has_direct = TRUE 
      AND is_direct = TRUE
) t2
WHERE t1.ID = t2.ID 
  AND t1."TIMESTAMP" = t2."TIMESTAMP" 
  AND t1."GROUP" = t2."GROUP";

注意：GROUP和TIMESTAMP是SQL保留关键字，引用时需要用双引号包裹，执行前请将your_table_name替换为实际的表名。

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