IIabm/SQL_analysis_experiment.sql

select distinct experiment.idx_scenario, 
n_max_trial, prf_size, prf_conn, cap_limit_prob_type, cap_limit_level, diff_new_conn, remove_t, netw_prf_n,
mean_count_firm_prod, mean_count_firm, mean_count_prod,
mean_max_ts_firm_prod, mean_max_ts_firm, mean_max_ts_prod,
mean_n_remove_firm_prod, mean_n_all_prod_remove_firm, mean_end_ts
from iiabmdb.with_exp_experiment as experiment
left join 
(
select 
idx_scenario,
sum(count_firm_prod) / count(*) as mean_count_firm_prod, # Note to use count(*), to include NULL
sum(count_firm) / count(*) as mean_count_firm,
sum(count_prod) / count(*) as mean_count_prod,
sum(max_ts_firm_prod) / count(*) as mean_max_ts_firm_prod,
sum(max_ts_firm) / count(*) as mean_max_ts_firm,
sum(max_ts_prod) / count(*) as mean_max_ts_prod,
sum(n_remove_firm_prod) / count(*) as mean_n_remove_firm_prod,
sum(n_all_prod_remove_firm) / count(*) as mean_n_all_prod_remove_firm,
sum(end_ts) / count(*) as mean_end_ts
from (
select sample.id, idx_scenario,
count_firm_prod, count_firm, count_prod,
max_ts_firm_prod, max_ts_firm, max_ts_prod,
n_remove_firm_prod, n_all_prod_remove_firm, end_ts
from iiabmdb.with_exp_sample as sample
# 1 2 3 + 9
left join iiabmdb.with_exp_experiment as experiment
on sample.e_id = experiment.id
left join (select s_id, 
count(distinct id_firm, id_product) as count_firm_prod,
count(distinct id_firm) as count_firm,
count(distinct id_product) as count_prod,
max(ts) as end_ts
from iiabmdb.with_exp_result group by s_id) as s_count
on sample.id = s_count.s_id
# 4
left join # firm prod
(select s_id, max(ts) as max_ts_firm_prod from
(select s_id, id_firm, id_product, min(ts) as ts
from iiabmdb.with_exp_result
where `status` = "D"
group by s_id, id_firm, id_product) as ts
group by s_id) as s_max_ts_firm_prod
on sample.id = s_max_ts_firm_prod.s_id
# 5
left join # firm
(select s_id, max(ts) as max_ts_firm from
(select s_id, id_firm, min(ts) as ts
from iiabmdb.with_exp_result
where `status` = "D"
group by s_id, id_firm) as ts
group by s_id) as s_max_ts_firm
on sample.id = s_max_ts_firm.s_id
# 6
left join # prod
(select s_id, max(ts) as max_ts_prod from
(select s_id, id_product, min(ts) as ts
from iiabmdb.with_exp_result
where `status` = "D"
group by s_id, id_product) as ts
group by s_id) as s_max_ts_prod
on sample.id = s_max_ts_prod.s_id
# 7 
left join
(select s_id, count(distinct id_firm, id_product) as n_remove_firm_prod
from iiabmdb.with_exp_result
where `status` = "R"
group by s_id) as s_n_remove_firm_prod
on sample.id = s_n_remove_firm_prod.s_id
# 8
left join
(select s_id, count(distinct id_firm) as n_all_prod_remove_firm from
(select s_id, id_firm, count(distinct id_product) as n_remove_prod
from iiabmdb.with_exp_result
where `status` = "R"
group by s_id, id_firm) as s_n_remove_prod
left join iiabmdb_basic_info.firm_n_prod as firm_n_prod
on s_n_remove_prod.id_firm = firm_n_prod.code
where n_remove_prod = n_prod
group by s_id) as s_n_all_prod_remove_firm
on sample.id = s_n_all_prod_remove_firm.s_id
) as secnario_count
group by idx_scenario
) as secnario_mean
on experiment.idx_scenario = secnario_mean.idx_scenario;