Simple ASCII Charting Using SQL

Posted March 13, 2019 by Jim Knicely, Big Data Solutions Architect, Vertica

City in Blur Motion
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/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/String/REPEAT.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Analytic/MAXAnalytic.htm https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/Functions/Aggregate/COUNTAggregate.htm