PostgreSQL psql 绘制饼图

3 minute read

背景

图像相比文字是更容易被理解的东西,在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)

参考

http://code.openark.org/blog/mysql/sql-pie-chart

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

Flag Counter

digoal’s 大量PostgreSQL文章入口