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 提供實時分析、可視化、事件管理與報警等功能。