如何每14天重置Grafana面板中图片计数的起始点？

核心思路 #

通过PostgreSQL的日期计算和窗口函数，直接在数据库层面完成14天周期分组，并在每个周期内生成从1开始递增的计数，无需依赖Grafana的复杂变量或逻辑。

假设数据表结构 #

假设你的图片表名为photos，存储拍摄日期的字段为capture_date（DATE类型）。

固定起始点的SQL查询 #

如果想从一个固定日期（比如2023-01-01）开始划分14天周期，使用以下SQL：

SELECT
  capture_date,
  -- 计算当前日期属于第几个14天周期
  FLOOR(EXTRACT(DAY FROM (capture_date - '2023-01-01'::DATE)) / 14) AS cycle_group,
  -- 每个周期内的累计计数，按拍摄日期排序
  ROW_NUMBER() OVER (PARTITION BY cycle_group ORDER BY capture_date) AS cycle_count
FROM photos
ORDER BY capture_date;

动态起始点的SQL查询 #

如果想从第一张图片的拍摄日期开始自动划分周期，使用以下SQL：

WITH first_capture AS (
  -- 获取最早的图片拍摄日期作为周期起始点
  SELECT MIN(capture_date) AS start_date FROM photos
)
SELECT
  p.capture_date,
  FLOOR(EXTRACT(DAY FROM (p.capture_date - fc.start_date)) / 14) AS cycle_group,
  ROW_NUMBER() OVER (PARTITION BY cycle_group ORDER BY p.capture_date) AS cycle_count
FROM photos p, first_capture fc
ORDER BY p.capture_date;

Grafana配置步骤 #

1. 在Grafana中选择PostgreSQL数据源，将上述SQL作为查询语句。
2. 可视化面板选择折线图或柱状图：
   • X轴绑定capture_date字段，设置为时间格式。
   • Y轴绑定cycle_count字段，即可看到每14天自动重置为1并递增的计数曲线。

方案优势 #

• 完全由数据库计算分组和计数，性能更优，避免Grafana前端逻辑的延迟或错误。
• 无需维护全局变量，逻辑简洁，数据一致性更高。

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