如何在Grafana中通过PostgreSQL查询为各图表设置不同时间间隔

问题背景 #

PostgreSQL中有一张表，数据写入间隔无规律：例如连续写入10分钟数据后，间隔8小时无数据，再写入10分钟数据，又间隔13小时无数据，以此循环。已知每次数据写入时长不超过10分钟，且各次数据的起止时间明确。需要在Grafana中创建仪表盘，实现最近3个数据集的可视化对比。

表中每个数据集有独立的序列编号sessions_num，最初尝试的查询语句如下：

SELECT date, data
FROM data_db
WHERE date >= to_timestamp(${__from} / 1000) and date < to_timestamp(${__to} / 1000) AND sessions_num = 2

该语句无效，因为依赖Grafana全局时间参数，无法精准定位每个数据集的时间范围。尝试修改Relative time和Time shift有一定效果，但仍需在查询中覆盖全局时间参数。补充说明：点击“Zoom to data”后图表会重新排列，希望在数据间隔不固定的情况下，能同时查看多个数据集的完整趋势。

数据示例：

+-------------------------------------------+
| date                | data | sessions_num |
+-------------------------------------------+
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 23:55:48 |  0   | 2            |
| 2024-07-08 13:10:09 |  0   | 1            |
| 2024-07-08 13:10:09 |  0   | 1            |
| 2024-07-08 13:10:09 |  0   | 1            |
| 2024-07-08 13:10:09 |  0   | 1            |
| 2024-07-08 13:10:09 |  0   | 1            |
| 2024-07-08 13:10:09 |  0   | 1            |
| 2024-07-08 13:10:09 |  0   | 1            |
| 2024-07-08 13:10:10 |  12948 | 1          |
+-------------------------------------------+

解决方案 #

方法1：动态获取数据集时间范围（无需全局时间参数） #

直接通过sessions_num定位对应数据集的所有数据，不依赖Grafana的全局时间选择。针对最近3个数据集，分别编写查询：

最新数据集（最大sessions_num）

WITH latest_session AS (
    SELECT MAX(sessions_num) AS session_id
    FROM data_db
)
SELECT date, data, '最新数据集' AS series_name
FROM data_db, latest_session
WHERE sessions_num = latest_session.session_id

上一个数据集

WITH latest_sessions AS (
    SELECT sessions_num
    FROM data_db
    GROUP BY sessions_num
    ORDER BY sessions_num DESC
    LIMIT 2
)
SELECT date, data, '上一个数据集' AS series_name
FROM data_db
WHERE sessions_num = (SELECT sessions_num FROM latest_sessions ORDER BY sessions_num LIMIT 1)

倒数第三个数据集

WITH latest_sessions AS (
    SELECT sessions_num
    FROM data_db
    GROUP BY sessions_num
    ORDER BY sessions_num DESC
    LIMIT 3
)
SELECT date, data, '倒数第三个数据集' AS series_name
FROM data_db
WHERE sessions_num = (SELECT sessions_num FROM latest_sessions ORDER BY sessions_num LIMIT 1 OFFSET 2)

将这三个查询添加到同一个图表面板，即可在同一张图中对比三个数据集的原始时间趋势；若需单独查看，可分别创建面板并使用对应查询。

方法2：使用Grafana变量灵活选择数据集 #

1. 创建数据集变量：

   • 进入仪表盘设置，添加新变量，类型选择Query，数据源指定你的PostgreSQL。
   • 查询语句填写：

     SELECT sessions_num FROM data_db GROUP BY sessions_num ORDER BY sessions_num DESC
     

   • 变量会自动列出所有sessions_num，可设置默认选择最近3个值。

2. 编写动态查询：
   在图表面板中使用变量代替固定的sessions_num值，示例：

   SELECT date, data, '数据集' || $session_num AS series_name
   FROM data_db
   WHERE sessions_num = $session_num
   

   可添加3个面板分别选择不同的变量值，或在同一个面板中选择多个变量值，Grafana会自动生成多条对比曲线。

方法3：对齐时间轴（解决跨时间段对比问题） #

若希望将不同时间的数据集对齐到同一时间轴（比如都从0分钟开始），可计算每个数据点相对于该数据集起始时间的偏移量，实现趋势对齐：

WITH session_time AS (
    SELECT sessions_num, MIN(date) AS start_time
    FROM data_db
    GROUP BY sessions_num
)
SELECT 
    EXTRACT(EPOCH FROM (d.date - st.start_time)) / 60 AS minutes_since_start,
    d.data,
    '数据集' || d.sessions_num AS series_name
FROM data_db d
JOIN session_time st ON d.sessions_num = st.sessions_num
WHERE d.sessions_num IN (
    SELECT sessions_num FROM data_db GROUP BY sessions_num ORDER BY sessions_num DESC LIMIT 3
)

该查询返回的是每个数据点相对于数据集开始的分钟数，所有数据集会从0分钟开始对齐，避免因原始时间间隔大导致的图表分散，更直观对比趋势变化。

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