如何用MySQL在Grafana中计算展示近1小时/天/周/月的体重增减情况

前提说明 #

假设存储体重数据的表名为weight_records，包含字段：

• ID：主键，自增整数
• weight：浮点类型，存储体重数值
• timestamp：datetime类型，存储记录上报时间
  以下所有查询兼容Grafana单值面板、时间范围变量的使用要求。

A) 近1小时体重增减值 #

计算逻辑：取当前最新体重值，减去1小时区间内最早上报的体重值

SELECT 
  (latest.weight - earliest.weight) AS 近1小时体重增减值
FROM 
  (SELECT weight FROM weight_records ORDER BY `timestamp` DESC LIMIT 1) latest,
  (SELECT weight FROM weight_records WHERE `timestamp` >= NOW() - INTERVAL 1 HOUR ORDER BY `timestamp` ASC LIMIT 1) earliest

B) 近1天体重增减值 #

计算逻辑：取当前最新体重值，减去24小时前后1小时区间内最接近24小时整的体重值（避免整点上报波动导致无数据）

SELECT 
  (latest.weight - day_ago.weight) AS 近1天体重增减值
FROM 
  (SELECT weight FROM weight_records ORDER BY `timestamp` DESC LIMIT 1) latest,
  (SELECT weight FROM weight_records 
   WHERE `timestamp` BETWEEN NOW() - INTERVAL 25 HOUR AND NOW() - INTERVAL 23 HOUR 
   ORDER BY ABS(TIMESTAMPDIFF(SECOND, `timestamp`, NOW() - INTERVAL 24 HOUR)) ASC 
   LIMIT 1) day_ago

C) 近1周体重增减值 #

计算逻辑：取当日平均体重，减去7天前当日的平均体重

SELECT 
  (latest_day.avg_weight - week_ago_day.avg_weight) AS 近1周体重增减值
FROM
  (SELECT AVG(weight) AS avg_weight 
   FROM weight_records 
   WHERE DATE(`timestamp`) = CURDATE()) latest_day,
  (SELECT AVG(weight) AS avg_weight 
   FROM weight_records 
   WHERE DATE(`timestamp`) = CURDATE() - INTERVAL 7 DAY) week_ago_day

D) 近1月体重增减值 #

计算逻辑：取当周平均体重，减去4周前当周的平均体重（YEARWEEK第二个参数为1代表周从周一开始，可根据需求调整为0）

SELECT 
  (latest_week.avg_weight - month_ago_week.avg_weight) AS 近1月体重增减值
FROM
  (SELECT AVG(weight) AS avg_weight 
   FROM weight_records 
   WHERE YEARWEEK(`timestamp`, 1) = YEARWEEK(NOW(), 1)) latest_week,
  (SELECT AVG(weight) AS avg_weight 
   FROM weight_records 
   WHERE YEARWEEK(`timestamp`, 1) = YEARWEEK(NOW() - INTERVAL 4 WEEK, 1)) month_ago_week

Grafana适配优化 #

• 若需使用Grafana全局时间范围控件，可将查询中的NOW()替换为Grafana内置变量$__timeTo()，时间过滤条件统一使用$__timeFilter(timestamp)
• 查询返回的单值可直接对接Grafana Stat面板，单位设置为kg后，可在阈值配置中自定义增减数值的变色规则

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