sql - 从一个数据组创建 N 个数组的函数

Question

我是 PL/pgsql 的新手，我想创建一个新函数。

在我的例子中，我有这张表:

Column 1: VARIABLE = 2 2 2 2 2 2 2 2 2 2Column 2: VALUE = 20 20 20 20 180 180 180 180 180 180Column3: STRETCH = 1 1 1 1 1 1 2 2 2 2 

我想做一个函数，从 STRETCH 不同的字段 VALUE 创建 N 个数组，例如:

Array 1: 20,20,20,20,180,180,Array 2: 180,180,180,180,

我该怎么做？

Answer 1

这将是提供测试用例的有用方法:

CREATE TABLE tbl (variable int, value real, stretch int);
INSERT INTO tbl VALUES
  (2, 20, 1)
 ,(2, 20, 1)
 ,(2, 20, 1)
 ,(2, 20, 1)
 ,(2, 180, 1)
 ,(2, 180, 1)
 ,(2, 180, 2)
 ,(2, 180, 2)
 ,(2, 180, 2)
 ,(2, 180, 2);

你问了什么

SELECT variable, stretch, array_agg(value) AS val_arr
FROM   tbl
GROUP  BY 1,2
ORDER  BY 1,2;

你似乎需要什么

I want to do this because then I would like get all my arrays and calculate the mean value each.

SELECT variable, stretch, avg(value) AS val_avg
FROM   tbl
GROUP  BY 1,2
ORDER  BY 1,2;

-> SQLfiddle demo.

Aggregate functions in the manual.

Try a search to find plenty of code examples how to create a function.