cdussud · February 17, 2021 20:42
diff --git a/quickly_increasing_timestamps.sql b/quickly_increasing_timestamps.sql

 -- Generates timestamps that follow a function: y = r^x 
 with
 quickly_increasing_timestamps as (
    select
        '2020-01-01'::timestamp as time_start,
        sysdate as time_end,
        date_diff('minute', time_start, time_end) as time_span_minutes,

        random() as x,  -- generate a random value from 0 to 1 for x
        5 as r,
        r^x as y,

        r^0 as y_start,
        r^1 as y_end,
        -- need to linearly scale y to between 0 and 1 to keep things simple
        -- at x = 0 y is 1 and at x = 1 y is 5
        -- subtract 1 to get between 0 and 4, then divide by 4 to get between 0 and 1
        (y-y_start)/(y_end - y_start) as scaled_y,

        date_add('minute',  -(time_span_minutes * scaled_y)::integer, time_end) as ts
    from dw_demo.numbers n 
    where n.number < time_span_minutes / 2  -- one timestamp every two minutes
 )

 select
    date_trunc('day', ts)::date AS day,
    count(1) as total_timestamps
 from quickly_increasing_timestamps
 group by day
 order by day asc

	-- Generates timestamps that follow a function: y = r^x
	with
	quickly_increasing_timestamps as (
	select
	'2020-01-01'::timestamp as time_start,
	sysdate as time_end,
	date_diff('minute', time_start, time_end) as time_span_minutes,

	random() as x, -- generate a random value from 0 to 1 for x
	5 as r,
	r^x as y,

	r^0 as y_start,
	r^1 as y_end,
	-- need to linearly scale y to between 0 and 1 to keep things simple
	-- at x = 0 y is 1 and at x = 1 y is 5
	-- subtract 1 to get between 0 and 4, then divide by 4 to get between 0 and 1
	(y-y_start)/(y_end - y_start) as scaled_y,

	date_add('minute', -(time_span_minutes * scaled_y)::integer, time_end) as ts
	from dw_demo.numbers n
	where n.number < time_span_minutes / 2 -- one timestamp every two minutes
	)

	select
	date_trunc('day', ts)::date AS day,
	count(1) as total_timestamps
	from quickly_increasing_timestamps
	group by day
	order by day asc