inst/QueryLibrary/Aggregate/drug_era/DER13.md

In sib-swiss/dsQueryLibraryServer: OMOP query Library

DER13: Distribution of number of distinct drugs persons take

Description

This query is used to provide summary statistics for the number of different distinct drugs (drug_concept_id) of all exposed persons: 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 tt AS (
  SELECT COUNT(DISTINCT t.drug_concept_id)::INTEGER AS stat_value
  ,      ROW_NUMBER() OVER (ORDER BY COUNT(DISTINCT t.drug_concept_id)) order_nr
  ,      (SELECT COUNT(DISTINCT person_id)::INTEGER FROM @cdm.drug_era) AS population_size
  FROM @cdm.drug_era t
  WHERE COALESCE(t.drug_concept_id, 0) > 0
  GROUP BY t.person_id
)
SELECT MIN(tt.stat_value)::numeric AS min_value
,      MAX(tt.stat_value)::numeric AS max_value
,      AVG(tt.stat_value)::numeric AS avg_value
,      ROUND(STDDEV(tt.stat_value), 0)::numeric AS STDEV_value
,      MIN(CASE WHEN order_nr < .25 * population_size THEN 9999 ELSE stat_value END)::numeric AS percentile_25
,      MIN(CASE WHEN order_nr < .50 * population_size THEN 9999 ELSE stat_value END)::numeric AS median_value
,      MIN(CASE WHEN order_nr < .75 * population_size THEN 9999 ELSE stat_value END)::numeric AS percentile_75
FROM tt;

Input

None

Output

| Field | Description | | --- | --- | | Min_value | Minimum number of distinct drugs persons take | | Max_value | Maximum number of distinct drugs persons take | | Avg_value | Average number of distinct drugs persons take | | Stdev_value | Standard deviation of drug era start date across all drug era records | | percentile_25_date | 25th percentile number of distinct drugs persons take | | median_date | Median number of distinct drugs persons take | | percentile_75_date | the 75th percentile number of distinct drugs persons take |

Example output record

| Field | Description | | --- | --- | | Min_value | 1 | | Max_value | 580 | | Avg_value | 12 | | Stdev_value | 17 | | percentile_25_date | 3 | | median_date | 6 | | percentile_75_date | 16 |

Documentation

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

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