inst/QueryLibrary/Aggregate/condition_occurrence/CO17.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

CO17: Distribution of condition occurrence records per person

Description

This query is used to provide summary statistics for the number of condition occurrence records (condition_occurrence_id) for all persons: the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. There is no input required for this query.

Query

WITH ranked AS
  (SELECT 
    num_of_conditions, 
    CAST(sum(1) over (partition BY 1
                 ORDER BY num_of_conditions 
                 ASC ROWS BETWEEN unbounded preceding 
                 AND CURRENT row
                 ) AS INTEGER) AS rownumasc
   FROM 
    (SELECT 
      COUNT(*)::integer AS num_of_conditions
     FROM @cdm.condition_occurrence
     WHERE person_id!=0
     GROUP BY person_id
    ) AS person_conditions
  ),
     other_stat AS
  (SELECT 
    COUNT(num_of_conditions)::integer                       AS condition_num_count,
    MIN(num_of_conditions)::integer                         AS condition_num_min,
    MAX(num_of_conditions)::integer                         AS condition_num_max,
    ROUND(AVG(num_of_conditions), 2)::numeric               AS condition_num_average,
    ROUND(STDDEV(num_of_conditions), 2)::numeric             AS condition_num_stddev
   FROM 
    (SELECT 
      COUNT(*)::integer AS num_of_conditions, 
      person_id
     FROM   @cdm.condition_occurrence
     WHERE person_id!=0
     GROUP BY person_id
     ) AS condition_stats
  )
SELECT DISTINCT
 (SELECT 
    COUNT(DISTINCT person_id)::integer
  FROM @cdm.condition_occurrence
  WHERE person_id!=0 
        AND condition_occurrence_id IS NULL
 )                                                  AS condition_null_count,
 condition_num_count,condition_num_min,condition_num_max,condition_num_average,condition_num_stddev 
 FROM other_stat,
 (SELECT 
    num_of_conditions                               AS condition_num_25percentile
  FROM 
    (SELECT num_of_conditions, rownumasc, (SELECT count(*) FROM ranked) AS rowno FROM ranked) AS all_1
  WHERE    (rownumasc = CAST(rowno*0.25 AS INT)   AND floor(rowno*25/100)  = rowno*25/100 ) 
        OR (rownumasc = CAST(rowno*0.25 AS INT)   AND floor(rowno*25/100) != rowno*25/100 )
        OR (rownumasc = CAST(rowno*0.25 AS INT)+1 AND floor(rowno*25/100) != rowno*25/100 )
 ) condition_num_25percentile,
 (SELECT 
    num_of_conditions                               AS condition_num_median
  FROM 
    (SELECT num_of_conditions, rownumasc, (SELECT count(*) FROM ranked) AS rowno FROM ranked) AS all_2
  WHERE    (rownumasc = CAST(rowno*0.50 AS INT)   AND floor(rowno*50/100)  = rowno*50/100 )
        OR (rownumasc = CAST(rowno*0.50 AS INT)   AND floor(rowno*50/100) != rowno*50/100 )
        OR (rownumasc = CAST(rowno*0.50 AS INT)+1 AND floor(rowno*50/100) != rowno*50/100 )
 ) condition_num_median,
 (SELECT 
    num_of_conditions                               AS condition_num_75percentile
  FROM 
    (SELECT num_of_conditions, rownumasc,(SELECT count(*) FROM ranked) AS rowno FROM ranked) AS all_3
  WHERE (rownumasc=cast (rowno*0.75 AS INT)      AND floor(rowno*75/100)  = rowno*75/100 )
        OR (rownumasc=cast (rowno*0.75 AS INT)   AND floor(rowno*75/100) != rowno*75/100 )
        OR (rownumasc=cast (rowno*0.75 AS INT)+1 AND floor(rowno*75/100) != rowno*75/100 )
 ) condition_num_75percentile;

Input

None

Output

| Field | Description | | --- | --- | | condition_null_count | Number of persons with at least one null condition_occurrence_id | | condition_num_count | Number of distinct persons with conditions | | condition_num_min | The lowest number of condition occurences | | condition_num_max | The highest number of condition occurences | | condition_num_average | The average number of condition occurences | | condition_num_stddev | The standard deviation of condition occurence numbers | | condition_num_25percentile | A condition occurence number where 25 percent of the other numbers are lower | | condition_num_median | A condition occurence number where half of the other numbers are lower and half are higher | | condition_num_75percentile | A condition occurence number where 75 percent of the other numbers are lower |

Example output record

| Field | Description | | --- | --- | | condition_null_count | 4395019 | | condition_num_count | | | condition_num_min | 1 | | condition_num_max | 7144 | | condition_num_average | 51 | | condition_num_stddev | 86.63 | | condition_num_25percentile | 11 | | condition_num_median | 26 | | condition_num_75percentile | 58 |

Documentation

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

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