inst/QueryLibrary/Aggregate/condition_era/CE16.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

CE16: Distribution of condition era length, stratified by condition and condition type

Description

This query is used to provide summary statistics for the condition era length across all condition era records stratified by condition (condition_concept_id) and (condition_type_concept_id, in CDM V2 condition_occurrence_type): the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The length of an era is defined as the difference between the start date and the end date. The input to the query is a value (or a comma-separated list of values) of a condition_concept_id. If the input is omitted, all existing value combinations are summarized.

Query

WITH parms as (
select cid::integer as cid  from unnest(regexp_split_to_array( nullif($1::text, '')::text, '\s*,\s*')) as cid), count_data AS (
SELECT ce.condition_concept_id, co.condition_type_concept_id, COUNT(*)::integer AS condition_occurrence_count
  FROM @cdm.condition_era ce
  JOIN @cdm.condition_occurrence co
    ON ce.condition_concept_id = co.condition_concept_id
   AND ce.person_id            = co.person_id
 WHERE ((select count(1) from parms) = 0 or ce.condition_concept_id in  (select cid from parms) )
 GROUP BY ce.condition_concept_id, co.condition_type_concept_id
), ordered_data AS (
SELECT condition_concept_id, condition_type_concept_id, condition_occurrence_count,
       cast(ROW_NUMBER()OVER(PARTITION BY condition_type_concept_id ORDER BY condition_occurrence_count) as integer) AS order_nr,
       cast(COUNT(*) OVER(PARTITION BY condition_type_concept_id) as integer) AS population_size
  FROM count_data
)
SELECT condition_concept_id,
       condition_type_concept_id,
       condition_occurrence_count,
       CAST(MIN(condition_occurrence_count)over(PARTITION BY condition_type_concept_id) AS INTEGER) AS min_count,
       CAST( MAX(condition_occurrence_count)over(PARTITION BY condition_type_concept_id) AS INTEGER) AS max_count,
       CAST(AVG(condition_occurrence_count)over(PARTITION BY condition_type_concept_id) AS NUMERIC) AS avg_count,
       CAST(ROUND(STDDEV(condition_occurrence_count)over(PARTITION BY condition_type_concept_id),0) AS INTEGER) AS stdev_count,
       CAST(MIN(CASE WHEN order_nr < .25 * population_size then 9999999999 else condition_occurrence_count END)OVER(PARTITION BY condition_type_concept_id) AS NUMERIC) AS percentile_25,
       CAST(MIN(CASE WHEN order_nr < .50 * population_size then 9999999999 else condition_occurrence_count END)OVER(PARTITION BY condition_type_concept_id) AS NUMERIC) AS median,
       CAST(MIN(CASE WHEN order_nr < .75 * population_size then 9999999999 else condition_occurrence_count END)OVER(PARTITION BY condition_type_concept_id) AS NUMERIC) AS percentile_75
  FROM ordered_data
 ORDER BY condition_type_concept_id, condition_concept_id;

Input

| Parameter | Example | Mandatory | Notes | | --- | --- | --- | --- | | condition_concept_id |4158346,195588 | No | |

Output

| Field | Description | | --- | --- | | condition_concept_id | A foreign key that refers to a standard condition concept identifier in the vocabulary. | | min | | | max | | | avg | | | STDEV | | | percentile_25 | | | median | | | percentile_75 | |

Documentation

https://github.com/OHDSI/CommonDataModel/wiki/

sib-swiss/dsQueryLibraryServer documentation built on Feb. 13, 2025, 8:07 p.m.