数据可视化grafana面板制作，展示订单统计信息

IT不难

发布于 2022-04-24 13:59:08

1.9K00

代码可运行

文章被收录于专栏：IT不难技术家园IT不难技术家园

运行总次数：0

代码可运行

前言

前面写过如何部署基于docker的grafana做数据可视化展示，因为常用数据库是mysql,所以数据源以mysql来制作。再使用过程中，有了新的需求。继续补充面版制作方法。

docker下运行grafana和grafana Image Renderer

制作面板

按规格统计计成功订单数

规格名称不是很标准，需要对查询的字段进行处理

注意结果的时间排序，开始因为倒序，一直报错

SELECT 
    unix_timestamp(time) as time_sec, 
    count(format_name) as value,
    format_name as metric  
FROM (
    select 
      date_format(b.create_time, '%Y-%m-%d') as time,
      substring_index(substring_index(c.format_name, '(', 1), '（', 1) as format_name
  from 
    order_record b 
  join 
    (select '10000' as format_id, '自定义' as format_name  union all select format_id,format_name from photo_format ) c 
  on 
    b.format_id = c.format_id 
  where 
    $__timeFilter(b.create_time)
  and 
    b.order_status = 10 
)
AS temp
GROUP BY time, format_name
order by time, format_name desc
;

SELECT 
    unix_timestamp(time) as time_sec, 
    count(format_name) as value,
    format_name as metric  
FROM (
    select 
      date_format(b.create_time, '%Y-%m-%d %H') as time,
      c.format_name as format_name
  from 
    order_record b 
  join 
    (select '10000' as format_id, '自定义' as format_name  union all select format_id,format_name from photo_format ) c 
  on 
    b.format_id = c.format_id 
  where 
    $__timeFilter(b.create_time)
  and 
    b.order_status = 10 
)
AS temp
GROUP BY time, format_name
order by time, format_name desc
;