inst/QueryLibrary/Aggregate/observation_period/OP08.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

OP08: Distribution of observation period records per person

Description

Counts the number of observation period records (observation_period_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 obser_person AS
  ( SELECT
      observation_period.person_id,
      COUNT(*)::integer AS observation_periods
    FROM @cdm.observation_period
    INNER JOIN @cdm.person 
    ON observation_period.person_id = person.person_id
    GROUP BY observation_period.person_id
  )

SELECT 
  MIN( observation_periods )                                                              AS min_periods ,
  MAX( observation_periods )                                                              AS max_periods ,
  round( avg( observation_periods )::numeric, 2 )::numeric                                                  AS avg_periods ,
  round( STDDEV( observation_periods )::numeric, 1 )::numeric                                                AS STDEV_periods ,
  MIN(CASE WHEN order_nr < .25 * population_size THEN 9999 ELSE observation_periods END)  AS percentile_25,
  MIN(CASE WHEN order_nr < .50 * population_size THEN 9999 ELSE observation_periods END)  AS median_value,
  MIN(CASE WHEN order_nr < .75 * population_size THEN 9999 ELSE observation_periods END)  AS percentile_75

FROM 
 ( SELECT 
     observation_periods                                                     AS observation_periods,
     ROW_NUMBER() OVER (ORDER BY observation_periods)                        AS order_nr,
     (SELECT COUNT(*)::numeric FROM obser_person )                                    AS population_size
   FROM obser_person
 ) ordered_data;

Input

None

Output

| Field | Description | | --- | --- | | min_periods | Minimum number of periods | | max_periods | Maximum number of periods | | avg_periods | Average number of periods | | STDEV_periods | Standard Deviation of periods | | percentile_25 | 25th percentile of periods | | median | Median of periods | | percentile_75 | 75th percentile of periods |

Example output record

| Field | Value | | --- | --- | | min_periods | 1 | | max_periods | 10 | | avg_periods | 1.12 | | STDEV_periods | 0.30 | | percentile_25 | 1 | | median | 1 | | percentile_75 | 1 |

Documentation

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

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