如何在Sequelize中按月份分组统计记录？date_trunc无法使用

问题 #

需要统计所有订单记录并按月份分组，尝试过date_trunc函数但无法生效，当前Sequelize代码只能按日期分组得到每日统计结果，希望调整为每月统计。

当前代码：

let saleByAllMonth = await Order.findAll({
  attributes: [
    [db.sequelize.fn("DATE", db.sequelize.col("date")), "date"],
    [db.sequelize.fn("count", "*"), "count"],
  ],
  group: ["date"],
});

当前输出：

{
    "success": 1,
    "saleByAllMonth": [
        {"date": "2023-01-07", "count": 2},
        {"date": "2023-01-08", "count": 7},
        {"date": "2023-01-09", "count": 14},
        {"date": "2023-01-11", "count": 12},
        {"date": "2023-01-13", "count": 2},
        {"date": "2023-01-14", "count": 2},
        {"date": "2023-01-19", "count": 3},
        {"date": "2023-01-30", "count": 2},
        {"date": "2023-02-13", "count": 3}
    ]
}

期望输出：

{
    "success": 1,
    "saleByAllMonth": [
        {"date": "1", "count": 49},
        {"date": "2", "count": 3}
    ]
}

解决方案 #

核心是用数据库对应的提取月份的函数替代原有的DATE函数，同时分组依据改为提取出的月份值。以下分两种常见数据库场景给出代码：

场景1：使用MySQL/MariaDB #

用MONTH函数提取日期中的月份数字：

let saleByAllMonth = await Order.findAll({
  attributes: [
    [db.sequelize.fn("MONTH", db.sequelize.col("date")), "date"],
    [db.sequelize.fn("count", "*"), "count"],
  ],
  group: [db.sequelize.fn("MONTH", db.sequelize.col("date"))],
  order: [[db.sequelize.fn("MONTH", db.sequelize.col("date")), "ASC"]] // 可选：按月份升序排列
});

场景2：使用PostgreSQL #

如果date_trunc不生效，可改用EXTRACT函数提取月份：

let saleByAllMonth = await Order.findAll({
  attributes: [
    [db.sequelize.fn("EXTRACT", db.sequelize.literal('MONTH FROM "date"')), "date"],
    [db.sequelize.fn("count", "*"), "count"],
  ],
  group: [db.sequelize.fn("EXTRACT", db.sequelize.literal('MONTH FROM "date"'))],
  order: [[db.sequelize.fn("EXTRACT", db.sequelize.literal('MONTH FROM "date"')), "ASC"]] // 可选：按月份升序排列
});

说明 #

• 替换原DATE函数为对应数据库的月份提取函数，确保分组依据和查询的字段一致
• 加上order选项可以保证结果按月份顺序排列，避免乱序
• 如果需要区分不同年份的同月（比如2023年1月和2024年1月），可以同时提取年份，比如MySQL用DATE_FORMAT(date, '%Y-%m')，PostgreSQL用date_trunc('month', date)，再按该值分组

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