This query is used to provide summary statistics for the age across all drug era records stratified by drug (drug_concept_id): the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. The age value is defined by the earliest exposure. The input to the query is a value (or a comma-separated list of values) of a drug_concept_id. If the input is omitted, age is summarized for all existing drug_concept_id values.
Input:
| Parameter |
Example |
Mandatory |
Notes |
| list of concept_id |
1300978, 1304643, 1549080 |
Yes |
|
Sample query run:
The following is a sample run of the query. The input parameters are highlighted in blue
-- Oracle
SELECT tt.drug_concept_id
, min(tt.stat_value) AS min_value
, max(tt.stat_value) AS max_value
, avg(tt.stat_value) AS avg_value
, (round(stdDev(tt.stat_value)) ) AS stdDev_value
, PERCENTILE_DISC(0.25) WITHIN GROUP( ORDER BY tt.stat_value )
AS percentile_25
, PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY tt.stat_value )
AS median_value
, PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY tt.stat_value )
AS percential_75
FROM
( SELECT
extract(year from (min(t.drug_era_start_date) over(partition by t.person_id, t.drug_concept_id) )) - p.year_of_birth as stat_value,
t.drug_concept_id
FROM drug_era t,
person p
where t.person_id = p.person_id
and t.drug_concept_id in (1300978, 1304643, 1549080)
) tt
group by tt.drug_concept_id
order by tt.drug_concept_id
;
Output:
Output field list:
| Field |
Description |
| drug_concept_id |
|
| person_id |
A foreign key identifier to the person about whom the observation was recorded. The demographic details of that person are stored in the person table. |
| year_of_birth |
|
| median_value |
|
| percential_75 |
|
Sample output record:
| Field |
Description |
| Field |
Description |
| drug_concept_id |
|
| person_id |
|
| year_of_birth |
|
| percentile_25 |
|
| median_value |
|
| percential_75 |
|
|
|
