Click or drag to resize
sqx

PERCENTILE Structure

Aggregate [float] PERCENTILE([float] Value, [float] Percentile, [smallint] Method)

Namespace:  sqx
Assembly:  SQX (in SQX.dll) Version: 1.0.2.6 (1.0.2.6)
Syntax
C#
Copy
[SerializableAttribute]
public struct PERCENTILE : IBinarySerialize
Remarks
Percentile aggregate function.
Method:
  • 0 - Returns the Percentile of the Nearest Rank.
  • 1 - Returns the Percentile of the Linear Interpolation Between Closest Ranks. First variant.
  • 2 - Returns the Percentile of the Linear Interpolation Between Closest Ranks. Second variant inclusive.
  • 3 - Returns the Percentile of the Linear Interpolation Between Closest Ranks. Third variant excludes both endpoints.
Percentile Support: 0 < Percentile <= 1

NOTE: All parameters except Value must remain constant within the GROUP BY.

Attributes

Format.UserDefined, IsInvariantToDuplicates = false, IsInvariantToNulls = true, IsInvariantToOrder = true, IsNullIfEmpty = true, MaxByteSize = -1
Examples
SQL
Copy
DECLARE @Percentile float = 0.75 -- 75%
;WITH sample_data AS (SELECT X FROM (VALUES (7), (9), (15), (18), (33), (35), (37.5), (45)) AS T(X))
SELECT sqx.PERCENTILE(X,@Percentile,0) M0, sqx.PERCENTILE(X,@Percentile,1) M1
, sqx.PERCENTILE(X,@Percentile,2) M2, sqx.PERCENTILE(X,@Percentile,3) M3
FROM sample_data
/*
M0           M1           M2           M3
------------ ------------ ------------ ------------
35           36.25        35.625       36.875*/
SQL
Copy
/* Winsorize */
DECLARE @MinPercentile float = 0.05, @MaxPercentile float = 0.95
;WITH sample_data AS (SELECT X FROM (VALUES (92), (19), (101), (58), (1053), (91), (26), (78), (10), (13), (-40), (101), (86), (85), (15), (89), (89), (28), (-5), (41)) AS T(X))
SELECT X, sqx.RowMax(MinValue, sqx.RowMin(MaxValue, X)) AS X_Winsorize
FROM (SELECT sqx.PERCENTILE(X,@MinPercentile,2) MinValue, sqx.PERCENTILE(X,@MaxPercentile,2) MaxValue FROM sample_data) p
, sample_data
ORDER BY X_Winsorize
See Also

Reference

sqx Namespace