Project

General

Profile

« Previous | Next » 

Revision 48611

Added by Tsampikos Livisianos over 6 years ago

fix project chart queries

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

  
931 931
  END;$$;
932 932

  

Also available in: Unified diff