Postgres中jsonb数据按日期及10分钟时段聚合需求

我的数据库表中有一个jsonb字段，存储的数据如下：

{"access": {
  "D2024.06.13": [{"qty": 1, "time": "12:05"}, {"qty": 3, "time": "12:32"}],
  "D2024.06.14": [{"qty": 1, "time": "08:37"}]
}}

希望将数据按日期分组，并按10分钟时段（00:00至23:50）进行分组，得到如下格式的结果：

2024.06.13
12:00 : 1
12:10 : 0
12:20 : 0
12:30 : 3
12:40 : 0
...

{'2024.06.13' : {'12:00':1, '12:10':0, '12:20':0, '12:30':3, '12:40':0 ....}, '2024.06.14' : {'08:30':1 ....}

以便后续绘制时间线。

步骤1：拆解JSONB数据 #

先把嵌套的JSON结构展开为扁平行，提取日期、时间和数量：

SELECT
  REPLACE(access_date.key, 'D', '') AS date_str,
  (access_time->>'time')::time AS access_time,
  (access_time->>'qty')::int AS qty
FROM
  your_table,
  jsonb_each(your_jsonb_column->'access') AS access_date,
  jsonb_to_recordset(access_date.value) AS access_time(time text, qty int);

执行后会得到如下结构的结果：

步骤2：生成全量10分钟时段 #

用generate_series生成一天内所有10分钟间隔的起始时段：

SELECT
  to_char(interval '10 minutes' * n, 'HH24:MI') AS time_slot
FROM
  generate_series(0, 143) AS n; -- 24*6=144个时段，覆盖00:00到23:50

步骤3：关联聚合得到时段统计 #

将拆解后的数据与全量时段做关联，按日期和时段求和，缺失的时段补0：

WITH all_time_slots AS (
  SELECT to_char(interval '10 minutes' * n, 'HH24:MI') AS time_slot
  FROM generate_series(0, 143) AS n
),
flattened_data AS (
  SELECT
    REPLACE(access_date.key, 'D', '') AS date_str,
    to_char(date_trunc('minute', (access_time->>'time')::time) - (extract(minute from (access_time->>'time')::time) % 10 || ' minutes')::interval, 'HH24:MI') AS time_slot,
    sum((access_time->>'qty')::int) AS total_qty
  FROM
    your_table,
    jsonb_each(your_jsonb_column->'access') AS access_date,
    jsonb_to_recordset(access_date.value) AS access_time(time text, qty int)
  GROUP BY date_str, time_slot
)
SELECT
  f.date_str,
  a.time_slot,
  COALESCE(f.total_qty, 0) AS qty
FROM
  all_time_slots a
CROSS JOIN (SELECT DISTINCT date_str FROM flattened_data) d
LEFT JOIN flattened_data f ON d.date_str = f.date_str AND a.time_slot = f.time_slot
ORDER BY d.date_str, a.time_slot;

步骤4：生成目标JSON格式 #

如果需要直接得到嵌套JSON结构的结果，用jsonb_object_agg聚合：

WITH all_time_slots AS (
  SELECT to_char(interval '10 minutes' * n, 'HH24:MI') AS time_slot
  FROM generate_series(0, 143) AS n
),
flattened_data AS (
  SELECT
    REPLACE(access_date.key, 'D', '') AS date_str,
    to_char(date_trunc('minute', (access_time->>'time')::time) - (extract(minute from (access_time->>'time')::time) % 10 || ' minutes')::interval, 'HH24:MI') AS time_slot,
    sum((access_time->>'qty')::int) AS total_qty
  FROM
    your_table,
    jsonb_each(your_jsonb_column->'access') AS access_date,
    jsonb_to_recordset(access_date.value) AS access_time(time text, qty int)
  GROUP BY date_str, time_slot
),
date_slot_qty AS (
  SELECT
    d.date_str,
    jsonb_object_agg(a.time_slot, COALESCE(f.total_qty, 0)) AS slot_qty
  FROM
    all_time_slots a
  CROSS JOIN (SELECT DISTINCT date_str FROM flattened_data) d
  LEFT JOIN flattened_data f ON d.date_str = f.date_str AND a.time_slot = f.time_slot
  GROUP BY d.date_str
)
SELECT jsonb_object_agg(date_str, slot_qty) AS result_json
FROM date_slot_qty;

执行后会返回符合需求的JSON对象，可直接用于时间线绘制。

内容的提问来源于stack exchange，提问作者user3603985