PostgreSQL实时统计频次并写入JSONB列的SQL查询实现

原始表数据 #

假设原始表名为browser_stats，结构及数据如下：

id |date                   |browser
---+-----------------------+---------
101|2024-03-12 00:00:00.000|Chrome
102|2024-03-12 00:00:00.000|Firefox
103|2024-03-13 00:00:00.000|Chrome
104|2024-03-13 00:00:00.000|Firefox
105|2024-03-13 00:00:00.000|Brave
106|2024-03-14 00:00:00.000|Chrome
107|2024-03-14 00:00:00.000|Firefox
108|2024-03-14 00:00:00.000|Edge

目标要求 #

需要按日期分组统计各浏览器的出现次数，将统计结果以JSON格式存入目标表（假设名为daily_browser_summary）的json_count字段（类型为jsonb），目标表结构示例如下：

id |date                   |json_count
---+-----------------------+----------------------------------------
101|2024-03-12 00:00:00.000|{"Chrome": 1, "Firefox": 1}
102|2024-03-13 00:00:00.000|{"Chrome": 1, "Firefox": 1, "Brave": 1}
103|2024-03-14 00:00:00.000|{"Chrome": 1, "Firefox": 1, "Edge": 1}

实现SQL语句 #

1. 基础统计插入 #

如果目标表的id为自增主键，直接执行以下语句即可：

INSERT INTO daily_browser_summary (date, json_count)
SELECT 
    date,
    jsonb_object_agg(TRIM(browser), count) AS json_count
FROM (
    SELECT 
        date,
        browser,
        COUNT(*) AS count
    FROM browser_stats
    GROUP BY date, browser
) AS grouped_stats
GROUP BY date
ORDER BY date;

2. 手动生成连续ID #

如果需要手动指定连续递增的id值，可结合ROW_NUMBER()函数：

INSERT INTO daily_browser_summary (id, date, json_count)
SELECT 
    ROW_NUMBER() OVER (ORDER BY date) AS id,
    date,
    jsonb_object_agg(TRIM(browser), count) AS json_count
FROM (
    SELECT 
        date,
        browser,
        COUNT(*) AS count
    FROM browser_stats
    GROUP BY date, browser
) AS grouped_stats
GROUP BY date
ORDER BY date;

关键说明 #

• TRIM(browser)：清理浏览器名称前后的空格，避免JSON键出现无效空格
• COUNT(*)：统计每个日期下单一浏览器的出现频次
• jsonb_object_agg(key, value)：PostgreSQL专属聚合函数，将分组后的键值对（浏览器名称-频次）组装成标准jsonb对象
• 外层按date二次分组，确保每个日期仅生成一条统计结果

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