本文共 13791 字，大约阅读时间需要 45 分钟。
PostgreSQL , SQL , PLPGSQL , 绘制饼图
图像相比文字是更容易被理解的东西，在BI可视化领域，经常会使用图像来代替数值，展示一些信息，例如柱状图、饼图、线图等。
AWR文字报告里面，如果多几个图像来代替列表，其实也是不错的。
那么SQL能直接画图吗，把一行行的结果，转换成图像。
例如
1、TOP SQL的总耗时占比饼图。
2、数据库对象类型占比饼图。
3、数据库空间占比饼图。
4、TOP对象的空间占。
PostgreSQL凭借丰富的SQL语法，画图，小CASE。
使用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)
是不是很有意思？
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) 转载地址：http://dwvra.baihongyu.com/