inst/QueryLibrary/Aggregate/person/PE12.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

PE12: Distribution of year of birth

Description

This query is used to to provide summary statistics for the age across all patient records: the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum. No input is required for this query.

Query

SELECT
  percentile_25,
  median,
  percentile_75,
  CAST(AVG(year_of_birth) AS INTEGER) AS mean,
  STDDEV(year_of_birth)::numeric                AS stddev
FROM
  (SELECT
     MAX(CASE WHEN (percentile = 1) THEN year_of_birth END) AS percentile_25,
     MAX(CASE WHEN (percentile = 2) THEN year_of_birth END) AS median,
     MAX(CASE WHEN (percentile = 3) THEN year_of_birth END) AS percentile_75
   FROM
     (SELECT
        year_of_birth,
        births,
        /* The first sum is the sum of all the values from the first year of birth
        to the current year.  The second sum is the total of all the years of birth.
        The result is a cumulative percent of the total for each year.  You want to
        capture when the percentage goes from 24 to 25 as percentile_25, from 49 to 50
        as the median and from 74 to 75 as the percentile_75.  Multiplying by 4 then SA
        adding 1 just makes so that instead of looking at percentage, you get the whole
        number 1 if the percentage is less than 25, 2 when the percentage is between 25
        and 50, and so on.
        */
        FLOOR(CAST(SUM(births)
                   OVER (
                     ORDER BY year_of_birth
                     ROWS UNBOUNDED PRECEDING ) AS numeric)
              / CAST(SUM(births)
                     OVER (
                       ORDER BY year_of_birth
                       ROWS BETWEEN UNBOUNDED PRECEDING
                       AND UNBOUNDED FOLLOWING ) AS numeric)
              * 4
        ) + 1 AS percentile
      FROM 
        (SELECT
           year_of_birth,
           COUNT(*)::integer AS births
         FROM @cdm.person
         GROUP BY year_of_birth
        ) t
     ) tt
   WHERE percentile <= 3
  ) percentile_table
CROSS JOIN @cdm.person
GROUP BY percentile_25, median, percentile_75
;

Input

None

Output

| Field | Description | | --- | --- | | percentile_25 | 25th percentile of year of birth | | median | Median patient year of birth | | percentile_75 | 75th percentile of year of birth | | mean | Mean patient year of birth | | stddev | Standard deviation of year of birth |

Example output record

| Field | Value | | --- | --- | | percentile_25 | 1954 | | median | 1965 | | percentile_75 | 1979 | | mean | 1968 | | stddev | 17.277 |

Documentation

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

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