使用Sequelize查询单日每小时交易总额统计数据的方法

你当前的代码没有对时间做小时维度的截断，也没有配置分组规则，所以无法得到按小时聚合的结果，按以下步骤修改即可：

核心修改点 #

• 使用数据库内置时间函数将createdAt字段截断到小时精度
• 新增group配置，按截断后的小时字段分组
• 调整属性返回字段，返回小时标识和对应的聚合金额

适配不同数据库的代码示例 #

1. MySQL 环境 #

if (ExpenseGraphTimeSeries === 'today') {
    const startOfDay = new Date();
    startOfDay.setHours(0, 0, 0, 0);
    const createdAt = {
        [Op.lt]: new Date(),
        [Op.gte]: startOfDay
    };

    ExpenseGraphCondition.createdAt = createdAt
    ExpenseGraph = await model.Expense.findAll({
        where: createdAt,
        attributes: [
            [model.sequelize.fn('sum', model.sequelize.col('Amount')), 'expense'],
            // 按小时格式化时间，返回格式如 2020-01-01 13:00:00
            [model.sequelize.fn('DATE_FORMAT', model.sequelize.col('createdAt'), '%Y-%m-%d %H:00:00'), 'hour']
        ],
        // 按格式化后的小时字段分组
        group: ['hour']
    });
}

2. PostgreSQL 环境 #

if (ExpenseGraphTimeSeries === 'today') {
    const startOfDay = new Date();
    startOfDay.setHours(0, 0, 0, 0);
    const createdAt = {
        [Op.lt]: new Date(),
        [Op.gte]: startOfDay
    };

    ExpenseGraphCondition.createdAt = createdAt
    ExpenseGraph = await model.Expense.findAll({
        where: createdAt,
        attributes: [
            [model.sequelize.fn('sum', model.sequelize.col('Amount')), 'expense'],
            // 截断时间到小时精度
            [model.sequelize.fn('DATE_TRUNC', 'hour', model.sequelize.col('createdAt')), 'hour']
        ],
        group: ['hour']
    });
}

补充说明 #

如果需要返回0-23点所有小时的结果，即使对应小时没有交易，你可以在代码中先生成24个小时的初始数组，再把查询结果映射到对应小时的位置，没有交易的小时金额默认设为0即可。如果你使用其他数据库，可以替换时间函数为对应数据库的小时截断函数即可。

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