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 it’s possible you’ll know, jOOQ can produce fancy charts out of your jOOQ outcomes. However that requires you employ jOOQ, and also you will not be 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 any case, 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 take a look at it. Look:
-- The instance makes use of https://www.jooq.org/sakila, however you possibly can simply exchange
-- the "supply" desk with the rest
with
-- This half is what you possibly can 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 gadgets:
constants as (
choose
-- the peak of the y axis
15 as top,
-- 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 info on the x axis by
-- - filling gaps (if int, bigint, numeric, timestamp,
-- timestamptz)
-- - scaling the x axis to "width"
true as normalise_x
),
-- The remaining does not must 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 okay, 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 (okay)
left be part of supply
on supply.key >= t.okay and supply.key < t.okay + kstep
group by okay
),
-- Change source_normalised by supply should 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), depend(*)::int)
else depend(*)::int
finish
from actual_source, constants
),
-- Extra 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, top) from dims_and_consts
),
-- Rendering the ASCII chart
chart (rn, chart) as (
choose
y,
lpad(y * (vmax - vmin) / top || '', label_pad)
|| ' | '
|| string_agg(
case
when top * 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, top, 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
;
Operating in opposition to 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 elements:
supply: The precise question, producing knowledge. That is what you possibly can substitute and place your personal, as an alternativeconstants: The configuration part, the place you possibly can tweak dimensions, bar chart characters, and many others.- the remainder, which you don’t must 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 forged that up to now to have the ability to get every day income. However in an effort to fill the gaps utilizing PostgreSQL’s generate_series, now we have to forged 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 a must to do is produce a set of information in a key/worth kind. You possibly can exchange this by the rest, 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 (presently) should patch the normalisation again to false (the padding of gaps isn’t right 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.
