IIabm/SQL_analysis_experiment_test.sql

select * from iiabmdb.with_exp_result limit 0, 20;
select count(distinct s_id) from iiabmdb.with_exp_result;
select count(*) from iiabmdb.with_exp_sample;

select distinct s_id, id_firm, id_product from iiabmdb.with_exp_result order by s_id, id_firm, id_product;

select distinct s_id, count(distinct id_firm, id_product) as count_firm_prod from iiabmdb.with_exp_result group by s_id;

select distinct s_id, count(distinct id_firm, id_product) as count_firm_prod 
from iiabmdb.with_exp_result group by s_id;

select distinct 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
from iiabmdb.with_exp_result group by s_id;

# 控制问题需要处理，否则最后 experiment avg出来的东西不对
# 1 2 3
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
from (
select sample.id, idx_scenario, count_firm_prod, count_firm, count_prod 
from iiabmdb.with_exp_sample as sample
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
from iiabmdb.with_exp_result group by s_id) as s_count
on sample.id = s_count.s_id) as secnario_count
group by idx_scenario;

# 4 5 6
select sample.id, idx_scenario, max_ts_firm_prod, max_ts_firm, max_ts_prod
from iiabmdb.with_exp_sample as sample
left join iiabmdb.with_exp_experiment as experiment
on sample.e_id = experiment.id
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
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
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 8 9 
select sample.id, idx_scenario, n_remove_firm_prod, n_all_prod_remove_firm, end_ts
from iiabmdb.with_exp_sample as sample
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 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
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
left join
(select s_id, max(ts) as end_ts 
from iiabmdb.with_exp_result
group by s_id) as s_end_ts
on sample.id = s_end_ts.s_id;