inst/QueryLibrary/Aggregate/drug_exposure/DEX31.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

DEX31: Distribution of drug exposure records per person

Description

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

Query

The following is a sample run of the query.

SELECT
    MIN(stat_value)                                                                    AS min_value,
    MAX(stat_value)                                                                    AS max_value,
    ROUND(AVG(stat_value), 1)                                                          AS avg_value,
    ROUND(STDEV(stat_value), 1)                                                        AS STDEV_value,
    MIN(CASE WHEN order_nr < .25 * population_size THEN 9999 ELSE stat_value END)      AS percentile_25,
    MIN(CASE WHEN order_nr < .50 * population_size THEN 9999 ELSE stat_value END)      AS median_value,
    MIN(CASE WHEN order_nr < .75 * population_size THEN 9999 ELSE stat_value END)      AS percentile_75

FROM (
  SELECT
    COUNT(*)                                                        AS stat_value,
    ROW_NUMBER() OVER (ORDER BY count(*) )                          AS order_nr,
    (SELECT COUNT(DISTINCT person_id) FROM @cdm.drug_exposure )     AS population_size
  FROM @cdm.drug_exposure
  GROUP BY person_id
) ordered_data;

Input

None

Output

| Field | Description | | --- | --- | | min_value | | | max_value | | | avg_value | | | STDEV_value | | | percentile_25 | | | median_value | | | percentile_75 | |

Example output record

| Field | Description | | --- | --- | | min_value | | | max_value | | | avg_value | | | STDEV_value | | | percentile_25 | | | median_value | | | percentile_75 | |

Documentation

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

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