In our previous article Aggregating NBA data, PostgreSQL vs MongoDB we spent time comparing the pretty new MongoDB Aggregation Framework with the decades old SQL aggregates. Today, let’s showcase more of those SQL aggregates, producing a nice histogram right from our SQL console.

Table of Contents

PostgreSQL and Mathematics

The other day while giving a Practical SQL training my attention drifted to the width_bucket function available as part of the Mathematical Functions and Operators PostgreSQL is offering to its fearless SQL users.

Here’s what the documentation says about it:

The function width_bucket(op numeric, b1 numeric, b2 numeric, count int) returns (as an int) the bucket to which operand would be assigned in an equidepth histogram with count buckets, in the range b1 to b2.

Let’s have a look at our dataset from the NBA games and statistics, and get back to counting rebounds in the drb field. A preliminary query informs us that we have stats ranging from 10 to 54 rebounds per team in a single game, a good information we can use in the following query:

  select width_bucket(drb, 10, 54, 9) as buckets,
         count(*)
    from team_stats
group by buckets
order by buckets;

We asked for 9 separations so we have 10 groups as a result:

 width_bucket | count 
--------------+-------
            1 |    52
            2 |  1363
            3 |  8832
            4 | 20917
            5 | 20681
            6 |  9166
            7 |  2093
            8 |   247
            9 |    20
           10 |     1
(10 rows)

Console Histograms

Now, what would it take to actually be able to display the full story right into our psql console, for preview before actually integrating a new diagram in our reporting solution? Turns out it’s not very complex.

First, we want to avoid hard coding the range of rebounds we’re processing, so we are going to compute that in a first step. Then we want the histogram data, which is a ordered list of ranges with a min and a max value and a frequency, which is how many games were recorded with a number or rebounds within any given bucket range. And last, we want to display something a little more visual than just a list of numbers:

with drb_stats as (
    select min(drb) as min,
           max(drb) as max
      from team_stats
),
     histogram as (
   select width_bucket(drb, min, max, 9) as bucket,
          int4range(min(drb), max(drb), '[]') as range,
          count(*) as freq
     from team_stats, drb_stats
 group by bucket
 order by bucket
)
 select bucket, range, freq,
        repeat('■',
               (   freq::float
                 / max(freq) over()
                 * 30
               )::int
        ) as bar
   from histogram;

The first part of the query is the drb_stats common table expression, and its role is to fetch the min and max values of drb in our whole dataset. It’s better than doing the query separately and then injecting the result in the next query, because there’s no roundtrip, but it’s basically the idea.

Then the histogram common table expression is the meat of the query, where we compute the bucket distribution of our data set. Finally, we use a cheap trick with the repeat function where we scale down the frequency into a display of 30 characters:

 bucket |  range  | freq  |              bar               
--------+---------+-------+--------------------------------
      1 | [10,15) |    52 | 
      2 | [15,20) |  1363 | ■■
      3 | [20,25) |  8832 | ■■■■■■■■■■■■■
      4 | [25,30) | 20917 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
      5 | [30,35) | 20681 | ■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
      6 | [35,40) |  9166 | ■■■■■■■■■■■■■
      7 | [40,45) |  2093 | ■■■
      8 | [45,50) |   247 | 
      9 | [50,54) |    20 | 
     10 | [54,55) |     1 | 
(10 rows)

Time: 53.570 ms

Also, as we’re using PostgreSQL, just having two columns with the min and max as separate values is not enough, what we actually need is a discrete range of rebounds for each bucket, hence using the int4range range constructor function.

Conclusion

PostgreSQL is YeSQL! PostgreSQL is YeSQL!

The only remaining step then consists into hacking our way into actually displaying something visual enough for a quick less-than-1-minute effort of data crunching, using the repeat function which is part of PostgreSQL String Functions and Operators. Note that we’re using the Window Function expression max(freq) over() to have access the highest frequency value from each and every result row.

By the way, the whole scripting and data and SQL is available at github/dimitri/nba, and there’s an Hacker News entry to comment on the article if you’re interested.