主页 / dba / 问题 / 214070
Accepted
ST-DDT
Asked: 2018-08-05 09:21:38 +0800 CST

使自定义聚合函数更易于使用（接受更多输入类型而无需创建变体）

  • 772

最近我在 postgres 中编写了一个自定义聚合函数，它将返回与使用不同列的max/聚合匹配的行的特定列。min

虽然代码本身运行良好，但为我可能需要的每个可能的输入组合创建自定义数据类型有点麻烦。

这是我使用的代码

CREATE TYPE agg_tuple_text AS
(
    exists boolean,
    value numeric,
    text text
);

--------------------------------------------------------------------------------

CREATE FUNCTION valued_min(old_tuple agg_tuple_text, new_value numeric, new_text text)
    RETURNS agg_tuple_text
    LANGUAGE plpgsql
AS $$
    BEGIN
        IF (old_tuple).exists = false THEN
            RETURN (true, new_value, new_text);
        ELSIF (old_tuple).value > new_value THEN
            RETURN (true, new_value, new_text);
        ELSE
            RETURN old_tuple;
        END IF;
    END;
$$;
--------------------------------------------------------------------------------

CREATE FUNCTION unpack_agg_tuple_text(value agg_tuple_text)
    RETURNS text
    LANGUAGE plpgsql
AS $$
BEGIN
    IF (value).exists = false THEN
        RETURN NULL;
    ELSE
        RETURN (value).text;
    END IF;
END
$$;

--------------------------------------------------------------------------------

CREATE AGGREGATE valued_min(numeric, text)
(
        INITCOND = '(false, 0, null)',
        STYPE = agg_tuple_text,
        SFUNC = valued_min,
        FINALFUNC = unpack_agg_tuple_text
);

--------------------------------------------------------------------------------

-- Example
SELECT min(value) as min_value, valued_min(value, name) as min_name, max..., avg... FROM kv;
-- Output:
-- min_value | min_name           | ...
-- ----------+--------------------+----
--     11.11 | this is the lowest | ...

编辑：我的目标是为 TSDB 绘制最小/最大/平均图表，并分别显示最小和最大条目的名称。

有没有办法在不为每种可能的组合创建所有这些的情况下实现这一目标？（也许是 Java 或类似语言中存在的某种通用参数）

  • 值列类型
    • 各种日期/时间类型
    • 数值类型
    • 也许文本
    • （任何可比较的类型）
  • 数据列类型
    • 随便哪种

如果我只能将它用于数据值就足够了，因为它不用于该代码内的任何计算。不幸的是，自定义数据类型中不允许使用任何元素类型。

我已经考虑过使用 json 类型作为输入，但感觉有点不对，因为它丢失了类型信息（尤其是日期/时间类型）。

我使用没有扩展的 Postgres 10，但如果可以使用 postgres 1x 或使用特殊扩展，我愿意尝试。

我也考虑过加入这些值，但后来我遇到了性能问题和具有相同值的潜在重复项/行。

postgresql aggregate

2 个回答

  1. Best Answer

    在进入细节之前——你确定你不是在重新发明轮子吗？这可能会导致非常流行的话题。

    您的查询：

    SELECT min(value) as value, valued_min(value, name) as name FROM kv;

    可以用股票 Postgres 重写为：

    SELECT value, name
FROM   kv
ORDER  BY value
LIMIT  1;

    它也可以在索引或仅索引扫描上(value)或(value, name)用于索引或仅索引扫描使用简单的 btree 索引 -快得多。

    我很确定任何其他示例也可以使用内置功能来解决。要获得每组一行，您的查询将是：

    SELECT grp_col, min(value) AS value, valued_min(value, name) AS name
FROM   kv
GROUP  BY grp_col;

    用。。。来代替：

    SELECT DISTINCT ON (grp_col)
       grp_col, value, name
FROM   kv
ORDER  BY grp_col, value;

    再次，更快。而且用途更广。详细解释：

    • 2

  2. 对不起，我不能发表评论！< 50 声望
    请在接受的答案下发表评论

    后续问题：将附加值聚合到最小值和最大值

    • 0

相关问题