Label
PostgreSQL, SQL, PLPGSQL, Drawing Pie Charts
background
Image is easier to understand than text. In the field of BI visualization, image is often used instead of numeric value to display some information, such as histogram, pie chart, line graph, etc.
In the AWR text report, it would be good to have several more images instead of lists.
So can SQL draw directly and convert the result of a row into an image?
for example
1. The total time-consuming proportion of TOP SQL is pie chart.
2. Pie chart showing the proportion of database object types.
3. The pie chart of the proportion of database space.
4. The space occupied by TOP objects.
PostgreSQL relies on rich SQL grammar, drawing, small CASE.
Drawing an example of SQL
https://wiki.postgresql.org/wiki/Pie_Charts
The method of drawing with psql terminal is as follows:
1. Setting variables, breadth and height of pie chart, characters representing different colors, etc.
\set width 80 \set height 25 \set radius 1.0 \set colours '''#;o:X"@+-=123456789abcdef'''
2. Drawing DEMO SQL, converting 4 rows of records into pie charts
These four lines are recorded as follows:
VALUES ('red',1), ('blue',2), ('orange',3), ('white',4) )
Drawing SQL as follows
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. The image results are as follows.
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)
Is it interesting?
Converting Drawing SQL to Function Interface to Provide Arbitrary Calls
1. Function interfaces are as follows
create or replace function gen_charts( sql text, -- SQL，Returns two columns, the first is the description, and the second is the value of the description. width int default 80, height int default 25, radius numeric default 1.0, -- change into float8 Type, print solid pie chart 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;
Examples of Graphic Function Interface
1. Print the proportion of different object types in the current database
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. Print objects larger than 8K, the top ten objects in the spatial ranking, their respective proportion
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. Print the space proportion of different databases
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)