TRIMMEAN_q
Updated 2023-10-24 14:48:55.157000
Syntax
SELECT [westclintech].[wct].[TRIMMEAN_q] (
<@Values_RangeQuery, nvarchar(4000),>
,<@Percent, float,>)
Description
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.
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 Type
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 #s1', .2);
This produces the following result
----------------------
6.94117647058824