创建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展示的点