-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathesgf_statistics.sql
54 lines (51 loc) · 18.7 KB
/
esgf_statistics.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
create table cross_dmart_project_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),project_name character varying(20),status smallint,month numeric(2,0),year numeric(4,0));
create table cross_dmart_project_host_geolocation (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),project_name character varying(20),status smallint,country_code character varying(10));
create table cmip6_dmart_experiment_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),experiment_id character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table cmip6_dmart_source_id_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),source_id character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table cmip6_dmart_variable_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),variable_code character varying(100),variable_long_name character varying(200), cf_standard_name character varying(200),status smallint,month numeric(2,0),year numeric(4,0));
create table cmip5_dmart_experiment_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),experiment_name character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table cmip5_dmart_model_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),model_name character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table cmip5_dmart_variable_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),variable_code character varying(20),variable_long_name character varying(200), cf_standard_name character varying(200),status smallint,month numeric(2,0),year numeric(4,0));
create table cordex_dmart_driving_model_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),driving_model character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table cordex_dmart_domain_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),domain character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table cordex_dmart_variable_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),variable_code character varying(20),variable_long_name character varying(200), cf_standard_name character varying(200),status smallint,month numeric(2,0),year numeric(4,0));
create table cordex_dmart_rcm_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),rcm_name character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table obs4mips_dmart_variable_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),variable_code character varying(20),variable_long_name character varying(200), cf_standard_name character varying(200),status smallint,month numeric(2,0),year numeric(4,0));
create table obs4mips_dmart_source_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),source_id character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table obs4mips_dmart_realm_host_time (id bigserial,total_size bigint,number_of_downloads bigint,number_of_replica_downloads bigint,average_duration bigint,host_name character varying(100),realm character varying(50),status smallint,month numeric(2,0),year numeric(4,0));
create table isenes3_kpis_eu_dm (id bigserial,eu_size bigint,eu_downloads bigint,status smallint, month numeric(2,0),year numeric(4,0));
create table isenes3_kpis_eu_bynode_dm (id bigserial,hostname varchar(50), eu_size bigint,eu_downloads bigint,status smallint, month numeric(2,0),year numeric(4,0));
create table isenes3_kpis_noteu_dm (id bigserial,noteu_size bigint,noteu_downloads bigint,status smallint,month numeric(2,0),year numeric(4,0));
create table isenes3_kpis_noteu_bynode_dm (id bigserial,hostname varchar(50),noteu_size bigint,noteu_downloads bigint,status smallint,month numeric(2,0),year numeric(4,0));
create table isenes3_kpis_notavailable_dm (id bigserial,size bigint,downloads bigint,status smallint,month numeric(2,0),year numeric(4,0));
create table isenes3_kpis_notavailable_bynode_dm (id bigserial,hostname varchar(50),size bigint,downloads bigint,status smallint,month numeric(2,0),year numeric(4,0));
create table isenes3_eu_clients (id bigserial,eu_country varchar(2),hostname varchar(50),month numeric(2,0),year numeric(4,0));
create table isenes3_noteu_clients (id bigserial,noteu_country varchar(2),hostname varchar(50),month numeric(2,0),year numeric(4,0));
create table isenes3_na_clients (id bigserial,na_country varchar(2),hostname varchar(50),month numeric(2,0),year numeric(4,0));
create table isenes3_eu_clients_by_time (id bigserial,eu_clients bigint,hostname varchar(50),month numeric(2,0),year numeric(4,0));
create table isenes3_noteu_clients_by_time (id bigserial,noteu_clients bigint,hostname varchar(50),month numeric(2,0),year numeric(4,0));
create table isenes3_na_clients_by_time (id bigserial,na_clients bigint,hostname varchar(50),month numeric(2,0),year numeric(4,0));
INSERT INTO cross_dmart_project_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,project_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, upper(project) as project_name,status,month, year FROM cross_project_dw WHERE status IN (200,206) and year >= 2018 and size_log is not null GROUP BY hostname,project, status,month, year ORDER BY year, month ASC;
INSERT INTO cross_dmart_project_host_geolocation (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,project_name,status,country_code) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, upper(project) as project_name,status,country_code FROM cross_project_dw WHERE status IN (200,206) and year >= 2018 and size_log is not null GROUP BY hostname,project,status,country_code;
INSERT INTO cmip6_dmart_experiment_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,experiment_id,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, experiment_id as experiment_id,status,month,year FROM cmip6_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,experiment_id, status,month, year ORDER BY year, month ASC;
INSERT INTO cmip6_dmart_source_id_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,source_id,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, source_id as source_id,status,month,year FROM cmip6_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,source_id, status,month, year ORDER BY year, month ASC;
INSERT INTO cmip6_dmart_variable_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,variable_code,variable_long_name,cf_standard_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name,variable_code as variable_code, variable_long_name as variable_long_name, cf_standard_name as cf_standard_name,status,month, year FROM cmip6_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,cf_standard_name,variable_long_name,variable_code,status,month, year ORDER BY year, month ASC;
INSERT INTO cmip5_dmart_experiment_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,experiment_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, experiment as experiment_name,status,month,year FROM cmip5_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,experiment, status,month, year ORDER BY year, month ASC;
INSERT INTO cmip5_dmart_model_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,model_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, model as model_name, status,month, year FROM cmip5_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,model,status,month, year ORDER BY year, month ASC;
INSERT INTO cmip5_dmart_variable_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,variable_code,variable_long_name,cf_standard_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, variable_code as variable_code, variable_long_name as variable_long_name, cf_standard_name as cf_standard_name,status,month, year FROM cmip5_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,cf_standard_name,variable_long_name,variable_code,status,month, year ORDER BY year, month ASC;
INSERT INTO cordex_dmart_driving_model_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,driving_model,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, driving_model as driving_model, status,month, year FROM cordex_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,driving_model,status,month, year ORDER BY year, month ASC;
INSERT INTO cordex_dmart_domain_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,domain,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, domain as domain, status,month, year FROM cordex_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,domain,status,month, year ORDER BY year, month ASC;
INSERT INTO cordex_dmart_variable_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,variable_code,variable_long_name,cf_standard_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name,variable_code as variable_code, variable_long_name as variable_long_name, cf_standard_name as cf_standard_name,status,month, year FROM cordex_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,cf_standard_name,variable_long_name,variable_code,status,month, year ORDER BY year, month ASC;
INSERT INTO cordex_dmart_rcm_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,rcm_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, rcm_name as rcm_name, status,month, year FROM cordex_project_specific_dw WHERE status IN (200,206) and year>= 2018 and size_log is not null GROUP BY hostname,rcm_name,status,month, year ORDER BY year, month ASC;
INSERT INTO obs4mips_dmart_variable_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,variable_code,variable_long_name,cf_standard_name,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, variable_code as variable_code, variable_long_name as variable_long_name, cf_standard_name as cf_standard_name,status,month, year FROM obs4mips_project_specific_dw WHERE status IN (200,206) and year >= 2018 and size_log is not null GROUP BY hostname,cf_standard_name,variable_long_name,variable_code,status,month, year ORDER BY year, month ASC;
INSERT INTO obs4mips_dmart_source_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,source_id,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, source_id as source_id, status,month, year FROM obs4mips_project_specific_dw WHERE status IN (200,206) and year >= 2018 and size_log is not null GROUP BY hostname,source_id,status,month,year ORDER BY year, month ASC;
INSERT INTO obs4mips_dmart_realm_host_time (total_size,number_of_downloads,number_of_replica_downloads,average_duration,host_name,realm,status,month,year) SELECT SUM(size_log) as total_size,COUNT(*) as number_of_downloads,COUNT(CASE WHEN replica THEN true END) AS number_of_replica_downloads,ROUND(AVG(duration)) AS average_duration, hostname as host_name, realm as realm, status,month, year FROM obs4mips_project_specific_dw WHERE status IN (200,206) and year >= 2018 and size_log is not null GROUP BY hostname,realm,status,month,year ORDER BY year, month ASC;
INSERT INTO isenes3_kpis_eu_dm (eu_size,eu_downloads, status,month, year ) SELECT SUM(size_log) AS eu_size,COUNT(*) AS eu_downloads, dw.status,dw.month, dw.year FROM cross_project_dw AS dw WHERE status IN (200,206) AND year>=2018 AND country_code IN (SELECT country_code from country where continent_code='EU') and size_log is not null and hostname in () GROUP BY status,month, year ORDER BY status,year, month ASC;
INSERT INTO isenes3_kpis_noteu_dm (noteu_size,noteu_downloads, status,month, year ) SELECT SUM(size_log) AS noteu_size,COUNT(*) AS noteu_downloads, dw.status,dw.month, dw.year FROM cross_project_dw AS dw WHERE status IN (200,206) AND year>=2018 AND country_code IN (SELECT country_code from country where continent_code<>'EU') and size_log is not null and hostname in () GROUP BY status,month, year ORDER BY status,year, month ASC;
INSERT INTO isenes3_kpis_notavailable_dm (size,downloads, status,month, year) SELECT SUM(size_log) AS size,COUNT(*) AS downloads, dw.status,dw.month, dw.year FROM cross_project_dw AS dw WHERE status IN (200,206) AND year>=2018 AND country_code NOT IN (SELECT country_code from country where continent_code='EU') AND country_code NOT IN (SELECT country_code from country where continent_code<>'EU') and size_log is not null and hostname in () GROUP BY status,month, year ORDER BY status,year, month ASC;
INSERT INTO isenes3_kpis_eu_bynode_dm (hostname, eu_size,eu_downloads, status,month, year) SELECT hostname as hostname, SUM(size_log) AS eu_data_volume,COUNT(*) AS eu_downloads, dw.status,dw.month, dw.year FROM cross_project_dw AS dw WHERE status IN (200,206) AND year>=2018 AND country_code IN (SELECT country_code from country where continent_code='EU') and size_log is not null and hostname in () GROUP BY hostname,status,month, year ORDER BY hostname,year, month ASC;
INSERT INTO isenes3_kpis_noteu_bynode_dm (hostname, noteu_size, noteu_downloads, status,month, year) SELECT hostname as hostname,SUM(size_log) AS noteu_data_volume,COUNT(*) AS noteu_downloads, dw.status,dw.month, dw.year FROM cross_project_dw AS dw WHERE status IN (200,206) AND year>=2018 AND country_code IN (SELECT country_code from country where continent_code<>'EU') and size_log is not null and hostname in () GROUP BY hostname,status,month, year ORDER BY hostname,year, month ASC;
INSERT INTO isenes3_kpis_notavailable_bynode_dm (hostname, size,downloads, status,month, year) SELECT hostname as hostname,SUM(size_log) AS not_available_data_volume,COUNT(*) AS not_available_downloads, dw.status,dw.month, dw.year FROM cross_project_dw AS dw WHERE status IN (200,206) AND year>=2018 AND country_code NOT IN (SELECT country_code from country where continent_code='EU') AND country_code NOT IN (SELECT country_code from country where continent_code<>'EU') and size_log is not null and hostname in () GROUP BY hostname,status,month, year ORDER BY hostname,year, month ASC;
INSERT INTO isenes3_eu_clients_by_time (eu_clients,hostname, month, year) select count(*) as eu_clients,hostname, month,year from isenes3_eu_clients group by hostname,month,year order by month,year,hostname;
INSERT INTO isenes3_noteu_clients_by_time (noteu_clients,hostname, month, year) select count(*) as noteu_clients,hostname, month,year from isenes3_noteu_clients group by hostname,month,year order by month,year,hostname;
INSERT INTO isenes3_na_clients_by_time (na_clients,hostname, month, year) select count(*) as na_clients,hostname, month,year from isenes3_na_clients group by hostname,month,year order by month,year,hostname;