Project

General

Profile

« Previous | Next » 

Revision 49533

Added by Tsampikos Livisianos over 6 years ago

add datasource data chart

View differences:

modules/dnet-openaire-stats/trunk/src/main/resources/eu/dnetlib/data/mapreduce/hbase/statsExport/stats_db_schema.sql
921 921 
CREATE TABLE "shadow".chart_datasource_type AS SELECT rd.datasource, rc.type, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".result_classifications rc WHERE rd.id=rc.id GROUP BY rd.datasource, rc.type;
922 922 
CREATE TABLE "shadow".chart_datasource_year AS SELECT rd.datasource, r.year, count(distinct rd.id) FROM "shadow".result r, "shadow".result_datasources rd WHERE rd.id=r.id GROUP By rd.datasource, r.year;
923 923 
CREATE TABLE "shadow".chart_datasource_funders AS SELECT rd.datasource, p.funder, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.funder;
924 
CREATE TABLE "shadow".chart_datasource_projects AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr WHERE p.id=pr.id AND pr.result=rd.id GROUP BY rd.datasource, p.title;
924 
CREATE TABLE "shadow".chart_datasource_projects_pubs AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=rd.id AND pr.result=r.id AND r.type='publication' GROUP BY rd.datasource, p.title;
925 
CREATE TABLE "shadow".chart_datasource_projects_data AS SELECT rd.datasource, p.title, count(distinct rd.id) FROM "shadow".result_datasources rd, "shadow".project p, "shadow".project_results pr, "shadow".result r WHERE p.id=pr.id AND pr.result=rd.id AND pr.result=r.id and r.type='dataset' GROUP BY rd.datasource, p.title;
925 926 

  		




  926
  927
  
    
-- project charts


  		




  927
  
  
    
CREATE TABLE "shadow".chart_project_year AS SELECT p.id, r.year, count( distinct r.id) FROM "shadow".result r, "shadow".result_projects rp, "shadow".project p WHERE r.id=rp.id AND p.id=rp.project AND r.year>=p.start_year AND r.year<=p.end_year GROUP BY p.id, r.year;


  		




  928
  
  
    
CREATE TABLE "shadow".chart_project_license AS SELECT pr.id, r.bestlicense, count(distinct r.id) FROM "shadow".result r, "shadow".project_results pr WHERE r.id=pr.result AND r.type='publication' GROUP BY pr.id, r.bestlicense;


  		




  929
  
  
    
CREATE TABLE "shadow".chart_project_repos AS SELECT pr.id, d.name, count (distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".datasource d, "shadow".datasource_results dr WHERE r.id=dr.result AND d.id=dr.id AND r.id=pr.result AND r.type='publication' GROUP BY pr.id, d.name;


  		




  
  928
  
    
CREATE TABLE "shadow".chart_project_year AS SELECT p.id, r.year, count( distinct r.id) FROM "shadow".result r, "shadow".result_projects rp, "shadow".project p WHERE r.id=rp.id AND p.id=rp.project AND r.year>=p.start_year GROUP BY p.id, r.year;


  		




  
  929
  
    
CREATE TABLE "shadow".chart_project_license AS SELECT pr.id, r.bestlicense, count(distinct r.id) FROM "shadow".result r, "shadow".project_results pr WHERE r.id=pr.result GROUP BY pr.id, r.bestlicense;


  		




  
  930
  
    
CREATE TABLE "shadow".chart_project_repos AS SELECT pr.id, d.name, count (distinct r.id) FROM "shadow".result r, "shadow".project_results pr, "shadow".datasource d, "shadow".datasource_results dr WHERE r.id=dr.result AND d.id=dr.id AND r.id=pr.result GROUP BY pr.id, d.name;


  		




  930
  931
  
    

  		




  931
  932
  
    
  END;$$;


  		




  932
  933
  
    

  		




  ......




  953
  954
  
    
CREATE INDEX chart_datasource_type_datasource ON "shadow".chart_datasource_type USING btree(datasource);


  		




  954
  955
  
    
CREATE INDEX chart_datasource_year_datasource ON "shadow".chart_datasource_year USING btree(datasource);


  		




  955
  956
  
    
CREATE INDEX chart_datasource_funders_datasource ON "shadow".chart_datasource_funders USING btree(datasource);


  		




  956
  
  
    
CREATE INDEX chart_datasource_projects_datasource ON "shadow".chart_datasource_projects USING btree(datasource);


  		




  
  957
  
    
CREATE INDEX chart_datasource_projects_pubs_datasource ON "shadow".chart_datasource_projects_pubs USING btree(datasource);


  		




  
  958
  
    
CREATE INDEX chart_datasource_projects_data_datasource ON "shadow".chart_datasource_projects_data USING btree(datasource);


  		




  957
  959
  
    

  		




  958
  960
  
    
-- project


  		




  959
  961
  
    
CREATE INDEX chart_project_year_id ON "shadow".chart_project_year USING btree(id);


  		












Also available in:  Unified diff