Revision 29991
Added by Eri Katsari about 10 years ago
modules/dnet-openaire-stats/trunk/src/main/resources/eu/dnetlib/data/mapreduce/hbase/statsExport/stats_db_schema.sql | ||
---|---|---|
47 | 47 |
CREATE INDEX result_result_projects ON "shadow".result USING btree (result_projects); |
48 | 48 |
CREATE INDEX result_result_topics ON "shadow".result USING btree (result_topics); |
49 | 49 |
CREATE INDEX result_year ON "shadow".result USING btree (year); |
50 |
--CREATE INDEX result_projects_id ON "result_projects" USING btree (id);
|
|
50 |
CREATE INDEX result_projects_id ON "result_projects" USING btree (id); |
|
51 | 51 |
CREATE INDEX result_date ON "shadow"."result" USING btree ("date"); |
52 | 52 |
CREATE INDEX project_acronym ON "shadow"."project" USING btree (acronym); |
53 | 53 |
CREATE INDEX project_enddate ON "shadow"."project" USING btree (enddate); |
... | ... | |
93 | 93 |
AND "shadow"."result_projects".project = "shadow"."project".id AND "shadow"."result".date!='NULL' |
94 | 94 |
AND "shadow"."project".enddate !='NULL' AND "shadow"."result"."type" = 'publication'); |
95 | 95 |
|
96 |
|
|
97 |
|
|
96 |
|
|
98 | 97 |
CREATE OR REPLACE VIEW "shadow".datasource_topics AS |
99 | 98 |
SELECT distinct "shadow".datasource.id, "shadow".result_topics.topic |
100 | 99 |
FROM "shadow".datasource, "shadow".datasource_results, "shadow".result, "shadow".result_topics |
... | ... | |
628 | 627 |
BEGIN |
629 | 628 |
|
630 | 629 |
|
631 |
drop table if exists shadow.project_results_publication CASCADE ; |
|
630 |
--drop table if exists shadow.project_results_publication CASCADE ;
|
|
632 | 631 |
|
633 |
CREATE table shadow.project_results_publication AS |
|
634 |
SELECT "shadow".project_results.result AS result, |
|
635 |
"shadow".project_results.id AS project_results, |
|
632 |
--CREATE table shadow.project_results_publication AS |
|
633 |
--SELECT "shadow".project_results.result AS result, |
|
634 |
--"shadow".project_results.id AS project_results, |
|
635 |
--"shadow"."result"."date" as resultdate, |
|
636 |
--"shadow"."project"."enddate" as projectenddate, |
|
637 |
--to_date("shadow"."result"."date", 'YYYY-MM-DD') - to_date("shadow"."project"."enddate", 'YYYY-MM-DD') as daysfromend |
|
638 |
--FROM "shadow".project_results, "shadow".result, "shadow".project |
|
639 |
--where "shadow".project_results.result="shadow".result.id and "shadow".result.type='publication' and "shadow".project.project_results= "shadow".project_results.id; |
|
640 |
|
|
641 |
CREATE OR REPLACE VIEW shadow.project_results_publication AS |
|
642 |
SELECT "shadow".result_projects.id AS result, |
|
643 |
"shadow".result_projects.project AS project_results, |
|
636 | 644 |
"shadow"."result"."date" as resultdate, |
637 |
"shadow"."project"."enddate" as projectenddate, |
|
638 |
to_date("shadow"."result"."date", 'YYYY-MM-DD') - to_date("shadow"."project"."enddate", 'YYYY-MM-DD') as daysfromend |
|
639 |
FROM "shadow".project_results, "shadow".result, "shadow".project |
|
640 |
where "shadow".project_results.result="shadow".result.id and "shadow".result.type='publication' and "shadow".project.project_results= "shadow".project_results.id; |
|
645 |
"shadow"."project"."enddate" as projectenddate, |
|
646 |
"shadow".result_projects.daysfromend |
|
647 |
as daysfromend FROM "shadow".result_projects, |
|
648 |
"shadow".result, |
|
649 |
"shadow".project |
|
650 |
where "shadow".result_projects.id="shadow".result.result_projects |
|
651 |
and "shadow".result.type='publication' |
|
652 |
and "shadow".project.project_results= "shadow".result_projects.project; |
|
641 | 653 |
|
642 |
|
|
643 | 654 |
END |
644 | 655 |
$BODY$ |
645 | 656 |
LANGUAGE plpgsql VOLATILE |
... | ... | |
684 | 695 |
|
685 | 696 |
--COUNT PUBS |
686 | 697 |
|
687 |
UPDATE "shadow"."project" SET numpubs=(SELECT count from shadow.project_pub_count |
|
698 |
UPDATE "shadow"."project" SET numpubs=( SELECT count from shadow.project_pub_count
|
|
688 | 699 |
WHERE shadow.project_pub_count.project_results = shadow.project.project_results ) |
689 | 700 |
where "shadow"."project".project_results in ( SELECT "shadow".project_results_publication.project_results FROM shadow.project_results_publication ); |
690 | 701 |
|
Also available in: Unified diff