PostgreSQL Sequelize中如何在WHERE条件使用列求和结果

SQL语法执行顺序中，WHERE子句运行在GROUP BY聚合操作之前，仅能对表中单行原始数据做校验过滤；如果要对SUM/COUNT/AVG等聚合函数的计算结果做条件判断，必须使用HAVING子句，Sequelize的查询配置中提供了对应的having参数实现该能力。

修改后的可运行代码如下：

const { Op, literal, fn, col } = this.app.Sequelize;

const timeTableWhere = {
  [Op.and]: [
    {
      durationTimestamp: {
        [Op.gte]: hourTimestamp,
      },
    },
  ],
};
const timetables = await ctx.model.Timetable.findAll({
  attributes: [
    'userId',
    'dow',
    'type',
    [fn('SUM', col('duration_timestamp')), 'durationTimestamp'],
  ],
  where: timeTableWhere,
  group: ['dow', 'userId', 'type'],
  // 新增having配置，对分组聚合后的求和结果做过滤
  having: {
    [fn('SUM', col('duration_timestamp'))]: {
      [Op.gte]: hourTimestamp
    }
  }
});

可选简化写法 #

如果你使用的数据库（MySQL 8.0+、PostgreSQL 等）支持在HAVING子句中直接引用SELECT阶段定义的聚合别名，可以用更简洁的字面量写法，减少重复的聚合函数声明：

const timetables = await ctx.model.Timetable.findAll({
  attributes: [
    'userId',
    'dow',
    'type',
    [fn('SUM', col('duration_timestamp')), 'durationTimestamp'],
  ],
  where: timeTableWhere,
  group: ['dow', 'userId', 'type'],
  having: literal('durationTimestamp >= :hourThreshold'),
  replacements: {
    hourThreshold: hourTimestamp
  }
});

注意事项 #

• 原有where中的durationTimestamp过滤逻辑可以根据需求保留，它会在聚合前先过滤掉不符合条件的单行记录，减少聚合计算的数据量
• 不建议在where中直接引用聚合别名，会触发SQL语法错误，所有聚合结果的过滤逻辑都要放在having配置中

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