PostgreSQL psql 绘制饼图

作者

digoal

日期

2018-08-04

标签

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,
2PI() * 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 %s1/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.0generate_series(0,%s)/%s-1.0) AS x(x),
(SELECT 2.0generate_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://code.openark.org/blog/mysql/sql-pie-chart

https://wiki.postgresql.org/wiki/Pie_Charts

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.