时间戳

首页 > 解决方案 > redshift - 无法应用 listagg 功能

amazon-web-services - redshift - 无法应用 listagg 功能

问题描述

尝试使用listagg函数时出现错误。

询问

select 
        a.user_name, 
        listagg(a.group_name::text)
within group (order by a.group_name) as group_name
from (
        SELECT 
                usename as user_name, 
                groname as group_name
        FROM 
                pg_user 
        join
                 pg_group
        on 
                pg_user.usesysid = ANY(pg_group.grolist) AND 
                pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group) 
     )a
     
group by user_name

错误

[代码：500310，SQL 状态：XX000] Amazon 无效操作：必须在至少一个用户创建的表上应用一个或多个使用的函数。仅用户表函数的示例有 LISTAGG、MEDIAN、PERCENTILE_CONT 等；

没有一个值是null。

标签： amazon-web-servicesamazon-redshift

解决方案

就像有些功能只能在领导节点上运行一样，有些功能只能在计算节点上运行 - listagg() 就是其中之一。如果您需要在领导者数据上运行 list_agg() ，您可以使用以下几种方法：（抱歉，我现在不在集群中，因此无法直接测试这些 - 我看到您的问题已经过时，并认为我会让您开始. 一粒盐，因为我也不能直接观察你的问题，但我想我知道发生了什么。）

  1. 您可以使用游标保存领导节点中的数据并将其用作 list_agg() 的源。存储过程可以简化这一点。stackoverflow上有这样的例子。
  2. 您可以从领导节点数据中创建一个临时表并在 list_agg() 中使用它，但我希望您需要退出（卸载）并重新进入（复制）集群才能执行此操作。

如果没有某种类型的俯卧撑，就没有从仅领导节点结果到计算节点的直接路径。Redshift 的大型网络集群架构的后果。

更新

我有一些集群时间，这个有几个意想不到的问题。grolist 是一种数组类型，通常不支持集群范围，并且需要用户 pg_group 作为源是关键。所以这将需要上面的#1 和#2。

过程是这样的：

  1. 定义游标来保存 pg_user / pg_group join select 语句的结果
  2. 将光标结果移动到临时表
  3. 使用临时表作为外部源 (list_agg()) 选择

可以编写存储过程来执行 #1 和 #2 以简化事情。因此，您最终会得到以下 SQL：

CREATE OR REPLACE procedure make_user_group()
AS
$$
DECLARE 
  row record;
BEGIN
  --drop table if exists user_group;
  --open cur refcursor;
  create temp table user_group (user_name varchar(256),group_name varchar(256));
  for row in SELECT 
                usename::text as user_name, 
                groname::text as group_name
        FROM 
                pg_user 
        join
                 pg_group
        on 
                pg_user.usesysid = ANY(pg_group.grolist) AND 
                pg_group.groname in (SELECT DISTINCT pg_group.groname from pg_group)
   LOOP
    INSERT INTO user_group(user_name,group_name) VALUES (row.user_name,row.group_name);
  END LOOP;
END;
$$ LANGUAGE plpgsql;

call make_user_group();

select 
        user_name, 
        listagg(group_name::text)
within group (order by group_name) as group_name
from user_group
group by user_name;

显然，存储过程只需要创建一次，但每次需要创建临时表时都会调用。

推荐阅读