---------------------------------------------------------------------------
-- Non-uniform

-- Truncate outliers in ratio_MCMN before taking average
create table mcv_results_03182158_nonunif_trunc_avg as
with ranked_nonunif as (
	select
		branch,
		title,
		count,
		dcount,
		num_mcvs,
		ratio_least_common_mcv as ratio_lcm,
		ratio_most_common_non_mcv as ratio_mcnm,
		dense_rank() over (
			partition by branch, title
			order by ratio_most_common_non_mcv
		) as dr
	from mcv_results_03182158
	where title not like '%unif%'
), ranked_distinct as (
	select
		*,
		-- need to do this way in a separate named view since 'count distinct'
		-- is not supported in window functions
		max(dr) over (partition by branch, title) as dc  
	from ranked_nonunif
)
select
	branch,
	title, 
	count, 
	dcount, 
	cast(avg(num_mcvs) as numeric(5)) as avg_num_mcvs, 
	cast(avg(ratio_lcm) as numeric(5,2)) as avg_ratio_lcm, 
	cast(avg(ratio_mcnm) as numeric(5,2)) as avg_ratio_mcnm 
from ranked_distinct
-- remove records having least and greatest distinct value per test run
where dr <> 1 and dr <> dc
group by 1,2,3,4
;


-- Compare ratio of actual to estimated cardinality of least common MCV
select 
	v20.title,
	v20.avg_ratio_mcnm  as v20_ratio_mcnm,
	cc05.avg_ratio_mcnm as cc05_ratio_mcnm,
	v25.avg_ratio_mcnm  as v25_ratio_mcnm
from mcv_results_03182158_nonunif_trunc_avg v20
join mcv_results_03182158_nonunif_trunc_avg v25  on v20.title=v25.title  and  v20.branch = 'V2_20' and v25.branch = 'V2_25'
join mcv_results_03182158_nonunif_trunc_avg cc05 on v20.title=cc05.title and cc05.branch = 'CC_05'
where v20.avg_num_mcvs < 100
or v25.avg_num_mcvs < 100
or cc05.avg_num_mcvs < 100
order by v20.title;


-- Compare number of MCVs
select 
	v20.title,
	v20.avg_num_mcvs  as v20_num_mcvs,
	cc05.avg_num_mcvs as cc05_num_mcvs,
	v25.avg_num_mcvs  as v25_num_mcvs
from mcv_results_03182158_nonunif_trunc_avg v20
join mcv_results_03182158_nonunif_trunc_avg v25  on v20.title=v25.title  and  v20.branch = 'V2_20' and v25.branch = 'V2_25'
join mcv_results_03182158_nonunif_trunc_avg cc05 on v20.title=cc05.title and cc05.branch = 'CC_05'
where v20.avg_num_mcvs < 100
or v25.avg_num_mcvs < 100
or cc05.avg_num_mcvs < 100
order by v20.title;


---------------------------------------------------------------------------
-- Uniform

-- Filter out results where all values fit in the MCV before taking average
create table mcv_results_03182158_unif as
select
	branch,
	title, 
	count, 
	dcount, 
	cast(avg(num_mcvs) as numeric(5)) as avg_num_mcvs, 
	cast(avg(ratio_least_common_mcv) as numeric(5,2)) as avg_ratio_lcm, 
	cast(avg(ratio_most_common_non_mcv) as numeric(5,2)) as avg_ratio_mcnm 
from mcv_results_03182158
where title like '%unif%'
and ratio_most_common_non_mcv <> 0
group by 1,2,3,4
;


-- Compare number of MCVs
select 
	v20.title,
	v20.avg_num_mcvs  as v20_num_mcvs,
	cc05.avg_num_mcvs as cc05_num_mcvs,
	v25.avg_num_mcvs  as v25_num_mcvs
from mcv_results_03182158_unif v20
join mcv_results_03182158_unif v25  on v20.title=v25.title  and  v20.branch = 'V2_20' and v25.branch = 'V2_25'
join mcv_results_03182158_unif cc05 on v20.title=cc05.title and cc05.branch = 'CC_05'
order by v20.title;

