Periscope Data
The world’s fastest visualization platform for data analysts.

ASCII Art Charts in the Terminal

July 1, 2014

Charts The Old-Fashioned Way

At Periscope we love charts. Chart on dashboards. Charts on TVs. Charts in email. And when we’re at the the command line, charts in the terminal! Here’s a fun SQL trick for making charts like this:

     dt     |   ct   |                 chart                   
------------+--------+---------------------------------------- 
 2014-06-30 | 167916 | ========================== 
 2014-06-29 |  93729 | ============== 
 2014-06-28 | 115240 | ================== 
 2014-06-27 | 243014 | ====================================== 
 2014-06-26 | 219843 | ================================== 
 2014-06-25 | 184825 | ============================ 
 2014-06-24 | 239193 | ===================================== 
 2014-06-23 | 234194 | ==================================== 
 2014-06-22 |  79145 | ============ 
 2014-06-21 | 131561 | ====================

The query we’ll chart will be a simple count of events by date:

select
  date(created_at) dt,
  count(1) ct
from events
group by 1 order by 1 desc
limit 10

Postgres and Redshift

It’s very easy to make a chart column in Postgres and Redshift with a window function. Just add this line:

repeat('=', (50 * count(1)/max(count(1)) over ())::int) chart

Breaking it down:

  • max(count(1)) over () is the window function, it gets the maximum count(1) across all of the rows in the result set
  • Dividing count(1) by the windowed max gives us this row’s fraction of the result set maximum. Multiplying that by 50 gives us an integer between 0 and 50 that represents the length of the bar.
  • repeat('=', N) repeats '=' the first string argument N times - in our case the length of each row’s bar.

Here’s the full query:

select
  date(created_at) dt,
  count(1) ct,
  repeat('=', (50 * count(1)/max(count(1)) over ())::int) chart
from events
group by 1 order by 1 desc
limit 10

MySQL

MySQL doesn’t have window functions but we can achieve the same effect with a variable and a subquery. The inner query will get the counts per date and save the maximum count seen, the outer query shows the dates, counts, and chart:

select
  dt, ct, 
  repeat('=', 50 * ct / @max_ct) chart
from (
  select
    date(created_at) dt,
    case 
      when isnull(@max_ct) or @max_ct < count(1
        then @max_ct := count(1
      else count(1)
    end ct
  from events
  group by 1 order by 1 desc
  limit 10) t

Breaking it down:

  • @max_ct is the maximum-seen count. Initially it’s null, and then we use a case statement to assign it to the current row’s count if it’s higher. In both branches of the case statement we’re returning that row’s count, so the ct column stays correct.
  • The repeat('=', N) function is the same as in Postgres. We’re using it in an outer query so that we have access to @max_ct.

And that’s how to make ASCII art charts in the terminal. If you prefer your charts on dashboards, sign up for Periscope!

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?