OP08: Distribution of observation period records per person

Counts the number of observation period records (observation_period_id) for all persons: the mean, the standard deviation, the minimum, the 25th percentile, the median, the 75th percentile, the maximum and the number of missing values. There is no input required for this query.


Sample query run:
The following is a sample run of the query.
Oracle only
SELECT min( observation_periods ) AS min_periods
     , max( observation_periods ) AS max_periods
     , round( avg( observation_periods ), 2 ) AS avg_periods
     , round( stdDev( observation_periods ), 1 ) AS stdDev_periods
     , PERCENTILE_DISC(0.25) WITHIN GROUP( ORDER BY observation_periods ) 
                                     AS percentile_25
     , PERCENTILE_DISC(0.5)  WITHIN GROUP (ORDER BY observation_periods )
                                     AS median
     , PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY observation_periods )
                                     AS percentile_75
  FROM /* person, observation_periods */
     ( SELECT person_id, count(*) as observation_periods
         FROM observation_period
         JOIN person USING( person_id )
        GROUP BY person_id
Output field list:
 Field  Description
 min_periods  Minimum number of periods 
 max_periods  Maximum number of periods
 avg_periods  Average number of periods
 stdDev_periods  Standard Deviation of periods
 percentile_25  25th percentile of periods
 median  Median of periods
 percentile_75  75th percentile of periods

Sample output record:
 Field  Value
 min_periods  1
 max_periods  10
 avg_periods  1.12
 stdDev_periods  0.30
 percentile_25  1
 median  1
 percentile_75  1