ddrscott · December 9, 2022 18:03 · ddrscott · Aug 10, 2017
diff --git a/histogram.sql b/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
 ;
	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
	;