PostgreSQL中固定大小的JSONB数组的聚合

发布时间：2020-12-13 18:10:54 所属栏目：百科来源：网络整理

导读：我正在努力在PostgreSQL数据库中的 JSONB字段上进行聚合.这可能更容易用一个例子来解释,所以如果使用2列(id和分析)创建并填充一个名为analyze的表,如下所示： – create table analysis ( id serial primary key,analysis jsonb);insert into analysis (id,a

我正在努力在PostgreSQL数据库中的 JSONB字段上进行聚合.这可能更容易用一个例子来解释,所以如果使用2列(id和分析)创建并填充一个名为analyze的表,如下所示： –

create table analysis (
  id serial primary key,analysis jsonb
);

insert into analysis 
  (id,analysis) values
  (1,'{"category" : "news","results" : [1,2,3,4,5,6,7,8,9,10,11,12,13,14,null,null]}'),(2,"results" : [11,15,16,17,18,19,20,21,22,23,24,26]}'),(3,"results" : [31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46]}'),(4,'{"category" : "sport","results" : [51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66]}'),(5,"results" : [71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86]}'),(6,'{"category" : "weather","results" : [91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106]}');

如您所见,分析JSONB字段始终包含2个属性类别和结果. results属性将始终包含一个大小为16的固定长度数组.我使用了各种函数,例如jsonb_array_elements,但我要做的是以下内容： –

>按分析分组 – >’类别’
>每个数组元素的平均值

当我想要的是一个声明,返回按类别(即新闻,运动和天气)分组的3行和包含平均值的16个固定长度数组.更复杂的是,如果数组中有空值,那么我们应该忽略它们(即我们不是简单地按行数求和和求平均值).结果应如下所示： –

category  | analysis_average
-----------+--------------------------------------------------------------------------------------------------------------
 "news"    | [14.33,15.33,16.33,17.33,18.33,19.33,20.33,21.33,22.33,23.33,24.33,25.33,26.33,27.33,36]
 "sport"   | [61,66,67,68,69,70,71,76]
 "weather" | [91,00,106]

注意：请注意第1行最后2个数组中的45和36,这说明忽略了nullss.

我曾考虑创建一个视图,将数组分解为16列,即

create view analysis_view as
select a.*,(a.analysis->'results'->>0)::int as result0,(a.analysis->'results'->>1)::int as result1
/* ... etc for all 16 array entries .. */
from analysis a;

这对我来说似乎非常不优雅,并且首先消除了使用数组的优点,但可能会使用这种方法一起破解某些东西.

任何指针或提示将非常感谢！

此外,性能非常重要,因此性能越高越好！

这适用于任何数组长度

select category,array_agg(average order by subscript) as average
from (
    select
        a.analysis->>'category' category,subscript,avg(v)::numeric(5,2) as average
    from
        analysis a,lateral unnest(
            array(select jsonb_array_elements_text(analysis->'results')::int)
        ) with ordinality s(v,subscript)
    group by 1,2
) s
group by category
;
 category |                                                 average                                                  
----------+----------------------------------------------------------------------------------------------------------
 news     | {14.33,45.00,36.00}
 sport    | {61.00,62.00,63.00,64.00,65.00,66.00,67.00,68.00,69.00,70.00,71.00,72.00,73.00,74.00,75.00,76.00}
 weather  | {91.00,92.00,93.00,94.00,95.00,96.00,97.00,98.00,99.00,100.00,101.00,102.00,103.00,104.00,105.00,106.00}

table functions – with ordinality

lateral

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!