Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

Simple ASCII Charting Using SQL

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!

Sign In or Register to comment.

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.