sql - 在 PostgreSQL 'where' 语句中使用数组

Question

我有一个由三个表组成的 PostgreSQL 数据库结构

• separabilities 包含 id 的 int 数组中的数据带组合和一些与此处无关的列
• data_bands 包含将数组中的 id 从“separabilities”分配给实际文件和图像带的信息
• thematic_classes 包含有关“分离度”中使用的类的信息(不是我的问题的导入)

表定义:

CREATE TABLE separabilities (
 data_bands integer[] NOT NULL,
 thematic_class1 integer NOT NULL,
 thematic_class2 integer NOT NULL,
 jm_dist double precision NOT NULL )

CREATE TABLE thematic_classes (
 id integer NOT NULL,
 file_name text NOT NULL )

CREATE TABLE data_bands (
 id integer NOT NULL,
 file_name text NOT NULL,
 band integer NOT NULL )

我想要的是一个查询，它给我按 data_bands 分组的平均分离度和一个数组，其中包含与分离度中的 data_bands 数组元素相对应的实际文件名和波段。

在没有连接到文件名的情况下，查询工作并且看起来像这样:

select 
    sep.data_bands, 
    sum(sep.jm_dist)/count(sep.data_bands) as avarage_jm_dist 
from separabilities as sep 
group by sep.data_bands 
order by avarage_jm_dist

结果的示例行:{10,11} ; 0,7654

我需要的是:{10,11} ; {filename1:bandnumber, filename2:bandnumber}; 0,7654

Answer 1

第一步，取消data_bands嵌套，在avarage_jm_dist中加入row_number，区分连续的sep条目:

    select 
        unnest(sep.data_bands) as bands, 
        array[sep.jm_dist/array_length(sep.data_bands, 1),
            row_number() over (order by sep.jm_dist)] as avarage_jm_dist
    from separabilities as sep

第 2 步。根据未嵌套的 bands 加入 data_bands 并将结果聚合到按 avarage_jm_dist 分组的数组中

select 
    array_agg(sub.bands) as bands, 
    array_agg(b.file_name|| ' : '|| b.band) as file_names, 
    avarage_jm_dist[1]
from (
    select 
        unnest(sep.data_bands) as bands, 
        array[sep.jm_dist/array_length(sep.data_bands, 1),
            row_number() over (order by sep.jm_dist)] as avarage_jm_dist
    from separabilities as sep
    ) sub
join data_bands b on b.id = bands
group by avarage_jm_dist
order by avarage_jm_dist

注意1.我改错了

sum(sep.jm_dist)/count(sep.data_bands)

到

sep.jm_dist/array_length(sep.data_bands, 1)

因为第一个表达式总是给出 sep.jm_dist。

注意 2. 添加 row_number 以防 separabilities 中有两行(或更多行)具有相同的 avarage_jm_dist。