GROUP BY聚合结果合并为单行及多表分组统计SQL技术问询

根据你的需求，我整理了几种常见场景的实现方案，你可以根据实际需求选择：

场景1：全局汇总（所有项目的统计合并为一行） #

如果不需要区分单个项目，只想得到所有关联数据的全局统计值，直接去掉GROUP BY子句即可：

select 
  count(e.id) as total_count, 
  avg(opened) as overall_opened_avg, 
  avg(read_email) as overall_clicked_avg, 
  avg(started_video) as overall_started_watching_avg, 
  sum(views) as total_views 
from projects p 
inner join guests g on g.project_id = p.id 
inner join videos v on v.guest_id = g.id 
inner join emails e on e.video_id=v.id;

这个查询会返回一行数据，包含所有项目的总计数、全局平均值等汇总结果。

场景2：每个项目的聚合值打包为单行结构化字段 #

如果需要保留项目ID维度，但把多个聚合结果合并成单个结构化字段（方便后续业务处理），不同数据库有不同实现方式：

MySQL/MariaDB 版本 #

使用JSON_OBJECT将聚合值封装为JSON对象：

select 
  p.id,
  JSON_OBJECT(
    'count', count(e.id),
    'opened_avg', avg(opened),
    'clicked_avg', avg(read_email),
    'started_watching_avg', avg(started_video),
    'total_views', sum(views)
  ) as project_stats
from projects p 
inner join guests g on g.project_id = p.id 
inner join videos v on v.guest_id = g.id 
inner join emails e on e.video_id=v.id 
group by p.id;

结果中每个项目对应一行，project_stats字段会包含该项目的所有统计值。

PostgreSQL 版本 #

使用json_build_object生成结构化JSON：

select 
  p.id,
  json_build_object(
    'count', count(e.id),
    'opened_avg', avg(opened),
    'clicked_avg', avg(read_email),
    'started_watching_avg', avg(started_video),
    'total_views', sum(views)
  ) as project_stats
from projects p 
inner join guests g on g.project_id = p.id 
inner join videos v on v.guest_id = g.id 
inner join emails e on e.video_id=v.id 
group by p.id;

SQL Server 版本 #

使用FOR JSON PATH生成JSON结构：

select 
  p.id,
  (
    select 
      count(e.id) as count,
      avg(opened) as opened_avg,
      avg(read_email) as clicked_avg,
      avg(started_video) as started_watching_avg,
      sum(views) as total_views
    for json path, without_array_wrapper
  ) as project_stats
from projects p 
inner join guests g on g.project_id = p.id 
inner join videos v on v.guest_id = g.id 
inner join emails e on e.video_id=v.id 
group by p.id;

场景3：所有项目的统计合并为单行数组 #

如果想把所有项目的统计结果整合到一行的JSON数组里（方便一次性导出或批量处理），可以先分组统计每个项目，再聚合为数组：

PostgreSQL 示例 #

select json_agg(project_stats) as all_project_stats
from (
  select 
    p.id as project_id,
    count(e.id) as count,
    avg(opened) as opened_avg,
    avg(read_email) as clicked_avg,
    avg(started_video) as started_watching_avg,
    sum(views) as total_views
  from projects p 
  inner join guests g on g.project_id = p.id 
  inner join videos v on v.guest_id = g.id 
  inner join emails e on e.video_id=v.id 
  group by p.id
) as project_stats;

这个查询会返回一行数据，all_project_stats字段是包含所有项目统计信息的JSON数组。

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