Revision 7634
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.sql | ||
|---|---|---|
| 1126 | 1126 |
GRANT SELECT ON TABLE provider_count_view TO bien_read; |
| 1127 | 1127 |
GRANT SELECT ON TABLE provider_count_view TO public_; |
| 1128 | 1128 |
|
| 1129 |
ALTER TABLE provider_count ADD PRIMARY KEY (level, provider);
|
|
| 1129 |
ALTER TABLE provider_count ADD PRIMARY KEY (dataprovider, dataset);
|
|
| 1130 | 1130 |
$$; |
| 1131 | 1131 |
|
| 1132 | 1132 |
|
| ... | ... | |
| 3841 | 3841 |
-- |
| 3842 | 3842 |
|
| 3843 | 3843 |
CREATE TABLE provider_count ( |
| 3844 |
level text NOT NULL,
|
|
| 3845 |
provider text NOT NULL,
|
|
| 3846 |
count bigint,
|
|
| 3844 |
dataprovider text NOT NULL,
|
|
| 3845 |
dataset text NOT NULL,
|
|
| 3846 |
records bigint,
|
|
| 3847 | 3847 |
sourcetype sourcetype, |
| 3848 | 3848 |
observationtype observationtype |
| 3849 | 3849 |
); |
| ... | ... | |
| 3865 | 3865 |
-- |
| 3866 | 3866 |
|
| 3867 | 3867 |
CREATE VIEW provider_count_view AS |
| 3868 |
SELECT s.level, s.provider, s.count, source.sourcetype, source.observationtype FROM ((SELECT 'top-level'::text AS level, analytical_stem.datasource AS provider, count(*) AS count FROM analytical_stem GROUP BY analytical_stem.datasource UNION ALL SELECT 'data owner'::text AS level, sourcename.name AS provider, count(*) AS count FROM ((sourcename JOIN sourcelist USING (sourcelist_id)) JOIN specimenreplicate ON ((specimenreplicate.institution_id = sourcelist.sourcelist_id))) GROUP BY sourcename.name HAVING (count(*) > 0)) s LEFT JOIN source ON ((source.shortname = s.provider))) ORDER BY s.level DESC, s.count DESC, s.provider;
|
|
| 3868 |
(SELECT s.dataprovider, '(total)'::text AS dataset, s.records, source.sourcetype, source.observationtype FROM ((SELECT analytical_stem.datasource AS dataprovider, count(*) AS records FROM analytical_stem GROUP BY analytical_stem.datasource) s JOIN source ON ((source.shortname = s.dataprovider))) ORDER BY s.records DESC) UNION ALL (SELECT dataprovider.shortname AS dataprovider, s.dataset, s.records, dataset.sourcetype, COALESCE(dataset.observationtype, dataprovider.observationtype) AS observationtype FROM (((SELECT specimenreplicate.source_id AS dataprovider_id, sourcename.name AS dataset, sourcename.matched_source_id AS dataset_id, count(*) AS records FROM ((specimenreplicate JOIN sourcelist ON ((sourcelist.sourcelist_id = specimenreplicate.institution_id))) JOIN sourcename USING (sourcelist_id)) GROUP BY specimenreplicate.source_id, sourcename.name, sourcename.matched_source_id) s JOIN source dataprovider ON ((dataprovider.source_id = s.dataprovider_id))) LEFT JOIN source dataset ON ((dataset.source_id = s.dataset_id))) ORDER BY dataprovider.shortname, s.records DESC, s.dataset);
|
|
| 3869 | 3869 |
|
| 3870 | 3870 |
|
| 3871 | 3871 |
-- |
| ... | ... | |
| 6014 | 6014 |
-- |
| 6015 | 6015 |
|
| 6016 | 6016 |
ALTER TABLE ONLY provider_count |
| 6017 |
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (level, provider);
|
|
| 6017 |
ADD CONSTRAINT provider_count_pkey PRIMARY KEY (dataprovider, dataset);
|
|
| 6018 | 6018 |
|
| 6019 | 6019 |
|
| 6020 | 6020 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: provider_count_view: Group data by dataprovider and dataset, rather than by each separately, to satisfy the GBIF attribution requirements as described by Brad