with laser_gun_measurements as
( select time, distance -- 3 = 110, 2 = 75, 2.5 = 93, 3,5=124,4 = 145 4.5 = 168
from table( gun_measurement_tbl_t
( gun_measurement_t(0, 850.0)
, gun_measurement_t(0.1, 846.8)
, gun_measurement_t(0.2, 843.5)
, gun_measurement_t(0.3, 840.0)
, gun_measurement_t(0.5, 833.0)
, gun_measurement_t(0.6, 829.5)
, gun_measurement_t(0.7, 826.0)
, gun_measurement_t(0.9, 818.6)
, gun_measurement_t(1.1, 812.1)
, gun_measurement_t(1.2, 808.8)
, gun_measurement_t(1.3, 805.2)
, gun_measurement_t(1.5, 797.8)
, gun_measurement_t(1.6, 793.9)
, gun_measurement_t(1.7, 790.3)
, gun_measurement_t(1.9, 783.4)
, gun_measurement_t(2.1, 776.1)
, gun_measurement_t(2.2, 772.7)
, gun_measurement_t(2.3, 769.1)
, gun_measurement_t(2.4, 765.6)
, gun_measurement_t(2.5, 762.1)
, gun_measurement_t(2.6, 758.5)
, gun_measurement_t(2.7, 755.0)
, gun_measurement_t(2.8, 751.4)
, gun_measurement_t(2.9, 747.8)
, gun_measurement_t(3.0, 744.1)
, gun_measurement_t(3.1, 740.4)
, gun_measurement_t(3.2, 736.6)
, gun_measurement_t(3.3, 732.9)
, gun_measurement_t(3.4, 729.3)
, gun_measurement_t(3.5, 725.6)
, gun_measurement_t(3.6, 722.0)
, gun_measurement_t(3.7, 718.4)
, gun_measurement_t(3.8, 714.9)
, gun_measurement_t(3.9, 711.5)
, gun_measurement_t(4.0, 708.0)
, gun_measurement_t(4.1, 704.6)
, gun_measurement_t(4.2, 701.2)
, gun_measurement_t(4.3, 697.8)
, gun_measurement_t(4.4, 694.4)
, gun_measurement_t(4.5, 691.3)
)
)
)
, distances_covered as
( select time
, time - lag(time) over (order by time) period
, distance - lag(distance) over (order by time) delta_distance
from laser_gun_measurements
)
, speed_measurements as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select time
, abs(delta_distance/period * 3.6) speed
, min(abs(delta_distance/period * 3.6)) over (order by time range between 0 preceding and 2 following) min_speed_in_window
from distances_covered
)
select max(time) keep (dense_rank first order by min_speed_in_window desc)
, max(min_speed_in_window) keep (dense_rank first order by min_speed_in_window desc) maximum_two_sec_speed
from speed_measurements
-- the scatterplot for measured distance:
with laser_gun_measurements as
( select time, distance -- 3 = 110, 2 = 75, 2.5 = 93, 3,5=124,4 = 145 4.5 = 168
from table( gun_measurement_tbl_t
( gun_measurement_t(0, 850.0)
, gun_measurement_t(0.1, 846.8)
, gun_measurement_t(0.2, 843.5)
, gun_measurement_t(0.3, 840.0)
, gun_measurement_t(0.5, 833.0)
, gun_measurement_t(0.6, 829.5)
, gun_measurement_t(0.7, 826.0)
, gun_measurement_t(0.9, 818.6)
, gun_measurement_t(1.1, 812.1)
, gun_measurement_t(1.2, 808.8)
, gun_measurement_t(1.3, 805.2)
, gun_measurement_t(1.5, 797.8)
, gun_measurement_t(1.6, 793.9)
, gun_measurement_t(1.7, 790.3)
, gun_measurement_t(1.9, 783.4)
, gun_measurement_t(2.1, 776.1)
, gun_measurement_t(2.2, 772.7)
, gun_measurement_t(2.3, 769.1)
, gun_measurement_t(2.4, 765.6)
, gun_measurement_t(2.5, 762.1)
, gun_measurement_t(2.6, 758.5)
, gun_measurement_t(2.7, 755.0)
, gun_measurement_t(2.8, 751.4)
, gun_measurement_t(2.9, 747.8)
, gun_measurement_t(3.0, 744.1)
, gun_measurement_t(3.1, 740.4)
, gun_measurement_t(3.2, 736.6)
, gun_measurement_t(3.3, 732.9)
, gun_measurement_t(3.4, 729.3)
, gun_measurement_t(3.5, 725.6)
, gun_measurement_t(3.6, 722.0)
, gun_measurement_t(3.7, 718.4)
, gun_measurement_t(3.8, 714.9)
, gun_measurement_t(3.9, 711.5)
, gun_measurement_t(4.0, 708.0)
, gun_measurement_t(4.1, 704.6)
, gun_measurement_t(4.2, 701.2)
, gun_measurement_t(4.3, 697.8)
, gun_measurement_t(4.4, 694.4)
, gun_measurement_t(4.5, 691.3)
)
)
)
, chart_query as -- the query that drives the chart;
( select time x
, distance y
from laser_gun_measurements
)
, chart_dimensions as
( select 20 height
, 100 width -- the width of the chart, can be changed
, 10 x_margin -- positions to the left of the y-axis
, 5 y_marker_distance
, 20 x_marker_distance
, '*' marker
from dual
)
, limits as
( select min(y)*0.95 low_limit -- decide to use 0 as the baseline for the chartÅ› y-axis or some other value
, max(y)*1.05 high_limit
, min(x) start_x
, max(x) end_x
from chart_query
)
, lines as
( select level line
from chart_dimensions
connect by level <= height
)
, y_markers as
( select line
, (height-line)/height * (high_limit - low_limit) + low_limit marker_value
from lines cross join chart_dimensions cross join limits
where mod(height - line, y_marker_distance) = 0
)
, columns as
( select level x
from chart_dimensions
connect by level <= width
)
, x_axis as
( select x
, case mod(width - x, x_marker_distance)
when 0 then '|' else '_'
end character
, case
when mod(width-x , x_marker_distance)= 0 then substr( lpad(x/width * ( end_x- start_x) + start_x,4), 1,4)
when mod(width -x, x_marker_distance)< 4 then ''
else ' '
end marker_value
from columns cross join chart_dimensions cross join limits
)
, x_axis_1 (axis, x) as
( select character axis
, x
from x_axis
where x = 1
union all
select axis || character
, x_axis.x
from x_axis_1
join
x_axis
on (x_axis_1.x +1 = x_axis.x)
)
, x_axis_2 (axis, x) as
( select to_char(marker_value) axis
, x
from x_axis
where x = 1
union all
select axis || marker_value
, x_axis.x
from x_axis_2
join
x_axis
on (x_axis_2.x +1 = x_axis.x)
)
, values_on_line as
(
select line
, c.x
, round((c.x - start_x)/(end_x - start_x) * width) col
from lines l
cross join limits
cross join chart_dimensions
left outer join
chart_query c
on (c.y between (height-line-0.5)/height * (high_limit - low_limit) + low_limit
and
(height-line+0.5)/height * (high_limit - low_limit) + low_limit
)
)
, chart_lines (line, chart_line, col) as
( select line
, '|' axis
, 0
from lines
union all
select c.line
, chart_line|| case when v.line is null then ' ' else marker end
, c.col+1
from chart_lines c
left outer join
values_on_line v
on (v.line = c.line and c.col+1 = v.col)
cross join chart_dimensions
where c.col < width
)
select line
, substr(lpad(nvl(to_char(round(marker_value,1)),' '),25),26-x_margin,x_margin) ||chart_line y
from lines
left outer join
y_markers
using (line)
join
chart_lines
using (line)
cross join chart_dimensions
where chart_lines.col = width
union all
select height+1 line, lpad(' ',x_margin)||axis
from x_axis_1 cross join chart_dimensions
where x = width
union all
select height + 2 line, lpad(' ',x_margin)||axis
from x_axis_2 cross join chart_dimensions
where x = width
order
by line
-- the scatterplot for speed:
with laser_gun_measurements as
( select time, distance -- 3 = 110, 2 = 75, 2.5 = 93, 3,5=124,4 = 145 4.5 = 168
from table( gun_measurement_tbl_t
( gun_measurement_t(0, 850.0)
, gun_measurement_t(0.1, 846.8)
, gun_measurement_t(0.2, 843.5)
, gun_measurement_t(0.3, 840.0)
, gun_measurement_t(0.5, 833.0)
, gun_measurement_t(0.6, 829.5)
, gun_measurement_t(0.7, 826.0)
, gun_measurement_t(0.9, 818.6)
, gun_measurement_t(1.1, 812.1)
, gun_measurement_t(1.2, 808.8)
, gun_measurement_t(1.3, 805.2)
, gun_measurement_t(1.5, 797.8)
, gun_measurement_t(1.6, 793.9)
, gun_measurement_t(1.7, 790.3)
, gun_measurement_t(1.9, 783.4)
, gun_measurement_t(2.1, 776.1)
, gun_measurement_t(2.2, 772.7)
, gun_measurement_t(2.3, 769.1)
, gun_measurement_t(2.4, 765.6)
, gun_measurement_t(2.5, 762.1)
, gun_measurement_t(2.6, 758.5)
, gun_measurement_t(2.7, 755.0)
, gun_measurement_t(2.8, 751.4)
, gun_measurement_t(2.9, 747.8)
, gun_measurement_t(3.0, 744.1)
, gun_measurement_t(3.1, 740.4)
, gun_measurement_t(3.2, 736.6)
, gun_measurement_t(3.3, 732.9)
, gun_measurement_t(3.4, 729.3)
, gun_measurement_t(3.5, 725.6)
, gun_measurement_t(3.6, 722.0)
, gun_measurement_t(3.7, 718.4)
, gun_measurement_t(3.8, 714.9)
, gun_measurement_t(3.9, 711.5)
, gun_measurement_t(4.0, 708.0)
, gun_measurement_t(4.1, 704.6)
, gun_measurement_t(4.2, 701.2)
, gun_measurement_t(4.3, 697.8)
, gun_measurement_t(4.4, 694.4)
, gun_measurement_t(4.5, 691.3)
)
)
)
, distances_covered as
( select time
, time - lag(time) over (order by time) period
, distance - lag(distance) over (order by time) delta_distance
from laser_gun_measurements
)
, chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
( select time x
, abs(delta_distance/period * 3.6) y
from distances_covered
)
, chart_dimensions as
( select 20 height
, 100 width -- the width of the chart, can be changed
, 10 x_margin -- positions to the left of the y-axis
, 5 y_marker_distance
, 20 x_marker_distance
, '*' marker
from dual
)
, limits as
( select min(y)*0.8 low_limit -- decide to use 0 as the baseline for the chartÅ› y-axis or some other value
, max(y)*1.2 high_limit
, min(x) start_x
, max(x) end_x
from chart_query
)
, lines as
( select level line
from chart_dimensions
connect by level <= height
)
, y_markers as
( select line
, (height-line)/height * (high_limit - low_limit) + low_limit marker_value
from lines cross join chart_dimensions cross join limits
where mod(height - line, y_marker_distance) = 0
)
, columns as
( select level x
from chart_dimensions
connect by level <= width
)
, x_axis as
( select x
, case mod(width - x, x_marker_distance)
when 0 then '|' else '_'
end character
, case
when mod(width-x , x_marker_distance)= 0 then substr( lpad(x/width * ( end_x- start_x) + start_x,4), 1,4)
when mod(width -x, x_marker_distance)< 4 then ''
else ' '
end marker_value
from columns cross join chart_dimensions cross join limits
)
, x_axis_1 (axis, x) as
( select character axis
, x
from x_axis
where x = 1
union all
select axis || character
, x_axis.x
from x_axis_1
join
x_axis
on (x_axis_1.x +1 = x_axis.x)
)
, x_axis_2 (axis, x) as
( select to_char(marker_value) axis
, x
from x_axis
where x = 1
union all
select axis || marker_value
, x_axis.x
from x_axis_2
join
x_axis
on (x_axis_2.x +1 = x_axis.x)
)
, values_on_line as
(
select line
, c.x
, round((c.x - start_x)/(end_x - start_x) * width) col
from lines l
cross join limits
cross join chart_dimensions
left outer join
chart_query c
on (c.y between (height-line-0.5)/height * (high_limit - low_limit) + low_limit
and
(height-line+0.5)/height * (high_limit - low_limit) + low_limit
)
)
, chart_lines (line, chart_line, col) as
( select line
, '|' axis
, 0
from lines
union all
select c.line
, chart_line|| case when v.line is null then ' ' else marker end
, c.col+1
from chart_lines c
left outer join
values_on_line v
on (v.line = c.line and c.col+1 = v.col)
cross join chart_dimensions
where c.col < width
)
select line
, substr(lpad(nvl(to_char(round(marker_value,1)),' '),25),26-x_margin,x_margin) ||chart_line y
from lines
left outer join
y_markers
using (line)
join
chart_lines
using (line)
cross join chart_dimensions
where chart_lines.col = width
union all
select height+1 line, lpad(' ',x_margin)||axis
from x_axis_1 cross join chart_dimensions
where x = width
union all
select height + 2 line, lpad(' ',x_margin)||axis
from x_axis_2 cross join chart_dimensions
where x = width
order
by line