时间戳

首页 > 解决方案 > AWS Redshift 数据库中字符串列的筛选和分组策略

sql - AWS Redshift 数据库中字符串列的筛选和分组策略

问题描述

如何制定策略以筛选和分组 AWS Redshift 数据库中的字符串列？

Table_Id | Categories          | Value
<ID>     | AAA1; AAA1-1; AAA2  | 10
<ID>     | AAA1; AAA1-2; AAA2  | 15
<ID>     | AAA2                | 5
.....

现在我想根据单个类别过滤记录，例如“AAA1”或“AAA1 和 AAA2”查询的预期输出如下：

Table_Id | Categories         | Value
<ID>     | AAA1               | 25
<ID>     | AAA1-1             | 10
<ID>     | AAA1-2             | 15
<ID>     | AAA2               | 30
.....

因此需要根据各个类别对结果进行分组。请注意，这个问题不能满足我的用例，因为不可能在大量记录上运行正则表达式或 split_part。运行该解决方案需要 4 个多小时才能获取数据。

我们尝试过的其他替代方法：

  1. 为每个可能的组合生成一个哈希值，然后使用此哈希进行查找。但是，这会导致大量的哈希值。
  2. 为每个类别分配一个不同的素数，然后存储素数与值的乘积。但是，这会导致无法存储在数据库中的非常大的数字。

是否有任何其他数学或其他策略可用于解决此问题？

标签： sqlamazon-redshift

解决方案

您需要更好格式的数据进行查询。有两种可能的设计：

单个表，每个属性有一列

Table_Id | Categories          | Value | CAT-AAA1 | CAT-AAA1-1 | CAT-AAA2
<ID>     | AAA1; AAA1-1; AAA2  | 10    | TRUE     | TRUE       | TRUE
<ID>     | AAA1; AAA1-2; AAA2  | 15    | TRUE     | FALSE      | TRUE
<ID>     | AAA2                | 5     | FALSE    | FALSE      | TRUE
.....

这将涉及为每个属性添加一个列，然后运行一些 UPDATE 命令来填充这些列，例如：

UPDATE <table> SET CAT-AAA1 = TRUE WHERE Categories CONTAINS '%AAA1;%'

然后，很容易查询表：

SELECT SUM(Value) FROM <table> WHERE CAT-AAA1 AND CAT-AAA1-2;

Redshift 每个表最多可以处理 1600 列。在数据仓库中拥有宽表是很正常的。

一对多表

此选项将涉及创建一个将每一行链接到多个类别的新表：

Table_Id | Category
1     | AAA1
1     | AAA1-1
1     | AAA1-2
2     | AAA1

然后，您可以通过加入此查找表进行查询以查找正确的行，例如：

SELECT SUM(Value)
FROM <table>
JOIN <lookup-table> USING Table_Id
WHERE Category = 'AAA1';

推荐阅读