如何用Sequelize实现类似MongoDB $bucket的分组聚合查询？

我有一个users表，包含name、email、state、isOnline、createdAt五个字段，数据如下：

需要用Sequelize ORM实现类似MongoDB $bucket的分组聚合，得到如下格式的结果：

{
    "states": [
        {
            "name": "TamilNadu",
            "count": 3
        },
        {
            "name": "Karnataka",
            "count": 3
        },
        {
            "name": "Kerala",
            "count": 1
        }
    ],
    "isOnline": 1,
    "isOffline": 6,
    "createdAt": [
        {
            "name": "withinADay",
            "count": 1
        },
        {
            "name": "withinAWeek",
            "count": 7
        },
        {
            "name": "withinAMonth",
            "count": 7
        }
    ]
}

同时需要支持应用中的筛选功能，请指导编写对应的查询语句。

1. 动态构建筛选条件 #

先通过对象存储筛选规则，后续自动拼接为SQL的WHERE子句，避免硬编码：

// 示例：可根据业务动态传入的筛选参数
const filterParams = {
  // state: ['TamilNadu', 'Kerala'], // 按省份筛选
  // isOnline: true, // 按在线状态筛选
  // createdAtRange: { start: new Date('2022-08-01'), end: new Date('2022-08-30') } // 按注册时间范围筛选
};

2. 编写聚合查询逻辑 #

通过Sequelize原生查询实现复杂桶聚合，同时结合参数绑定避免SQL注入，代码如下：

const { Op } = require('sequelize');
const sequelize = require('./your-sequelize-instance'); // 替换为你的Sequelize实例

async function getUserAggregation(filter = {}) {
  // 构建WHERE子句和参数替换集合
  const whereClauses = [];
  const replacements = {};

  if (filter.state) {
    whereClauses.push(`state IN (:states)`);
    replacements.states = filter.state;
  }
  if (filter.isOnline !== undefined) {
    whereClauses.push(`isOnline = :isOnline`);
    replacements.isOnline = filter.isOnline;
  }
  if (filter.createdAtRange) {
    whereClauses.push(`createdAt BETWEEN :startDate AND :endDate`);
    replacements.startDate = filter.createdAtRange.start;
    replacements.endDate = filter.createdAtRange.end;
  }

  const whereStr = whereClauses.length ? `WHERE ${whereClauses.join(' AND ')}` : '';

  // 1. 按省份分组统计
  const stateStats = await sequelize.query(`
    SELECT state AS name, COUNT(*) AS count
    FROM users
    ${whereStr}
    GROUP BY state
    ORDER BY count DESC
  `, { replacements, type: sequelize.QueryTypes.SELECT });

  // 2. 统计在线/离线用户数量
  const onlineOfflineStats = await sequelize.query(`
    SELECT 
      SUM(CASE WHEN isOnline = true THEN 1 ELSE 0 END) AS isOnline,
      SUM(CASE WHEN isOnline = false THEN 1 ELSE 0 END) AS isOffline
    FROM users
    ${whereStr}
  `, { replacements, type: sequelize.QueryTypes.SELECT });

  // 3. 按注册时间桶统计
  const now = new Date();
  const oneDayAgo = new Date(now.getTime() - 24 * 60 * 60 * 1000);
  const oneWeekAgo = new Date(now.getTime() - 7 * 24 * 60 * 60 * 1000);
  const oneMonthAgo = new Date(now.getTime() - 30 * 24 * 60 * 60 * 1000);

  const dateBucketStats = await sequelize.query(`
    SELECT 'withinADay' AS name, COUNT(*) AS count
    FROM users
    ${whereStr} AND createdAt >= :oneDayAgo
    UNION ALL
    SELECT 'withinAWeek' AS name, COUNT(*) AS count
    FROM users
    ${whereStr} AND createdAt >= :oneWeekAgo
    UNION ALL
    SELECT 'withinAMonth' AS name, COUNT(*) AS count
    FROM users
    ${whereStr} AND createdAt >= :oneMonthAgo
  `, { 
    replacements: { ...replacements, oneDayAgo, oneWeekAgo, oneMonthAgo },
    type: sequelize.QueryTypes.SELECT 
  });

  // 整合最终结果
  return {
    states: stateStats,
    ...onlineOfflineStats[0],
    createdAt: dateBucketStats
  };
}

// 调用示例：无筛选
getUserAggregation().then(res => console.log(res));

// 调用示例：带省份筛选
getUserAggregation({ state: ['Kerala'] }).then(res => console.log(res));

3. 扩展说明 #

• 筛选维度可按需扩展，比如添加name模糊搜索：只需在whereClauses中加入name LIKE :nameKeyword，并在replacements中传入nameKeyword: '%xxx%'。
• 时间桶的范围可根据业务调整，比如修改为固定日期而非基于当前时间的相对时间。
• 使用原生查询是因为Sequelize ORM的聚合API在处理多层桶统计时灵活性不足，原生查询能更直接实现类似MongoDB $bucket的效果。

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