Project

General

Profile

« Previous | Next » 

Revision 48550

Added by Tsampikos Livisianos almost 7 years ago

enable project charts

View differences:

modules/dnet-openaire-stats/trunk/src/main/resources/eu/dnetlib/data/mapreduce/hbase/statsExport/stats_db_schema.sql
924 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;
925 925 

  		




  926
  926
  
    
-- project charts


  		




  927
  
  
    
-- SELECT p.id, r.year, count( distinct r.id) INTO "shadow".chart_project_year 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
  
  
    
-- SELECT pr.id, r.bestlicense, count(distinct r.id) INTO "shadow".chart_project_license FROM "shadow".result r, "shadow".project_results pr WHERE r.id=pr.result AND r.type='publication' GROUP BY pr.id, r.bestlicense;


  		




  929
  
  
    
-- SELECT pr.id, d.name, count (distinct r.id) INTO "shadow".chart_project_repos 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;


  		




  
  927
  
    
SELECT p.id, r.year, count( distinct r.id) INTO "shadow".chart_project_year 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
  
    
SELECT pr.id, r.bestlicense, count(distinct r.id) INTO "shadow".chart_project_license FROM "shadow".result r, "shadow".project_results pr WHERE r.id=pr.result AND r.type='publication' GROUP BY pr.id, r.bestlicense;


  		




  
  929
  
    
SELECT pr.id, d.name, count (distinct r.id) INTO "shadow".chart_project_repos 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;


  		




  930
  930
  
    

  		




  931
  931
  
    
  END;$$;


  		




  932
  932
  
    

  		




  ......




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


  		




  957
  957
  
    

  		




  958
  958
  
    
-- project


  		




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


  		




  960
  
  
    
-- CREATE INDEX chart_project_license_id ON "shadow".chart_project_license USING btree(id);


  		




  961
  
  
    
-- CREATE INDEX chart_project_repos_id ON "shadow".chart_project_repos USING btree(id);


  		




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


  		




  
  960
  
    
CREATE INDEX chart_project_license_id ON "shadow".chart_project_license USING btree(id);


  		




  
  961
  
    
CREATE INDEX chart_project_repos_id ON "shadow".chart_project_repos USING btree(id);


  		




  962
  962
  
    

  		




  963
  963
  
    

  		




  964
  964
  
    
  END;$$;


  		












Also available in:  Unified diff