时间戳

首页 > 解决方案 > 具有文件数据集唯一标识符的数组聚类

sql - 具有文件数据集唯一标识符的数组聚类

问题描述

我在 s3 中有一个带有大 int 数组列的数据集，我想根据数组值有效地过滤行。我知道我们可以在 sql 表中使用 gin 索引，但需要解决方案来处理 s3 数据集。我计划为数组中的每个元素组合使用集群 ID（因为它们的基数并不大。最大 2500），然后将其存储为新列，稍后可以在该列上应用过滤器。例子，

Table A
+------+------+-----------+
| Col1 | Col2 |   Col3    |
+------+------+-----------+
|    1 |  101 | [123,234] |
|    2 |  102 | [123]     |
|    3 |  103 | [234,345] |
+------+------+-----------+

我正在尝试添加新列，例如，

Table B (column Col3 will be removed from actual schema)
+------+------+-----------+-----------+
| Col1 | Col2 |   Col3    | Cid       |
+------+------+-----------+-----------+
|    1 |  101 | [123,234] |    1      |
|    2 |  102 | [123]     |    2      |
|    3 |  103 | [234,345] |    3      |
+------+------+-----------+-----------+

并且会有另一个 col3 和 Cid 的映射表，

Table C
+-----------+-----+
|   Col3    | Cid |
+-----------+-----+
| [123,234] |   1 |
| [123]     |   2 |
| [234,345] |   3 |
+-----------+-----+

如果创建了新组合，则此表 C 将添加一个新条目，如果添加或删除任何数组元素，则将更新 B。目标是能够根据数组列中的值有效地过滤掉表 A 中的记录。像这样的查询 123 = Any(Col3)可以作为，Cid = 2或者像 [123, 345] = Any(Col3) 这样的查询可以作为Cid in (2,3).

有没有更好的方法来解决这个问题？此外，我正在考虑在运行时创建所需的组合以限制组合的数量。创建最小组合是个好主意吗？

标签： sqlarraysdatabaseapache-sparkanalytics

解决方案

在 Postgres 中，您可以创建表并用于join计算值：

create table array_dim as 
    select col3 as arr, row_number() over (order by min(col1)) as array_id
    from t
    group by col3;

然后，您可以添加新列：

select a.*, ad.array_id
from a join 
     array_dim ad
     on a.col3 = ad.arr

推荐阅读