PostgreSQL / TypeORM：在数组列中搜索数组 - 仅返回最高数组的交集

Question

假设图中有两条边，每条边上都有许多事件，每个事件都有一个或多个与之相关的标签:

假设第一条边有8个事件带有这些标签:ABC ABC AC BC A A b

第二条边有3个事件:BC, BC, c。

我们希望用户能够搜索

• 每条边发生的事件数
• 由一组给定的标签组成，它们不是互斥的，也没有严格的层次关系。

我们用2个预聚合表来表示这个模式:

边表:

+----+
| id |   
+----+
| 1  |
| 2  |  
+----+

EdgeStats表(通过tag_id包含与Edges表的关系):

+------+---------+-----------+---------------+
| id   | edge_id | tags      | metric_amount |
+------+---------+-----------+---------------+
| 1    | 1       | [A, B, C] | 7             |
| 2    | 1       | [A, B]    | 7             |
| 3    | 1       | [B, C]    | 5             |
| 4    | 1       | [A, C]    | 6             |
| 5    | 1       | [A]       | 5             |
| 6    | 1       | [B]       | 4             |
| 7    | 1       | [C]       | 4             |
| 8    | 1       | null      | 7             | //null represents aggregated stats for given edge, not important here.
| 9    | 2       | [B, C]    | 3             |
| 10   | 2       | [B]       | 2             |
| 11   | 2       | [C]       | 3             |
| 12   | 2       | null      | 3             |
+------+---------+-----------+---------------+

请注意，当表有标签[A, B]时，例如，它表示与此标签中的任何一个相关联的事件数量。A或者B，或者两者都有

因为用户可以通过这些标签的任何组合进行过滤，DataTeam用每个给定边观察到的所有标签排列填充EdgeStats表(边是完全相互独立的，但是我正在寻找通过一次查询查询所有边的方法)。

我需要根据用户选择的标签来过滤这个表，比如[A, C, D]。问题是数据中没有标签D。期望的回报是:

+------+---------+-----------+---------------+
| id   | edge_id | tags      | metric_amount |
+------+---------+-----------+---------------+
| 4    | 1       | [A, C]    | 6             |
| 11   | 2       | [C]       | 3             |
+------+---------+-----------+---------------+

。对于每条边，用户搜索的内容与我们在标签列中拥有的内容之间的最高匹配子集。没有返回id为5和7的行，因为关于它们的信息已经包含在第4行中。

为什么对于[A, C, D]搜索返回[A, C] ?因为边1上没有标签D的数据，那么[A, C]的度量量等于[A, C, D]的度量量。

我如何写查询返回这个?

---

如果你能回答上面的问题，你可以忽略下面的内容:

如果我需要通过[A]， [B]或[A, B]进行过滤，问题将是微不足道的-我可以只搜索精确的数组匹配:

query.where("edge_stats.tags = :filter",
{
filter: [A, B],
}
)

然而，在EdgeStats表中，我没有用户可以搜索的所有标签组合(因为它太多了)，所以我需要找到更聪明的解决方案。

这里列出了一些可能的解决方案，都是不完美的:

1. 尝试对用户搜索词的所有子集进行精确匹配-所以如果用户通过标签[A, C, D]搜索，首先尝试查询[A, C, D]，如果没有精确匹配，尝试[C, D]， [A, D]， [A, C]，瞧，我们得到了匹配!
2. 使用@>接线员:

.where(
"edge_stats.tags <@ :tags",
{
tags:[A, C, D],
}
)

这将返回包含A、C或D的所有行，即1、2、3、4、5、7、11、13行。然后就可以过滤掉代码中除了最高子集匹配之外的所有匹配。但是使用这种方法，我们不能使用SUM和类似的函数，并且返回太多的行不是一个好的实践。

3. 基于2)并受此答案启发的方法:

.where(
"edge_stats.tags <@ :tags",
{
tags: [A, C, D],
}
)
.addOrderBy("edge.id")
.addOrderBy("CARDINALITY(edge_stats.tags)", "DESC")
.distinctOn(["edge.id"]);

它所做的是对每条边，找到包含A, C或D的所有标签，并获得最高匹配(高到数组最长)(由于按基数排序并只选择一个)。

所以返回的行确实是4,11。

这种方法很好，但是当我将其用作更大查询的过滤部分时，我需要添加一堆groupBy语句，实际上它增加了比我想要的更多的复杂性。

我想知道是否有一种更简单的方法，即简单地在查询参数中获得表列中数组的最高匹配?

Answer 1

您的方法#3应该是好的，特别是如果您在CARDINALITY(edge_stats.tags)上有一个索引。然而,

DataTeam用每个给定边观察到的所有标签排列填充EdgeStats表

如果您使用预聚合方法，而不是在原始数据上运行查询，我建议还在Edges表中记录每个给定边缘"观察到的标记。

这样，你就可以

SELECT s.edge_id, s.tags, s.metric_amount
FROM "EdgeStats" s
JOIN "Edges" e ON s.edge_id = e.id
WHERE s.tags = array_intersect(e.observed_tags, $1)

从这里使用array_intersect函数。