85 lines
3.0 KiB
SQL
85 lines
3.0 KiB
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; |