DIFF 函数用户手册

函数概述

DIFF 函数用于计算数据列中相邻两行数据的差值，通常用于分析数据的变化趋势和增量。该函数特别适用于监控智能电表数据的变化模式。

语法

SELECT DIFF(column_name [, ignore_negative]) FROM table_name;

参数说明

• column_name: 数值型列名，支持整数和浮点数类型
• ignore_negative: 可选参数，整数类型
  • 0 或省略：保留负数结果
  • 1：忽略负数结果，负数结果显示为 NULL

返回值类型

• 输入为整数类型：返回 BIGINT
• 输入为浮点数类型：返回 DOUBLE

智能电表场景应用示例

基于智能电表数据库结构：

-- 数据库和表结构
USE test;
-- meters 超级表包含 ts, current, voltage, phase 字段和 location, groupid 标签

基础用法示例

电流变化监控

-- 计算电流的变化量
SELECT ts, current, DIFF(current) as current_diff 
FROM meters 
WHERE tbname = 'd1001' 
ORDER BY ts;-- 计算电压的变化量
SELECT ts, voltage, DIFF(voltage) as voltage_diff 
FROM meters 
WHERE tbname = 'd1001' 
ORDER BY ts;-- 计算相位的变化量
SELECT ts, phase, DIFF(phase) as phase_diff 
FROM meters 
WHERE tbname = 'd1001' 
ORDER BY ts;

忽略负数差值

-- 只关注电流增长的情况
SELECT ts, current, DIFF(current, 1) as current_growth 
FROM meters 
WHERE tbname = 'd1001' 
ORDER BY ts;-- 只关注电压上升的情况
SELECT ts, voltage, DIFF(voltage, 1) as voltage_rise 
FROM meters 
WHERE tbname = 'd1001' 
ORDER BY ts;

用电量增量分析

累计用电量变化

-- 分析累计电流的变化（模拟累计用电量）
SELECT ts, current, DIFF(current) as power_increment 
FROM meters 
WHERE location = 'California.LosAngles' AND tbname = 'd1001'
ORDER BY ts;

按时间段分析用电增量

-- 分析最近一小时的用电增量
SELECT ts, current, DIFF(current) as hourly_increment 
FROM meters 
WHERE tbname = 'd1001' AND ts >= NOW() - 1h
ORDER BY ts;-- 分析今日的用电增量
SELECT ts, current, DIFF(current) as daily_increment 
FROM meters 
WHERE tbname = 'd1001' AND ts >= TODAY()
ORDER BY ts;

电网负载变化监控

实时负载变化

-- 监控实时负载变化
SELECT ts, current, DIFF(current) as load_change 
FROM meters 
WHERE location = 'California.SanFrancisco'AND tbname = 'd1002'
ORDER BY ts DESC 
LIMIT 100;

异常负载检测

-- 检测电流突变（变化超过5A）
SELECT ts, current, DIFF(current) as current_change 
FROM meters 
WHERE tbname = 'd1001'AND ABS(DIFF(current)) > 5
ORDER BY ts;

电压质量监控

电压波动分析

-- 分析电压波动情况
SELECT ts, voltage, DIFF(voltage) as voltage_fluctuation 
FROM meters 
WHERE location = 'California.Cupertino'AND tbname = 'd1003'
ORDER BY ts;

相位角变化监控

相位漂移检测

-- 监控相位漂移
SELECT ts, phase, DIFF(phase) as phase_drift 
FROM meters 
WHERE location = 'California.MountainView'AND tbname = 'd1004'
ORDER BY ts;

相位稳定性分析

-- 分析相位稳定性（忽略负变化）
SELECT ts, phase, DIFF(phase, 1) as phase_improvement 
FROM meters 
WHERE tbname = 'd1004'
ORDER BY ts;

多设备对比分析

按设备分组计算差值

-- 按设备分组计算各自的电流变化
SELECT tbname, ts, current, DIFF(current) as current_change 
FROM meters 
WHERE location = 'California.SanJose'
PARTITION BY tbname 
ORDER BY tbname, ts;

设备性能对比

-- 对比不同设备的电压变化模式
SELECT tbname, ts, voltage, DIFF(voltage) as voltage_change 
FROM meters 
WHERE location = 'California.Sunnyvale'
PARTITION BY tbname 
ORDER BY tbname, ts;

能效分析应用

设备效率变化

-- 分析设备效率变化（电流与电压的关系变化）
SELECT ts, current, voltage, DIFF(current) as current_change,DIFF(voltage) as voltage_change
FROM meters 
WHERE tbname = 'd1001'
ORDER BY ts;

重要限制和注意事项

1. 函数限制：

   • TDengine 不支持函数嵌套：DIFF(ABS(current)) ❌
   • 不支持在 WHERE 中直接使用：WHERE DIFF(current) > 10 ❌

2. 数据要求：

   • 第一行数据的 DIFF 值为 NULL（没有前一行数据）
   • 输入列必须是数值型
   • 按时间戳顺序计算差值

3. 结果特点：

   • 返回行数 = 输入行数（第一行为 NULL）
   • NULL 值参与计算时结果为 NULL
   • 支持分区计算，各分区独立计算差值

关于 TDengine

TDengine 专为物联网IoT平台、工业大数据平台设计。其中，TDengine TSDB 是一款高性能、分布式的时序数据库（Time Series Database），同时它还带有内建的缓存、流式计算、数据订阅等系统功能；TDengine IDMP 是一款AI原生工业数据管理平台，它通过树状层次结构建立数据目录，对数据进行标准化、情景化，并通过 AI 提供实时分析、可视化、事件管理与报警等功能。