在PostgreSQL 'where'-statement中使用数组

Question

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

• separability 包含id的int数组和一些不相关的列的数据带组合
• data_bands包含将数组中的id从'可分离性'分配到实际文件和图像波段的信息
• 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

步骤1。在data_bands之后，将row_number添加到avarage_jm_dist以区分连续的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。如果separabilities中有两个(或更多)行具有相同的avarage_jm_dist，则添加row_number。