Histogram for PostgreSQL without width_bucket.

histogram.sql

WITH params AS (
    -- Parameters for down stream queries
    SELECT
       15 AS bucket_count,
       80 AS max_bars
),
numbers AS (
    -- Change this query to select real data.
    -- For now we make random set of numbers.
    SELECT
        floor(random() * 100)::int AS num    
    FROM params,
         generate_series(1, 10000)
),
overall AS (
    SELECT
        MAX(num) max_num
    FROM numbers
),
buckets AS (
    -- Build list of bucket ranges
    SELECT
        bucket,
        floor((max_num::numeric / bucket_count) * bucket)::int AS min_range,
        floor((max_num::numeric / bucket_count) * (bucket + 1) - 1)::int AS max_range
    FROM params,
         overall,
         generate_series(0, bucket_count - 1) AS t(bucket)
),
counts AS (
    -- Join numbers with buckets and count up how many fall between the ranges
    SELECT
        bucket,
        min_range,
        max_range,
        COUNT(num) AS count_num
    FROM numbers
    JOIN buckets ON numbers.num BETWEEN min_range AND max_range
    GROUP BY bucket, min_range, max_range
    ORDER BY bucket
),
count_ranges AS (
    -- Figure out the min/max counts for each range.
    -- This is use to normalize the width of the graph.
    SELECT
        MIN(count_num) min_count_num,
        MAX(count_num) max_count_num,
        SUM(count_num) sum_count_num
    FROM counts
),
percentages AS (
    -- Calculate how close count_num is to the max count for the entire graph.
    SELECT
        counts.*,
        count_num::numeric / max_count_num AS bar_pct
    FROM params, counts, count_ranges
),
graph AS (
    -- Render the final chart
    SELECT
        bucket,
        min_range,
        max_range,
        count_num,
        repeat('0', (bar_pct * max_bars)::int) AS chart
    FROM params,
         percentages
)
-- Select which part of the query to display by changing the `FROM` target
SELECT * FROM graph
;

Sample Output:

 bucket | min_range | max_range | count_num |                                      chart
--------+-----------+-----------+-----------+----------------------------------------------------------------------------------
      0 |         0 |         5 |       601 | 00000000000000000000000000000000000000000000000000000000000000000
      1 |         6 |        12 |       734 | 00000000000000000000000000000000000000000000000000000000000000000000000000000000
      2 |        13 |        19 |       728 | 0000000000000000000000000000000000000000000000000000000000000000000000000000000
      3 |        20 |        25 |       574 | 00000000000000000000000000000000000000000000000000000000000000
      4 |        26 |        32 |       734 | 00000000000000000000000000000000000000000000000000000000000000000000000000000000
      5 |        33 |        39 |       678 | 00000000000000000000000000000000000000000000000000000000000000000000000000
      6 |        40 |        45 |       604 | 000000000000000000000000000000000000000000000000000000000000000000
      7 |        46 |        52 |       705 | 00000000000000000000000000000000000000000000000000000000000000000000000000000
      8 |        53 |        59 |       721 | 000000000000000000000000000000000000000000000000000000000000000000000000000000
      9 |        60 |        65 |       556 | 000000000000000000000000000000000000000000000000000000000000
     10 |        66 |        72 |       719 | 000000000000000000000000000000000000000000000000000000000000000000000000000000
     11 |        73 |        79 |       736 | 00000000000000000000000000000000000000000000000000000000000000000000000000000000
     12 |        80 |        85 |       613 | 0000000000000000000000000000000000000000000000000000000000000000000
     13 |        86 |        92 |       653 | 00000000000000000000000000000000000000000000000000000000000000000000000
     14 |        93 |        99 |       644 | 0000000000000000000000000000000000000000000000000000000000000000000000
(15 rows)

Time: 28.250 ms