Saturday, October 1, 2022
HomeJavaEasy methods to Plot an ASCII Bar Chart with SQL – Java,...

Easy methods to Plot an ASCII Bar Chart with SQL – Java, SQL and jOOQ.


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 alternative
  • constants: 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.



RELATED ARTICLES

LEAVE A REPLY

Please enter your comment!
Please enter your name here

Most Popular

Recent Comments