Aggregate#
Function |
Description |
|---|---|
Returns an approximation of the given quantile of the grouping. |
|
Returns the average (mean) of all the elements in the grouping. |
|
Returns the Pearson correlation coefficient between two columns. |
|
Returns the amount of elements in the grouping. |
|
Returns the covariance between two columns. |
|
Returns the first element of the grouping. |
|
Returns the last element of the grouping. |
|
Returns the greatest (maximum) of all the elements in the grouping. |
|
Returns the median element from the grouping. |
|
Returns the smallest (minimum) of all the elements in the grouping. |
|
Returns the continuous quantile element from the grouping (interpolated value between two closest values). |
|
Divides the [0, 1] interval into equal-length subintervals, each corresponding to a value, and returns the value associated with the subinterval where the quantile value falls. |
|
Returns the standard deviation of all the elements in the grouping. |
|
Concatenates the input string values into a single string, separated by a delimiter. |
|
Returns the sum of all the elements in the grouping. |
|
Returns the sum of all the elements in the grouping, returning zero (rather than null) if there are no non-null values. |
|
Returns the variance of all the elements in the grouping. |
Filtering aggregates#
Any aggregate function call can be qualified with a FILTER (WHERE β¦) clause
that restricts it to the rows where the predicate is true. The clause is attached
to an individual aggregate call and is independent of the queryβs WHERE clause,
so multiple aggregates in the same SELECT can see different row sets.
Note
FILTER can be combined with OVER only for SUM, COUNT, MIN, MAX,
AVG and TOTAL.
Example:
df = pl.DataFrame(
{
"category": ["A", "B", "A", "B", "A", "B"],
"value": [10, 20, 30, 40, 50, 60],
}
)
df.sql("""
SELECT
category,
SUM(value) AS total,
SUM(value) FILTER (WHERE value > 25) AS total_high,
SUM(value) FILTER (WHERE value <= 25) AS total_low,
COUNT(*) FILTER (WHERE value >= 40) AS n_top
FROM self
GROUP BY category
ORDER BY category
""")
# shape: (2, 5)
# ββββββββββββ¬ββββββββ¬βββββββββββββ¬ββββββββββββ¬ββββββββ
# β category β total β total_high β total_low β n_top β
# β --- β --- β --- β --- β --- β
# β str β i64 β i64 β i64 β u32 β
# ββββββββββββͺββββββββͺβββββββββββββͺββββββββββββͺββββββββ‘
# β A β 90 β 80 β 10 β 1 β
# β B β 120 β 100 β 20 β 2 β
# ββββββββββββ΄ββββββββ΄βββββββββββββ΄ββββββββββββ΄ββββββββ
APPROX_QUANTILE#
Returns an approximation of the given quantile of the grouping, computed from a sketch that trades accuracy for memory. Prefer QUANTILE_CONT when the data fits in memory.
Takes an optional allowed rank error (a fraction of the number of rows; default 0.001) and
an optional sketch method: one of 'auto', 'kll', 'req_lo', 'req_hi' or
'req_both' (default 'auto').
Example:
df = pl.DataFrame({"foo": [5, 20, 10, 30, 70, 40, 10, 90]})
df.sql("""
SELECT
APPROX_QUANTILE(foo, 0.25) AS foo_q25,
APPROX_QUANTILE(foo, 0.50) AS foo_q50,
APPROX_QUANTILE(foo, 0.75, 0.01) AS foo_q75,
APPROX_QUANTILE(foo, 0.99, 0.01, 'req_hi') AS foo_q99,
FROM self
""")
# shape: (1, 4)
# βββββββββββ¬ββββββββββ¬ββββββββββ¬ββββββββββ
# β foo_q25 β foo_q50 β foo_q75 β foo_q99 β
# β --- β --- β --- β --- β
# β i64 β i64 β i64 β i64 β
# βββββββββββͺββββββββββͺββββββββββͺββββββββββ‘
# β 10 β 30 β 40 β 90 β
# βββββββββββ΄ββββββββββ΄ββββββββββ΄ββββββββββ
AVG#
Returns the average (mean) of all the elements in the grouping.
Example:
df = pl.DataFrame({"bar": [20, 10, 30, 40]})
df.sql("""
SELECT AVG(bar) AS bar_avg FROM self
""")
# shape: (1, 1)
# βββββββββββ
# β bar_avg β
# β --- β
# β f64 β
# βββββββββββ‘
# β 25.0 β
# βββββββββββ
CORR#
Returns the Pearson correlation coefficient between two columns.
Example:
df = pl.DataFrame({"foo": [1, 2, 3, 4, 5], "bar": [2, 4, 7, 5, 9]})
df.sql("""
SELECT CORR(foo, bar) AS corr FROM self
""")
# shape: (1, 1)
# ββββββββββββ
# β corr β
# β --- β
# β f64 β
# ββββββββββββ‘
# β 0.877809 β
# ββββββββββββ
COUNT#
Returns the amount of elements in the grouping.
Example:
df = pl.DataFrame(
{
"foo": ["b", "a", "b", "c"],
"bar": [20, 10, 30, 40]
}
)
df.sql("""
SELECT
COUNT(bar) AS n_bar,
COUNT(DISTINCT foo) AS n_foo_unique
FROM self
""")
# shape: (1, 2)
# βββββββββ¬βββββββββββββββ
# β n_bar β n_foo_unique β
# β --- β --- β
# β u32 β u32 β
# βββββββββͺβββββββββββββββ‘
# β 4 β 3 β
# βββββββββ΄βββββββββββββββ
COVAR#
Returns the covariance between two columns.
Aliases
COVAR_SAMP
Example:
df = pl.DataFrame({"foo": [1, 2, 3, 4, 5], "bar": [2, 4, 7, 5, 9]})
df.sql("""
SELECT COVAR(foo, bar) AS covar FROM self
""")
# shape: (1, 1)
# βββββββββ
# β covar β
# β --- β
# β f64 β
# βββββββββ‘
# β 3.75 β
# βββββββββ
FIRST#
Returns the first element of the grouping.
Example:
df = pl.DataFrame({"foo": ["b", "a", "b", "c"]})
df.sql("""
SELECT FIRST(foo) AS ff FROM self
""")
# shape: (1, 1)
# βββββββ
# β ff β
# β --- β
# β str β
# βββββββ‘
# β b β
# βββββββ
LAST#
Returns the last element of the grouping.
Example:
df = pl.DataFrame({"foo": ["b", "a", "b", "c"]})
df.sql("""
SELECT LAST(foo) AS lf FROM self
""")
# shape: (1, 1)
# βββββββ
# β lf β
# β --- β
# β str β
# βββββββ‘
# β c β
# βββββββ
MAX#
Returns the greatest (maximum) of all the elements in the grouping.
Example:
df = pl.DataFrame({"bar": [20, 10, 30, 40]})
df.sql("""
SELECT MAX(bar) AS bar_max FROM self
""")
# shape: (1, 1)
# βββββββββββ
# β bar_max β
# β --- β
# β i64 β
# βββββββββββ‘
# β 40 β
# βββββββββββ
MEDIAN#
Returns the median element from the grouping.
Example:
df = pl.DataFrame({"bar": [20, 10, 30, 40]})
df.sql("""
SELECT MEDIAN(bar) AS bar_median FROM self
""")
# shape: (1, 1)
# ββββββββββββββ
# β bar_median β
# β --- β
# β f64 β
# ββββββββββββββ‘
# β 25.0 β
# ββββββββββββββ
MIN#
Returns the smallest (minimum) of all the elements in the grouping.
Example:
df = pl.DataFrame({"bar": [20, 10, 30, 40]})
df.sql("""
SELECT MIN(bar) AS bar_min FROM self
""")
# shape: (1, 1)
# βββββββββββ
# β bar_min β
# β --- β
# β i64 β
# βββββββββββ‘
# β 10 β
# βββββββββββ
QUANTILE_CONT#
Returns the continuous quantile element from the grouping (interpolated value between two closest values).
Example:
df = pl.DataFrame({"foo": [5, 20, 10, 30, 70, 40, 10, 90]})
df.sql("""
SELECT
QUANTILE_CONT(foo, 0.25) AS foo_q25,
QUANTILE_CONT(foo, 0.50) AS foo_q50,
QUANTILE_CONT(foo, 0.75) AS foo_q75,
FROM self
""")
# shape: (1, 3)
# βββββββββββ¬ββββββββββ¬ββββββββββ
# β foo_q25 β foo_q50 β foo_q75 β
# β --- β --- β --- β
# β f64 β f64 β f64 β
# βββββββββββͺββββββββββͺββββββββββ‘
# β 10.0 β 25.0 β 47.5 β
# βββββββββββ΄ββββββββββ΄ββββββββββ
QUANTILE_DISC#
Divides the [0, 1] interval into equal-length subintervals, each corresponding to a value, and returns the value associated with the subinterval where the quantile value falls.
Example:
df = pl.DataFrame({"foo": [5, 20, 10, 30, 70, 40, 10, 90]})
df.sql("""
SELECT
QUANTILE_DISC(foo, 0.25) AS foo_q25,
QUANTILE_DISC(foo, 0.50) AS foo_q50,
QUANTILE_DISC(foo, 0.75) AS foo_q75,
FROM self
""")
# shape: (1, 3)
# βββββββββββ¬ββββββββββ¬ββββββββββ
# β foo_q25 β foo_q50 β foo_q75 β
# β --- β --- β --- β
# β f64 β f64 β f64 β
# βββββββββββͺββββββββββͺββββββββββ‘
# β 10.0 β 20.0 β 40.0 β
# βββββββββββ΄ββββββββββ΄ββββββββββ
STDDEV#
Returns the sample standard deviation of all the elements in the grouping.
Aliases
STDEV, STDEV_SAMP, STDDEV_SAMP
Example:
df = pl.DataFrame(
{
"foo": [10, 20, 8],
"bar": [10, 7, 18],
}
)
df.sql("""
SELECT STDDEV(foo) AS foo_std, STDDEV(bar) AS bar_std FROM self
""")
# shape: (1, 2)
# ββββββββββββ¬βββββββββββ
# β foo_std β bar_std β
# β --- β --- β
# β f64 β f64 β
# ββββββββββββͺβββββββββββ‘
# β 6.429101 β 5.686241 β
# ββββββββββββ΄βββββββββββ
STRING_AGG#
Concatenates the input string values into a single string, separated by the given
delimiter. Supports DISTINCT and in-argument ORDER BY and LIMIT
clauses to control which values are concatenated and in what order; the
separator is optional, defaulting to ",".
Aliases
GROUP_CONCAT, LISTAGG
Example:
df = pl.DataFrame(
{
"category": ["A", "B", "A", "B", "A", "B"],
"label": ["x1", "y1", "x2", "y2", "x3", "y3"],
"value": [10, 20, 30, 40, 50, 60],
}
)
df.sql("""
SELECT
category,
STRING_AGG(label LIMIT 2) AS two_labels,
STRING_AGG(label, ':' ORDER BY value DESC) AS labels_desc,
STRING_AGG(label, ',' ORDER BY value ASC) FILTER(WHERE label !~ '1$') AS labels_omit_1,
FROM self
GROUP BY category
ORDER BY category
""")
# shape: (2, 4)
# ββββββββββββ¬βββββββββββββ¬ββββββββββββββ¬ββββββββββββββββ
# β category β two_labels β labels_desc β labels_omit_1 β
# β --- β --- β --- β --- β
# β str β str β str β str β
# ββββββββββββͺβββββββββββββͺββββββββββββββͺββββββββββββββββ‘
# β A β x1,x2 β x3:x2:x1 β x2,x3 β
# β B β y1,y2 β y3:y2:y1 β y2,y3 β
# ββββββββββββ΄βββββββββββββ΄ββββββββββββββ΄ββββββββββββββββ
SUM#
Returns the sum of all the elements in the grouping.
Example:
df = pl.DataFrame(
{
"foo": [1, 2, 3],
"bar": [6, 7, 8],
"ham": ["a", "b", "c"],
}
)
df.sql("""
SELECT SUM(foo) AS foo_sum, SUM(bar) AS bar_sum FROM self
""")
# shape: (1, 2)
# βββββββββββ¬ββββββββββ
# β foo_sum β bar_sum β
# β --- β --- β
# β i64 β i64 β
# βββββββββββͺββββββββββ‘
# β 6 β 21 β
# βββββββββββ΄ββββββββββ
TOTAL#
Returns the sum of all the elements in the grouping. Unlike SUM (which
returns null for an all-null input, per the SQL standard), TOTAL returns zero.
The result preserves the summed columnβs dtype.
Example:
df = pl.DataFrame(
{"foo": [1, 2, 3], "bar": [None, None, None]},
schema={"foo": pl.Int64, "bar": pl.Int64},
)
df.sql("""
SELECT
SUM(bar) AS bar_sum,
TOTAL(foo) AS foo_total,
TOTAL(bar) AS bar_total
FROM self
""")
# shape: (1, 3)
# βββββββββββ¬ββββββββββββ¬ββββββββββββ
# β bar_sum β foo_total β bar_total β
# β --- β --- β --- β
# β i64 β i64 β i64 β
# βββββββββββͺββββββββββββͺββββββββββββ‘
# β null β 6 β 0 β
# βββββββββββ΄ββββββββββββ΄ββββββββββββ
VARIANCE#
Returns the variance of all the elements in the grouping.
Aliases
VAR, VAR_SAMP
Example:
df = pl.DataFrame(
{
"foo": [10, 20, 8],
"bar": [10, 7, 18],
}
)
df.sql("""
SELECT VARIANCE(foo) AS foo_var, VARIANCE(bar) AS bar_var FROM self
""")
# shape: (1, 2)
# βββββββββββββ¬ββββββββββββ
# β foo_var β bar_var β
# β --- β --- β
# β f64 β f64 β
# βββββββββββββͺββββββββββββ‘
# β 41.333333 β 32.333333 β
# βββββββββββββ΄ββββββββββββ
