sql - 用于计算数组集中出现次数的聚合函数

Question

我有几个大表，其中包含一个存储标识符数组的类似列。这些是分段标识符，表示该行属于哪些分段。

数据的一个非常简化的版本可能如下所示：

| score | segmentIds |
|-------|------------|
| 3     | [1, 2, 3]  |
| 22    | [3, 4, 5]  |
| 15    | [2, 4, 6]  |

几种不同类型的查询需要知道聚合行相对于这些分段标识符的分布。这是通过计算在整组数组中找到的每个标识符的出现次数来完成的。理想情况下，这可以通过保持在数组中找到每个标识符的次数的运行计数来完成。

根据以上数据，取分数的平均值和段的分布，可能会得到以下结果：

avg(score): 13.333
distribution(segmentIds): {1: 1, 2: 2, 3: 2, 4: 2, 5: 1, 6: 1}

我已经编写了一个自定义聚合函数来完成此操作，但我希望可以找到一种更有效的方法（从时间和空间的角度来看）。在最坏的情况下，它需要在几十万行上运行，其中每一行将包含一行，数组中有大约 10-30 个标识符。

CREATE TYPE array_union_type AS (
    a       int[],
    l       int
);

CREATE FUNCTION array_union_all(array_union_type, INT[], int) RETURNS array_union_type
AS $$
BEGIN
  RETURN ROW($1.a + $2, $3);
END;
$$ LANGUAGE PLPGSQL STRICT PARALLEL SAFE IMMUTABLE ;


CREATE FUNCTION array_union_combine(array_union_type, array_union_type) RETURNS array_union_type
AS $$
BEGIN
  RETURN ROW($1.a + $2.a, $1.l);
END;
$$ LANGUAGE PLPGSQL STRICT PARALLEL SAFE IMMUTABLE ;

CREATE FUNCTION array_count_final(array_union_type) RETURNS JSONB
AS $$ SELECT
    jsonb_object_agg(i, c)
FROM
    (
        SELECT
            i,
            COUNT(*) AS c
        FROM
            unnest($1.a) AS t(i)
        GROUP BY
            1) AS t
where
     c >= $1.l
$$ LANGUAGE SQL STRICT PARALLEL SAFE IMMUTABLE ;

CREATE aggregate array_count_agg(int[], int)
(
  SFUNC = array_union_all,
  STYPE = array_union_type,
  INITCOND = '({}, 0)',
  COMBINEFUNC = array_union_combine,
  FINALFUNC = array_count_final,
  PARALLEL = SAFE
);

我创建了自定义类型，因为我需要围绕“限制”参数发送，这最终会丢弃任何出现时间少于给定时间的段。这不是很漂亮，但据我所知，不可能将其他参数传递给最终函数，所以这似乎是实现这一目标的唯一方法。

从 CS 的角度来看，将数组连接在一起数千次然后计算出现次数并不理想。相反，在处理每一行时保持运行计数会很好，但我无法弄清楚如何在 PL/SQL 中表示该状态。

我应该注意，这需要在 AWS RDS 上的 Postgres 上运行，因此我们不能使用 C 函数。

标签： sqlpostgresql