如何在单次查询中实现子查询按日期及月份分组生成JSON数组？

嘿，这个场景太常见了——不想重复写一遍核心查询又要生成不同维度的聚合结果对吧？咱们用**公共表表达式（CTE）**就能完美解决，让核心统计逻辑只执行一次，同时生成日维度和月维度的JSON数组。

具体实现方案 #

假设你原来生成tt的核心子查询是每天的员工统计逻辑，先把这部分抽到CTE里复用：

WITH daily_employee_stats AS (
    -- 这里放你原来生成tt的核心查询逻辑
    -- 示例：包含日期、员工ID、统计字段的分组查询
    SELECT 
        date_made,
        employee_id,
        SUM(hours_worked) AS total_hours,
        COUNT(task_id) AS task_count
    FROM employee_tasks
    WHERE date_made BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY date_made, employee_id
)
SELECT
    -- 原需求：按date聚合的JSON数组
    json_agg(ds ORDER BY ds.date_made) AS employee_summary_arr,
    -- 新需求：按月份分组聚合的JSON对象（键为月份，值为当月统计数组）
    json_object_agg(
        to_char(monthly_stats.month, 'YYYY-MM'),
        monthly_stats.monthly_summary
    ) AS employee_summary_arr_by_month
FROM daily_employee_stats ds
-- 关联预聚合的月统计结果
JOIN (
    SELECT
        date_trunc('month', date_made) AS month,
        json_agg(ds_month ORDER BY ds_month.date_made) AS monthly_summary
    FROM daily_employee_stats ds_month
    GROUP BY date_trunc('month', date_made)
) monthly_stats ON date_trunc('month', ds.date_made) = monthly_stats.month
-- 强制结果为单行，和原employee_summary_arr的输出结构保持一致
GROUP BY ()

为什么这方案优雅？ #

• 无重复执行：核心的日统计逻辑只在CTEdaily_employee_stats里跑一次，后续的日聚合、月聚合都基于这一份数据，避免了重复子查询的性能浪费。
• 可读性拉满：把核心逻辑抽出来单独维护，后续要修改统计规则，只需要改CTE里的内容就行，不用两处都改。
• 灵活调整输出结构：如果不想用JSON对象格式，也可以改成数组结构，比如把每个月份的统计包成{"month": "2024-01", "summary": [...]}这样的对象，只需要把json_object_agg换成json_agg(json_build_object(...))就行。

另一种紧凑写法（适合单行结果场景） #

如果你的输出本来就是单行全局统计，也可以用子查询直接生成两个聚合列，本质和CTE逻辑一致：

SELECT
    (SELECT json_agg(ds ORDER BY ds.date_made) FROM daily_employee_stats ds) AS employee_summary_arr,
    (SELECT json_object_agg(to_char(month, 'YYYY-MM'), summary) 
     FROM (
         SELECT
             date_trunc('month', date_made) AS month,
             json_agg(ds_month ORDER BY ds_month.date_made) AS summary
         FROM daily_employee_stats ds_month
         GROUP BY date_trunc('month', date_made)
     ) monthly) AS employee_summary_arr_by_month
FROM (
    -- 核心子查询，和CTE内容一致
    SELECT 
        date_made,
        employee_id,
        SUM(hours_worked) AS total_hours,
        COUNT(task_id) AS task_count
    FROM employee_tasks
    WHERE date_made BETWEEN '2024-01-01' AND '2024-12-31'
    GROUP BY date_made, employee_id
) daily_employee_stats
LIMIT 1

两种方式都能达到复用核心逻辑的目的，CTE版本可读性更好，子查询版本更紧凑，看你个人习惯选就行~

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