SQL PIE CHARTS AND DOUGHNUT CHARTS

Pie charts and Doughnut charts are a type of graph that represents the data in a circular graph. The pie slices show the data's relative size, and it is a type of pictorial representation of data. A pie chart requires a list of categorical variables and numerical variables. Here, the term “pie” represents the whole, and the “slices” represent the parts of the whole.

The “pie chart” and “doughnut chart” divides the circular statistical graphic into sectors or sections to illustrate the numerical problems. Each sector denotes a proportionate part of the whole. Pie-chart works best at that time to find out the composition of something. In most cases, pie charts replace other graphs like bar graphs, line plots, histograms, etc.

In this blog, I have provided a set of codes written in PostgreSQL, creating a dynamic pie chart and doughnut chart while executing in a Postgres server.

Table Creation:

At first, a table named Employees was created and values are inserted into the table as below:

The SQL code is given below. The code walk-through is provided at the end of the code.

\set width 80

\set height 35

\set radius 1.0

\set colours '''#;o:X"@+-=123456789abcdef'''

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

(select emp_name,emp_salary from Employees

) 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 0.0 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;

Explanation of the psql code:

We would like to draw a pie chart based on the salary of a set of employees stored in the Employees table. The Employee's table content is shown below:

Select * from Employees

There are 5 employees with different salaries and the pie chart with these data will have 5 slices as shown below:

Consider the following first set of code:

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

(select emp_name,emp_salary from Employees ) AS data(name,value)

The execution of the above code produces the following result.

Let us do a detailed look at the result. The Slice represents the row number, name, and value from the Employees table, the percentage column provides the percentage of the respective person’s salary concerning the sum of all salaries and the radians provide the slice details (angle out of 2*PI() radians for the specific person from the X axis.

The computation logic is provided below:

Percentage = 100.0 * age of the person / Total Age

Radians (angle of the slice) = 2*PI() * sum of salaries from the first row to the current row / Total salaries

The radians will return values between 0 and 6.283 (PI() = 3.14 and 2PI() = 6.28)

Now to prepare a pie chart, a string array is prepared with the required number of rows and columns. In this case, the number of rows is 35 and the columns are 80. This array represents 80x35 elements and each element represents one point represented as (x,y). The center point of the array is 0,0 and the maximum length of each coordinate will be 1 which is the radius of the circle pie. So point (0,0) is the center point and all points (1,0), (-1,0), (0,1), (0,-1) represent endpoints that fall on the circumference of the pie chart circle.

To prepare the array, we need a column series and row series which is achieved by the following piece of code:

(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)

The above code generates a points array with the width x height (The width and heights are static variables provided at the beginning of the psql program. In our case, the width = 80 and height =35, so there will be 80 * 35 points in the array with 50% on the positive side and 50% on the negative side. The center point of the array is (0,0) and the center points of each side are (1,0), (-1,0), (0,1), and (0,-1) as represented in the following picture. Providing -1 in the generate_series is used to create positive and negative number series.

The following piece of code is used for preparing and printing the pie chart.

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 0.0 AND :radius)

THEN ' '

ELSE substring(:colours,

(select min(slice) from slices where radians >= PI() + atan2(y,-x)), 1)

END AS c

1. Create an array with a specified size

2. Convert the array into a string array with a center point as 0,0

3. Select every point in the array

4. Find the length of the line joining that point from center point using the function sqrt() and pow() functions.

5. Check what all the points are falling outside the pie circle (logic: If the length of the line joining any point and origin is greater than the radius of the pie circle (in this case the radius is 1) then that point falls outside the pie-circle and replace that point with an empty character (in this case a space).

6. If the point falls within the pie circle, then find out the angle of the point from the x-axis using the atan2() function and find the pie slice in which that point is falling. 2*PI() * atan(y,-x) returns the angle of the point from x-axis. This angle can be from 0 to 2*PI() radians. This is illustrated below:

7. Replace the point with the character assigned for that pie slice.

8. Complete the cycle and prepare the entire pie with slices.

9. Print the array which will be the required pie chart.

The last part of the code is used to print some details like the character used for filling each pie slice, the input data, and the pie angle percentage of each slice.

UNION ALL

SELECT repeat(substring(:colours,slice,1), 2) || ' ' ||

name || ': ' ||

value || ' (' || round(percentage,0) || '%)'

FROM slices;

The following |set commands are used to assign a few local variables.

\set width 80

\set height 35

\set radius 1.0

\set colours '''#;o:X"@+-=123456789abcdef'''

Execution of these Codes:

Copy the psql code from the pgAdmin and paste into the PSQL Tool and click enter…

PIE-CHART

The pie-chart output for the data provided from employee_data is as follows:

DOUGHNUT CHART

A doughnut chart can be prepared for the same data by doing a small change in the following code:

CASE WHEN NOT (sqrt(pow(x, 2) + pow(y, 2)) BETWEEN 0.6 AND :radius)

THEN ' '

Functions Used:

Some functions used in this psql code are given below:

row_number(): Window function that assigns a sequential integer to each row in a result set.

array_to_string() is a system function that converts the elements of an array to a string and joins them using the specified delimiter.

array_agg() function is an aggregate function that accepts a set of values and returns an array in which each value in the set is assigned to an element of the array. The ORDER BY clause is an optional clause. It specifies the order of rows processed in the aggregation, which determines the order of the elements in the result array.

pow(a, b) function returns the bth power value of a. For example, pow(5,2) will return 25 (5*5).

sqrt() function returns the square root of a numeric value. For example, sqrt(25) will return 5.

substring() function extracts a substring from a string. For example substring(‘ABC’,1,1) returns ‘A’ and substring(‘ABCD’,2,2) returns ‘BC’

generate_series() returns sequential numbers between a start parameter and a stop parameter. When used to generate numeric data, generate_series() will increment the values by 1. However, there is an optional third parameter that can be used to specify the increment length, known as the step parameter.

Conclusion:

In PostgreSQL, which is a powerful open-source relational database management system, we don't directly create pie charts within the database itself. Instead,we will use SQL queries to retrieve the data needed for the pie chart and then use external tools or programming languages to generate the visualization.