Plotting simple bar charts in Clickhouse
I have discovered great bar()
Clickhouse function recently. This allows plotting simple charts right inside your terminal to get quick overview of data.
So the function is pretty simple:
SELECT bar(10, 0, 15, 20);┌─bar(10, 0, 15, 20)─┐
│ █████████████▎ │
└────────────────────┘
In this example we draw bar which is of size 10 out of 15 (max), with physical width of 20:
This cool function can be used to automaticlly plot bars inside SELECT
queries:
WITH ( SELECT max(value) FROM data WHERE (entity_id = 34)
AND (property = 'usd') AND empty(object)
AND (year(at) = 2021) )
AS max_value
SELECT date(at), value, bar(value, 0, max_value, 30)
FROM data
WHERE (entity_id = 34) AND (property = 'usd') AND empty(object) AND (year(at) = 2021);┌───DATE(at)─┬───────value─┬─bar(value, 0, max_value, 30)───┐
│ 2021-02-01 │ 2966188533 │ ██████▊ │
│ 2021-03-01 │ 4267240303 │ █████████▊ │
│ 2021-09-01 │ 4428807027 │ ██████████▎ │
│ 2021-08-01 │ 4463003564 │ ██████████▍ │
│ 2021-05-01 │ 4959032942 │ ███████████▌ │
│ 2021-11-01 │ 5059683648 │ ███████████▋ │
│ 2021-04-01 │ 5069026304 │ ███████████▋ │
│ 2021-06-01 │ 5378356519 │ ████████████▌ │
│ 2021-07-01 │ 5575512564 │ █████████████ │
│ 2021-10-01 │ 5924804713 │ █████████████▋ │
│ 2021-12-01 │ 6675097986 │ ███████████████▌ │
│ 2021-12-31 │ 12833813608 │ ██████████████████████████████ │
└────────────┴─────────────┴────────────────────────────────┘
Here we first use WITH
statement to fetch max value for the given filters. You can skip this if you understand what are min/max values for your future query. Then we use bar()
function with value
column from query and max_value
variable to plot beautiful bars.