如何使用Sequelize按数值区间分组统计数量？

我需要给数据表的amount字段按每1000的区间分组，统计各区间的数量，且只统计status=1的数据。当前用Sequelize写的查询能得到统计数，但输出格式不符合需求，想把区间标识设为id，统计数设为count，输出成数组对象的形式。

当前尝试的查询代码 #

x.find({
    where: { status: 1},
    attributes: [
        [literal('COUNT (CASE WHEN amount >= 100 AND amount <= 1000 THEN amount END)'), 'amount 100~1000'],
        [literal('COUNT (CASE WHEN amount >= 1001 AND amount <= 2000 THEN amount END)'), 'amount 1001~2000'],
        [literal('COUNT (CASE WHEN amount >= 2001 AND amount <= 3000 THEN amount END)'), 'amount 2001~3000'],
        [literal('COUNT (CASE WHEN amount >= 3001 THEN amount END)'), 'amount 3001~4000'],
    ],
});

当前输出 #

{
    "amount 100~1000": 3,
    "amount 1001~2000": 3,
    "amount 2001~3000": 1,
    "amount 3001~4000": 0,
}

期望输出 #

[
    {
        "id":1, // amount 100~1000
        "count": 3
    },
    {
        "id":2, // amount 1001~2000
        "count": 3
    },
    {
        "id":3, // amount 2001~3000
        "count": 1
    }
]

解决方案 #

要实现行式输出，不能用原有的横向统计方式，以下是三种可行方案：

方案1：查询结果后转换格式 #

如果不想修改SQL逻辑，可在拿到查询结果后手动转换结构：

// 执行原查询
const result = await x.find({
    where: { status: 1},
    attributes: [
        [literal('COUNT (CASE WHEN amount >= 100 AND amount <= 1000 THEN amount END)'), 'range1'],
        [literal('COUNT (CASE WHEN amount >= 1001 AND amount <= 2000 THEN amount END)'), 'range2'],
        [literal('COUNT (CASE WHEN amount >= 2001 AND amount <= 3000 THEN amount END)'), 'range3'],
        [literal('COUNT (CASE WHEN amount >= 3001 THEN amount END)'), 'range4'],
    ],
});

// 转换为期望格式，可选过滤count为0的项
const formattedResult = [
    { id: 1, count: result.range1 },
    { id: 2, count: result.range2 },
    { id: 3, count: result.range3 },
    { id: 4, count: result.range4 },
].filter(item => item.count > 0);

方案2：并行执行多区间统计查询 #

通过定义区间配置，用Promise并行执行多个统计查询：

const { Op } = require('sequelize');

// 定义区间配置
const ranges = [
    { id: 1, min: 100, max: 1000 },
    { id: 2, min: 1001, max: 2000 },
    { id: 3, min: 2001, max: 3000 },
    { id: 4, min: 3001, max: Infinity },
];

// 生成每个区间的统计查询
const queries = ranges.map(range => 
    x.count({
        where: {
            status: 1,
            amount: {
                [Op.gte]: range.min,
                [Op.lte]: range.max === Infinity ? Op.gte : range.max,
            }
        }
    }).then(count => ({ id: range.id, count }))
);

// 并行执行并处理结果
const formattedResult = await Promise.all(queries);
const filteredResult = formattedResult.filter(item => item.count > 0);

方案3：用SQL UNION ALL生成区间虚拟表（推荐） #

通过UNION ALL创建区间虚拟表，左连接原表完成统计，单条SQL即可得到目标格式：

const { literal } = require('sequelize');

const result = await x.findAll({
    attributes: [
        'range_id',
        [literal('COUNT(t.id)'), 'count']
    ],
    from: [
        literal(`(
            SELECT 1 as range_id, 100 as min_amount, 1000 as max_amount UNION ALL
            SELECT 2, 1001, 2000 UNION ALL
            SELECT 3, 2001, 3000 UNION ALL
            SELECT 4, 3001, 999999999
        ) as ranges
        LEFT JOIN \`x\` t ON t.status = 1 AND t.amount BETWEEN ranges.min_amount AND ranges.max_amount`)
    ],
    group: ['range_id'],
    order: ['range_id']
});

// 转换为纯对象数组并过滤无效项
const formattedResult = result.map(item => ({
    id: item.range_id,
    count: item.count
})).filter(item => item.count > 0);

方案说明 #

• 方案1：操作简单，适合区间固定且数量少的场景，仅做前端数据转换。
• 方案2：代码简洁，但会发起多次数据库请求，小数据量场景适用。
• 方案3：单SQL完成统计，性能更优，适合大数据量场景，直接从数据库获取目标格式结果。

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