尧图网站建设 
文章目录一、分类总览1、函数分类2、全部函数二、聚合函数基础1、定义2、区别3、简单记忆4、案例三、基础聚合类1、定义2、区别3、简单记忆4、案例四、计数 / 去重类1、定义2、区别3、简单记忆4、案例五、极值关联取值类1、定义2、区别3、简单记忆4、案例六、布尔 / 位聚合类1、定义2、区别3、简单记忆4、案例七、集合 / Map / 字符串类1、定义2、区别3、简单记忆4、案例八、统计分析类1、定义2、区别3、简单记忆4、案例九、百分位 / 分布类1、定义2、区别3、简单记忆4、案例十、最容易混淆的函数对比十一、参考资料一、分类总览1、函数分类分类主要解决的问题代表函数1、基础聚合类求和、平均、最大、最小、中位数、任取一个值SUM、AVG、MAX、MIN、MEDIAN、ANY_VALUE2、计数 / 去重类行数统计、条件计数、近似去重COUNT、COUNT_IF、APPROX_DISTINCT3、极值关联取值类找最大/最小指标对应的另一列值ARG_MAX、ARG_MIN、MAX_BY、MIN_BY4、布尔 / 位聚合类布尔逻辑与或、位运算聚合ANY、BOOL_AND、BOOL_OR、BITWISE_AND_AGG、BITWISE_OR_AGG、BITWISE_XOR_AGG5、集合 / Map / 字符串类聚合成数组、Map、字符串COLLECT_LIST、COLLECT_SET、MAP_AGG、MAP_UNION、MAP_UNION_SUM、MULTIMAP_AGG、HISTOGRAM、WM_CONCAT6、统计分析类相关性、协方差、标准差、方差CORR、COVAR_POP、COVAR_SAMP、STDDEV、STDDEV_SAMP、VAR_SAMP、VARIANCE/VAR_POP7、百分位 / 分布类百分位和近似分布PERCENTILE、PERCENTILE_APPROX、PERCENTILE_CONT、PERCENTILE_DISC、NUMERIC_HISTOGRAM2、全部函数分类函数功能1、基础聚合类ANY_VALUE在指定范围内任选一个非 NULL 值返回1、基础聚合类AVG计算平均值1、基础聚合类MAX计算最大值1、基础聚合类MEDIAN计算中位数1、基础聚合类MIN计算最小值1、基础聚合类SUM计算汇总值2、计数 / 去重类APPROX_DISTINCT计算非重复值的近似数量2、计数 / 去重类COUNT计算记录数2、计数 / 去重类COUNT_IF计算条件为 TRUE 的记录数3、极值关联取值类ARG_MAX返回最大值对应行的另一列值3、极值关联取值类ARG_MIN返回最小值对应行的另一列值3、极值关联取值类MAX_BY返回最大值对应行的另一列值3、极值关联取值类MIN_BY返回最小值对应行的另一列值4、布尔 / 位聚合类ANY判断是否至少存在一个 TRUE4、布尔 / 位聚合类BOOL_AND对一组布尔值执行 AND4、布尔 / 位聚合类BOOL_OR对一组布尔值执行 OR4、布尔 / 位聚合类BITWISE_AND_AGG对整数执行按位 AND 聚合4、布尔 / 位聚合类BITWISE_OR_AGG对整数执行按位 OR 聚合4、布尔 / 位聚合类BITWISE_XOR_AGG对整数执行按位 XOR 聚合5、集合 / Map / 字符串类COLLECT_LIST聚合为保留重复值的数组5、集合 / Map / 字符串类COLLECT_SET聚合为去重数组5、集合 / Map / 字符串类HISTOGRAM统计每个值出现次数并构造 Map5、集合 / Map / 字符串类MAP_AGG将两列聚合为 Key-Value Map5、集合 / Map / 字符串类MAP_UNION合并多个 Map5、集合 / Map / 字符串类MAP_UNION_SUM合并多个 Map并对相同 Key 的数值求和5、集合 / Map / 字符串类MULTIMAP_AGG聚合为 Key → Array 的 Map5、集合 / Map / 字符串类WM_CONCAT使用指定分隔符聚合字符串6、统计分析类CORR计算皮尔逊相关系数6、统计分析类COVAR_POP计算总体协方差6、统计分析类COVAR_SAMP计算样本协方差6、统计分析类STDDEV计算总体标准差6、统计分析类STDDEV_SAMP计算样本标准差6、统计分析类VAR_SAMP计算样本方差6、统计分析类VARIANCE / VAR_POP计算总体方差7、百分位 / 分布类NUMERIC_HISTOGRAM计算数值列的近似直方图7、百分位 / 分布类PERCENTILE计算精确百分位适合较小数据量7、百分位 / 分布类PERCENTILE_APPROX计算近似百分位适合大数据量7、百分位 / 分布类PERCENTILE_CONT计算连续型精确百分位可线性插值7、百分位 / 分布类PERCENTILE_DISC计算离散型百分位返回实际存在的值二、聚合函数基础语法功能GROUP BY按字段分组后分别聚合DISTINCT聚合前去重FILTER (WHERE ...)只对满足条件的数据参与聚合WITHIN GROUP (ORDER BY ...)聚合前先对组内数据排序1、定义聚合函数将多条记录汇总成一个结果。常见形式SELECTdeptno,SUM(sal)FROMempGROUPBYdeptno;2、区别写法核心区别GROUP BY决定按什么维度分别计算DISTINCT先去重再参与聚合FILTER只让满足条件的记录进入聚合函数WITHIN GROUP聚合之前先定义组内顺序其中WITHIN GROUP主要用于需要顺序的聚合函数例如WM_CONCAT、COLLECT_LIST、COLLECT_SET。3、简单记忆GROUP BY 分组算。DISTINCT 去重算。FILTER 筛完再算。WITHIN GROUP 排完再聚。4、案例-- 结果按部门分别统计工资总额SELECTdeptno,SUM(sal)AStotal_salFROMempGROUPBYdeptno;-- 结果统计不同部门数量SELECTCOUNT(DISTINCTdeptno)ASdept_cntFROMemp;-- 结果分别统计 10、20、30 部门工资总额SELECTSUM(sal)FILTER(WHEREdeptno10),SUM(sal)FILTER(WHEREdeptno20),SUM(sal)FILTER(WHEREdeptno30)FROMemp;-- 结果1,2,3SELECTWM_CONCAT(,,y)WITHINGROUP(ORDERBYy)FROMVALUES(k,1),(k,3),(k,2)ASt(x,y)GROUPBYx;三、基础聚合类函数功能SUM求和AVG平均值MAX最大值MIN最小值MEDIAN中位数ANY_VALUE任取一个值1、定义这一类负责最常见的数值或单值汇总。2、区别函数核心区别SUM总和AVG算术平均值MAX / MIN最大 / 最小MEDIAN排序后的中间位置ANY_VALUE不关心具体哪一条只随机/任选一个值最容易混淆AVG是平均值容易受极端值影响MEDIAN是中位数更偏向“中间水平”。3、简单记忆SUM加起来。AVG平均。MAX / MIN最大 / 最小。MEDIAN中间值。ANY_VALUE随便取一个。4、案例-- 结果37775SELECTSUM(sal)FROMemp;-- 结果2222.0588235294117SELECTAVG(sal)FROMemp;-- 结果5000SELECTMAX(sal)FROMemp;-- 结果800SELECTMIN(sal)FROMemp;-- 结果1600.0SELECTMEDIAN(sal)FROMemp;-- 结果返回任意一名员工姓名例如 SMITHSELECTANY_VALUE(ename)FROMemp;四、计数 / 去重类函数功能COUNT统计记录数COUNT_IF统计条件为 TRUE 的记录数APPROX_DISTINCT近似统计去重值数量1、定义COUNT统计记录数量也可使用DISTINCT去重后计数。COUNT_IF直接统计满足布尔条件的记录数量。APPROX_DISTINCT近似统计唯一值数量以降低大数据量去重统计成本。2、区别对比区别COUNT(*)所有行COUNT(col)只统计 col 非 NULL 的行COUNT(DISTINCT col)精确去重计数COUNT_IF(condition)条件计数APPROX_DISTINCT(col)近似去重计数官方说明存在约 5% 标准误差3、简单记忆COUNT 数行。COUNT_IF 满足条件才数。APPROX_DISTINCT 大数据量下近似去重。4、案例-- 结果17SELECTCOUNT(*)FROMemp;-- 结果3SELECTCOUNT(DISTINCTdeptno)FROMemp;-- 结果15SELECTCOUNT_IF(sal1000)FROMemp;-- 结果近似去重工资数量例如 12SELECTAPPROX_DISTINCT(sal)FROMemp;五、极值关联取值类函数功能ARG_MAX最大指标对应的另一列MAX_BY最大指标对应的另一列ARG_MIN最小指标对应的另一列MIN_BY最小指标对应的另一列1、定义这类函数不是返回“最大值本身”而是先找到某列最大或最小的那一行再返回该行另一列的值。2、区别ARG_MAX和MAX_BY功能相同主要区别是参数顺序相反函数写法ARG_MAXARG_MAX(比较列, 返回列)MAX_BYMAX_BY(返回列, 比较列)ARG_MINARG_MIN(比较列, 返回列)MIN_BYMIN_BY(返回列, 比较列)如果最大值或最小值对应多行可能从这些并列行中返回其中一行。3、简单记忆ARG_MAX(sal, ename) 工资最大返回姓名。MAX_BY(ename, sal) 返回姓名按工资最大找。ARG_MIN / MIN_BY同理。4、案例-- 结果KINGSELECTARG_MAX(sal,ename)FROMemp;-- 结果KINGSELECTMAX_BY(ename,sal)FROMemp;-- 结果SMITHSELECTARG_MIN(sal,ename)FROMemp;-- 结果SMITHSELECTMIN_BY(ename,sal)FROMemp;六、布尔 / 位聚合类函数功能ANY是否至少一个 TRUEBOOL_AND所有布尔值做 ANDBOOL_OR所有布尔值做 ORBITWISE_AND_AGG多个整数按位 ANDBITWISE_OR_AGG多个整数按位 ORBITWISE_XOR_AGG多个整数按位 XOR1、定义这一类把多行布尔值或整数位信息合并成一个结果。2、区别对比区别ANY至少有一个 TRUE 即 TRUEBOOL_OR也是布尔 OR和ANY语义接近BOOL_AND全部有效布尔值都为 TRUE 才为 TRUEBITWISE_AND_AGG按二进制位 ANDBITWISE_OR_AGG按二进制位 ORBITWISE_XOR_AGG按二进制位 XOR布尔函数操作的是 TRUE / FALSE位聚合操作的是整数的二进制位。3、简单记忆ANY / BOOL_OR 有一个真就行。BOOL_AND 全部都真。BITWISE_* 对整数二进制位做聚合。4、案例-- 结果trueSELECTANY(x)FROMVALUES(true),(false),(false)ASt(x);-- 结果falseSELECTBOOL_AND(x)FROMVALUES(true),(false),(true)ASt(x);-- 结果trueSELECTBOOL_OR(x)FROMVALUES(true),(false),(false)ASt(x);-- 结果02 AND 1 0SELECTBITWISE_AND_AGG(v)FROMVALUES(2L),(1L)ASt(v);-- 结果32 OR 1 3SELECTBITWISE_OR_AGG(v)FROMVALUES(2L),(1L)ASt(v);-- 结果32 XOR 1 3SELECTBITWISE_XOR_AGG(v)FROMVALUES(2L),(1L)ASt(v);七、集合 / Map / 字符串类函数功能COLLECT_LIST聚合成数组保留重复COLLECT_SET聚合成数组去重HISTOGRAM值 → 出现次数MAP_AGG两列构造 MapMULTIMAP_AGG相同 Key 的多个 Value 聚合为数组MAP_UNION合并多个 MapMAP_UNION_SUM合并 Map 并对相同 Key 求和WM_CONCAT多行字符串拼成一个字符串1、定义这一类的核心是把多行记录聚合成ARRAYMAPSTRING等复杂结果。2、区别对比区别COLLECT_LISTvsCOLLECT_SET保留重复 vs 去重HISTOGRAM自动生成“值 → 次数”MAP_AGG一个 Key 对应一个 ValueMULTIMAP_AGG一个 Key 对应多个 Value 数组MAP_UNION多个 Map 合并相同 Key 只保留一个值MAP_UNION_SUM多个 Map 合并相同 Key 数值相加WM_CONCAT最终结果是字符串不是 ARRAY3、简单记忆LIST保重复。SET去重复。HISTOGRAM数次数。MAP_AGG Key → Value。MULTIMAP_AGG Key → 多个 Value。MAP_UNION_SUM Map 合并后同 Key 求和。WM_CONCAT 多行拼字符串。4、案例-- 结果[1,2,2]SELECTCOLLECT_LIST(x)FROMVALUES(1),(2),(2)ASt(x);-- 结果[1,2]SELECTCOLLECT_SET(x)FROMVALUES(1),(2),(2)ASt(x);-- 结果{hi:1,apple:2,pie:1}SELECTHISTOGRAM(x)FROMVALUES(hi),(NULL),(apple),(pie),(apple)ASt(x);-- 结果构造 Map例如 {1:apple,2:hi}SELECTMAP_AGG(k,v)FROMVALUES(1L,apple),(2L,hi)ASt(k,v);-- 结果{1:[apple,pie],2:[hi]}SELECTMULTIMAP_AGG(k,v)FROMVALUES(1L,apple),(2L,hi),(1L,pie)ASt(k,v);-- 结果合并为一个 MapSELECTMAP_UNION(m)FROMVALUES(MAP(1L,a,2L,b)),(MAP(3L,c))ASt(m);-- 结果{a:4,b:2}SELECTMAP_UNION_SUM(m)FROMVALUES(MAP(a,1L,b,2L)),(MAP(a,3L))ASt(m);-- 结果a,b,cSELECTWM_CONCAT(,,x)WITHINGROUP(ORDERBYx)FROMVALUES(b),(a),(c)ASt(x);八、统计分析类函数功能CORR皮尔逊相关系数COVAR_POP总体协方差COVAR_SAMP样本协方差STDDEV总体标准差STDDEV_SAMP样本标准差VARIANCE / VAR_POP总体方差VAR_SAMP样本方差1、定义这一类用于衡量两列之间是否一起变化一列数据自身的离散程度。2、区别函数关注点CORR两列线性相关程度结果通常在 -1 到 1COVAR_POP / COVAR_SAMP两列共同变化程度STDDEV / STDDEV_SAMP一列数据标准差VARIANCE / VAR_POP / VAR_SAMP一列数据方差总体与样本总体样本COVAR_POPCOVAR_SAMPSTDDEVSTDDEV_SAMPVARIANCE / VAR_POPVAR_SAMP3、简单记忆CORR 相关性。COVAR 协方差。STDDEV 标准差。VAR 方差。_POP Population总体。_SAMP Sample样本。4、案例-- 结果x 与 y 完全正相关时为 1.0SELECTCORR(x,y)FROMVALUES(1D,2D),(2D,4D),(3D,6D)ASt(x,y);-- 结果计算总体协方差SELECTCOVAR_POP(x,y)FROMVALUES(1D,2D),(2D,4D),(3D,6D)ASt(x,y);-- 结果计算样本协方差SELECTCOVAR_SAMP(x,y)FROMVALUES(1D,2D),(2D,4D),(3D,6D)ASt(x,y);-- 结果计算总体标准差SELECTSTDDEV(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果计算样本标准差SELECTSTDDEV_SAMP(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果计算总体方差SELECTVARIANCE(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果与 VARIANCE 等价计算总体方差SELECTVAR_POP(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果计算样本方差SELECTVAR_SAMP(x)FROMVALUES(1D),(2D),(3D)ASt(x);九、百分位 / 分布类函数功能PERCENTILE精确百分位适合较小数据量PERCENTILE_APPROX近似百分位适合大数据量PERCENTILE_CONT连续型精确百分位PERCENTILE_DISC离散型百分位NUMERIC_HISTOGRAM近似数值直方图1、定义这一类用于回答P50、P90、P95 是多少数据主要分布在哪些区间。2、区别对比区别PERCENTILE精确计算适合较小数据量PERCENTILE_APPROX近似计算适合大数据量PERCENTILE_CONT精确连续百分位可以插值PERCENTILE_DISC离散百分位只返回实际存在的值NUMERIC_HISTOGRAM不直接返回一个百分位而是近似描述整个分布PERCENTILE与PERCENTILE_CONT都属于精确百分位但接口和支持类型不同日常更重要的是区分精确与近似、连续与离散。3、简单记忆PERCENTILE 精确百分位。APPROX 近似适合大数据。CONT Continuous可以插值。DISC Discrete只拿实际值。HISTOGRAM 看整体分布。4、案例-- 结果emp 示例数据的 30% 百分位为 1290.0SELECTPERCENTILE(sal,0.3)FROMemp;-- 结果emp 示例数据的近似 30% 百分位约为 1252.5SELECTPERCENTILE_APPROX(sal,0.3)FROMemp;-- 结果1.5SELECTPERCENTILE_CONT(x,0.5)FROMVALUES(0D),(3D),(1D),(2D)ASt(x);-- 结果bSELECTPERCENTILE_DISC(x,0.5)FROMVALUES(c),(b),(a)ASt(x);-- 结果返回 MapKey 为近似区间点Value 为近似频数SELECTNUMERIC_HISTOGRAM(3,x)FROMVALUES(1D),(2D),(3D),(4D),(5D)ASt(x);十、最容易混淆的函数对比函数组合最核心区别COUNT(*)vsCOUNT(col)所有行 vs 只统计非 NULLCOUNT(DISTINCT)vsAPPROX_DISTINCT精确去重 vs 近似去重COUNT_IFvsCOUNT FILTER直接条件计数 vs 聚合函数过滤表达式AVGvsMEDIAN平均值 vs 中位数MAXvsARG_MAX返回最大值本身 vs 返回最大值所在行的另一列ARG_MAXvsMAX_BY功能相同参数顺序相反ARG_MINvsMIN_BY功能相同参数顺序相反ANYvsBOOL_OR都表示至少有一个 TRUE语义接近BOOL_ANDvsBOOL_OR全部为真 vs 至少一个为真COLLECT_LISTvsCOLLECT_SET保留重复 vs 去重MAP_AGGvsMULTIMAP_AGGKey→单值 vs Key→数组MAP_UNIONvsMAP_UNION_SUM合并并任选冲突值 vs 合并并对同 Key 数值求和HISTOGRAMvsNUMERIC_HISTOGRAM精确统计离散值次数 vs 近似描述连续数值分布CORRvsCOVAR_POP标准化相关程度 vs 原始协方差STDDEVvsSTDDEV_SAMP总体标准差 vs 样本标准差VAR_POPvsVAR_SAMP总体方差 vs 样本方差PERCENTILEvsPERCENTILE_APPROX精确、小数据 vs 近似、大数据PERCENTILE_CONTvsPERCENTILE_DISC可插值 vs 返回实际值WM_CONCATvsCOLLECT_LIST返回字符串 vs 返回数组十一、参考资料阿里云 MaxCompute 官方文档https://help.aliyun.com/zh/maxcompute/aggregate-functions MaxCompute中聚合函数
文章目录一、分类总览1、函数分类2、全部函数二、聚合函数基础1、定义2、区别3、简单记忆4、案例三、基础聚合类1、定义2、区别3、简单记忆4、案例四、计数 / 去重类1、定义2、区别3、简单记忆4、案例五、极值关联取值类1、定义2、区别3、简单记忆4、案例六、布尔 / 位聚合类1、定义2、区别3、简单记忆4、案例七、集合 / Map / 字符串类1、定义2、区别3、简单记忆4、案例八、统计分析类1、定义2、区别3、简单记忆4、案例九、百分位 / 分布类1、定义2、区别3、简单记忆4、案例十、最容易混淆的函数对比十一、参考资料一、分类总览1、函数分类分类主要解决的问题代表函数1、基础聚合类求和、平均、最大、最小、中位数、任取一个值SUM、AVG、MAX、MIN、MEDIAN、ANY_VALUE2、计数 / 去重类行数统计、条件计数、近似去重COUNT、COUNT_IF、APPROX_DISTINCT3、极值关联取值类找最大/最小指标对应的另一列值ARG_MAX、ARG_MIN、MAX_BY、MIN_BY4、布尔 / 位聚合类布尔逻辑与或、位运算聚合ANY、BOOL_AND、BOOL_OR、BITWISE_AND_AGG、BITWISE_OR_AGG、BITWISE_XOR_AGG5、集合 / Map / 字符串类聚合成数组、Map、字符串COLLECT_LIST、COLLECT_SET、MAP_AGG、MAP_UNION、MAP_UNION_SUM、MULTIMAP_AGG、HISTOGRAM、WM_CONCAT6、统计分析类相关性、协方差、标准差、方差CORR、COVAR_POP、COVAR_SAMP、STDDEV、STDDEV_SAMP、VAR_SAMP、VARIANCE/VAR_POP7、百分位 / 分布类百分位和近似分布PERCENTILE、PERCENTILE_APPROX、PERCENTILE_CONT、PERCENTILE_DISC、NUMERIC_HISTOGRAM2、全部函数分类函数功能1、基础聚合类ANY_VALUE在指定范围内任选一个非 NULL 值返回1、基础聚合类AVG计算平均值1、基础聚合类MAX计算最大值1、基础聚合类MEDIAN计算中位数1、基础聚合类MIN计算最小值1、基础聚合类SUM计算汇总值2、计数 / 去重类APPROX_DISTINCT计算非重复值的近似数量2、计数 / 去重类COUNT计算记录数2、计数 / 去重类COUNT_IF计算条件为 TRUE 的记录数3、极值关联取值类ARG_MAX返回最大值对应行的另一列值3、极值关联取值类ARG_MIN返回最小值对应行的另一列值3、极值关联取值类MAX_BY返回最大值对应行的另一列值3、极值关联取值类MIN_BY返回最小值对应行的另一列值4、布尔 / 位聚合类ANY判断是否至少存在一个 TRUE4、布尔 / 位聚合类BOOL_AND对一组布尔值执行 AND4、布尔 / 位聚合类BOOL_OR对一组布尔值执行 OR4、布尔 / 位聚合类BITWISE_AND_AGG对整数执行按位 AND 聚合4、布尔 / 位聚合类BITWISE_OR_AGG对整数执行按位 OR 聚合4、布尔 / 位聚合类BITWISE_XOR_AGG对整数执行按位 XOR 聚合5、集合 / Map / 字符串类COLLECT_LIST聚合为保留重复值的数组5、集合 / Map / 字符串类COLLECT_SET聚合为去重数组5、集合 / Map / 字符串类HISTOGRAM统计每个值出现次数并构造 Map5、集合 / Map / 字符串类MAP_AGG将两列聚合为 Key-Value Map5、集合 / Map / 字符串类MAP_UNION合并多个 Map5、集合 / Map / 字符串类MAP_UNION_SUM合并多个 Map并对相同 Key 的数值求和5、集合 / Map / 字符串类MULTIMAP_AGG聚合为 Key → Array 的 Map5、集合 / Map / 字符串类WM_CONCAT使用指定分隔符聚合字符串6、统计分析类CORR计算皮尔逊相关系数6、统计分析类COVAR_POP计算总体协方差6、统计分析类COVAR_SAMP计算样本协方差6、统计分析类STDDEV计算总体标准差6、统计分析类STDDEV_SAMP计算样本标准差6、统计分析类VAR_SAMP计算样本方差6、统计分析类VARIANCE / VAR_POP计算总体方差7、百分位 / 分布类NUMERIC_HISTOGRAM计算数值列的近似直方图7、百分位 / 分布类PERCENTILE计算精确百分位适合较小数据量7、百分位 / 分布类PERCENTILE_APPROX计算近似百分位适合大数据量7、百分位 / 分布类PERCENTILE_CONT计算连续型精确百分位可线性插值7、百分位 / 分布类PERCENTILE_DISC计算离散型百分位返回实际存在的值二、聚合函数基础语法功能GROUP BY按字段分组后分别聚合DISTINCT聚合前去重FILTER (WHERE ...)只对满足条件的数据参与聚合WITHIN GROUP (ORDER BY ...)聚合前先对组内数据排序1、定义聚合函数将多条记录汇总成一个结果。常见形式SELECTdeptno,SUM(sal)FROMempGROUPBYdeptno;2、区别写法核心区别GROUP BY决定按什么维度分别计算DISTINCT先去重再参与聚合FILTER只让满足条件的记录进入聚合函数WITHIN GROUP聚合之前先定义组内顺序其中WITHIN GROUP主要用于需要顺序的聚合函数例如WM_CONCAT、COLLECT_LIST、COLLECT_SET。3、简单记忆GROUP BY 分组算。DISTINCT 去重算。FILTER 筛完再算。WITHIN GROUP 排完再聚。4、案例-- 结果按部门分别统计工资总额SELECTdeptno,SUM(sal)AStotal_salFROMempGROUPBYdeptno;-- 结果统计不同部门数量SELECTCOUNT(DISTINCTdeptno)ASdept_cntFROMemp;-- 结果分别统计 10、20、30 部门工资总额SELECTSUM(sal)FILTER(WHEREdeptno10),SUM(sal)FILTER(WHEREdeptno20),SUM(sal)FILTER(WHEREdeptno30)FROMemp;-- 结果1,2,3SELECTWM_CONCAT(,,y)WITHINGROUP(ORDERBYy)FROMVALUES(k,1),(k,3),(k,2)ASt(x,y)GROUPBYx;三、基础聚合类函数功能SUM求和AVG平均值MAX最大值MIN最小值MEDIAN中位数ANY_VALUE任取一个值1、定义这一类负责最常见的数值或单值汇总。2、区别函数核心区别SUM总和AVG算术平均值MAX / MIN最大 / 最小MEDIAN排序后的中间位置ANY_VALUE不关心具体哪一条只随机/任选一个值最容易混淆AVG是平均值容易受极端值影响MEDIAN是中位数更偏向“中间水平”。3、简单记忆SUM加起来。AVG平均。MAX / MIN最大 / 最小。MEDIAN中间值。ANY_VALUE随便取一个。4、案例-- 结果37775SELECTSUM(sal)FROMemp;-- 结果2222.0588235294117SELECTAVG(sal)FROMemp;-- 结果5000SELECTMAX(sal)FROMemp;-- 结果800SELECTMIN(sal)FROMemp;-- 结果1600.0SELECTMEDIAN(sal)FROMemp;-- 结果返回任意一名员工姓名例如 SMITHSELECTANY_VALUE(ename)FROMemp;四、计数 / 去重类函数功能COUNT统计记录数COUNT_IF统计条件为 TRUE 的记录数APPROX_DISTINCT近似统计去重值数量1、定义COUNT统计记录数量也可使用DISTINCT去重后计数。COUNT_IF直接统计满足布尔条件的记录数量。APPROX_DISTINCT近似统计唯一值数量以降低大数据量去重统计成本。2、区别对比区别COUNT(*)所有行COUNT(col)只统计 col 非 NULL 的行COUNT(DISTINCT col)精确去重计数COUNT_IF(condition)条件计数APPROX_DISTINCT(col)近似去重计数官方说明存在约 5% 标准误差3、简单记忆COUNT 数行。COUNT_IF 满足条件才数。APPROX_DISTINCT 大数据量下近似去重。4、案例-- 结果17SELECTCOUNT(*)FROMemp;-- 结果3SELECTCOUNT(DISTINCTdeptno)FROMemp;-- 结果15SELECTCOUNT_IF(sal1000)FROMemp;-- 结果近似去重工资数量例如 12SELECTAPPROX_DISTINCT(sal)FROMemp;五、极值关联取值类函数功能ARG_MAX最大指标对应的另一列MAX_BY最大指标对应的另一列ARG_MIN最小指标对应的另一列MIN_BY最小指标对应的另一列1、定义这类函数不是返回“最大值本身”而是先找到某列最大或最小的那一行再返回该行另一列的值。2、区别ARG_MAX和MAX_BY功能相同主要区别是参数顺序相反函数写法ARG_MAXARG_MAX(比较列, 返回列)MAX_BYMAX_BY(返回列, 比较列)ARG_MINARG_MIN(比较列, 返回列)MIN_BYMIN_BY(返回列, 比较列)如果最大值或最小值对应多行可能从这些并列行中返回其中一行。3、简单记忆ARG_MAX(sal, ename) 工资最大返回姓名。MAX_BY(ename, sal) 返回姓名按工资最大找。ARG_MIN / MIN_BY同理。4、案例-- 结果KINGSELECTARG_MAX(sal,ename)FROMemp;-- 结果KINGSELECTMAX_BY(ename,sal)FROMemp;-- 结果SMITHSELECTARG_MIN(sal,ename)FROMemp;-- 结果SMITHSELECTMIN_BY(ename,sal)FROMemp;六、布尔 / 位聚合类函数功能ANY是否至少一个 TRUEBOOL_AND所有布尔值做 ANDBOOL_OR所有布尔值做 ORBITWISE_AND_AGG多个整数按位 ANDBITWISE_OR_AGG多个整数按位 ORBITWISE_XOR_AGG多个整数按位 XOR1、定义这一类把多行布尔值或整数位信息合并成一个结果。2、区别对比区别ANY至少有一个 TRUE 即 TRUEBOOL_OR也是布尔 OR和ANY语义接近BOOL_AND全部有效布尔值都为 TRUE 才为 TRUEBITWISE_AND_AGG按二进制位 ANDBITWISE_OR_AGG按二进制位 ORBITWISE_XOR_AGG按二进制位 XOR布尔函数操作的是 TRUE / FALSE位聚合操作的是整数的二进制位。3、简单记忆ANY / BOOL_OR 有一个真就行。BOOL_AND 全部都真。BITWISE_* 对整数二进制位做聚合。4、案例-- 结果trueSELECTANY(x)FROMVALUES(true),(false),(false)ASt(x);-- 结果falseSELECTBOOL_AND(x)FROMVALUES(true),(false),(true)ASt(x);-- 结果trueSELECTBOOL_OR(x)FROMVALUES(true),(false),(false)ASt(x);-- 结果02 AND 1 0SELECTBITWISE_AND_AGG(v)FROMVALUES(2L),(1L)ASt(v);-- 结果32 OR 1 3SELECTBITWISE_OR_AGG(v)FROMVALUES(2L),(1L)ASt(v);-- 结果32 XOR 1 3SELECTBITWISE_XOR_AGG(v)FROMVALUES(2L),(1L)ASt(v);七、集合 / Map / 字符串类函数功能COLLECT_LIST聚合成数组保留重复COLLECT_SET聚合成数组去重HISTOGRAM值 → 出现次数MAP_AGG两列构造 MapMULTIMAP_AGG相同 Key 的多个 Value 聚合为数组MAP_UNION合并多个 MapMAP_UNION_SUM合并 Map 并对相同 Key 求和WM_CONCAT多行字符串拼成一个字符串1、定义这一类的核心是把多行记录聚合成ARRAYMAPSTRING等复杂结果。2、区别对比区别COLLECT_LISTvsCOLLECT_SET保留重复 vs 去重HISTOGRAM自动生成“值 → 次数”MAP_AGG一个 Key 对应一个 ValueMULTIMAP_AGG一个 Key 对应多个 Value 数组MAP_UNION多个 Map 合并相同 Key 只保留一个值MAP_UNION_SUM多个 Map 合并相同 Key 数值相加WM_CONCAT最终结果是字符串不是 ARRAY3、简单记忆LIST保重复。SET去重复。HISTOGRAM数次数。MAP_AGG Key → Value。MULTIMAP_AGG Key → 多个 Value。MAP_UNION_SUM Map 合并后同 Key 求和。WM_CONCAT 多行拼字符串。4、案例-- 结果[1,2,2]SELECTCOLLECT_LIST(x)FROMVALUES(1),(2),(2)ASt(x);-- 结果[1,2]SELECTCOLLECT_SET(x)FROMVALUES(1),(2),(2)ASt(x);-- 结果{hi:1,apple:2,pie:1}SELECTHISTOGRAM(x)FROMVALUES(hi),(NULL),(apple),(pie),(apple)ASt(x);-- 结果构造 Map例如 {1:apple,2:hi}SELECTMAP_AGG(k,v)FROMVALUES(1L,apple),(2L,hi)ASt(k,v);-- 结果{1:[apple,pie],2:[hi]}SELECTMULTIMAP_AGG(k,v)FROMVALUES(1L,apple),(2L,hi),(1L,pie)ASt(k,v);-- 结果合并为一个 MapSELECTMAP_UNION(m)FROMVALUES(MAP(1L,a,2L,b)),(MAP(3L,c))ASt(m);-- 结果{a:4,b:2}SELECTMAP_UNION_SUM(m)FROMVALUES(MAP(a,1L,b,2L)),(MAP(a,3L))ASt(m);-- 结果a,b,cSELECTWM_CONCAT(,,x)WITHINGROUP(ORDERBYx)FROMVALUES(b),(a),(c)ASt(x);八、统计分析类函数功能CORR皮尔逊相关系数COVAR_POP总体协方差COVAR_SAMP样本协方差STDDEV总体标准差STDDEV_SAMP样本标准差VARIANCE / VAR_POP总体方差VAR_SAMP样本方差1、定义这一类用于衡量两列之间是否一起变化一列数据自身的离散程度。2、区别函数关注点CORR两列线性相关程度结果通常在 -1 到 1COVAR_POP / COVAR_SAMP两列共同变化程度STDDEV / STDDEV_SAMP一列数据标准差VARIANCE / VAR_POP / VAR_SAMP一列数据方差总体与样本总体样本COVAR_POPCOVAR_SAMPSTDDEVSTDDEV_SAMPVARIANCE / VAR_POPVAR_SAMP3、简单记忆CORR 相关性。COVAR 协方差。STDDEV 标准差。VAR 方差。_POP Population总体。_SAMP Sample样本。4、案例-- 结果x 与 y 完全正相关时为 1.0SELECTCORR(x,y)FROMVALUES(1D,2D),(2D,4D),(3D,6D)ASt(x,y);-- 结果计算总体协方差SELECTCOVAR_POP(x,y)FROMVALUES(1D,2D),(2D,4D),(3D,6D)ASt(x,y);-- 结果计算样本协方差SELECTCOVAR_SAMP(x,y)FROMVALUES(1D,2D),(2D,4D),(3D,6D)ASt(x,y);-- 结果计算总体标准差SELECTSTDDEV(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果计算样本标准差SELECTSTDDEV_SAMP(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果计算总体方差SELECTVARIANCE(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果与 VARIANCE 等价计算总体方差SELECTVAR_POP(x)FROMVALUES(1D),(2D),(3D)ASt(x);-- 结果计算样本方差SELECTVAR_SAMP(x)FROMVALUES(1D),(2D),(3D)ASt(x);九、百分位 / 分布类函数功能PERCENTILE精确百分位适合较小数据量PERCENTILE_APPROX近似百分位适合大数据量PERCENTILE_CONT连续型精确百分位PERCENTILE_DISC离散型百分位NUMERIC_HISTOGRAM近似数值直方图1、定义这一类用于回答P50、P90、P95 是多少数据主要分布在哪些区间。2、区别对比区别PERCENTILE精确计算适合较小数据量PERCENTILE_APPROX近似计算适合大数据量PERCENTILE_CONT精确连续百分位可以插值PERCENTILE_DISC离散百分位只返回实际存在的值NUMERIC_HISTOGRAM不直接返回一个百分位而是近似描述整个分布PERCENTILE与PERCENTILE_CONT都属于精确百分位但接口和支持类型不同日常更重要的是区分精确与近似、连续与离散。3、简单记忆PERCENTILE 精确百分位。APPROX 近似适合大数据。CONT Continuous可以插值。DISC Discrete只拿实际值。HISTOGRAM 看整体分布。4、案例-- 结果emp 示例数据的 30% 百分位为 1290.0SELECTPERCENTILE(sal,0.3)FROMemp;-- 结果emp 示例数据的近似 30% 百分位约为 1252.5SELECTPERCENTILE_APPROX(sal,0.3)FROMemp;-- 结果1.5SELECTPERCENTILE_CONT(x,0.5)FROMVALUES(0D),(3D),(1D),(2D)ASt(x);-- 结果bSELECTPERCENTILE_DISC(x,0.5)FROMVALUES(c),(b),(a)ASt(x);-- 结果返回 MapKey 为近似区间点Value 为近似频数SELECTNUMERIC_HISTOGRAM(3,x)FROMVALUES(1D),(2D),(3D),(4D),(5D)ASt(x);十、最容易混淆的函数对比函数组合最核心区别COUNT(*)vsCOUNT(col)所有行 vs 只统计非 NULLCOUNT(DISTINCT)vsAPPROX_DISTINCT精确去重 vs 近似去重COUNT_IFvsCOUNT FILTER直接条件计数 vs 聚合函数过滤表达式AVGvsMEDIAN平均值 vs 中位数MAXvsARG_MAX返回最大值本身 vs 返回最大值所在行的另一列ARG_MAXvsMAX_BY功能相同参数顺序相反ARG_MINvsMIN_BY功能相同参数顺序相反ANYvsBOOL_OR都表示至少有一个 TRUE语义接近BOOL_ANDvsBOOL_OR全部为真 vs 至少一个为真COLLECT_LISTvsCOLLECT_SET保留重复 vs 去重MAP_AGGvsMULTIMAP_AGGKey→单值 vs Key→数组MAP_UNIONvsMAP_UNION_SUM合并并任选冲突值 vs 合并并对同 Key 数值求和HISTOGRAMvsNUMERIC_HISTOGRAM精确统计离散值次数 vs 近似描述连续数值分布CORRvsCOVAR_POP标准化相关程度 vs 原始协方差STDDEVvsSTDDEV_SAMP总体标准差 vs 样本标准差VAR_POPvsVAR_SAMP总体方差 vs 样本方差PERCENTILEvsPERCENTILE_APPROX精确、小数据 vs 近似、大数据PERCENTILE_CONTvsPERCENTILE_DISC可插值 vs 返回实际值WM_CONCATvsCOLLECT_LIST返回字符串 vs 返回数组十一、参考资料阿里云 MaxCompute 官方文档https://help.aliyun.com/zh/maxcompute/aggregate-functions