No want for costly Tableau subscriptions. Ditch Microsoft Excel. Simply use native PostgreSQL to rapidly visualise your knowledge!
Right here’s an concept I had for some time. As you could know, jOOQ can produce fancy charts out of your jOOQ outcomes. However that requires you utilize jOOQ, and also you is probably not utilizing jOOQ, since you’re not coding in Java/Kotlin/Scala (in any other case, you’d be utilizing jOOQ). That’s OK. I assumed, why not do it with SQL (PostgreSQL, to be particular) immediately, then? In spite of everything, I’m me:
So, with the next fancy question, which I’ll keep and develop additional on this github repo, it is possible for you to to simply plot absolutely anything immediately out of your favorite SQL editor.
Simply have a look at it. Look:
-- The instance makes use of https://www.jooq.org/sakila, however you'll be able to simply substitute
-- the "supply" desk with anything
with
-- This half is what you'll be able to modify to adapt to your personal wants
--------------------------------------------------------------
-- Your knowledge producing question right here
supply (key, worth) as (
choose payment_date::date::timestamp, sum(quantity)
from fee
the place extract(yr from payment_date) < 2006
group by payment_date::date::timestamp
order by payment_date::date::timestamp
),
-- Some configuration objects:
constants as (
choose
-- the peak of the y axis
15 as peak,
-- the width of the x axis, if normalise_x, in any other case, ignored
25 as width,
-- the bar characters
'##' as characters,
-- the characters between bars
' ' as separator,
-- the padding of the labels on the y axis
10 as label_pad,
-- whether or not to normalise the information on the x axis by
-- - filling gaps (if int, bigint, numeric, timestamp,
-- timestamptz)
-- - scaling the x axis to "width"
true as normalise_x
),
-- The remainder would not should be touched
--------------------------------------
-- Pre-calculated dimensions of the supply knowledge
source_dimensions (kmin, kmax, kstep, vmin, vmax) as (
choose
min(key), max(key),
(max(key) - min(key)) / max(width),
min(worth), max(worth)
from supply, constants
),
-- Normalised knowledge, which fills the gaps in case the important thing knowledge
-- sort could be generated with generate_series (int, bigint,
-- numeric, timestamp, timestamptz)
source_normalised (key, worth) as (
choose ok, coalesce(sum(supply.worth), 0)
from source_dimensions
cross be part of constants
cross be part of lateral
generate_series(kmin, kmax, kstep) as t (ok)
left be part of supply
on supply.key >= t.ok and supply.key < t.ok + kstep
group by ok
),
-- Exchange source_normalised by supply when you do not just like the
-- normalised model
actual_source (i, key, worth) as (
choose row_number() over (order by key), key, worth
from source_normalised, constants
the place normalise_x
union all
choose row_number() over (order by key), key, worth
from supply, constants
the place not normalise_x
),
-- Pre-calculated dimensions of the particular knowledge
actual_dimensions (
kmin, kmax, kstep, vmin, vmax, width_or_count
) as (
choose
min(key), max(key),
(max(key) - min(key)) / max(width),
min(worth), max(worth),
case
when each(normalise_x) then least(max(width), rely(*)::int)
else rely(*)::int
finish
from actual_source, constants
),
-- Further comfort
dims_and_consts as (
with
temp as (
choose *,
(size(characters) + size(separator))
* width_or_count as bar_width
from actual_dimensions, constants
)
choose *,
(bar_width - size(kmin::textual content) - size(kmax::textual content))
as x_label_pad
from temp
),
-- A cartesian product for all (x, y) knowledge factors
x (x) as (
choose generate_series(1, width_or_count) from dims_and_consts
),
y (y) as (
choose generate_series(1, peak) from dims_and_consts
),
-- Rendering the ASCII chart
chart (rn, chart) as (
choose
y,
lpad(y * (vmax - vmin) / peak || '', label_pad)
|| ' | '
|| string_agg(
case
when peak * actual_source.worth / (vmax - vmin)
>= y then characters
else repeat(' ', size(characters))
finish, separator
order by x
)
from
x left be part of actual_source on actual_source.i = x,
y, dims_and_consts
group by y, vmin, vmax, peak, label_pad
union all
choose
0,
repeat('-', label_pad)
|| '-+-'
|| repeat('-', bar_width)
from dims_and_consts
union all
choose
-1,
repeat(' ', label_pad)
|| ' | '
|| case
when x_label_pad < 1 then ''
else kmin || repeat(' ', x_label_pad) || kmax
finish
from dims_and_consts
)
choose chart
from chart
order by rn desc
;
Working towards the sakila database, you’ll get this fancy chart:
chart |
----------------------------------------------------------------------------------------+
11251.7400 | ## |
10501.6240 | ## |
9751.50800 | ## |
9001.39200 | ## |
8251.27600 | ## |
7501.16000 | ## ## ## ## |
6751.04400 | ## ## ## ## |
6000.92800 | ## ## ## ## |
5250.81200 | ## ## ## ## ## ## |
4500.69600 | ## ## ## ## ## ## |
3750.58000 | ## ## ## ## ## ## ## ## |
3000.46400 | ## ## ## ## ## ## ## ## |
2250.34800 | ## ## ## ## ## ## ## ## ## ## ## |
1500.23200 | ## ## ## ## ## ## ## ## ## ## ## ## |
750.116000 | ## ## ## ## ## ## ## ## ## ## ## ## |
-----------+----------------------------------------------------------------------------|
| 2005-05-24 00:00:00 2005-08-23 00:00:00|
Isn’t that one thing!
How does it work?
The question has 3 components:
supply: The precise question, producing knowledge. That is what you’ll be able to substitute and place your personal, as an alternativeconstants: The configuration part, the place you’ll be able to tweak dimensions, bar chart characters, and so forth.- the remainder, which you don’t have to tamper with
The supply is only a question like this:
supply (key, worth) as (
choose payment_date::date::timestamp, sum(quantity)
from fee
the place extract(yr from payment_date) < 2006
group by payment_date::date::timestamp
order by payment_date::date::timestamp
)
It produces all income per fee date from the fee desk. Whereas payment_date is a timestamp, we solid that to this point to have the ability to get every day income. However with a view to fill the gaps utilizing PostgreSQL’s generate_series, we now have to solid the date worth again to timestamp, as a result of surprisingly, there’s no native generate_series(date, date) operate in PostgreSQL.
All it’s important to do is produce a set of knowledge in a key/worth type. You may substitute this by anything, e.g. to get cumulative income:
supply (key, worth) as (
choose
payment_date::date::timestamp,
sum(sum(quantity)) over (order by payment_date::date::timestamp)
from fee
the place extract(yr from payment_date) < 2006
group by payment_date::date::timestamp
order by payment_date::date::timestamp
)
… for which you (at present) need to patch the normalisation again to false (the padding of gaps isn’t appropriate but). Additionally, to save lots of house, I’ve made the bars a bit slimmer:
'#' as characters,
'' as separator,
false as normalise_x
And now you’ll get this good chart exhibiting the exponential enhance of income that we want so dearly to indicate our managers (it’s not really exponential, as a result of now, the gaps aren’t revered, however duh, it’s simply generated pattern knowledge):
chart |
-----------------------------------------------------+
66872.4100 | #|
62414.2493 | ##|
57956.0886 | ####|
53497.9280 | ######|
49039.7673 | #######|
44581.6066 | ##########|
40123.4460 | ###########|
35665.2853 | #############|
31207.1246 | ###############|
26748.9640 | ##################|
22290.8033 | ####################|
17832.6426 | ######################|
13374.4820 | #########################|
8916.32133 | #############################|
4458.16066 | #################################|
-----------+-----------------------------------------|
| 2005-05-24 00:00:00 2005-08-23 00:00:00|
Superior, huh! Mess around with it right here:
Ship your pull requests with enhancements. Challenges:
- Stacked charts
- Fill gaps additionally for cumulative knowledge
- Different options?
The sky is the restrict.
