时间戳

首页 > 解决方案 > PostgreSQL: How to return a subarray dynamically using array slices in postgresql

sql - PostgreSQL: How to return a subarray dynamically using array slices in postgresql

问题描述

I need to sum a subarray from an array using postgresql.

I need to create a postgresql query that will dynamically do this as the upper and lower indexes will be different for each array.

These indexes will come from two other columns within the same table.

I had the below query that will get the subarray:

SELECT 
    SUM(t) AS summed_index_values
FROM 
    (SELECT UNNEST(int_array_column[34:100]) AS t 
     FROM array_table
     WHERE id = 1) AS t;

...but I then realised I couldn't use variables or SELECT statements when using array slices to make the query dynamic: 


int_array_column[SELECT array_index_lower FROM array_table WHERE id = 1; : SELECT array_index_upper FROM array_table WHERE id = 1;]

...does anyone know how I can achieve this query dynamically?

标签： sqlarrayspostgresqlsumslice

解决方案

不需要子选择，只需使用列名：

SELECT SUM(t) AS summed_index_values
FROM (
    SELECT UNNEST(int_array_column[tb.array_index_lower:tb.array_index_upper])  AS t 
    FROM array_table tb
    WHERE id = 1
) AS t;

请注意，不建议在 SELECT 列表中使用 set-returning 函数（unnest）。最好将其放入 FROM 子句中：

SELECT sum(t.val)
FROM (
    SELECT t.val
    FROM array_table tb
       cross join UNNEST(int_array_column[tb.array_idx_lower:array_idx_upper])  AS t(val)
    WHERE id = 1
) AS t;

推荐阅读