Revision 11816
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.sql | ||
|---|---|---|
| 4089 | 4089 |
|
| 4090 | 4090 |
CREATE VIEW provider_count_view AS |
| 4091 | 4091 |
( SELECT s.dataprovider, |
| 4092 |
'(total)'::text AS dataset,
|
|
| 4092 |
s.dataset,
|
|
| 4093 | 4093 |
s.records, |
| 4094 |
source.sourcetype, |
|
| 4095 |
source.observationtype |
|
| 4096 |
FROM (( SELECT analytical_stem.datasource AS dataprovider, |
|
| 4097 |
count(*) AS records |
|
| 4098 |
FROM analytical_stem |
|
| 4099 |
GROUP BY analytical_stem.datasource) s |
|
| 4100 |
JOIN source ON ((source.shortname = s.dataprovider))) |
|
| 4101 |
ORDER BY s.records DESC) |
|
| 4094 |
s.sourcetype, |
|
| 4095 |
s.observationtype |
|
| 4096 |
FROM ( SELECT source.shortname AS dataprovider, |
|
| 4097 |
'(total)'::text AS dataset, |
|
| 4098 |
( SELECT count(*) AS count |
|
| 4099 |
FROM taxonoccurrence |
|
| 4100 |
WHERE (taxonoccurrence.source_id = source.source_id)) AS records, |
|
| 4101 |
source.sourcetype, |
|
| 4102 |
source.observationtype |
|
| 4103 |
FROM source |
|
| 4104 |
WHERE (EXISTS ( SELECT NULL::unknown |
|
| 4105 |
FROM taxonoccurrence |
|
| 4106 |
WHERE (taxonoccurrence.source_id = source.source_id) |
|
| 4107 |
LIMIT 1))) s |
|
| 4108 |
ORDER BY s.records DESC) |
|
| 4102 | 4109 |
UNION ALL |
| 4103 | 4110 |
( SELECT dataprovider.shortname AS dataprovider, |
| 4104 | 4111 |
s.dataset, |
Also available in: Unified diff
schemas/vegbien.sql: provider_count_view: source totals: use the much faster query developed for Brad (wiki.vegpath.org/VegBIEN_FAQ#from-Brad-on-2013-12-4), which avoids the need to do a GROUP BY on all of analytical_stem. eventually, we will want to apply the same optimization to the first publisher subtotals.