MySQL solution for creating horizontal histogram

Preface

Histogram is a basic statistical information provided by RDBMS. Its most typical use is to estimate the selectivity of query predicates in order to select an optimized query execution plan. Common types of histograms include: equal-width histogram, equal-height histogram, V-optimized histogram, MaxDiff histogram, etc.

Note: The test database version is MySQL 8.0

If you need to create tables and enter data under user scott, you can refer to:

Scott creates tables and enters data SQL script

1. Demand

Use SQL to generate a horizontally extending histogram.

For example, a horizontal histogram is used to display the number of volunteers in each department, with an asterisk "*" representing an employee.

The returned result set should be like:

±-------±-------+
| deptno | cnt |
±-------±-------+
| 10 | *** |
| 20 | ***** |
| 30 | ****** |
±-------±-------+

2. Solution

Use group by to group, find the total number, and then fill it with lpad

select deptno,
 lpad('*',count(*),'*') as cnt
 from emp
 group by deptno;

Testing Log:

mysql> select deptno,
 -> lpad('*',count(*),'*') as cnt
 -> from emp
 -> group by deptno;
+--------+--------+
| deptno | cnt |
+--------+--------+
| 10 | *** |
| 20 | ***** |
| 30 | ****** |
+--------+--------+
3 rows in set (0.00 sec)

Summarize

This is the end of this article about creating a horizontal histogram in MySQL. For more relevant content about creating a horizontal histogram in MySQL, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!