grafana 学习 - 柱状图(mysql)

样式 1

返回的数据格式( table 样式)：

+------------+------------+--------+----------+----------+----------+--------+
| time       | metric     | 待支付 | 部分支付 | 支付成功 | 支付取消 | 已退款 |
+------------+------------+--------+----------+----------+----------+--------+
| 1634745600 | 2021-10-21 | 0      | 0        | 0        | 0        | 1      |
| 1635177600 | 2021-10-26 | 0      | 0        | 0        | 0        | 5      |
| 1635350400 | 2021-10-28 | 0      | 0        | 0        | 0        | 3      |

sql 样式, 需要进行转列:

SELECT
	tor.time,
	tor.metric,
	sum(tor.`待支付`) as '待支付',
	sum(tor.`部分支付`)as '部分支付',
	sum(tor.`支付成功`) as '支付成功',
	sum(tor.`支付取消`) as '支付取消',
	sum(tor.`已退款`)  as '已退款'
FROM
(
SELECT
    UNIX_TIMESTAMP(date_format(create_time,'%Y-%m-%d')) AS time,
		date_format(create_time,'%Y-%m-%d') AS metric,
		IF(order_status=1, count(*),0) as '待支付',
    IF(order_status=2, count(*),0) as '部分支付',
		IF(order_status=3, count(*),0) as '支付成功',
		IF(order_status=4, count(*),0) as '支付取消',
		IF(order_status=5, count(*),0) as '已退款'
FROM
	t_order
	GROUP BY time
	ORDER BY time asc
) tor
	GROUP BY time
	ORDER BY time asc
	

返回数据格式( table 样式)：

+------------+-----+
| metric     | cnt |
+------------+-----+
| 已退款     | 588 |
| 成功       | 439 |
| 部分支付   |  15 |
| 取消(关闭) |  66 |
| 待支付     |   2 |
+------------+-----+

样式 2

SELECT
		CASE
			order_status 
			WHEN 1 THEN
			'待支付' 
			WHEN 2 THEN
			'部分支付' 
			WHEN 3 THEN
			'成功' 
			WHEN 4 THEN
			'取消(关闭)' 
			WHEN 5 THEN 
			'已退款' ELSE '未知' 
		END AS metric,
	count(*) cnt 
FROM
	t_order 
	where
	$__timeFilter(create_time)
GROUP BY 	order_status 
ORDER BY 	create_time ASC

样式选择 水平