求助：SQL计算指定genre图书各角色人均平均完成时长

原有SQL存在的核心问题 #

• 子查询未关联外层逻辑，会返回多条数据直接触发语法报错
• IN子句中的字符串常量Romance、Thriller、Comic未加单引号，会被数据库识别为字段名报错
• TIMESTAMP_DIFF参数顺序颠倒，应该用预计完成时间减去开始写作时间才能得到正的完成时长
• 缺少按genre分组的核心逻辑，也没有区分三类角色分别计算的逻辑
• 计算逻辑完全颠倒：人均平均完成时长是「单品类所有图书的总完成时长之和」除以「对应角色的去重总人数」，而非人数除以单条时长

正确可运行的SQL代码 #

SELECT
  genre,
  -- 计算作者人均完成时长：总完成天数和 / 去重作者数
  SUM(TIMESTAMP_DIFF(expected_completion_date, started_writing, DAY)) / COUNT(DISTINCT author) AS author_avg_completion_days,
  -- 计算编辑人均完成时长：总完成天数和 / 去重编辑数
  SUM(TIMESTAMP_DIFF(expected_completion_date, started_writing, DAY)) / COUNT(DISTINCT editor) AS editor_avg_completion_days,
  -- 计算译者人均完成时长：总完成天数和 / 去重译者数
  SUM(TIMESTAMP_DIFF(expected_completion_date, started_writing, DAY)) / COUNT(DISTINCT translator) AS translator_avg_completion_days
FROM data_table
-- 过滤指定三类genre
WHERE genre IN ('Romance', 'Thriller', 'Comic')
-- 按genre分组统计
GROUP BY genre
-- 可自行调整排序字段，此处以作者人均完成时长升序为例
ORDER BY author_avg_completion_days ASC;

逻辑说明 #

1. 先用WHERE子句过滤出仅需要的三类genre，自动排除了Crime和其他品类的数据
2. 用GROUP BY genre按图书品类分组，所有聚合计算都会基于每个品类单独统计
3. 每个角色的人均时长计算逻辑统一为：当前品类所有图书的总预计完成天数之和，除以当前品类下该角色的去重人数，得到单人均摊的完成天数
4. 结果会按genre输出三条记录，每条附带三类角色的人均完成时长指标

可选优化：如果部分图书没有译者/编辑，需要排除空值统计，可将对应COUNT(DISTINCT 角色)修改为COUNT(DISTINCT IF(角色 IS NOT NULL, 角色, NULL))即可。

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