postgresql / typeorm：搜索数组列中 - 仅返回最高数组＆＃x27;路口

Question

假设我们的图中有2个边缘，每个事件都在它们上观察到许多事件，每个事件都有一个或几个标签：

假设第一个边缘具有8个带有以下标签的事件：ABC ABC AC AC BC AA B.

Second Edge有3个事件：BC，BC，C。

我们希望用户能够

• 通过给定标签集在每个边缘发生了多少事件
• ，这些事件并非相互排斥，也不具有严格的层次结构关系。

我们用2个预聚集的表代表该模式：

边缘表：

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

edgestats表（其中包含通过tag_id与边缘表的关系）：

+------+---------+-----------+---------------+
| 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             |
+------+---------+-----------+---------------+

IE对于每个边缘，这是用户搜索与标签列中的最高匹配子集。没有返回具有ID 5和7的行，因为有关它们的信息已经包含在第4行中。

为什么要返回[A，C] for [a，c，d]搜索？因为由于带有标签D的边缘1上没有数据，因此[a，c]的度量量等于[a，c，d]的数据。

我如何编写查询以返回此内容？

---

如果您只能回答上面的问题，则可以忽略什么是bellow：

如果我需要在[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. 使用@＆gt;操作员：

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

这将返回所有包含A，C或D的行，因此行1,2,3,4,5,7,11,13。然后，可以在代码中滤除所有除最高子集匹配的所有东西。但是使用这种方法，我们无法使用总和和类似的功能，而返回太多行不是好习惯。

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语句，从本质上讲，它增加了比我想要的要复杂得多。

我想知道是否有一种更简单的方法，它在cool grognal中使用数组中的阵列中的数组中仅获得了最高匹配的方法？

Answer 1

您的方法＃3应该没问题，尤其是如果您在 cardinality（edge_stats.tags）上有索引。然而，

数据填充的edgestats表，所有标签的排列都在给定边缘

，我建议还记录“ tags persive gern”的“ 标签” /em>“，在边缘表中。

这样，您可以

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 函数。