标签
PostgreSQL , SQL , PLPGSQL , 绘制饼图
背景
图像相比文字是更容易被理解的东西，在BI可视化领域，经常会使用图像来代替数值，展示一些信息，例如柱状图、饼图、线图等。
AWR文字报告里面，如果多几个图像来代替列表，其实也是不错的。
那么SQL能直接画图吗，把一行行的结果，转换成图像。
例如
1、TOP SQL的总耗时占比饼图。
2、数据库对象类型占比饼图。
3、数据库空间占比饼图。
4、TOP对象的空间占。
PostgreSQL凭借丰富的SQL语法，画图，小CASE。
画图SQL举例
https://wiki.postgresql.org/wiki/Pie_Charts
使用psql终端绘图的方法如下：
1、设置变量，饼图的宽，高，代表不同颜色的字符等。
\set width 80
\set height 25
\set radius 1.0
\set colours '''#;o:X"@+-=123456789abcdef'''
2、绘图的DEMO SQL，将4行记录转换为饼图
这4行记录如下：
VALUES ('red',1),
('blue',2),
('orange',3),
('white',4)
)
绘图SQL如下
WITH slices AS (
SELECT CAST(ROW_NUMBER() OVER () AS INTEGER) AS slice,
name,
VALUE,
100.0 * VALUE / SUM(VALUE) OVER () AS percentage,
2*PI() * SUM(VALUE) OVER (ROWS unbounded preceding)
/ SUM(VALUE) OVER () AS radians
FROM (VALUES ('red',1),
('blue',2),
('orange',3),
('white',4)
) AS DATA(name,VALUE))
(
SELECT array_to_string(array_agg(c),'') AS pie_chart
FROM (
SELECT x, y,
CASE WHEN NOT (SQRT(pow(x, 2) + pow(y, 2))
BETWEEN :radius*1/10 AND :radius)
THEN ' '
ELSE SUBSTRING(:colours,
(SELECT MIN(slice)
FROM slices
WHERE radians >= PI() + atan2(y,-x)),
1)
END AS c
FROM (SELECT 2.0*generate_series(0,:width)/:width-1.0) AS x(x),
(SELECT 2.0*generate_series(0,:height)/:height-1.0) AS y(y)
ORDER BY y,x
) AS xy
GROUP BY y
ORDER BY y
)
UNION ALL
SELECT repeat(SUBSTRING(:colours,slice,1), 2) || ' ' ||
name || ': ' ||
VALUE || ' (' || round(percentage,0) || '%)'
FROM slices;
3、图像结果如下
pie_chart
-----------------------------------------------------------------------------------
;
oooo;;;;;;;;;;;;;;;;;;;;;;;;;;;
ooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
oooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
ooooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
ooooooooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
oooooooooooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;######
oooooooooooooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;###########
ooooooooooooooooooooooooooooooooo;;;;;;;;;;;;;;;;;;;;;;;;##################
ooooooooooooooooooooooooooooooooooo;;;;;;;;;;;;;;;;;;;#######################
ooooooooooooooooooooooooooooooooooooo;;;;;;;;;;;;;;############################
oooooooooooooooooooooooooooooooooooooo;;;;;;;;#################################
oooooooooooooooooooooooooooooooooooo ####################################
oooooooooooooooooooooooooooooooooooo ::::::::::::::::::::::::::::::::::::
ooooooooooooooooooooooooooooooooo::::::::::::::::::::::::::::::::::::::::::::::
oooooooooooooooooooooooooooo:::::::::::::::::::::::::::::::::::::::::::::::::::
ooooooooooooooooooooooo::::::::::::::::::::::::::::::::::::::::::::::::::::::
oooooooooooooooooo:::::::::::::::::::::::::::::::::::::::::::::::::::::::::
ooooooooooo::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
oooooo:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::::::::::::::
:::::::::::::::::::::::::::::::
:
## red: 1 (10%)
;; blue: 2 (20%)
oo orange: 3 (30%)
:: white: 4 (40%)
(30 rows)
是不是很有意思？
将绘图SQL转换为函数接口提供任意调用
1、函数接口如下
create or replace function gen_charts(
sql text, -- SQL，返回两列，第一列为描述，第二列为这个描述的数值
width int default 80,
height int default 25,
radius numeric default 1.0, -- 换成float8类型，打印实心饼图
colours text default '#;o:X"@+-=123456789abcdef'
) returns setof text as $$
declare
begin
return query execute format(
$_$
WITH slices AS (
SELECT CAST(ROW_NUMBER() OVER () AS INTEGER) AS slice,
name,
VALUE,
100.0 * VALUE / SUM(VALUE) OVER () AS percentage,
2*PI() * SUM(VALUE) OVER (ROWS unbounded preceding)
/ SUM(VALUE) OVER () AS radians
FROM (%s
) AS DATA(name,VALUE))
(
SELECT array_to_string(array_agg(c),'') AS pie_chart
FROM (
SELECT x, y,
CASE WHEN NOT (SQRT(pow(x, 2) + pow(y, 2))
BETWEEN %s*1/10 AND %s)
THEN ' '
ELSE SUBSTRING(%L,
(SELECT MIN(slice)
FROM slices
WHERE radians >= PI() + atan2(y,-x)),
1)
END AS c
FROM (SELECT 2.0*generate_series(0,%s)/%s-1.0) AS x(x),
(SELECT 2.0*generate_series(0,%s)/%s-1.0) AS y(y)
ORDER BY y,x
) AS xy
GROUP BY y
ORDER BY y
)
UNION ALL
SELECT repeat(SUBSTRING(%L,slice,1), 2) || ' ' ||
name || ': ' ||
VALUE || ' (' || round(percentage,0) || '%%)'
FROM slices;
$_$, sql, radius, radius, colours, width, width, height, height, colours);
return;
end;
$$ language plpgsql strict;
绘图函数接口使用举例
1、打印当前数据库中的不同对象类型占比
postgres=# select * from gen_charts('select relkind,count(*) from pg_class group by relkind');
gen_charts
-----------------------------------------------------------------------------------
;
;;;;;;;;;;;;;;;;;;;;;;;;;;;####
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;###########
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;#####################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;#########################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;############################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;##############################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;#################################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;###################################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;#####################################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;######################################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;; ####################################
;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;oo o:::::::::::::::::::::::::::::::::::
;;;;;;;;;;;;;;;;;;;;;;;ooooooooooooooooooooooooooooo:::::::::::::::::::::::::::
;;;;;;;;;;;;oooooooooooooooooooooooooooooooooooooooooooooooo:::::::::::::::::::
ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo::::::::::
oooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo:
ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
ooooooooooooooooooooooooooooooooooooooooooooooooooooooooooo
ooooooooooooooooooooooooooooooooooooooooooooooooooo
ooooooooooooooooooooooooooooooooooooooooooo
ooooooooooooooooooooooooooooooo
o
## r: 71 (20%)
;; v: 119 (34%)
oo i: 138 (40%)
:: t: 20 (6%)
XX c: 1 (0%)
(31 rows)
2、打印大于8K的对象，空间排行前十的对象，他们分别的占比
postgres=# select * from gen_charts('select relname , pg_relation_size(oid) from pg_class where pg_relation_size(oid) > 8192 order by pg_relation_size(oid) desc limit 10');
gen_charts
-----------------------------------------------------------------------------------
#
###############################
###########################################
###################################################
###########################################################
#################################################################
#####################################################################
#######################################################################
###########################################################################
#############################################################################
###############################################################################
###############################################################################
#################################### ####################################
#################################### ;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
##############################################;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;;
###################################################;;;;;;;;;;;;;;;;;;;;;;;;;;;;
#######################################################;;;;;;;;;;;;;;;;;;;;;;
##########################################################;;;;;;;;;;;;;;;;;
#############################################################;;;;;;;;;;
#################################################################;;;;
#################################################################
###########################################################
###################################################
###########################################
###############################
#
## idx_c_1: 2359795712 (90%)
;; c: 248135680 (10%)
oo pg_proc: 663552 (0%)
:: pg_depend: 548864 (0%)
XX pg_attribute: 458752 (0%)
"" pg_depend_reference_index: 458752 (0%)
@@ pg_depend_depender_index: 417792 (0%)
++ pg_toast_2618: 417792 (0%)
-- pg_statistic: 385024 (0%)
== pg_proc_proname_args_nsp_index: 294912 (0%)
(36 rows)
3、打印不同数据库的空间占比
postgres=# select * from gen_charts('select datname, pg_database_size(datname) from pg_database group by 1');
gen_charts
-----------------------------------------------------------------------------------
#
###############################
###########################################
###################################################
###########################################################
#################################################################
#####################################################################
#######################################################################
###########################################################################
#############################################################################
###############################################################################
###############################################################################
#################################### ####################################
#################################### ####################################
###############################################################################
###############################################################################
#############################################################################
###########################################################################
#######################################################################
#####################################################################
#################################################################
###########################################################
###################################################
###########################################
###############################
#
## postgres: 2616839287 (99%)
;; template0: 7741955 (0%)
oo template1: 7741955 (0%)
(29 rows)
