inst/QueryLibrary/Aggregate/observation_period/OP07.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

OP07: Distribution of age across all observation period records

Description

Count distribution of age across all observation period records: the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. No input is required for this query.

Query

WITH age_distribution AS 
  ( SELECT 
      DISTINCT u.person_id, 
      cast(date_part('year',first_observation_date) - year_of_birth as integer) AS age
    FROM 
    ( SELECT 
        person_id,
        MIN(observation_period_start_date) AS first_observation_date
      FROM @cdm.observation_period
      GROUP BY person_id
    ) AS u
    INNER JOIN @cdm.person 
    ON u.person_id = person.person_id
    WHERE year_of_birth IS NOT NULL
  ) 
SELECT 
  COUNT(*)::integer                                                                      AS num_people,
  min( age )::integer                                                                    AS min_age,
  max( age )::integer                                                                    AS max_age,
  round( avg( age )::numeric, 2 )                                                        AS avg_age,
  round( STDDEV( age )::numeric, 1 )                                                     AS stdev_age,
    ROUND(STDDEV(age)::numeric, 1)                                                        AS STDEV_value,
    MIN(CASE WHEN order_nr < .25 * population_size THEN 9999 ELSE age END)      AS percentile_25,
    MIN(CASE WHEN order_nr < .50 * population_size THEN 9999 ELSE age END)      AS median_value,
    MIN(CASE WHEN order_nr < .75 * population_size THEN 9999 ELSE age END)      AS percentile_75

FROM 
 ( SELECT 
     age                                                     AS age,
     ROW_NUMBER() OVER (ORDER BY age)                        AS order_nr,
     (SELECT COUNT(*)::integer FROM age_distribution ) AS population_size
   FROM age_distribution
 ) ordered_data;

Input

None

Output

| Field | Description | | --- | --- | | num_people | Number of people in a dataset | | min_age | Minimum age of person | | max_age | Maximum age of a person | | avg_age | Average age of people in the dataset | | STDEV_age | Standard deviation of person age | | percentile_25 | 25th percentile of of the age group | | median_age | 50th percentile of the age group | | percentile_75 | 75th percentile of the age group |

Example output record

| Field | Value | | --- | --- | | num_people | 151039265 | | min_age | 0 | | max_age | 85 | | avg_age | 31 | | stdev_age | 19.4 | | percentile_25 | 16 | | median_age | 31 | | percentile_75 | 47 |

Documentation

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

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