Note
This function is available starting with V5.0.1.
Declaration
AVGWEIGHTED([DISTINCT | ALL] value, weight)
Description
AVGWEIGHTED (also written as avgWeighted) is a weighted average aggregation function that calculates the result using the following formula:
Σ(value × weight) / Σ(weight)
Notice
This is a ClickHouse-compatible function. By default, it is not available. To use it, you must set the sql_func_extension_mode parameter to enable it.
Parameters
Parameter |
Description |
|---|---|
| value | The values involved in the calculation. |
| weight | Corresponding tovalueThe weight. |
| DISTINCT | Optional. YesvalueDeduplicate the data and then recalculate. |
| ALL | Optional. Default value. Specifies to compute for all rows. |
Response parameters
- Returns the weighted average, with the same data type as
value. - Rows where
valueorweightisNULLwill be skipped. - Returns
NULLwhen the sum of weights for valid rows is 0. - Returns
NULLif the input is empty or all rows areNULL.
Examples
Enable the ClickHouse extension function mode.
obclient> ALTER SYSTEM SET sql_func_extension_mode = "ClickHouse";Calculate the weighted average.
obclient> SELECT AVGWEIGHTED(v, w) FROM (SELECT 10 AS v, 2 AS w UNION ALL SELECT 20, 3) t;The return result is as follows:
+-------------------+ | AVGWEIGHTED(v, w) | +-------------------+ | 16 | +-------------------+ 1 row in setThe calculation process is
(10 × 2 + 20 × 3) / (2 + 3) = 16.