This query is used to to provide summary statistics for observation period start dates (observation_period_start_date) across all observation period records: 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.
Input:
<None>
Sample query run:
The following is a sample run of the query.
Oracle only
SELECT to_date( min( start_date ), 'J' ) AS min_start_date
, to_date( max( start_date ), 'J' ) AS max_start_date
, to_date( round( avg( start_date ) ), 'J' ) AS avg_start_date
, round( stdDev( start_date ) ) AS stdDev_days
, to_date( PERCENTILE_DISC(0.25) WITHIN GROUP( ORDER BY start_date ), 'J' )
AS percentile_25
, to_date( PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY start_date ), 'J' )
AS median
, to_date( PERCENTILE_DISC(0.75) WITHIN GROUP (ORDER BY start_date ), 'J' )
AS percentile_75
FROM /* start_date */
( SELECT to_number( to_char( observation_period_start_date, 'J' ) ) AS start_date
FROM observation_period
)
Output:
Output field list:
| Field |
Description |
| min_start_date |
Minimum start date value |
| max_start_date | Maximum start date value |
| avg_start_date | Average start date value |
| stdDev_days |
Standard Deviation of start date |
| percentile_25 | 25th percentile of start date | | median | Median of start date | | percentile_75 | 75th percentile of start date |
Sample output record:
| Field |
Value |
| min_start_date |
1/1/2003 |
| max_start_date |
6/30/2011 |
| avg_start_date |
2/5/2008 |
| stdDev_days |
741 |
| percentile_25 | 1/1/2006 | | median | 1/1/2009 | | percentile_75 | 1/1/2010 |
|
|
