PostgreSQL如何按小时聚合JSON中value值并输出指定JSON格式？

需求背景 #

原查询按分钟分组，将dbp_istasyondata表中指定站点、渠道的字段打包成JSON数组返回：

select datetime,array_to_json(array_agg(json_build_object('parameter',parameter,'channel_id',channel_id,'value',value,'status',status,'units',units)))  as parameters
from dbp_istasyondata 
where site_id=10 
  and channel_id IN (0,1,2,3,4) 
  and datetime between '2022-12-01T00:00:00' and '2022-12-01T01:30:00'
group by 1
order by 1;

返回结果示例（每分钟一条，包含对应渠道的JSON数组）：

"datetime"  "parameters"
"2022-12-01 00:00:00"   "[{\"channel_id\" : 0, \"value\" : 7.72},{\"channel_id\" : 1, \"value\" : 1593.87}]"
"2022-12-01 00:01:00"   "[{\"channel_id\" : 1, \"value\" : 1612.26},{\"channel_id\" : 0, \"value\" : 7.72}]"
"2022-12-01 00:02:00"   "[{\"channel_id\" : 0, \"value\" : 7.72},{\"channel_id\" : 1, \"value\" : 1615.36}]"
"2022-12-01 00:03:00"   "[{\"channel_id\" : 0, \"value\" : 7.72},{\"channel_id\" : 1, \"value\" : 1625.99}]"
"2022-12-01 00:04:00"   "[{\"channel_id\" : 0, \"value\" : 7.71},{\"channel_id\" : 1, \"value\" : 1623.12}]"
"2022-12-01 00:05:00"   "[{\"channel_id\" : 0, \"value\" : 7.72},{\"channel_id\" : 1, \"value\" : 1638.58}]"
"2022-12-01 01:00:00"   "[{\"channel_id\" : 0, \"value\" : 7.74},{\"channel_id\" : 1, \"value\" : 1647.09}]"
"2022-12-01 01:01:00"   "[{\"channel_id\" : 0, \"value\" : 7.74},{\"channel_id\" : 1, \"value\" : 1656.71}]"
"2022-12-01 01:02:00"   "[{\"channel_id\" : 1, \"value\" : 1646.86},{\"channel_id\" : 0, \"value\" : 7.74}]"
"2022-12-01 01:03:00"   "[{\"channel_id\" : 1, \"value\" : 1656.34},{\"channel_id\" : 0, \"value\" : 7.74}]"
"2022-12-01 01:04:00"   "[{\"channel_id\" : 1, \"value\" : 1652.63},{\"channel_id\" : 0, \"value\" : 7.74}]"
"2022-12-01 01:05:00"   "[{\"channel_id\" : 0, \"value\" : 7.74},{\"channel_id\" : 1, \"value\" : 1648.01}]"

现在需要按小时聚合，计算每个channel_id对应的value总和，输出格式保持和原查询一致的JSON数组结构，预期结果示例：

"datetime"  "parameters"
"2022-12-01 00:00:00"   "[{\"channel_id\" : 0, \"value\" : 46.59},{\"channel_id\" : 1, \"value\" : 9609.18}]"
"2022-12-01 01:00:00"   "[{\"channel_id\" : 0, \"value\" : 38.7},{\"channel_id\" : 1, \"value\" : 8257.64}]"

问题分析 #

用户尝试的查询仅针对单个渠道计算总和，且未将结果打包成JSON数组，不符合需求：

select date_trunc('hour', datetime), SUM (value) as total 
from dbp_istasyondata 
where channel_id=3 
  and site_id=16 
  and datetime between '2022-11-01T00:00:00' and '2022-12-01T04:00:00' 
group by 1;

解决方案 #

需要先按小时和渠道分组计算总和，再将同一小时的渠道数据聚合为JSON数组：

select 
    date_trunc('hour', datetime) as datetime,
    array_to_json(
        array_agg(
            json_build_object(
                'channel_id', channel_id,
                'value', sum_value,
                'parameter', parameter,
                'units', units
                -- 如需保留status字段，可在此补充，注意需确保同一渠道的status值唯一
            )
        )
    ) as parameters
from (
    -- 子查询：按小时+渠道分组，计算每个渠道每小时的value总和
    select 
        date_trunc('hour', datetime),
        channel_id,
        parameter,
        units,
        sum(value) as sum_value
    from dbp_istasyondata 
    where site_id=10 
      and channel_id IN (0,1,2,3,4) 
      and datetime between '2022-12-01T00:00:00' and '2022-12-01T01:30:00'
    group by date_trunc('hour', datetime), channel_id, parameter, units
) as hourly_channel_sums
group by datetime
order by datetime;

说明 #

1. 子查询先按小时+渠道分组，计算每个渠道每小时的value总和，同时保留parameter、units等需展示的字段，这些字段必须加入group by，确保同一渠道的对应值唯一。
2. 外层查询按小时分组，将同一小时内的所有渠道汇总数据打包成JSON数组，输出格式与原查询完全一致。

内容的提问来源于stack exchange，提问作者Fırat Badur