Login     Register

        Contact Us     Search

XLeratorDB/statistics Documentation

SQL Server RANK.AVG function


RANK_AVG

Updated: 24 May 2013


Use the table-valued function RANK_AVG to calculate the ranks for a collection of x- and y-values. Tied ranks receive the average of ranks for the tied value. Ranks are calculated in ascending order.
Syntax
SELECT * FROM [wctStatistics].[wct].[RANK_AVG](
   <@x_y_Query, nvarchar(max),>)
Arguments
@x_y_Query
a T-SQL statement, as a string, that specifies the x- and y-values. The x- and y-values must be of the type float or of a type that implicitly converts to float.
Return Type
RETURNS TABLE (
      [x] [float] NULL,
      [y] [float] NULL,
      [xrank] [float] NULL,
      [yrank] [float] NULL
)

Column
Column Description
x
the supplied x-value
y
the supplied y-value
xrank
the average rank of the x-value in the dataset of x-values
yrank
the average rank of the y-value in the dataset of y-values
Remarks
·         The function is insensitive to order; it does not matter what order the x- and y-values are passed in.
·         The x- and y-values pairs will be returned along with the average ranks.
Examples
SELECT *
INTO #r
FROM (
SELECT 125,110 UNION ALL
SELECT 115,122 UNION ALL
SELECT 130,125 UNION ALL
SELECT 140,120 UNION ALL
SELECT 140,140 UNION ALL
SELECT 115,124 UNION ALL
SELECT 140,123 UNION ALL
SELECT 125,137 UNION ALL
SELECT 140,135 UNION ALL
SELECT 135,145
) n(x,y)
 
SELECT *
FROM wct.RANK_AVG('SELECT x,y from #r')
DROP TABLE #r
This produces the following result.

RANK_AVG function for SQL Server

We could also have passed the same data into the function with the following syntax.
SELECT *
FROM wct.RANK_AVG('SELECT 125,110 UNION ALL
SELECT 115,122 UNION ALL
SELECT 130,125 UNION ALL
SELECT 140,120 UNION ALL
SELECT 140,140 UNION ALL
SELECT 115,124 UNION ALL
SELECT 140,123 UNION ALL
SELECT 125,137 UNION ALL
SELECT 140,135 UNION ALL
SELECT 135,145 ')
This produces the following result.


Since this is a table –valued function, you can re-arrange the output using TSQL. In the following example, we will put the data in x-value order and we will move the xrank column next to the x column.
SELECT x,xrank,y,yrank
FROM wct.RANK_AVG('SELECT 125,110 UNION ALL
SELECT 115,122 UNION ALL
SELECT 130,125 UNION ALL
SELECT 140,120 UNION ALL
SELECT 140,140 UNION ALL
SELECT 115,124 UNION ALL
SELECT 140,123 UNION ALL
SELECT 125,137 UNION ALL
SELECT 140,135 UNION ALL
SELECT 135,145 ')
ORDER BY 1, 3
This produces the following result.

RANK_AVG function for SQL Server


Copyright 2008-2017 Westclintech LLC         Privacy Policy        Terms of Service