如何在AWS Redshift中提取列中子串用于过滤与分组操作

嘿，我来帮你搞定Redshift里这个类别拆分、过滤和分组的需求！根据你的数据示例和期望输出，咱们可以通过Redshift的字符串拆分函数结合分组聚合来实现，下面分情况给你详细说明：

首先，我们需要把每条记录中用分号分隔的Categories字段拆分成单独的行，这样才能按单个类别进行分组。Redshift的REGEXP_SPLIT_TO_TABLE函数正好可以干这个活，记得用TRIM去掉类别前后的空格（因为你的数据里分号后面有空格）。

基础拆分的SQL片段：

SELECT 
    t.Table_Id,
    TRIM(split_cat) AS Category,
    t.Value
FROM 
    your_table t,
    REGEXP_SPLIT_TO_TABLE(t.Categories, ';') AS split_cat

这段代码会把每条记录的多个类别拆成多行，比如第一条记录会拆成3行：ABC1、ABC1-1、XYZ，每行对应原记录的Value。

如果你想过滤所有包含ABC1相关的类别（包括ABC1本身、ABC1-1、ABC1-2），然后按单个类别分组求和，可以用LIKE匹配前缀，再结合SUM聚合：

SELECT 
    t.Table_Id,
    TRIM(split_cat) AS Categories,
    SUM(t.Value) AS Value
FROM 
    your_table t,
    REGEXP_SPLIT_TO_TABLE(t.Categories, ';') AS split_cat
WHERE 
    TRIM(split_cat) LIKE 'ABC1%' -- 匹配所有以ABC1开头的类别
GROUP BY 
    t.Table_Id, TRIM(split_cat)
ORDER BY 
    Categories;

执行这个查询后，你会得到期望的结果：

这里分两种常见场景：

场景A：记录包含任意一个指定类别（比如ABC1或XYZ） #

如果只要记录包含ABC1或XYZ就保留，然后拆分分组：

SELECT 
    t.Table_Id,
    TRIM(split_cat) AS Categories,
    SUM(t.Value) AS Value
FROM 
    your_table t,
    REGEXP_SPLIT_TO_TABLE(t.Categories, ';') AS split_cat
WHERE 
    TRIM(split_cat) LIKE 'ABC1%' 
    OR TRIM(split_cat) = 'XYZ'
GROUP BY 
    t.Table_Id, TRIM(split_cat)
ORDER BY 
    Categories;

这个结果会包含ABC1、ABC1-1、ABC1-2和XYZ的求和值，其中XYZ的总和是10+15+5=30。

场景B：记录同时包含所有指定类别（比如同时有ABC1和XYZ） #

如果需要筛选出同时包含ABC1和XYZ的记录，再拆分分组，就要先判断原记录的Categories是否同时包含这两个关键词：

SELECT 
    t.Table_Id,
    TRIM(split_cat) AS Categories,
    SUM(t.Value) AS Value
FROM 
    your_table t,
    REGEXP_SPLIT_TO_TABLE(t.Categories, ';') AS split_cat
WHERE 
    t.Categories LIKE '%ABC1%' 
    AND t.Categories LIKE '%XYZ%' -- 确保记录同时包含两个类别
GROUP BY 
    t.Table_Id, TRIM(split_cat)
ORDER BY 
    Categories;

这个查询只会选中前两条记录（它们同时有ABC1和XYZ），结果里XYZ的总和是10+15=25。

• 确保你的Redshift版本支持REGEXP_SPLIT_TO_TABLE函数，这个函数在Redshift中是可用的，属于字符串处理函数。
• 如果Categories里存在空的类别（比如分号连续出现），可以在WHERE条件里加TRIM(split_cat) != ''来过滤掉空值。

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