Updated: 6 August 2010
Use TRIMMEAN_q to calculate the mean of the interior of a dataset. TRIMMEAN_q calculates the mean taken by excluding a percentage of data points from the top and bottom tails of a dataset.
Syntax
SELECT [wctStatistics].[wct].[TRIMMEAN_q] (
<@Values_RangeQuery, nvarchar(4000),>
,<@Percent, float,>)
Arguments
@Values_RangeQuery
the select statement, as text, used to determine values to be used in the TRIMMEAN_q calculation.
@Percent
is the fractional number of data points to exclude from the calculation. For example, if percent = 0.2, 4 points are trimmed from a dataset of 20 points (20 x 0.2): 2 from the top and 2 from the bottom of the set. @Percent is an expression of type float or of a type that can be implicitly converted to float.
Return Types
float
Remarks
· If @Percent < 0 or @Percent > 1, TRIMMEAN_q returns an error.
· TRIMMEAN_q rounds the number of excluded data points down to the nearest multiple of 2. If percent = 0.1, 10 percent of 30 data points equals 3 points. For symmetry, TRIMMEAN_q excludes a single value from the top and bottom of the dataset.
· No GROUP BY is required for this function even though it produces aggregated results.
Examples
CREATE TABLE #s1(
[num] [float] NOT NULL
)
INSERT INTO #s1 VALUES (1)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (2)
INSERT INTO #s1 VALUES (3)
INSERT INTO #s1 VALUES (4)
INSERT INTO #s1 VALUES (5)
INSERT INTO #s1 VALUES (6)
INSERT INTO #s1 VALUES (7)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (8)
INSERT INTO #s1 VALUES (9)
INSERT INTO #s1 VALUES (10)
INSERT INTO #s1 VALUES (11)
INSERT INTO #s1 VALUES (12)
INSERT INTO #s1 VALUES (13)
INSERT INTO #s1 VALUES (13)
INSERT INTO #s1 VALUES (14)
To calculate the mean of the interior of a dataset above, with 20 percent excluded from calculation:
select wct.TRIMMEAN_q('Select num from #t1',.2)
This produces the following result
----------------------
3.77777777777778
(1 row(s) affected)