Watermill Queries for MP-CNN

mpcnn_watermill_queries.sql

-- Get experiment group name and counts for a dataset
select name, count(*) from experiments e join experiment_groups g on e.group_id=g.gid where json_extract(args, '$.dataset') == 'wikiqa' group by group_id;

-- Above query but also by Hyperband epochs
select name, json_extract(args, '$.epochs') as epochs, count(*) from experiments e join experiment_groups g on e.group_id=g.gid where json_extract(args, '$.dataset') == 'wikiqa' group by group_id, epochs;

-- Best performance regardless of search method
select name, json_extract(dev_metric, '$.map') as map, json_extract(args, '$.epochs') as epochs from experiments e join experiment_groups g on e.group_id=g.gid where json_extract(args, '$.dataset') == 'wikiqa' order by map desc limit 20;

-- manual deduping
select rowid, json_extract(args, '$.seed') as seed, json_extract(args, '$.dataset') as ds, json_extract(args, '$.arch') as arch, dev_metric from experiments where group_id=12 and ds='trecqa' order by seed, arch;

-- manual deduping but showing dup rowids together
select rowid, json_extract(args, '$.seed') as seed, json_extract(args, '$.dataset') as ds, json_extract(args, '$.arch') as arch, dev_metric metric, count(*) as cnt, group_concat(rowid) from experiments where group_id=12 and ds='sick' group by seed, arch, metric having cnt > 1 order by seed, arch;

-- hyperparam best
select rowid, json_extract(args, '$.arch') as arch, json_extract(args, '$.dataset') as dataset, json_extract(args, '$.dropout') as dropout, json_extract(args, '$.sparse_features') as sparse, json_extract(args, '$.attention') as attention, json_extract(args, '$.wide_conv') as wide, dev_metric, test_metric from experiments where group_id=27 and dataset='trecqa' and arch='mpcnn_no_per_dim_no_multi_pooling' and attention='none' order by json_extract(dev_metric, '$.cross_entropy_loss') asc limit 5;