在数组上聚合函数 [英] Aggregate functions over arrays
问题描述
我有一个这样的表:
+-----+----------------+
| ID | array300 |
+-----+----------------+
| 100 | {110,25,53,..} |
| 101 | {56,75,59,...} |
| 102 | {65,93,82,...} |
| 103 | {75,70,80,...} |
+-----+----------------+
array300 列是300个元素的数组。我需要有100个元素的数组,每个元素代表 array300 的3个元素的平均值。对于此示例,答案将类似于:
array100
{62.66,...}
{63.33,...}
{80,...}
{78.33,...}
array300 column is an array of 300 elements. I need to have arrays of 100 elements with every element representing the average of 3 elements of array300. For this example the answer will be like:
array100
{62.66,...}
{63.33,...}
{80,...}
{78.33,...}
推荐答案
尝试如下操作:
SELECT id, unnest(array300) as val, ntile(100) OVER (PARTITION BY id) as bucket_num
FROM your_table
此选择
将为每个 array300
提供300条具有相同 id
的记录,并将它们分配给 bucket_num
(第1个元素3个元素,第2个元素3个,依此类推)。
This SELECT
will give you 300 records per array300
with same id
and assing them the bucket_num
(1 for firs 3 elements, 2 for next 3, and so on).
然后使用此选择获取存储桶中元素的 avg
:
Then use this select to get the avg
of elements in the bucket:
SELECT id, avg(val) as avg_val
FROM (...previous select here...)
GROUP BY id, bucket_num
下一步-只需将 avg_val
汇总到数组中即可:
Next - just aggregate the avg_val
into array:
SELECT id, array_agg(avg_val) as array100
FROM (...previous select here...)
GROUP BY id
详细信息:不必要, ntile , array_agg , OVER(按 PARTITION BY)
UPD:尝试此函数:
UPD: Try this function:
CREATE OR REPLACE FUNCTION public.array300_to_100 (
p_array300 numeric []
)
RETURNS numeric [] AS
$body$
DECLARE
dim_start int = array_length(p_array300, 1); --size of input array
dim_end int = 100; -- size of output array
dim_step int = dim_start / dim_end; --avg batch size
tmp_sum NUMERIC; --sum of the batch
result_array NUMERIC[100]; -- resulting array
BEGIN
FOR i IN 1..dim_end LOOP --from 1 to 100.
tmp_sum = 0;
FOR j IN (1+(i-1)*dim_step)..i*dim_step LOOP --from 1 to 3, 4 to 6, ...
tmp_sum = tmp_sum + p_array300[j];
END LOOP;
result_array[i] = tmp_sum / dim_step;
END LOOP;
RETURN result_array;
END;
$body$
LANGUAGE 'plpgsql'
IMMUTABLE
RETURNS NULL ON NULL INPUT;
需要一个 array300
并输出一个 array100
。要使用它:
It takes one array300
and outputs one array100
. To use it:
SELECT id, array300_to_100(array300)
FROM table1;
如果您在理解它时遇到任何问题,请问我。
If you have any problems understanding it - just ask me.
这篇关于在数组上聚合函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!