Project

General

Profile

« Previous | Next » 

Revision 50180

Added by Tsampikos Livisianos over 6 years ago

filter project charts by publications

View differences:

stats_db_schema.sql
926 926 

  		




  927
  927
  
    
-- project charts


  		




  928
  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;


  		




  
  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 AND r.type='publication' 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 AND r.type='publication' GROUP BY pr.id, d.name;


  		




  931
  931
  
    

  		




  
  932
  
    
-- funder nums


  		




  
  933
  
    

  		




  
  934
  
    
-- funder charts


  		




  
  935
  
    

  		




  932
  936
  
    
  END;$$;


  		




  933
  937
  
    

  		




  934
  938
  
    
--


  		












Also available in:  Unified diff