如何按位置对PostgreSQL数组元素求和并按SKU分组汇总

Let's break down how to solve this problem: we need to group by SKU, then sum up the elements in the exact same position across all job output arrays for each product. Here's a practical implementation using PostgreSQL (since it has robust native array support):

Example Query #

First, let's replicate your sample data to test with (replace this CTE with your actual production table):

WITH production_jobs AS (
    SELECT 'A1' AS sku, 123 AS job, ARRAY[2,4,6,5,5,5,5] AS outputs
    UNION ALL
    SELECT 'A1' AS sku, 135 AS job, ARRAY[0,0,0,3,5,7,9] AS outputs
    UNION ALL
    SELECT 'B3' AS sku, 109 AS job, ARRAY[3,2,3,2,3,2,3] AS outputs
    UNION ALL
    SELECT 'C5' AS sku, 144 AS job, ARRAY[5,5,5,5,5,5,5] AS outputs
)
SELECT
    sku,
    -- Sum each position of the array and reconstruct the summed result
    ARRAY[
        SUM(outputs[1]),
        SUM(outputs[2]),
        SUM(outputs[3]),
        SUM(outputs[4]),
        SUM(outputs[5]),
        SUM(outputs[6]),
        SUM(outputs[7])
    ] AS summed_daily_outputs
FROM production_jobs
GROUP BY sku;

How This Works #

1. Extract & Sum: Since we know the arrays are fixed to 7 elements (one per day), we pull the value at each position using outputs[n] (PostgreSQL uses 1-based indexing for arrays) and sum those values across all jobs for the SKU.
2. Reconstruct Array: We wrap the summed values back into a new array using ARRAY[] to get the final 7-day summed output per SKU.

Expected Result #

Notes for Other Databases #

If you're using a database that handles arrays as JSON (like MySQL), adjust the syntax to use JSON functions:

• Use JSON_EXTRACT(outputs, '$[0]') to get elements (MySQL uses 0-based indexing for JSON arrays)
• Rebuild the final array with JSON_ARRAY() after summing each position.

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