python - 将查询结果附加到 PostgreSQL 中的同一结果行 - Redshift

Question

我有一个表，有 3 列 A、B、C - 其中 A 不是主键。我们需要为每个不同的 A(按 A 分组)选择 B、C 对，并将结果附加到最终结果集的末尾。这在 sql 中可能吗？

A | B | C
a1| b1| c1
a1| b2| c2
a1| b3| c3
a2| b1| c2
a2| b2| c5

我需要得到

a1 | (c1,b1) ; (c2,b2);(c3;b3) 
a2 | (c2,b1) ; (c5,b2) 

作为最后附加的行。我通常通过 sqlalchemy 执行此操作，然后最终在 Python 中转换数据，有没有一种方法可以直接在 SQL 中执行此操作？

编辑和开放问题:red shift (Postgres 8.0.2) 中 string_agg() 的替代方法是什么 - 有关上述用例的更多信息。

在使用 string_agg 时，我得到 ERROR: function string_agg(text, "unknown") does not exist 提示:没有函数匹配给定的名称和参数类型。您可能需要添加显式类型转换

编辑 2:使用自定义聚合函数添加错误

An error occurred when executing the SQL command:
CREATE FUNCTION cut_semicolon(text) RETURNS text AS $$
BEGIN
  RETURN SUBSTRING($1 FROM 4)

ERROR: unterminated dollar-quoted string at or near "$$
BEGIN
  RETURN SUBSTRING($1 FROM 4)"
  Position: 53

CREATE FUNCTION cut_semicolon(text) RETURNS text AS $$
                                                    ^

Execution time: 0.24s
(Statement 1 of 7 finished)

0 rows affected
END executed successfully

Execution time: 0.22s
(Statement 2 of 7 finished)

An error occurred when executing the SQL command:
$$ LANGUAGE 'plpgsql' IMMUTABLE

ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE 'plpgsql' IMMUTABLE"
  Position: 1

$$ LANGUAGE 'plpgsql' IMMUTABLE
^

Execution time: 0.22s
(Statement 3 of 7 finished)

An error occurred when executing the SQL command:
CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS $$
BEGIN
  RETURN $1 || ' ; ' || $2

ERROR: unterminated dollar-quoted string at or near "$$
BEGIN
  RETURN $1 || ' ; ' || $2"
  Position: 62

CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS $$
                                                             ^

Execution time: 0.22s
(Statement 4 of 7 finished)

0 rows affected
END executed successfully

Execution time: 0.22s
(Statement 5 of 7 finished)

An error occurred when executing the SQL command:
$$ LANGUAGE 'plpgsql' IMMUTABLE

ERROR: unterminated dollar-quoted string at or near "$$ LANGUAGE 'plpgsql' IMMUTABLE"
  Position: 1

$$ LANGUAGE 'plpgsql' IMMUTABLE
^

Execution time: 0.22s
(Statement 6 of 7 finished)

An error occurred when executing the SQL command:
CREATE AGGREGATE concat_semicolon(
  BASETYPE=text,
  SFUNC=concat_semicolon,
  STYPE=text,
  FINALFUNC=cut_semicolon,
  INITCOND=''
)

ERROR: SQL command "CREATE AGGREGATE concat_semicolon(
  BASETYPE=text,
  SFUNC=concat_semicolon,
  STYPE=text,
  FINALFUNC=cut_semicolon,
  INITCOND=''
)" not supported.

Execution time: 0.23s
(Statement 7 of 7 finished)


5 statements failed.
Script execution finished
Total script execution time: 1.55s

也翻了一下谷歌群里的一个相关答案，&好像是把分隔符“;”换掉了可能有帮助？ - 虽然我不确定，哪个；在此函数定义中替换。引用:https://groups.google.com/forum/#!topic/sql-workbench/5LHVUXTm3BI

编辑 3:也许，Redshift 不支持创建函数本身？ “错误:不支持创建函数” 2013 年的一个线程这样说 forums.aws.amazon.com/thread.jspa?threadID=121137

编辑 4 :

select A, concat(concat(concat(C, ',' ) , cast(B as varchar)), ',')
from  my_table
group by A,B,C


-- Is it ok to group by all A,B, C - since I can't group by A alone, which removes the related "C" columns-- 

gives -:
a1 c1b1b2b3
a2 c2b1b2

但不是 C 的所有条目(并且带有分号)

a1 c1,b1;c2,b2;c2,b3
a2 c2,b1;c5,b2

但我想要中间的逗号 & 还需要知道按 A、B、C 分组是否可以？

Answer 1

PostgreSQL

SELECT
  a,
  STRING_AGG('(' || c || ',' || b || ')', ' ; ')
FROM
  tbl
GROUP BY
  a;

编辑:对于 9.0 之前(引入 STRING_AGG 时)甚至 8.4 之前(添加 ARRAY_AGG 时)的 PostgreSQL 版本，您可以创建自己的 custom aggregate function .

编辑2:对于8.0之前的版本(可能Amazon Redshift不知何故是基于PostgreSQL 7.4的)不支持$$语法，所以函数体需要用引号括起来，引号里面 body 需要逃脱。

CREATE FUNCTION cut_semicolon(text) RETURNS text AS '
BEGIN
  RETURN SUBSTRING($1 FROM 4);
END;
' LANGUAGE 'plpgsql' IMMUTABLE;


CREATE FUNCTION concat_semicolon(text, text) RETURNS text AS '
BEGIN
  RETURN $1 || '' ; '' || $2;
END;
' LANGUAGE 'plpgsql' IMMUTABLE;

CREATE AGGREGATE concat_semicolon(
  BASETYPE=text,
  SFUNC=concat_semicolon,
  STYPE=text,
  FINALFUNC=cut_semicolon,
  INITCOND=''
);

然后改用该聚合。

SELECT
  a,
  CONCAT_SEMICOLON('(' || c || ',' || b || ')')
FROM
  tbl
GROUP BY
  a;

---

MySQL

SELECT
  a,
  GROUP_CONCAT(CONCAT('(', c, ',', b, ')') SEPARATOR ' ; ')
FROM
  tbl
GROUP BY
  a;