This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

GoogleSQL for BigQuery supports KLL functions.

The KLL16 algorithm estimatesquantiles fromsketches. If you don't wantto work with sketches and don't need customized precision, considerusingapproximate aggregate functionswith system-defined precision.

KLL functions are approximate aggregate functions.Approximate aggregation requires significantly less memory than an exactquantiles computation, but also introduces statistical error.This makes approximate aggregation appropriate for large data streams forwhich linear memory usage is impractical, as well as for data that isalready approximate.

Due to the non-deterministic nature of the KLL algorithm, sketches createdon the same set of data with the same precision might not be identical, leadingto variation in the approximate quantile results.

KLL_QUANTILES.EXTRACT_INT64(sketch,num_quantiles)

WITHDataAS(SELECTxFROMUNNEST(GENERATE_ARRAY(1,100))ASx)SELECTKLL_QUANTILES.EXTRACT_INT64(kll_sketch,2)AShalves,KLL_QUANTILES.EXTRACT_INT64(kll_sketch,3)ASterciles,KLL_QUANTILES.EXTRACT_INT64(kll_sketch,4)ASquartiles,KLL_QUANTILES.EXTRACT_INT64(kll_sketch,6)ASsextiles,FROM(SELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROMData);/*------------+---------------+------------------+------------------------+ | halves     | terciles      | quartiles        | sextiles               | +------------+---------------+------------------+------------------------+ | [1,50,100] | [1,34,67,100] | [1,25,50,75,100] | [1,17,34,50,67,84,100] | +------------+---------------+------------------+------------------------*/

KLL_QUANTILES.EXTRACT_FLOAT64(sketch,num_quantiles)

LikeKLL_QUANTILES.EXTRACT_INT64,but accepts KLL sketches initialized on data of typeFLOAT64.

Return Type

ARRAY<FLOAT64>

KLL_QUANTILES.EXTRACT_POINT_INT64

KLL_QUANTILES.EXTRACT_POINT_INT64(sketch,phi)

Description

Takes a single KLL sketch asBYTES and returns a single quantile.Thephi argument specifies the quantile to return as a fraction of the totalnumber of rows in the input, normalized between 0 and 1. This means that thefunction will return a valuev such that approximately Φ *n inputs are lessthan or equal tov, and a (1-Φ) *n inputs are greater than or equal tov.This is a scalar function.

Returns an error if the underlying type of the input sketch isn't compatiblewith typeINT64.

Returns an error if the input isn't a valid KLL quantiles sketch.

Supported Argument Types

• sketch:BYTES KLL sketch initialized onINT64 data type
• phi:FLOAT64 between 0 and 1

Return Type

INT64

Example

The following query initializes a KLL sketch from five rows of data. Thenit returns the value of the eighth decile or 80th percentile of the sketch.

SELECTKLL_QUANTILES.EXTRACT_POINT_INT64(kll_sketch,.8)ASquintileFROM(SELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT1ASxUNIONALLSELECT2ASxUNIONALLSELECT3ASxUNIONALLSELECT4ASxUNIONALLSELECT5ASx));/*----------+ | quintile | +----------+ |      4   | +----------*/

KLL_QUANTILES.EXTRACT_POINT_FLOAT64

KLL_QUANTILES.EXTRACT_POINT_FLOAT64(sketch,phi)

Description

LikeKLL_QUANTILES.EXTRACT_POINT_INT64,but accepts KLL sketches initialized on data of typeFLOAT64.

Supported Argument Types

• sketch:BYTES KLL sketch initialized onFLOAT64 data type
• phi:FLOAT64 between 0 and 1

Return Type

FLOAT64

KLL_QUANTILES.INIT_INT64

KLL_QUANTILES.INIT_INT64(input[,precision[,weight=>input_weight]])

Description

Takes one or moreinput values and aggregates them into aKLL sketch. This function represents the output sketchusing theBYTES data type. This is anaggregate function.

Supported Argument Types

• input:INT64
• precision: AnINT64 value that defines the exactness of the returnedapproximate quantileq. The default value is 1000. For more informationabout precision, seePrecision for KLL sketches. The value ofprecision must be between 1 and 100,000.
• input_weight:INT64. By default, values in an initialized KLL sketch areweighted equally as1. To weight values differently, usethe named argument,weight, which assigns a weight to each input in theresulting KLL sketch.weight is a multiplier. For example, if you assign aweight of3 to an input value, it's as if three instances of the input valueare included in the generation of the KLL sketch. The minimum value forweight is1 and the maximum value is2,147,483,647.

Return Type

KLL sketch asBYTES

Examples

The following query takes a column of typeINT64 and outputs a sketch asBYTES that allows you to retrieve values whose ranks are within±1/1000 * 5 = ±1/200 ≈ 0 ranks of their exact quantile.

SELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT1ASxUNIONALLSELECT2ASxUNIONALLSELECT3ASxUNIONALLSELECT4ASxUNIONALLSELECT5ASx);

The following examples illustrate how weight works when you initialize aKLL sketch. The results are converted to quantiles.

WITHpointsAS(SELECT1ASx,1ASyUNIONALLSELECT2ASx,1ASyUNIONALLSELECT3ASx,1ASyUNIONALLSELECT4ASx,1ASyUNIONALLSELECT5ASx,1ASy)SELECTKLL_QUANTILES.EXTRACT_INT64(kll_sketch,2)AShalvesFROM(SELECTKLL_QUANTILES.INIT_INT64(x,1000,weight=>y)ASkll_sketchFROMpoints);/*---------+ | halves  | +---------+ | [1,3,5] | +---------*/

WITHpointsAS(SELECT1ASx,1ASyUNIONALLSELECT2ASx,3ASyUNIONALLSELECT3ASx,1ASyUNIONALLSELECT4ASx,1ASyUNIONALLSELECT5ASx,1ASy)SELECTKLL_QUANTILES.EXTRACT_INT64(kll_sketch,2)AShalvesFROM(SELECTKLL_QUANTILES.INIT_INT64(x,1000,weight=>y)ASkll_sketchFROMpoints);/*---------+ | halves  | +---------+ | [1,2,5] | +---------*/

KLL_QUANTILES.INIT_FLOAT64

KLL_QUANTILES.INIT_FLOAT64(input[,precision[,weight=>input_weight]])

Description

LikeKLL_QUANTILES.INIT_INT64,but acceptsinput of typeFLOAT64.

KLL_QUANTILES.INIT_FLOAT64 orders values according to the GoogleSQLfloating point sort order. For example,NaN orders before‑inf.

Supported Argument Types

• input:FLOAT64
• precision:INT64
• input_weight:INT64

Return Type

KLL sketch asBYTES

KLL_QUANTILES.MERGE_INT64

KLL_QUANTILES.MERGE_INT64(sketch,num_quantiles)

Description

Takes KLL sketches asBYTES and merges them intoa new sketch, then returns the quantiles that divide the input intonum_quantiles equal-sized groups, along with the minimum and maximum values of theinput. The output is anARRAY containing the exact minimum value fromthe input data that you used to initialize the sketches, eachapproximate quantile, and the exact maximum value from the initial input data.This is an aggregate function.

If the merged sketches were initialized with different precisions, the precisionis downgraded to the lowest precision involved in the merge — except if theaggregations are small enough to still capture the input exactly — then themergee's precision is maintained.

Returns an error if the underlying type of one or more input sketches isn'tcompatible with typeINT64.

Returns an error if the input isn't a valid KLL quantiles sketch.

Supported Argument Types

• sketch:BYTES KLL sketch initialized onINT64 data type
• num_quantiles: A positiveINT64 value that represents the number ofroughly equal-sized groups to divide the merged sketchesinto. The maximum value is 100,000.

Return Type

ARRAY<INT64>

Example

The following query initializes two KLL sketches from five rows of data each.Then it merges these two sketches and returns anARRAY containing the minimum,median, and maximum values in the input sketches.

SELECTKLL_QUANTILES.MERGE_INT64(kll_sketch,2)AShalvesFROM(SELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT1ASxUNIONALLSELECT2ASxUNIONALLSELECT3ASxUNIONALLSELECT4ASxUNIONALLSELECT5)UNIONALLSELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT6ASxUNIONALLSELECT7ASxUNIONALLSELECT8ASxUNIONALLSELECT9ASxUNIONALLSELECT10ASx));/*----------+ | halves   | +----------+ | [1,5,10] | +----------*/

KLL_QUANTILES.MERGE_FLOAT64

KLL_QUANTILES.MERGE_FLOAT64(sketch,num_quantiles)

Description

LikeKLL_QUANTILES.MERGE_INT64,but accepts KLL sketches initialized on data of typeFLOAT64.

KLL_QUANTILES.MERGE_FLOAT64 orders values according to the GoogleSQLfloating point sort order. For example,NaN orders before‑inf.

Supported Argument Types

• sketch:BYTES KLL sketch initialized onFLOAT64 data type
• num_quantiles:INT64

Return Type

ARRAY<FLOAT64>

KLL_QUANTILES.MERGE_PARTIAL

KLL_QUANTILES.MERGE_PARTIAL(sketch)

Description

Takes KLL sketches of the same underlying type and merges them to return a newsketch of the same underlying type. This is an aggregate function.

If the merged sketches were initialized with different precisions, the precisionis downgraded to the lowest precision involved in the merge — except if theaggregations are small enough to still capture the input exactly — then themergee's precision is maintained.

Returns an error if two or more sketches don't have compatible underlying types,such as one sketch ofINT64 values and another ofFLOAT64 values.

Returns an error if one or more inputs aren't a valid KLL quantiles sketch.

IgnoresNULL sketches. If the input contains zero rows or onlyNULLsketches, the function returnsNULL.

Supported Argument Types

• sketch:BYTES KLL sketch

Return Type

KLL sketch asBYTES

Example

The following query initializes two KLL sketches from five rows of data each.Then it merges these two sketches into a new sketch, also asBYTES. Bothinput sketches have the same underlying data type and precision.

SELECTKLL_QUANTILES.MERGE_PARTIAL(kll_sketch)ASmerged_sketchFROM(SELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT1ASxUNIONALLSELECT2ASxUNIONALLSELECT3ASxUNIONALLSELECT4ASxUNIONALLSELECT5)UNIONALLSELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT6ASxUNIONALLSELECT7ASxUNIONALLSELECT8ASxUNIONALLSELECT9ASxUNIONALLSELECT10ASx));

KLL_QUANTILES.MERGE_POINT_INT64

KLL_QUANTILES.MERGE_POINT_INT64(sketch,phi)

Description

Takes KLL sketches asBYTES and merges them, then extracts a singlequantile from the merged sketch. Thephi argument specifies the quantileto return as a fraction of the total number of rows in the input, normalizedbetween 0 and 1. This means that the function will return a valuev such thatapproximately Φ *n inputs are less than or equal tov, and a (1-Φ) *ninputs are greater than or equal tov. This is an aggregate function.

If the merged sketches were initialized with different precisions, the precisionis downgraded to the lowest precision involved in the merge — except if theaggregations are small enough to still capture the input exactly — then themergee's precision is maintained.

Returns an error if the underlying type of one or more input sketches isn'tcompatible with typeINT64.

Returns an error if the input isn't a valid KLL quantiles sketch.

Supported Argument Types

• sketch:BYTES KLL sketch initialized onINT64 data type
• phi:FLOAT64 between 0 and 1

Return Type

INT64

Example

The following query initializes two KLL sketches from five rows of data each.Then it merges these two sketches and returns the value of the ninth decile or90th percentile of the merged sketch.

SELECTKLL_QUANTILES.MERGE_POINT_INT64(kll_sketch,.9)ASquantileFROM(SELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT1ASxUNIONALLSELECT2ASxUNIONALLSELECT3ASxUNIONALLSELECT4ASxUNIONALLSELECT5)UNIONALLSELECTKLL_QUANTILES.INIT_INT64(x,1000)ASkll_sketchFROM(SELECT6ASxUNIONALLSELECT7ASxUNIONALLSELECT8ASxUNIONALLSELECT9ASxUNIONALLSELECT10ASx));/*----------+ | quantile | +----------+ |        9 | +----------*/

KLL_QUANTILES.MERGE_POINT_FLOAT64

KLL_QUANTILES.MERGE_POINT_FLOAT64(sketch,phi)

Description

LikeKLL_QUANTILES.MERGE_POINT_INT64,but accepts KLL sketches initialized on data of typeFLOAT64.

KLL_QUANTILES.MERGE_POINT_FLOAT64 orders values according to theGoogleSQLfloating point sort order. For example,NaNorders before‑inf.

Supported Argument Types

• sketch:BYTES KLL sketch initialized onFLOAT64 data type
• phi:FLOAT64 between 0 and 1

Return Type

FLOAT64