sql - 转置 PostgreSQL 表或聚合函数什么都不做

Question

我有一个 PostgreSQL 表，我必须转置它。
原始数据如下所示:

user_id role_id assigned
P0001   SA      t
P0001   Guest   t
P0002   SA      f
P0002   Guest   t

转置之后应该是这样的:

user_id SA  Guest
P0001   t   t
P0002   f   t

下面是我用来生成转置表的:

SELECT user_id, 
CAST(CAST(CASE role_id WHEN 'SA' THEN 1 ELSE 0 END) AS int) AS boolean) AS "SA",
CAST(CAST((CASE role_id WHEN 'Guest' THEN 1 ELSE 0 END) AS int) AS boolean) AS "Guest" 
FROM user_to_roles GROUP BY user_id

但它看起来很丑。有没有一种方法可以编写自定义聚合函数，除了返回原始值外什么都不做。所以上面的语句可以重写为:

SELECT user_id, 
Do_Nothing(CASE role_id WHEN 'SA' THEN true ELSE false END) AS "SA",
Do_Nothing(CASE role_id WHEN 'Guest' THEN true ELSE false END) AS "Guest" 
FROM user_to_roles GROUP BY user_id

编辑:

实际上，我需要一个动态生成的列，并在 this article 的帮助下使用游标实现了它.我查看了文档并编写了一个自定义聚合，如下所示:

CREATE AGGREGATE do_nothing(anyelement) (
  SFUNC=donothing,
  STYPE=anyelement,
  INITCOND=''
);

CREATE OR REPLACE FUNCTION donothing(anyelement var)
  RETURNS anyelement AS
$BODY$
    RETURN var;
$BODY$
  LANGUAGE sql IMMUTABLE
  COST 100;

因为想做成通用的，所以这里使用了anyelment。
但是上面的代码根本不起作用。

Answer 1

交叉表()

SELECT * FROM crosstab(
       'SELECT user_id, role_id, assigned
        FROM   tbl
        ORDER  BY 1,2'

      ,$$VALUES ('SA'::text), ('Guest')$$)
AS ct (user_id text, "SA" bool, "Guest" bool);

这与当前版本的 Postgres 一样快速。 (比下面的替代方案更快。)

• PostgreSQL Crosstab Query

但这两种解决方案都不是完全“动态的”，不完全可能:

• Dynamic alternative to pivot with CASE and GROUP BY

对于完全动态的解决方案，您必须输出数组或其他一些容器类型以返回可变数量的列。最近的相关问题及答案:

• Looping in select query

带有聚合 CASE 表达式的穷人交叉表

仅针对几列:简单的 CASE 语句与聚合函数相结合。

min() 或 max() 适用于大多数基本类型 ... per documentation :

any array, numeric, string, or date/time type

但不适用于 boolean。使用 bool_or() 像 @Richard suggested .
FALSE OR NULL 会产生 NULL，但所有提到的聚合函数(包括。bool_or())忽略 NULL，所以它也适用于 NULL。

SELECT user_id
      ,bool_or(CASE WHEN role_id = 'SA'    THEN assigned ELSE NULL END) AS "SA"
      ,bool_or(CASE WHEN role_id = 'Guest' THEN assigned ELSE NULL END) AS "Guest"
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

您必须适应预期的数据类型。

任何类型的自定义聚合

没有以您需要的方式适用于任何 数据类型的标准聚合函数。你可以创建一个:

CREATE OR REPLACE FUNCTION f_echo1(anyelement,anyelement)
  RETURNS anyelement AS 'SELECT $1' LANGUAGE sql IMMUTABLE STRICT;

CREATE AGGREGATE first_notnull(anyelement) (
   SFUNC = f_echo1
 , STYPE = anyelement
);

这是您在编辑中概述的内容的简单有效版本:
从 NULL(无 INITCOND)开始，使用 STRICT 转换函数，它采用第一个非空值，转换函数只保留该值一。所以我们得到聚合函数遇到的第一个非空值。

这适用于任何输入类型:

SELECT user_id
      ,first_notnull(CASE WHEN role_id = 'SA'    THEN assigned ELSE NULL END) AS "SA"
      ,first_notnull(CASE WHEN role_id = 'Guest' THEN assigned ELSE NULL END) AS "Guest"
FROM   tbl
GROUP  BY 1
ORDER  BY 1;

将bool_or()复制为max()

sql 或 plpgsql 中的自定义聚合函数比用 C 编写并经过优化的内置函数慢得多。你可以自己用 C 写一个。不会那么难。或者你可以复制 pg_proc 中 max 名称下的 bool_or 的定义，并在 pg_aggregate 中的另一个副本中引用它> 使 max() 也适用于 boolean。不过，不建议临时用户乱用目录表。如果你破坏了某些东西，你的系统可能会崩溃......