quantilesGK

Introduced in: v23.4.0

Computes multiple quantiles of a numeric data sequence at different levels simultaneously using the Greenwald-Khanna algorithm.

This function works similarly with quantileGK but allows computing multiple quantile levels in a single pass, which is more efficient than calling individual quantile functions.

The Greenwald-Khanna algorithm is an algorithm used to compute quantiles on a stream of data in a highly efficient manner. It was introduced by Michael Greenwald and Sanjeev Khanna in 2001. The algorithm is highly efficient, taking only O(log n) space and O(log log n) time per item (where n is the size of the input). It is also highly accurate, providing approximate quantile values with controllable accuracy.

Syntax

quantilesGK(accuracy, level1, level2, ...)(expr)

Arguments

• expr — Expression over the column values resulting in numeric data types, Date, DateTime or DateTime64. (U)Int* or Int128 or UInt128 or Int256 or UInt256 or Float* or Decimal* or Date or DateTime or DateTime64

Returned value

Array of quantiles of the specified levels in the same order as the levels were specified. For numeric data types the output format matches the input format. Array((U)Int*) or Array(Int128) or Array(UInt128) or Array(Int256) or Array(UInt256) or Array(Float*) or Array(Decimal*) or Array(Date) or Array(DateTime) or Array(DateTime64)

Examples

Computing multiple quantiles with GK algorithm

SELECT quantilesGK(1, 0.25, 0.5, 0.75)(number + 1) FROM numbers(1000);

┌─quantilesGK(1, 0.25, 0.5, 0.75)(plus(number, 1))─┐
│ [1,1,1]                                          │
└──────────────────────────────────────────────────┘

Higher accuracy quantiles

SELECT quantilesGK(100, 0.25, 0.5, 0.75)(number + 1) FROM numbers(1000);

┌─quantilesGK(100, 0.25, 0.5, 0.75)(plus(number, 1))─┐
│ [251,498,741]                                      │
└────────────────────────────────────────────────────┘