Skip to content

Instantly share code, notes, and snippets.

@djtfmartin

djtfmartin/weblog_v1_species.sql

Last active August 19, 2024 13:41
Show Gist options
  • Save djtfmartin/80deceae7d603eca4e42d7e66c8b8fe2 to your computer and use it in GitHub Desktop.
Save djtfmartin/80deceae7d603eca4e42d7e66c8b8fe2 to your computer and use it in GitHub Desktop.
Download ZIP
weblog v1 species usage
Raw
weblog_v1_species.sql
SELECT
SUM(CASE WHEN requestpath LIKE '/v1/species/match%' THEN 1 ELSE 0 END) AS match,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/vernacularNames' THEN 1 ELSE 0 END) AS vernacularNames,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/verbatim' THEN 1 ELSE 0 END) AS verbatim,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/typeSpecimens' THEN 1 ELSE 0 END) AS typeSpecimens,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/toc' THEN 1 ELSE 0 END) AS toc_count,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/synonyms' THEN 1 ELSE 0 END) AS synonyms_count,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/speciesProfiles' THEN 1 ELSE 0 END) AS speciesProfiles,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/related' THEN 1 ELSE 0 END) AS related,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/references' THEN 1 ELSE 0 END) AS species_references,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/parents' THEN 1 ELSE 0 END) AS parents,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/name' THEN 1 ELSE 0 END) AS name,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/metrics' THEN 1 ELSE 0 END) AS metrics,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/media' THEN 1 ELSE 0 END) AS media,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/iucnRedListCategory' THEN 1 ELSE 0 END) AS iucnRedListCategory,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/identifier' THEN 1 ELSE 0 END) AS identifier,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/distributions' THEN 1 ELSE 0 END) AS distributions,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/descriptions' THEN 1 ELSE 0 END) AS descriptions,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/combinations' THEN 1 ELSE 0 END) AS combinations,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/childrenAll' THEN 1 ELSE 0 END) AS childrenAll,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/children' THEN 1 ELSE 0 END) AS children,
SUM(CASE WHEN requestpath LIKE '/v1/species/%/suggest' THEN 1 ELSE 0 END) AS suggest,
SUM(CASE WHEN requestpath LIKE '/v1/species/root/%' THEN 1 ELSE 0 END) AS root_count,
SUM(CASE WHEN requestpath LIKE '/v1/species' AND requestquery like '%name=%' THEN 1 ELSE 0 END) AS name_lookups,
SUM(CASE WHEN requestpath LIKE '/v1/species' AND requestquery like '%sourceId=%' THEN 1 ELSE 0 END) AS id_lookups,
SUM(CASE WHEN requestpath LIKE '/v1/species' AND requestquery like '%datasetKey=%' AND (requestquery like '%offset=%' OR requestquery like '%limit=%') THEN 1 ELSE 0 END) AS dataset_paging_lookups,
SUM(CASE WHEN requestpath LIKE '/v1/species' AND (requestquery = '&' OR requestquery like '%taxonKey=%' OR requestquery like '%taxon_key=%' OR requestquery like '%cachebust=%') THEN 1 ELSE 0 END) AS junk
FROM
weblog
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment