General Queries‎ > ‎

G16: Statistics about Condition Mapping of Source Vocabularies

The following query contains the coverage for mapped source vocabularies in the Condition domains to SNOMED-CT.

Input:
<None>

Sample query run:
The following query returns list of statistics.
SELECT 
  mapped.vocabulary_id,
  mapped.vocabulary_name,
  (CASE mapped.concept_level WHEN 0 THEN 'Not mapped' ELSE to_char(mapped.concept_level, '9999') END) AS concept_level,
  mapped.mapped_codes,
  sum(mapped.mapped_codes) over (partition by vocabulary_id) as total_mapped_codes,
  to_char(mapped.mapped_codes*100/sum(mapped.mapped_codes) over (partition by vocabulary_id), '990.9') AS pct_mapped_codes,
  mapped.mapped_concepts,
  (SELECT count(8)
   FROM vocabulary.concept 
   WHERE vocabulary_id=1 
     AND concept_level=mapped.concept_level 
     AND concept_class='Clinical finding' 
     AND invalid_reason is null)
 AS concepts_in_level,
    to_char(mapped.mapped_concepts*100/(
    SELECT CASE count(8) WHEN 0 THEN 1e16 ELSE count(8) END 
    FROM vocabulary.concept 
    WHERE vocabulary_id=1 
      AND concept_level=mapped.concept_level 
      AND concept_class='Clinical finding' 
      AND invalid_reason is null
  ), '990.9') AS pct_mapped_concepts
FROM (
  SELECT 
    m.source_vocabulary_id AS vocabulary_id, 
    v.vocabulary_name,
    c.concept_level,
    COUNT(8) AS mapped_codes,
    COUNT(DISTINCT target_concept_id) AS mapped_concepts
  FROM vocabulary.source_to_concept_map m
  JOIN vocabulary.concept c on c.concept_id=m.target_concept_id
  JOIN vocabulary.vocabulary v on v.vocabulary_id=m.source_vocabulary_id
  WHERE m.target_vocabulary_id=1 
    AND lower(m.mapping_type)='condition' 
  GROUP BY m.source_vocabulary_id, v.vocabulary_name, c.concept_level
) mapped
Output:
Output field list:
 Field  Description
 vocabulary_id  Source Vocabulary ID
 vocabulary_name  Source Vocabulary name
 concept_level  Concept Level Number
 mapped_codes  Number of mapped codes
 total_mapped_codes  Total number of mapped codes for source vocabulary
 pct_mapped_codes Percentile of mapped code 
 mapped_concepts Number of mapped concepts 
 concepts_in_level Number of mapped concepts 
 pct_mapped_concepts Percentile of of mapped concepts

Sample output record:
 Field  Value
 vocabulary_id  2
 vocabulary_name  ICD9-CT
 concept_level  1
 mapped_codes  4079
 total_mapped_codes  10770
 pct_mapped_codes 37.0
 mapped_concepts 3733
 concepts_in_level 69280
 pct_mapped_concepts 5.0
Comments