Project

General

Profile

« Previous | Next » 

Revision 29991

Added by Eri Katsari about 10 years ago

View differences:

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