MODE_q
Updated: 16 February 2017
Use the scalar function MODE_q to return the mode of the given numbers. The mode of a set of numbers is the one that occurs most often. For large groups of data MODE_q may provide better performance than the aggregate function MODE, provided that the query string includes an ORDER BY. For smaller groups, the ORDER BY adds overhead which will cause comparatively slower performance.
Syntax
SELECT [wct].[MODE_q](
<@Values_RangeQuery, nvarchar(max),>
,<@Behavior, float,>)
Arguments
@Values_RangeQuery
the SELECT statement, as text, used to determine the values to be used in the MODE_q calculation.
@Behavior
identifies how to handle ties.
Return Type
float
Remarks
· MODE_q only includes values of type float or of a type that can be implicitly converted to float. All other values and NULL are excluded.
· If there are no values occurring more than once, MODE_q returns NULL.
· @Behavior is truncated to zero decimal places.
· If TRUNC(@Behavior,0) < 0 or TRUNC(@Behavior,0) > 2, MODE_q returns an error.
· Set @Behavior = 0 to return the lowest mode value when there are ties
· Set @Behavior = 1 to return the greatest mode value when there are ties
· Set @Behavior = 2 to return NULL when there are ties.
Examples
Example #1
CREATE TABLE #m1([num] [float] NOT NULL)
INSERT INTO #m1 VALUES (5.6)
INSERT INTO #m1 VALUES (4)
INSERT INTO #m1 VALUES (4)
INSERT INTO #m1 VALUES (3)
INSERT INTO #m1 VALUES (2)
INSERT INTO #m1 VALUES (3)
SELECT wct.MODE_q('SELECT num FROM #m1',0) as MODE
This produces the following result
Example #2
CREATE TABLE #m2([num] [float] NOT NULL)
INSERT INTO #m2 VALUES (1)
INSERT INTO #m2 VALUES (2)
INSERT INTO #m2 VALUES (3)
INSERT INTO #m2 VALUES (1)
INSERT INTO #m2 VALUES (2)
INSERT INTO #m2 VALUES (3)
SELECT
n.behavior
,wct.MODE_q('SELECT num FROM #m2',n.behavior) as MODE
FROM (VALUES (0),(1),(2))n(behavior)
This produces the following result.
Example #3
In this example we use the XLeratorDB table-valued function SeriesInt to create 2,389,000 rows of random integers with values between 1 and 2000. The data are grouped in 5 categories and we calculate the mode for each category. Note the use of the ORDER BY in @Values_RangeQuery.
SELECT
n.m,
k.SeriesValue as x
INTO #t
FROM (VALUES (0,500000),(1,1100000),(2,650000),(3,130000),(4,9000))n(m,p)
CROSS APPLY wct.SeriesInt(1,2000,NULL,n.p,'R')k
SELECT
m,
wct.MODE_q('SELECT x FROM #t WHERE m = '+CONVERT(varchar(max), n.m)+' ORDER BY x',0) as [MODE]
FROM (SELECT DISTINCT m FROM #t)n
ORDER BY m
This produces the following result. Your results will be different.