聚合查询中拼接数组：气象数据1小时数组聚合问题

Got it, let's tackle this problem step by step. You've got a table with 15-minute weather records, each row holding a 15-element numeric array of 1-minute leaf moisture samples, and you need to roll these up into hourly records with a 60-element array. Let's break down why your initial array_cat attempt failed, then walk through two solid solutions.

First, why did array_cat throw an error? PostgreSQL's built-in array_cat is a binary function—it only takes two arrays and concatenates them. It's not an aggregate function, so you can't just pass a column of arrays to it in a GROUP BY clause and expect it to chain them all together. That's why the system complained about missing the function—you were trying to use it in a context it wasn't designed for.

Solution 1: Unnest the Arrays, Then Re-Aggregate (Recommended) #

This is the most straightforward approach, and it avoids needing to create any custom objects. Here's how it works:

• Unnest each array while preserving the order of elements (using WITH ORDINALITY to track each element's position in the original array).
• Group by hour using date_trunc to bucket your 15-minute rows into hourly chunks.
• Re-aggregate the elements in the correct order (first by the original timestamp to keep 15-minute blocks in sequence, then by the element position to maintain 1-minute order within each block).

Here's the SQL for this, assuming your table is named weather_data with columns timestamp (the 15-minute interval timestamp) and leaf_moisture (the 15-element numeric array):

SELECT
    date_trunc('hour', timestamp) AS hour_timestamp,
    array_agg(moisture ORDER BY timestamp, pos) AS hourly_leaf_moisture
FROM
    weather_data,
    unnest(leaf_moisture) WITH ORDINALITY AS u(moisture, pos)
GROUP BY
    hour_timestamp;

This will reliably produce a 60-element array for each hour, with the moisture samples in the exact 1-minute sequence they were collected.

Solution 2: Create a Custom Array Concatenation Aggregate #

If you prefer a more concise query, you can create a custom aggregate function that wraps array_cat to handle multiple arrays. This lets you concatenate all arrays in a group directly, without unnesting.

First, create the aggregate function (you'll need CREATE AGGREGATE privileges):

CREATE AGGREGATE array_cat_agg(numeric[]) (
    SFUNC = array_cat,
    STYPE = numeric[],
    INITCOND = '{}'
);

Then use it in your query, making sure to order the arrays by timestamp to preserve sequence:

SELECT
    date_trunc('hour', timestamp) AS hour_timestamp,
    array_cat_agg(leaf_moisture ORDER BY timestamp) AS hourly_leaf_moisture
FROM weather_data
GROUP BY hour_timestamp;

This works because the custom aggregate starts with an empty array (INITCOND = '{}') and repeatedly applies array_cat to add each subsequent array in the group (sorted by timestamp) to the result.

Critical Note: Always Order Your Data #

No matter which method you use, never skip the ORDER BY clause in the aggregation. Without it, PostgreSQL doesn't guarantee the order of the arrays/elements in the group, which would scramble your 1-minute moisture data and make the aggregated array useless.

内容的提问来源于stack exchange，提问作者Fabio