[TOC] #### 7.2.4.1 avg **函数定义**: `avg([DISTINCT] expr)` 用于返回选中字段的平均值。可选字段DISTINCT参数可以用来返回去重后的平均值。 **示例**: `mysql> SELECT datetime, AVG(cost_time) FROM log_statis group by datetime;` `+---------------------+--------------------+` `| datetime            | avg('cost_time') |` `+---------------------+--------------------+` `| 2019-07-03 21:01:20 | 25.827794561933533 |` `+---------------------+--------------------+` `mysql> SELECT datetime, AVG(distinct cost_time) FROM log_statis group by datetime;` `+---------------------+---------------------------+` `| datetime            | avg(DISTINCT 'cost_time') |` `+---------------------+---------------------------+` `| 2019-07-04 02:23:24 |        20.666666666666668 |` `+---------------------+---------------------------+` **关键字**: `AVG` #### 7.2.4.2 Bitmap **建表** 建表时需要使用聚合模型,数据类型是 bitmap , 聚合函数是 bitmap\_union。例如: `CREATE TABLE 'pv_bitmap' (` `'dt' int(11) NULL COMMENT "",` `'page' varchar(10) NULL COMMENT "",` `'user_id' bitmap BITMAP_UNION NULL COMMENT ""` `) ENGINE=OLAP` `AGGREGATE KEY('dt', 'page')` `COMMENT "OLAP"` `DISTRIBUTED BY HASH('dt') BUCKETS 2;` > 注:当数据量很大时,最好为高频率的 bitmap\_union 查询建立对应的 rollup 表。 `ALTER TABLE pv_bitmap ADD ROLLUP pv (page, user_id);` **导入数据** TO\_BITMAP(expr):将 0 ~ 18446744073709551615 的 unsigned bigint 转为 bitmap。 BITMAP\_EMPTY():生成空 bitmap 列,用于 insert 或导入时填充默认值。 BITMAP\_HASH(expr):将任意类型的列通过 Hash 的方式转为 bitmap。 * Stream Load `cat data | curl --location-trusted -u user:passwd -T - -H "columns: dt,page,user_id, user_id=to_bitmap(user_id)" http://host:8410/api/test/testDb/_stream_load` `cat data | curl --location-trusted -u user:passwd -T - -H "columns: dt,page,user_id, user_id=bitmap_hash(user_id)" http://host:8410/api/test/testDb/_stream_load` `cat data | curl --location-trusted -u user:passwd -T - -H "columns: dt,page,user_id, user_id=bitmap_empty()" http://host:8410/api/test/testDb/_stream_load` * Insert Into id2 的列类型是 bitmap。 `insert into bitmap_table1 select id, id2 from bitmap_table2;` `insert into bitmap_table1 (id, id2) values (1001, to_bitmap(1000)), (1001, to_bitmap(2000));` `insert into bitmap_table1 select id, bitmap_union(id2) from bitmap_table2 group by id;` id2 的列类型是 int。 `insert into bitmap_table1 select id, to_bitmap(id2) from table;` id2 的列类型是 string。 `insert into bitmap_table1 select id, bitmap_hash(id_string) from table;` **查询数据** 函数定义: * BITMAP\_UNION(expr):计算输入 Bitmap 的并集,返回新的Bitmap。 * BITMAP\_UNION\_COUNT(expr):计算输入 Bitmap 的并集,返回其基数。该函数与 BITMAP\_COUNT(BITMAP\_UNION(expr)) 等价。目前推荐优先使用 BITMAP\_UNION\_COUNT ,其性能优于 BITMAP\_COUNT(BITMAP\_UNION(expr))。 * BITMAP\_UNION\_INT(expr):计算 TINYINT,SMALLINT 和 INT 类型的列中不同值的个数,返回值和 COUNT(DISTINCT expr) 相同。 * INTERSECT\_COUNT(bitmap\_column\_to\_count, filter\_column, filter\_values ...):计算满足 filter\_column 过滤条件的多个 bitmap 的交集的基数值。 bitmap\_column\_to\_count 是 bitmap 类型的列,filter\_column 是变化的维度列,filter\_values 是维度取值列表。 **示例** 计算 user\_id 的去重值: `select bitmap_union_count(user_id) from pv_bitmap;` `select bitmap_count(bitmap_union(user_id)) from pv_bitmap;` 计算 id 的去重值: `select bitmap_union_int(id) from pv_bitmap;` 计算 user\_id 的 留存: `select intersect_count(user_id, page, 'meituan') as meituan_uv,` `intersect_count(user_id, page, 'waimai') as waimai_uv,` `intersect_count(user_id, page, 'meituan', 'waimai') as retention //在 'meituan' 和 'waimai' 两个页面都出现的用户数` `from pv_bitmap` `where page in ('meituan', 'waimai');` **关键字** `BITMAP, BITMAP_COUNT, BITMAP_EMPTY, BITMAP_UNION, BITMAP_UNION_INT, TO_BITMAP, BITMAP_UNION_COUNT, INTERSECT_COUNT` #### 7.2.4.3 count **函数定义**: `COUNT([DISTINCT] expr)` 用于返回满足要求的行的数目。 **示例**: `MySQL > select count(*) from log_statis group by datetime;` `+----------+` `| count(*) |` `+----------+` `| 28515903 |` `+----------+` `MySQL > select count(datetime) from log_statis group by datetime;` `+-------------------+` `| count('datetime') |` `+-------------------+` `| 28521682 |` `+-------------------+` `MySQL > select count(distinct datetime) from log_statis group by datetime;` `+-------------------------------+` `| count(DISTINCT 'datetime')    |` `+-------------------------------+` `| 71045 |` `+-------------------------------+` **关键字**: `COUNT` #### 7.2.4.4 hll\_union\_agg **函数定义**: `HLL_UNION_AGG(hll)` HLL是基于HyperLogLog算法的工程实现,用于保存HyperLogLog计算过程的中间结果。 它只能作为表的value列类型,通过聚合来不断的减少数据量,以此来实现加快查询的目的。 基于它得到的是一个估算结果,误差大概在1%左右。hll列是通过其它列或者导入数据里面的数据生成的。 导入的时候通过hll\_hash函数来指定数据中哪一列用于生成hll列,它常用于替代count distinct,通过结合rollup在业务上用于快速计算UV等。 **示例**: `MySQL > select HLL_UNION_AGG(uv_set) from test_uv;;` `+-------------------------+` `| HLL_UNION_AGG('uv_set') |` `+-------------------------+` `| 17721 |` `+-------------------------+` **关键字**: `HLL_UNION_AGG, HLL, UNION, AGG` #### 7.2.4.5 max **函数定义**: `MAX(expr)` 返回expr表达式的最大值。 **示例**: `MySQL > select max(scan_rows) from log_statis group by datetime;` `+------------------+` `| max('scan_rows') |` `+------------------+` `|          4671587 |` `+------------------+` **关键字**: `MAX` #### 7.2.4.6 min **函数定义**: `MIN(expr)` 返回expr表达式的最小值。 **示例**: `MySQL > select min(scan_rows) from log_statis group by datetime;` `+------------------+` `| min('scan_rows') |` `+------------------+` `|                0 |` `+------------------+` **关键字**: `MIN` #### 7.2.4.7 approx\_count\_distinct **函数定义**: `APPROX_COUNT_DISTINCT(expr)` 返回类似于 COUNT(DISTINCT col) 结果的近似值聚合函数。 该函数比 COUNT 和 DISTINCT 组合的速度更快,并使用固定大小的内存,因此对于高基数的列可以使用更少的内存。 **示例**: `MySQL > select approx_count_distinct(query_id) from log_statis group by datetime;` `+-----------------+` `| approx_count_distinct('query_id') |` `+-----------------+` `| 17721 |` `+-----------------+` **关键字**: `APPROX_COUNT_DISTINCT` #### 7.2.4.8 percentile\_approx **函数定义**: `PERCENTILE_APPROX(expr, DOUBLE p[, DOUBLE compression])` 返回第p个百分位点的近似值,p的值介于0到1之间。 compression参数是可选项,可设置范围是\[2048, 10000\],值越大,精度越高,内存消耗越大,计算耗时越长。 compression参数未指定或设置的值在\[2048, 10000\]范围外,以10000的默认值运行。 该函数使用固定大小的内存,因此对于高基数的列可以使用更少的内存,可用于计算top 99等统计值。 **示例**: `MySQL > select 'table', percentile_approx(cost_time,0.99) from log_statis group by 'table';` `+---------------------+---------------------------+` `| table    | percentile_approx('cost_time', 0.99) |` `+----------+--------------------------------------+` `| test     |                                54.22 |` `+----------+--------------------------------------+` `MySQL > select 'table', percentile_approx(cost_time,0.99, 4096) from log_statis group by 'table';` `+---------------------+---------------------------+` `| table    | percentile_approx('cost_time', 0.99, 4096.0) |` `+----------+--------------------------------------+` `| test     |                                54.21 |` `+----------+--------------------------------------+` **关键字**: `PERCENTILE_APPROX, PERCENTILE, APPROX` #### 7.2.4.9 stddev, stddev\_pop **函数定义**: `STDDEV(expr)` 返回expr表达式的标准差。 **示例**: `MySQL > select stddev(scan_rows) from log_statis group by datetime;` `+---------------------+` `| stddev('scan_rows') |` `+---------------------+` `|  2.3736656687790934 |` `+---------------------+` `MySQL > select stddev_pop(scan_rows) from log_statis group by datetime;` `+-------------------------+` `| stddev_pop('scan_rows') |` `+-------------------------+` `|      2.3722760595994914 |` `+-------------------------+` **关键字**: `STDDEV, STDDEV_POP, POP` #### 7.2.4.10 stddev\_samp **函数定义**: `STDDEV_SAMP(expr)` 返回expr表达式的样本标准差。 **示例**: `MySQL > select stddev_samp(scan_rows) from log_statis group by datetime;` `+--------------------------+` `| stddev_samp('scan_rows') |` `+--------------------------+` `|        2.372044195280762 |` `+--------------------------+` **关键字**: `STDDEV_SAMP, STDDEV, SAMP` #### 7.2.4.11 sum **函数定义**: `SUM(expr)` 用于返回选中字段所有值的和。 **示例**: `MySQL > select sum(scan_rows) from log_statis group by datetime;` `+------------------+` `| sum('scan_rows') |` `+------------------+` `| 8217360135 |` `+------------------+` **关键字**: `SUM` #### 7.2.4.12 var\_samp, variance\_samp **函数定义**: `VAR_SAMP(expr)` 返回expr表达式的样本方差。 **示例**: `MySQL > select var_samp(scan_rows) from log_statis group by datetime;` `+-----------------------+` `| var_samp('scan_rows') |` `+-----------------------+` `|    5.6227132145741789 |` `+-----------------------+` 关键字: `VAR_SAMP, VARIANCE_SAMP, VAR, SAMP, VARIANCE` #### 7.2.4.13 variance, var\_pop, variance\_pop **函数定义**: `VARIANCE(expr)` 返回expr表达式的方差。 **示例**: `MySQL > select variance(scan_rows) from log_statis group by datetime;` `+-----------------------+` `| variance('scan_rows') |` `+-----------------------+` `|    5.6183332881176211 |` `+-----------------------+` `MySQL > select var_pop(scan_rows) from log_statis group by datetime;` `+----------------------+` `| var_pop('scan_rows') |` `+----------------------+` `| 5.6230744719006163 |` `+----------------------+` **关键字**: `VARIANCE, VAR_POP, VARIANCE_POP, VAR, POP`