TDengine 选择函数 MIN() 用户手册

MIN 函数用户手册

函数定义

MIN(expr)

功能说明

MIN() 函数是一个聚合函数，用于返回指定列或表达式在结果集中的最小值。该函数会忽略 NULL 值，只在所有值都为 NULL 时返回 NULL。

版本要求

• 最低版本: v3.0.0.0

返回值

• 数据类型: 与输入参数相同的数据类型
• 返回内容: 结果集中的最小值

参数说明

适用数据类型

根据源码分析，MIN 函数支持以下数据类型：

• 数值类型: TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、UTINYINT、USMALLINT、UINT、UBIGINT、DECIMAL
• 字符串类型: VARCHAR、NCHAR

适用范围

• 表类型: 表和超级表
• 查询支持: 支持聚合查询、窗口查询（INTERVAL）
• 函数特性: 聚合函数、选择函数、忽略 NULL 值、支持 TSMA

基本用法示例

单列查询

-- 查找电流的最小值
SELECT MIN(current) FROM meters;

-- 查找电压的最小值
SELECT MIN(voltage) FROM meters;

-- 查找相位的最小值
SELECT MIN(phase) FROM meters;

多列查询

-- 查找多个字段的最小值
SELECT 
    MIN(current) as min_current,
    MIN(voltage) as min_voltage,
    MIN(phase) as min_phase
FROM meters;

字符串类型的最小值

-- 查找设备名称的最小值（按字典序）
SELECT MIN(device_name) FROM devices;

时间戳类型的最小值

-- 查找最早的时间戳
SELECT MIN(ts) as earliest_timestamp FROM meters;

智能电表场景应用示例

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

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

场景1：全局最小值查询

-- 查找所有电表中的最小电流值
SELECT MIN(current) as min_current FROM meters;

-- 查找所有电表中的最小电压值
SELECT MIN(voltage) as min_voltage FROM meters;

-- 查找最早的记录时间
SELECT MIN(ts) as earliest_record_time FROM meters;

场景2：按区域分组的最小值

-- 查找每个区域的最小电流和电压
SELECT 
    location,
    MIN(current) as min_current,
    MIN(voltage) as min_voltage
FROM meters 
GROUP BY location;

场景3：按设备组分析

-- 查找每个设备组的最小值数据
SELECT 
    groupid,
    location,
    MIN(current) as min_current,
    MIN(voltage) as min_voltage,
    MIN(phase) as min_phase,
    COUNT(*) as record_count
FROM meters 
GROUP BY groupid, location
ORDER BY min_current ASC;

场景4：负载基线分析

-- 分析各电表的负载基线（最小负载）
SELECT 
    tbname,
    location,
    MIN(current) as baseline_load,
    MIN(voltage) as min_voltage,
    CASE 
        WHEN MIN(current) < 1 THEN '空载状态'
        WHEN MIN(current) < 5 THEN '低负载设备'
        WHEN MIN(current) < 10 THEN '中负载设备'
        ELSE '高负载设备'
    END as load_category
FROM meters 
GROUP BY tbname, location
ORDER BY baseline_load ASC;

场景5：时间窗口内的最小值

-- 每小时的最小电流
SELECT 
    location,
    MIN(current) as hourly_min_current,
    MIN(voltage) as hourly_min_voltage
FROM meters 
INTERVAL(1h) GROUP BY location;

-- 每天的最小值数据
SELECT 
    location,
    MIN(current) as daily_min_current,
    MIN(voltage) as daily_min_voltage,
    MIN(ts) as first_record_time
FROM meters 
INTERVAL(1d) GROUP BY location;

场景6：电网低谷监控

-- 监控电网各区域的负载低谷
SELECT 
    location,
    MIN(current) as region_min_load,
    COUNT(*) as device_count,
    CASE 
        WHEN MIN(current) < 0.5 THEN '超低负荷区域'
        WHEN MIN(current) < 2 THEN '低负荷区域'
        WHEN MIN(current) < 5 THEN '正常低谷区域'
        ELSE '高基线负荷区域'
    END as low_load_status
FROM meters 
GROUP BY location
ORDER BY region_min_load ASC;

场景7：设备基准评估

-- 评估设备的最小运行基准
SELECT 
    tbname,
    location,
    MIN(current) as min_current_usage,
    MIN(voltage) as min_voltage_reading,
    CASE 
        WHEN MIN(current) = 0 THEN '设备曾停机'
        WHEN MIN(current) < 1 THEN '基准负载极低'
        WHEN MIN(current) < 3 THEN '基准负载较低'
        ELSE '基准负载正常'
    END as baseline_assessment
FROM meters 
GROUP BY tbname, location
ORDER BY min_current_usage ASC;

场景8：电压稳定性检测

-- 检测电压低值的设备
SELECT 
    location,
    COUNT(*) as total_devices,
    MIN(voltage) as min_voltage,
    CASE 
        WHEN MIN(voltage) < 180 THEN '存在严重欠压'
        WHEN MIN(voltage) < 200 THEN '存在轻微欠压'
        ELSE '电压稳定'
    END as voltage_stability
FROM meters 
GROUP BY location;

场景9：历史低值对比

-- 对比不同时间段的最小值
SELECT 
    location,
    MIN(current) as all_time_min,
    MIN(CASE WHEN ts >= NOW() - 7d THEN current END) as week_min,
    MIN(CASE WHEN ts >= NOW() - 1d THEN current END) as today_min
FROM meters 
GROUP BY location
ORDER BY all_time_min ASC;

场景10：设备运行基线指标

-- 设备运行基线综合评估
SELECT 
    tbname,
    location,
    MIN(current) as baseline_current,
    MIN(voltage) as baseline_voltage,
    MIN(phase) as min_phase,
    COUNT(*) as data_points,
    CASE 
        WHEN MIN(current) = 0 OR MIN(voltage) < 180 THEN '需要检查'
        WHEN MIN(current) < 2 AND MIN(voltage) BETWEEN 200 AND 240 THEN '基线良好'
        ELSE '基线正常'
    END as baseline_status
FROM meters 
GROUP BY tbname, location
ORDER BY baseline_current ASC, baseline_voltage ASC;

与其他聚合函数组合使用

-- 同时查询最小值、最大值、平均值和计数
SELECT 
    location,
    MIN(current) as min_current,
    MAX(current) as max_current,
    AVG(current) as avg_current,
    COUNT(*) as record_count
FROM meters 
GROUP BY location;

-- 分析电流的分布范围
SELECT 
    location,
    MIN(current) as min_current,
    MAX(current) as max_current,
    MAX(current) - MIN(current) as current_range,
    CASE 
        WHEN MAX(current) - MIN(current) > 15 THEN '电流波动大'
        WHEN MAX(current) - MIN(current) > 5 THEN '电流波动适中'
        ELSE '电流稳定'
    END as stability_assessment
FROM meters 
GROUP BY location;

注意事项

1. NULL 值处理: MIN 函数自动忽略 NULL 值，只在所有值都为 NULL 时返回 NULL
2. 字符串比较: 对于字符串类型，按字典序进行比较，返回字典序最小的值
3. 数据类型一致性: 返回值的数据类型与输入参数保持一致
4. 时间戳处理: 对于时间戳类型，返回最早（最小）的时间戳值
5. 性能优化: 在有索引的列上使用 MIN 函数可以获得更好的性能
6. 精度保持: 对于 DECIMAL 类型，返回值保持原有的精度和标度

相关函数

• MAX(): 返回最大值
• AVG(): 返回平均值
• SUM(): 返回总和
• COUNT(): 返回计数
• BOTTOM(): 返回最小的 k 个值
• FIRST(): 返回最先的非 NULL 值

关于 TDengine

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