Skip to content

Instantly share code, notes, and snippets.

@ddrscott

ddrscott/histogram.sql

Last active December 9, 2022 18:03
Show Gist options
  • Save ddrscott/c81e0ce66fd7cb92b4132a01ab213fd6 to your computer and use it in GitHub Desktop.
Save ddrscott/c81e0ce66fd7cb92b4132a01ab213fd6 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. ddrscott revised this gist Aug 10, 2017. 1 changed file with 22 additions and 16 deletions.
    38 changes: 22 additions & 16 deletions histogram.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,28 +1,32 @@
    WITH params AS (
    -- Parameters for down stream queries
    SELECT
    100 AS max_num,
    10000 AS samples,
    15 AS bucket_count,
    80 AS max_bars
    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::numeric) * bucket)::int AS min_range,
    floor((max_num::numeric / bucket_count::numeric) * (bucket + 1) - 1)::int AS max_range
    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)
    ),
    numbers AS (
    -- Change this query to select real data.
    -- For now we make random set of numbers.
    SELECT
    floor(random() * max_num)::int AS num
    FROM params,
    generate_series(1, samples)
    ),
    counts AS (
    -- Join numbers with buckets and count up how many fall between the ranges
    SELECT
    @@ -59,7 +63,9 @@ graph AS (
    max_range,
    count_num,
    repeat('0', (bar_pct * max_bars)::int) AS chart
    FROM params, percentages
    FROM params,
    percentages
    )
    -- Select which part of the query to display by changing the `FROM` target
    SELECT * FROM graph;
    SELECT * FROM graph
    ;
  2. ddrscott revised this gist Aug 10, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion histogram.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -58,7 +58,7 @@ graph AS (
    min_range,
    max_range,
    count_num,
    repeat('0', (bar_pct * max_bars)::int)
    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
  3. ddrscott revised this gist Aug 10, 2017. No changes.
  4. ddrscott created this gist Aug 10, 2017.
    65 changes: 65 additions & 0 deletions histogram.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,65 @@
    WITH params AS (
    -- Parameters for down stream queries
    SELECT
    100 AS max_num,
    10000 AS samples,
    15 AS bucket_count,
    80 AS max_bars
    ),
    buckets AS (
    -- Build list of bucket ranges
    SELECT
    bucket,
    floor((max_num::numeric / bucket_count::numeric) * bucket)::int AS min_range,
    floor((max_num::numeric / bucket_count::numeric) * (bucket + 1) - 1)::int AS max_range
    FROM params,
    generate_series(0, bucket_count - 1) AS t(bucket)
    ),
    numbers AS (
    -- Change this query to select real data.
    -- For now we make random set of numbers.
    SELECT
    floor(random() * max_num)::int AS num
    FROM params,
    generate_series(1, samples)
    ),
    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)
    FROM params, percentages
    )
    -- Select which part of the query to display by changing the `FROM` target
    SELECT * FROM graph;