> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# APPROX_PERCENTILE

> Estimates one or more percentiles of non-NULL numeric values.

<div className="e6-meta"><span className="e6-eyebrow e6-eyebrow--quiet">Aggregate · Window</span></div>

NULL values are ignored. An empty input returns NULL, whether requesting one percentile or an array of percentiles.

## Syntax

A window function keeps each input row and adds a result for that row. `PARTITION BY` groups rows with matching values; without it, all input rows form one group.

### Aggregate

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_percentile(value, percentile [, accuracy])
```

* Source dialect is not PRESTO; Spark variant.

### Window

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_percentile(value, percentile [, accuracy]) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])
```

* Source dialect is not PRESTO; Spark variant.
* Use a frame that includes all rows in the group or starts at the first row (`UNBOUNDED PRECEDING`). Sliding frames that drop earlier rows are not supported. RANGE frames with a numeric or time offset are not supported.

### Presto (aggregate)

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_percentile(value, percentile)
```

* Source dialect is PRESTO.

### Presto weighted (aggregate)

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
approx_percentile(value, weight, percentile)
```

* Source dialect is PRESTO.

## Arguments

Type notation used below:

* F is TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s).

### Aggregate and window

| Name | Type | Required | Description |
| - | - | - | - |
| value | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s) | Yes | Values to estimate. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| percentile | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s); or ARRAY\<F> | Yes | Fraction(s) in \[0,1]. Every scalar or array element must be a constant fraction in \[0, 1]; fractions are converted to the floating-point form. Must be a constant argument. |
| accuracy | positive INTEGER constant | No | Controls accuracy. Higher values use more memory. Must be a positive constant integer. Default: 10000. |

### Presto

| Name | Type | Required | Description |
| - | - | - | - |
| value | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s) | Yes | Values to estimate. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| percentile | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s); or ARRAY\<F> | Yes | Fraction(s) in \[0,1]. Every scalar or array element must be a constant fraction in \[0, 1]; fractions are converted to the floating-point form. Must be a constant argument. |

### Presto weighted

| Name | Type | Required | Description |
| - | - | - | - |
| value | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s) | Yes | Values to estimate. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point). |
| weight | INTEGER | Yes | Integer multiplicity at least 1. |
| percentile | TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE or DECIMAL(p, s); or ARRAY\<F> | Yes | Fraction(s) in \[0,1]. Every scalar or array element must be a constant fraction in \[0, 1]; fractions are converted to the floating-point form. Must be a constant argument. |

## Returns

* Type: same type as value, or ARRAY\<same type as value>
* NULL behavior: NULL values are ignored. An empty input returns NULL, whether requesting one percentile or an array of percentiles.
* Empty input: Returns NULL on empty input.
* Shape: Scalar fraction returns scalar; array fractions return an array of matching length.
* Ordering: Array result order follows requested fractions.

## Properties

| Property | Value |
| - | - |
| Deterministic | Partial (Estimated percentiles can vary with the order in which values are processed.) |
| Supports DISTINCT | Partial (Removes duplicates in grouped aggregation only.) |
| Supports window usage | Partial (Use all rows in the group or start at its first row. Sliding frames and RANGE offsets are not supported.) |
| Supports window frames | Partial (Use all rows in the group or start at its first row. Sliding frames and RANGE offsets are not supported.) |
| Exact result | No |

* Approximation bound: The Spark-style form has relative rank error 1/accuracy; accuracy defaults to 10000. The Presto-style form has no fixed maximum error guarantee and does not accept an accuracy argument.

## Usage notes

* Fraction and accuracy must be constant expressions. They cannot vary by row.
* Higher accuracy uses more memory. Approximate results can vary with the order in which values are processed.
* In the PRESTO form, weighted calls use (value, weight, percentile). The Spark-style form uses (value, percentile, accuracy).
* PRESTO ignores NULL values and NULL weights. Non-NULL values paired with weight \<1 raise errors; weights on NULL values are ignored.
* In the PRESTO form, DISTINCT removes duplicate values from unweighted calls and duplicate (value, weight) pairs from weighted calls.
* INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
* DISTINCT removes duplicates in grouped aggregation. Do not use it to remove duplicates in a window call.

## Examples

### Basic aggregate example

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t("value") AS (VALUES (0),(1),(2),(10)) SELECT approx_percentile("value",array(0.5,0.4,0.1),100) AS result FROM t;
```

| result |
| - |
| \[1,1,0] |

### Basic Presto example

* PRESTO source dialect.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH t(v) AS (VALUES (CAST(1 AS DOUBLE)),(CAST(2 AS DOUBLE)),(CAST(3 AS DOUBLE))) SELECT approx_percentile(v,array(1.0,0.0,0.5)) AS result FROM t;
```

| result |
| - |
| \[3.0,1.0,2.0] |

### Default accuracy across all rows in each group

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH sample(v) AS (VALUES (2), (2), (2))
SELECT approx_percentile(v, 0.5) OVER () AS median
FROM sample;
```

| median |
| - |
| 2 |
| 2 |
| 2 |

## Limitations

* Sliding windows that drop earlier rows are not supported. Start the frame at the first row in the group.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.