mysql数据可视化_利用grafana让mysql数据可视化

grafana是一个强大的数据可视化。这里我记录一个比较实例。起到抛砖引玉的作用。

案例

使用.png

模板.png

设置sql.png

通过模板 定制搜索功能

实例 sql

SELECT date as '日期',account_number as '账号',agent_name as '名字',agent_phone as '手机号',byue as '包月',tgbyue as '包月总金额',sm as '扫码',tgsm as '扫码总金额',agent_type as '类型',scan_ratio as '扫码比例',monthly_recharge_price as '包月单价'

from (

SELECT date_format(b1.created_at,'%Y-%m-%d') as date,b1.*,a1.account_number,a1.agent_name,a1.agent_phone,a1.scan_ratio,a1.monthly_recharge_price,a1.agent_type from (

SELECT created_at,l1_user_id,TRUNCATE(SUM(IF(l2_price - l1_price>0,l2_price - l1_price,0)),2) as byue,TRUNCATE(sum(amount),2) as tgbyue,TRUNCATE(sum(ql1_price),2) as sm,TRUNCATE(sum(qamount),2) as tgsm

from orders

WHERE `status` = 2 and

date('$bdate') <= date(created_at) and date('$edate') >= date(created_at)

GROUP BY date_format(created_at,'%Y-%m-%d'),l1_user_id

) b1,agent_infos as a1 WHERE b1.l1_user_id = a1.id and a1.agent_type = "一级代理"

and (a1.account_number like '%$agaccount%' or a1.agent_name like '%$agaccount%')

ORDER BY date_format(b1.created_at,'%Y-%m-%d'),b1.sm DESC

) bbb1;