如何用Sequelize计算同一列数值的差值及平均值？

需求说明 #

现有reading表，结构及数据如下：

需要计算每两个相邻Units的差值（取绝对值），再求这些差值的平均值。

实现方案 #

核心思路是利用SQL窗口函数LAG()获取上一行的Units值，计算当前行与上一行的差值绝对值，最后对所有有效差值求平均。以下提供两种实现方式：

方式1：原生SQL查询

直接编写原生SQL，通过Sequelize执行：

const sequelize = require('./path-to-your-sequelize-instance'); // 引入你的Sequelize实例

async function getAverageDifference() {
  const [result] = await sequelize.query(`
    SELECT AVG(ABS(current_units - previous_units)) AS average_difference
    FROM (
      SELECT 
        Units AS current_units,
        LAG(Units) OVER (ORDER BY Id) AS previous_units
      FROM reading
    ) AS difference_data
    WHERE previous_units IS NOT NULL;
  `, { type: sequelize.QueryTypes.SELECT });

  console.log('平均差值:', result.average_difference);
  return result.average_difference;
}

// 调用执行
getAverageDifference();

代码说明：

• 子查询中，LAG(Units) OVER (ORDER BY Id)按Id顺序获取上一行的Units值；
• 外层查询过滤掉无前置行的数据（previous_units IS NOT NULL），计算差值绝对值的平均值。

方式2：Sequelize查询构建器

如果偏好使用Sequelize的查询API而非原生SQL字符串，可以通过子查询实现：

const { fn, literal } = require('sequelize');
const Reading = require('./path-to-your-models/Reading'); // 引入Reading模型
const sequelize = require('./path-to-your-sequelize-instance');

async function getAverageDifference() {
  // 构建子查询：获取每行的Units及上一行的Units
  const subQuery = Reading.findAll({
    attributes: [
      'Units',
      [literal('LAG(Units) OVER (ORDER BY Id)'), 'previous_units']
    ],
    raw: true
  });

  // 基于子查询计算平均差值
  const [result] = await sequelize.query(`
    SELECT AVG(ABS(Units - previous_units)) AS average_difference
    FROM (${subQuery.query}) AS difference_data
    WHERE previous_units IS NOT NULL;
  `, {
    replacements: subQuery.replacements,
    type: sequelize.QueryTypes.SELECT
  });

  console.log('平均差值:', result.average_difference);
  return result.average_difference;
}

// 调用执行
getAverageDifference();

代码说明：

• 先通过findAll构建子查询，用literal插入窗口函数逻辑；
• 再将子查询嵌入外层的平均值计算查询中，确保语法兼容Sequelize的参数替换机制。

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