postgresql Postgres，CREATE AGGREGATE，访问[非]oxford逗号文本的聚合函数内的索引和行计数

flseospp 于 2023-01-25 发布在 PostgreSQL

关注(0)|答案(2)|浏览(106)

我们的目标是使用CREATE AGGREGATE创建一个名为string_agg_oxford的自定义聚合函数;它是一个聚合函数，其工作方式类似于string_agg，除了它足够聪明，能够知道它聚合了多少项，以便它能够在最后一项之前放置“and”。
因此，string_agg(items, ', ')将返回"item1, item2, item3"，string_agg_oxford(items)将返回"item1, item2, and item3"。
我失败的尝试是从累加器的一个类型开始的，该类型包括总行数和当前行的索引：

CREATE TYPE oxford_accumulator as (
  row_count numeric,
  i numeric,
  acc text
);

现在我们需要累加器函数：

CREATE OR REPLACE FUNCTION oxford_acc (acc oxford_accumulator, curr text)
  RETURNS oxford_accumulator
  LANGUAGE PLPGSQL
  AS $$
BEGIN
  IF acc.i + 1 = acc.row_count THEN
    RETURN (acc.row_count, acc.i + 1, acc.acc || curr);
  END IF;

  IF (acc.i + 2 = acc.row_count) AND (acc.row_count = 2)  THEN
    RETURN (acc.row_count, acc.i + 1, acc.acc || curr || ' and ');
  END IF;

  IF (i + 2 = acc.row_count) THEN
    RETURN (acc.row_count, acc.i + 1, acc.acc || curr || ', and ');
  END IF;

  RETURN (acc.row_count, acc.i + 1, acc.acc || curr || ', ');
END;
$$;

因为累加器已经吞掉了总计数和索引，所以当累加器以X1 M6 N1 X结束时，我们必须释放该信息。

CREATE OR REPLACE FUNCTION oxford_final (acc oxford_accumulator)
  RETURNS text
  LANGUAGE PLPGSQL
  AS $$
BEGIN
  RETURN acc.acc;
END;
$$;

我的想法在这里福尔斯了，我们需要将其全部连接起来，因为似乎没有一种方法来参数化总行数...所以失败吧。

CREATE OR REPLACE AGGREGATE string_agg_oxford (text, row_count numeric) (
  INITCOND = (row_count, 0, ''),
    --         ^^^ fail
  STYPE = oxford_accumulator,
  SFUNC = oxford_acc,
  FINALFUNC = oxford_final
);

我知道使用常规函数也可以实现类似的功能，但是如果有一种方法可以作为聚合器在SELECT string_agg_oxford(clients.full_name) FROM matters GROUP BY matters.matter_id;这样的select语句中使用，我还不准备给予

postgresql

来源：https://stackoverflow.com/questions/75217287/postgres-create-aggregate-accessing-index-and-row-count-inside-aggregate-funct

2条答案

按热度按时间

blpfk2vs1#

对，你不知道你已经完成了，直到你完成了。你必须对数据进行两次传递，一次得到计数，另一次构造字符串。这不是很有效，而且没有明显的方法让PostgreSQL这样做，可以用作一个简单的聚合。我认为你可以使用一个窗口函数来得到总计数。然后是一个带有两个参数（string和total count）的聚合，但我认为您需要将这部分内容写入“外部查询”，否则它将无法访问total count。
最直接的方法是使用与string_agg(x, ', ')相同的累加器和转换函数，但是让终结器函数从累加器中剥离出最后一个值，然后用'，and '将其接回。
或者你可以用(acc text, prev text)定义一个accumulator，让transition函数把prev添加到acc中（如果prev不为空），然后把它的当前值存储到prev中，然后让finalizer用'，and '分隔符把final prev添加到acc中。

赞(0）回复(0）举报 2023-01-25

q1qsirdb2#

1.初始条件必须是“数据类型state_data_type接受的字符串常量”，因此无法传递动态值。
1.出于效率的考虑，“从累加器中剥离最后一个值”会更容易。
1.让我们使用一个唯一的分隔符来缓解匹配““的任何问题，因为这是相当常见的-所以我们将使用一个唯一的分隔符，然后在ffunc中适当地替换它们。

Postgres不支持在其lookaheadAssert中包含捕获组，因此我们将反转字符串并颠倒处理，而不是使用花哨的正则表达式来查找唯一分隔符的最后一次出现。

CREATE OR REPLACE FUNCTION oxford_acc (acc text, curr text)
  RETURNS text
  LANGUAGE PLPGSQL
  AS $$
BEGIN
  RETURN acc || curr || '@$@$';
  --                     ^^^ unique separator, can be anything unique
END;
$$;

CREATE OR REPLACE FUNCTION oxford_final (acc text)
  RETURNS text
  LANGUAGE PLPGSQL
  AS $$
DECLARE
    my_result text;
    my_counter numeric;
BEGIN
    SELECT left(acc, -4) INTO my_result;
    -- ^^^ removes the last separator
    SELECT count(*) FROM regexp_matches(my_result, '@\$@\$', 'g') INTO my_counter;
    
    IF my_counter = 0 THEN
      RETURN my_result;
    END IF;
    
    IF my_counter = 1 THEN
      RETURN regexp_replace(my_result, '@\$@\$', ' and ');
    END IF;
    
    SELECT reverse(my_result) INTO my_result;
    
    SELECT regexp_replace(my_result, '\$@\$@', ' dna ,') INTO my_result;
    
    SELECT reverse(my_result) INTO my_result;
    
    RETURN regexp_replace(
      my_result, 
      '@\$@\$',
      ', ',
      'g'
    );
END;
$$;

CREATE OR REPLACE AGGREGATE oxford_agg (text) (
  INITCOND = '',
  STYPE = text,
  SFUNC = oxford_acc,
  FINALFUNC = oxford_final
);

欢迎提出改进建议！

赞(0）回复(0）举报 2023-01-25

我来回答

postgresql Postgres，CREATE AGGREGATE，访问[非]oxford逗号文本的聚合函数内的索引和行计数

2条答案

相关问题

热门标签

最新问答