如何在Sequelize中实现嵌套左连接？解决聚合求和偏差问题

问题描述 #

现有一段Sequelize查询代码，能返回数据但关联操作导致sum聚合结果偏差，原代码如下：

key: "listGoogleTextAds",
prototype: "(customer_id: Int, start_date: String, end_date: String): [GoogleTextAds]",
run: async args => {
  const allIds = await google_text_ads
    .findAll({
      attributes: [
        "ad_id",
        [sequelize.fn("max", sequelize.col("google_text_ads.date")), "date"],
        [sequelize.fn("sum", sequelize.col("google_text_ads.impressions")), "impressions"],
        [sequelize.fn("sum", sequelize.col("google_text_ads.clicks")), "clicks"],
        [sequelize.fn("sum", sequelize.col("google_text_ads.cost")), "cost"]
      ],
      where: {
        customer_id: args.customer_id,
        date: {
          [Op.gte]: args.start_date,
          [Op.lte]: args.end_date
        },
        status: {
          [Op.in]: ["ENABLED"]
        },
        type: {
          [Op.in]: ["EXPANDED_TEXT_AD"]
        }
      },
      group: ["ad_id"],
      include: [
        {
          model: google_ads_group_name,
          as: 'google_ads_group_name',
          required: true,
          attributes: [
            "ad_group_name",
            "ad_group_id"
          ],
          where: {
            customer_id: args.customer_id,
            date: {
              [Op.gte]: args.start_date,
              [Op.lte]: args.end_date
            },
            status: {
              [Op.in]: ["ENABLED"]
            }
          },
          required: false
        },
      ]
    })
    .map(item => item.toJSON());
  const textAds = uniqBy(
    await google_text_ads.findAll({
      where: sequelize.where(
        sequelize.fn(
          "concat",
          sequelize.col("ad_id"),
          "-",
          sequelize.col("date")
        ),
        {
          [Op.in]: allIds.map(({ ad_id, date }) => `${ad_id}-${date}`)
        }
      )
    }),
    ({ ad_id, date }) => `${ad_id}-${date}`
  );
  return textAds.map(ad => {
    const idMap = allIds.find(({ ad_id }) => ad_id === ad.ad_id);
    return {
      ...ad.toJSON(),
      clicks: idMap.clicks,
      impressions: idMap.impressions,
      cost: idMap.cost,
      google_ads_group_name: idMap.google_ads_group_name
    };
  });
}

已写出正确的SQL查询语句，但不清楚如何在Sequelize中实现这种嵌套左连接，正确SQL如下：

SELECT 
  `google_text_ads`.`ad_id`,
  MAX(`google_text_ads`.`date`) AS `date`,
  SUM(`google_text_ads`.`impressions`) AS `impressions`,
  SUM(`google_text_ads`.`clicks`) AS `clicks`,
  SUM(`google_text_ads`.`cost`) AS `cost`,
  ra.ad_group_name
FROM
  `google_text_ads` AS `google_text_ads`
LEFT JOIN
  ( SELECT
      ad_group_name,
      ad_group_id
    FROM 
      google_ads_groups 
    GROUP BY
      ad_group_id
  ) ra 
ON google_text_ads.ad_group_id = ra.ad_group_id     
WHERE
  `google_text_ads`.`customer_id` = 139
  AND (`google_text_ads`.`date` >= '2022-09-07'
  AND `google_text_ads`.`date` <= '2022-10-06')
  AND `google_text_ads`.`status` IN ('ENABLED')
  AND `google_text_ads`.`type` IN ('EXPANDED_TEXT_AD')
GROUP BY `ad_id`;

解决方案 #

方案一：使用Sequelize ORM语法实现嵌套左连接 #

通过include的from选项指定子查询，配合on定义关联条件，避免重复数据导致聚合错误：

run: async args => {
  const result = await google_text_ads.findAll({
    attributes: [
      "ad_id",
      [sequelize.fn("max", sequelize.col("google_text_ads.date")), "date"],
      [sequelize.fn("sum", sequelize.col("google_text_ads.impressions")), "impressions"],
      [sequelize.fn("sum", sequelize.col("google_text_ads.clicks")), "clicks"],
      [sequelize.fn("sum", sequelize.col("google_text_ads.cost")), "cost"],
      [sequelize.col("ra.ad_group_name"), "ad_group_name"]
    ],
    where: {
      customer_id: args.customer_id,
      date: {
        [Op.gte]: args.start_date,
        [Op.lte]: args.end_date
      },
      status: {
        [Op.in]: ["ENABLED"]
      },
      type: {
        [Op.in]: ["EXPANDED_TEXT_AD"]
      }
    },
    group: ["ad_id"],
    include: [
      {
        attributes: [], // 无需重复返回子查询属性
        model: sequelize.models.google_ads_groups,
        as: 'ra',
        required: false, // 左连接，对应SQL的LEFT JOIN
        from: sequelize.literal(`(SELECT ad_group_name, ad_group_id FROM google_ads_groups GROUP BY ad_group_id) ra`),
        on: sequelize.where(sequelize.col('google_text_ads.ad_group_id'), '=', sequelize.col('ra.ad_group_id'))
      }
    ],
    raw: true // 返回原始数据对象，减少模型实例处理开销
  });
  return result;
}

方案二：直接执行原生SQL查询 #

如果ORM语法实现复杂，直接复用已验证的原生SQL是更高效的方式：

run: async args => {
  const sql = `
    SELECT 
      \`google_text_ads\`.\`ad_id\`,
      MAX(\`google_text_ads\`.\`date\`) AS \`date\`,
      SUM(\`google_text_ads\`.\`impressions\`) AS \`impressions\`,
      SUM(\`google_text_ads\`.\`clicks\`) AS \`clicks\`,
      SUM(\`google_text_ads\`.\`cost\`) AS \`cost\`,
      ra.ad_group_name
    FROM
      \`google_text_ads\` AS \`google_text_ads\`
    LEFT JOIN
      ( SELECT
          ad_group_name,
          ad_group_id
        FROM 
          google_ads_groups 
        GROUP BY
          ad_group_id
      ) ra 
    ON google_text_ads.ad_group_id = ra.ad_group_id     
    WHERE
      \`google_text_ads\`.\`customer_id\` = :customer_id
      AND (\`google_text_ads\`.\`date\` >= :start_date
      AND \`google_text_ads\`.\`date\` <= :end_date)
      AND \`google_text_ads\`.\`status\` IN ('ENABLED')
      AND \`google_text_ads\`.\`type\` IN ('EXPANDED_TEXT_AD')
    GROUP BY \`ad_id\`;
  `;
  const result = await sequelize.query(sql, {
    replacements: {
      customer_id: args.customer_id,
      start_date: args.start_date,
      end_date: args.end_date
    },
    type: sequelize.QueryTypes.SELECT
  });
  return result;
}

关键说明 #

• 原代码问题：关联google_ads_group_name时未对广告组数据去重，导致单条广告对应多条广告组记录，sum聚合时重复计算数据
• 解决核心：先对广告组数据按ad_group_id分组去重，再通过左连接关联主表，确保每条广告只对应一条广告组记录，聚合结果准确

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