TypeORM合并子查询报错求优化：活动任务成员均值统计

在Node.js项目中使用TypeORM时遇到统计需求的优化问题：

数据关系：每个Campaign（活动）包含多个Mission（任务），每个Mission包含多个Member（成员）。需要统计每个活跃Campaign下，所有Mission的：

• 平均参与成员数（已开始的成员）
• 平均完成成员数（已完成的成员）

现有实现能满足需求，但存在两个逻辑重复仅WHERE条件不同的子查询，代码如下：

const campaignStatistics = await this.campaignRepository
  .createQueryBuilder('campaign')
  .select(['campaign.id', 'campaign.title', 'campaign.dateFrom'])
  .where("campaign.status = 'active'")
  .addSelect(subQuery =>
    subQuery
      .select('CAST(AVG(participation_count) AS DECIMAL(5, 1)) AS "participationAverage"')
      .from(
        subQuery =>
          subQuery
            .select("COUNT(members.id) FILTER (WHERE missions.triggered = 'true' AND members.started = 'true')", 'participation_count')
            .from(CampaignEntity, 'campaign_sub')
            .where('campaign_sub.id = campaign.id')
            .leftJoin('campaign_sub.missions', 'missions')
            .leftJoin('missions.members', 'members')
            .groupBy('missions.id'),
        'participation_count'
      )
  )
  .addSelect(subQuery =>
    subQuery
      .select('CAST(AVG(completion_count) AS DECIMAL(5, 1)) AS "completionAverage"')
      .from(
        subQuery =>
          subQuery
            .select("COUNT(members.id) FILTER (WHERE missions.triggered = 'true' AND members.completed = 'true')", 'completion_count')
            .from(CampaignEntity, 'campaign_sub')
            .where('campaign_sub.id = campaign.id')
            .leftJoin('campaign_sub.missions', 'missions')
            .leftJoin('missions.members', 'members')
            .groupBy('missions.id'),
          'completion_count'
      )
  )
  .orderBy('campaign.dateFrom', 'DESC')
  .limit(3)
  .getRawAndEntities();

尝试合并子查询时出现错误 ERROR: subquery must return only one column，合并代码如下：

.addSelect(subQuery =>
  subQuery
    .select([
      'CAST(AVG(participation_count) AS DECIMAL(5, 1)) AS "participationAverage"',
      'CAST(AVG(completion_count) AS DECIMAL(5, 1)) AS "completionAverage"'
    ])
    .from(
      subQuery =>
        subQuery
          .select([
            "COUNT(members.id) FILTER (WHERE missions.triggered = 'true' AND members.started = 'true') AS participation_count",
            "COUNT(members.id) FILTER (WHERE missions.triggered = 'true' AND members.completed = 'true') AS completion_count"
          ])
          .from(CampaignEntity, 'campaign_sub')
          .where('campaign_sub.id = campaign.id')
          .leftJoin('campaign_sub.missions', 'missions')
          .leftJoin('missions.members', 'members')
          .groupBy('missions.id'),
      'count'
    )
)

当前代码生成的SQL参考：

SELECT   "campaign"."id"        AS "campaign_id",
         "campaign"."title"     AS "campaign_title",
         "campaign"."date_from" AS "campaign_date_from",
         (
                SELECT Cast( Avg(participation_count) AS DECIMAL(5, 1) ) AS "participationAverage"
                FROM   (
                                 SELECT    Count("members"."id") filter ( WHERE "missions"."triggered" = 'true'
                                 AND       "members"."started" = 'true' ) AS "participation_count"
                                 FROM      "campaign" "campaign_sub"
                                 LEFT JOIN "mission" "missions"
                                 ON        "missions"."campaignId" = "campaign_sub"."id"
                                 LEFT JOIN "mission_member" "members"
                                 ON        "members"."missionId" = "missions"."id"
                                 WHERE     "campaign_sub"."id" = "campaign"."id"
                                 GROUP BY  "missions"."id" ) "participation_count" ),
         (
                SELECT Cast( Avg(completion_count) AS DECIMAL(5, 1) ) AS "completionAverage"
                FROM   (
                                 SELECT    Count("members"."id") FILTER ( WHERE "missions"."triggered" = 'true'
                                 AND       "members"."completed" = 'true' ) AS "completion_count"
                                 FROM      "campaign" "campaign_sub"
                                 LEFT JOIN "mission" "missions"
                                 ON        "missions"."campaignId" = "campaign_sub"."id"
                                 LEFT JOIN "mission_member" "members"
                                 ON        "members"."missionId" = "missions"."id"
                                 WHERE     "campaign_sub"."id" = "campaign"."id"
                                 GROUP BY  "missions"."id" ) "completion_count" )
FROM     "campaign" "campaign"
WHERE    "campaign"."status" = 'active'
ORDER BY "campaign"."date_from" DESC
LIMIT 3 

需求：找到能达成相同统计结果且提升性能的实现方案。

核心思路：一次关联完成全量统计，避免重复遍历数据 #

原方案的两个子查询会对同一Campaign的Mission和Member数据进行两次遍历，存在性能浪费。改为先按Mission聚合单任务的参与/完成数，再按Campaign聚合计算平均值，全程仅遍历一次数据。

TypeORM实现代码 #

const campaignStatistics = await this.campaignRepository
  .createQueryBuilder('campaign')
  .select([
    'campaign.id',
    'campaign.title',
    'campaign.dateFrom',
    'CAST(AVG(mission_stats.participation_count) AS DECIMAL(5,1)) AS "participationAverage"',
    'CAST(AVG(mission_stats.completion_count) AS DECIMAL(5,1)) AS "completionAverage"'
  ])
  .where("campaign.status = 'active'")
  // 关联预统计好的任务数据
  .leftJoin(
    (subQuery) => subQuery
      .select([
        'missions.campaignId',
        'missions.id AS mission_id',
        'COUNT(members.id) FILTER (WHERE missions.triggered = true AND members.started = true) AS participation_count',
        'COUNT(members.id) FILTER (WHERE missions.triggered = true AND members.completed = true) AS completion_count'
      ])
      .from(MissionEntity, 'missions')
      .leftJoin('missions.members', 'members')
      .groupBy('missions.id'),
    'mission_stats',
    'mission_stats.campaignId = campaign.id'
  )
  .groupBy('campaign.id')
  .orderBy('campaign.dateFrom', 'DESC')
  .limit(3)
  .getRawAndEntities();

对应SQL逻辑 #

生成的SQL会先批量计算所有Mission的参与/完成数，再关联到Campaign计算平均值，仅执行一次关联查询：

SELECT 
  "campaign"."id",
  "campaign"."title",
  "campaign"."dateFrom",
  CAST(AVG("mission_stats"."participation_count") AS DECIMAL(5,1)) AS "participationAverage",
  CAST(AVG("mission_stats"."completion_count") AS DECIMAL(5,1)) AS "completionAverage"
FROM "campaign" "campaign"
LEFT JOIN (
  SELECT 
    "missions"."campaignId",
    "missions"."id" AS "mission_id",
    COUNT("members"."id") FILTER (WHERE "missions"."triggered" = true AND "members"."started" = true) AS "participation_count",
    COUNT("members"."id") FILTER (WHERE "missions"."triggered" = true AND "members"."completed" = true) AS "completion_count"
  FROM "mission" "missions"
  LEFT JOIN "mission_member" "members" ON "members"."missionId" = "missions"."id"
  GROUP BY "missions"."id"
) "mission_stats" ON "mission_stats"."campaignId" = "campaign"."id"
WHERE "campaign"."status" = 'active'
GROUP BY "campaign"."id"
ORDER BY "campaign"."dateFrom" DESC
LIMIT 3

性能提升点 #

1. 减少数据遍历次数：原方案每个Campaign对应两次子查询遍历，优化后仅遍历一次Mission和Member数据
2. 降低嵌套子查询开销：改用左关联+分组的方式，数据库查询优化器更容易生成高效执行计划
3. 索引利用率提升：确保mission.campaignId、mission_member.missionId、campaign.status、campaign.dateFrom字段有索引，可进一步加快查询速度

注意事项 #

• 若某个Campaign无任何Mission，AVG会返回NULL，可通过COALESCE(AVG(...), 0)设置默认值
• 需确保MissionEntity与MemberEntity的关联关系在TypeORM实体中配置正确，否则查询构建器的关联会出错

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