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!

Tags: