Revision 48611
Added by Tsampikos Livisianos over 6 years ago
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
fix project chart queries