创建Mysql Docker

docker run --name some-mysql -e MYSQL_ROOT_PASSWORD=admin -p:3306:3306 -d mysql:8.0.26

进入容器中的mysql

docker exec -it some-mysql bash
mysql -h localhost -u root -padmin

创建grafana可以读取的mysql数据源

create database grafana;

CREATE USER 'grafana' IDENTIFIED BY 'grafana';

GRANT SELECT ON grafana.* TO 'grafana';

创建mysql表，并插入数据

create table test (
`id` bigint unsigned NOT NULL AUTO_INCREMENT COMMENT '自增id',
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '修改时间',
`group_id` tinyint NOT NULL COMMENT '指标分组id',
`group_name` varchar(256) NOT NULL COMMENT '指标分组名',
`sale`  BIGINT NOT NULL COMMENT '指标值',
 primary key (id)
);

INSERT INTO test (group_id,group_name,sale) VALUES(1,"g1",1);
INSERT INTO test (group_id,group_name,sale) VALUES(1,"g1",1);
INSERT INTO test (group_id,group_name,sale) VALUES(2,"g2",2);
INSERT INTO test (group_id,group_name,sale) VALUES(2,"g2",2);

SELECT
  $__timeGroupAlias(create_time,$__interval),
  group_name AS metric,
  sum(sale) AS "sale"
FROM test
WHERE
  $__timeFilter(create_time)
GROUP BY 1,2
ORDER BY $__timeGroup(create_time,$__interval)

"$__timeGroup",进行时间上的取整，按分钟整点将数据切割为多个“$__interval”范围的数据桶，为后续的聚合计算做准备。将被 GROUP BY 子句中可用的表达式替换。 例如，*cast(cast(UNIX_TIMESTAMP(dateColumn)/(300) as signed)*300 as signed),*。

“$__timeFilter(dateColumn)”将被使用指定列名称的时间范围过滤器替换。 例如，dateColumn BETWEEN FROM_UNIXTIME(1494410783) AND FROM_UNIXTIME(1494410983)

"$__interval" 最小聚合的时间范围，本例是1分钟

 

查询15分钟区间范围，只有两个点

 

翻译成mysql的sql为

SELECT
  UNIX_TIMESTAMP(create_time) DIV 60 * 60 AS "time",
  group_name AS metric,
  sum(sale) AS "sale"
FROM test
WHERE
  create_time BETWEEN FROM_UNIXTIME(1633936515) AND FROM_UNIXTIME(1633937415)
GROUP BY 1,2
ORDER BY UNIX_TIMESTAMP(create_time) DIV 60 * 60

UNIX_TIMESTAMP(create_time) DIV 60 * 60是将时间戳除以60，再乘以60，取整为分钟级别。

grafana配置

 

grafana展示的点