Blog

At Sisense 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 Sisense for Cloud Data Teams!