Simple ASCII Charting Using SQL

Jim_Knicely · March 2019

It is very easy to create a simple ASCII chart in Vertica using a SQL analytic function!

Example:

dbadmin=> SELECT c1 a_date,
dbadmin->        count(*) cnt
dbadmin->   FROM some_data_2_chart
dbadmin->  GROUP BY 1
dbadmin->  ORDER BY 1 DESC;
   a_date   |   cnt
------------+---------
2019-03-20 | 7600474
2019-03-19 | 4254718
2019-03-18 | 7327122
2019-03-17 | 8274362
2019-03-16 | 6151465
(5 rows)

dbadmin=> SELECT c1 a_date,
dbadmin->        COUNT(1) cnt,
dbadmin->        REPEAT('*', (50 * COUNT(1)/MAX(COUNT(1)) OVER ())::INT) chart
dbadmin->   FROM some_data_2_chart
dbadmin->  GROUP BY 1
dbadmin->  ORDER BY 1 DESC;
   a_date   |   cnt   |                       chart
------------+---------+----------------------------------------------------
2019-03-20 | 7600474 | **********************************************
2019-03-19 | 4254718 | **************************
2019-03-18 | 7327122 | ********************************************
2019-03-17 | 8274362 | **************************************************
2019-03-16 | 6151465 | *************************************
(5 rows)

It’s a lot easier to visualize the differences in row counts when viewing them as a chart column!

Helpful Links:
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/String/REPEAT.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/MAXAnalytic.htm
https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm

Have fun!