SQL方言参考
聚合函数对一组值进行操作，返回单个标量值。标准规定，除了 VAR_POP、VAR_SAMP、STDDEV_POP 和 STDDEV_SAMP 之外，所有聚合函数都应该能够处理参数之前的两个附加量词之一：ALL（特性 ID E091-06）和 DISTINCT（特性 ID E091-07）。ALL 是默认值，可以省略，而 DISTINCT 意味着只有唯一的值会被传递给聚合函数。为了使表达更简洁，这两个量词不会针对每个函数单独讨论，而是在函数支持两者时指定为 [DISTINCT|ALL]。
此页面包含可能很大且复杂的比较表格。
虽然完全可以查看它们的完整状态，但建议安装一个比较表格扩展，该扩展允许选择感兴趣的特定列进行比较。
Aggregate function operate on a group of values, returning single scalar value. The standard specifies that with the exception of VAR_POP, VAR_SAMP, STDDEV_POP and STDDEV_SAMP all aggregate function should be able to handle one of two additional quantifier before an argument: ALL (feature ID E091-06) and DISTINCT (feature ID E091-07). ALL is the default and can be omitted and DISTINCT means that only unique values would be passed in aggregate function. To make presentation more compact, these two quantifiers aren't discussed separately for every function, but specified as [DISTINCT|ALL] if function supports both of them.
| SQL version | Feature | Standard SQL:2011 |
DB2 | Firebird | Ingres | Linter | MSSQL | MySQL Vers. 5.x |
MonetDB | Oracle Vers. 11.x |
PostgreSQL | SQLite | Virtuoso |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ? | Count all rows in a group | COUNT(*) ... GROUP BY <grouping criterion> | COUNT(*) | COUNT(*) | COUNT(*) | ? | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | COUNT(*) | ? |
| ? | Count non-NULL values in x | COUNT(x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | COUNT([DISTINCT] x) | ? |
| ? | Concatenate non-NULL values in x using y as separator | N/A | ? | LIST([DISTINCT] x, y) | ? | ? | N/A[1] | GROUP_CONCAT([DISTINCT] x SEPARATOR y) | listagg(x) listagg(x, y) sys.group_concat(x) sys.group_concat(x, y) |
since R11.2: LISTAGG(x, y)[2] | STRING_AGG(x, y) since 9.0 [3] | GROUP_CONCAT([DISTINCT] x [, y]) | ? |
| ? | Sum of x | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM(x) | SUM([DISTINCT] x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM(x) | SUM([DISTINCT] x) | SUM([DISTINCT] x) TOTAL([DISTINCT] x) |
? |
| ? | Average of x | AVG(x) | AVG([DISTINCT] x) | AVG(x) | AVG(x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | AVG(x) | AVG([DISTINCT] x) | AVG([DISTINCT] x) | ? |
| ? | Minimum value in x | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | MIN(x) | ? |
| ? | Maximum value in x | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | MAX(x) | ? |
| ? | Standard deviation | STDDEV_POP(x) STDDEV_SAMP(x) |
STDDEV([DISTINCT] x) | STDDEV_POP(x) STDDEV_SAMP(x) |
STDDEV_POP(x) | STDDEV([DISTINCT] x) | STDEV(x) STDEVP(x) |
STD(x) STDDEV(x) STDDEV_POP(x) |
sys.stddev_pop(x) sys.stddev_samp(x) |
STDDEV([DISTINCT|ALL] x) | STDDEV(x) STDDEV_POP(x) STDDEV_SAMP(x) |
N/A | STDDEV(x, y) |
| ? | Variance | VAR_POP(x) VAR_SAMP(x) |
VARIANCE([DISTINCT] x) | VAR_POP(x) VAR_SAMP(x) |
? | VARIANCE([DISTINCT] x) | VAR(x) VARP(x) |
VARIANCE(x) VAR_POP(x) |
sys.var_pop(x) sys.var_samp(x) |
VARIANCE(x) | VARIANCE(x) VAR_POP(x) VAR_SAMP(x) |
N/A | VAR(x) |
| ? | Population covariance of x and y | COVAR_POP(x, y) | COVARIANCE(x, y) COVAR(x, y) |
COVAR_POP(x, y) | ? | ? | N/A | N/A | sys.covar_pop(x, y) | COVAR_POP(x, y) | COVAR_POP(x, y) | N/A | ? |
| ? | Sample covariance of x and y | COVAR_SAMP(x, y) | N/A | COVAR_SAMP(x, y) | ? | ? | N/A | N/A | sys.covar_samp(x, y) | COVAR_SAMP(x, y) | COVAR_SAMP(x, y) | N/A | ? |
- ↑ Can be implemented using user-defined aggregator functions or several other approaches[1]
- ↑ Can be implemented using user-defined aggregator function, undocumented wmsys.wm_concat function and using several other approaches[2]
- ↑ Can be implemented for older versions using PostgreSQL arrays and user-defined aggregator functions[3]