19      阿里云

统计项计算__SQL_日志分析平台_态势感知-阿里云

ODPS SQL中支持用户自定义的统计项。此功能可用于数据质量监控等。ODPS在将数据写到目标表时，会根据统计的内容尽量自动得出统计值从而方便的使用户了解到数据的质量情况。目前能够统计的项包括：

• 表的总行数。
• 表的指定列为NULL的行数。
• 表的指定列值的总和。
• 表的指定列的最大值。
• 表的指定列的最小值。
• 表的符合某个表达式的行数。
• 表的某些列的唯一值个数（此统计项无法自动得出统计值）

注解：不可以在视图上面定义统计项，如果用户使用的是INSERT INTO语句或动态分区，系统也无法自动得出统计值，这时要使用analyze语句进行手工统计。关于analyze，请参考 执行统计过程 。

统计项操作

添加统计信息

默认情况下，系统不会自动收集表的统计信息。用户可以根据需要，指定为某张表添加统计项，命令为：

add statistic <TABLENAME> <STATISTICNAME> [<STATISTICRULE>];

其中：

• < TABLENAME >描述表名。
• < STATISTICNAME > 描述需要添加的统计项的名字。
• < STATISTICRULE > 描述某些特定的统计项所需要的规则；部分统计项没有规则定义。

需要注意的是：

• 当表被删除掉之后重新创建时，表中已添加的统计项和已经收集的统计值会丢失。
• 如果规则定义包含列名，当该列名被修改成其他名字后，规则定义不会自动修改，使用analyze执行统计流程在语义分析阶段会报无效的列。
• 使用CREATE TABLE AS命令创建的表不会继承原表的统计项信息。
• 部分用户定义的统计项并不会在QUERY执行的过程中自动收集数据，而是要通过执行analyze命令来手动收集。
• 在一张表上最多可添加100个统计项，在统计项中如果有表达式时，表达式及结果的长度最长不可以超过500字节。
• 如果自动统计过程中产生了异常，比如sum的值溢出，此时不会报异常（否则会导致整个SQL的失败），而是将结果置为inf，在使用时必须注意

目前可以统计的统计项为以下七种：

定义表的总行数

• 别名：table_count
• 统计返回值：整型

• 设置方法：

  • 添加信息项：ADD STATISTIC < tablename > TABLE_COUNT; 如:

  add statistic tbl_shop table_count;

  • 移除信息项：REMOVE STATISTIC < tablename > TABLE_COUNT; 如:

  remove statistic tbl_shop table_count;

  该统计项的作用与”count < TABLENAME> ;”命令相同。

• 显示示例：

table_count: 10000

或（对于partition的表）：

(col1='2011-10-25 00:00:00', col2=1)            table_count: 2000

定义表的指定列为NULL的行数:

• 别名：null_value
• 统计返回值：整型

• 设置方法：

  • 添加信息项： ADD STATISTIC < tablename> NULL_VALUE < columnname> ; 如:

  add statistic tbl_shop null_value shop_name;

  • 移除信息项： REMOVE STATISTIC < tablename > NULL_VALUE < columnname > ; 如:

  remove statistic tbl_shop null_value shop_name;

注解：当用户需要指定为多列统计NULL值时，需要分别为每一列定义。

• 显示示例

null_value col1=10000

或（对于partition的表）：

(col1='2011-10-25 00:00:00', col2=1)            null_value col1=5000

定义表的指定列总和

• 别名：column_sum
• 统计返回值：Bigint类型或Double类型，取决于作用于的列的类型

• 设置方法：

  • 添加信息项： ADD STATISTIC < tablename > COLUMN_SUM < columnname > [ < filter_expr >]; 如:

  add statistic tbl_shop column_sum revenue;

  • 移除信息项： REMOVE STATISTIC < tablename > COLUMN_SUM < columnname > [ < filter_expr >]; 如:

  remove statistic tbl_shop column_sum revenue;

注解：

• 该统计项仅能作用于Bigint类型和Double类型的列之上，否则在设置时报错。
• 当列中有部分值是NULL，NULL不做统计。当列的全部值都是NULL，结果是NULL
• 利用此统计项，以及所有记录数，可以得到该列的平均值。
• FILTER_EXPR是一个有效的表达式，表示只统计符合此表达式的行的值，参见 定义统计符合某个表达式的行数： 。

• 显示示例：

column_sum col3=10000
column_sum col4=111.11

或（对于partition的表）

(col1='2011-10-25 00:00:00', col2=1)            column_sum col3: 2000
(col1='2011-10-25 00:00:00', col2=1)            column_sum col4: 200.2

定义表的指定列最大值

• 别名：column_max

• 格式：Bigint, Double或Datetime类型，取决于作用于的列的类型

• 设置方法：

  • 添加信息项： ADD STATISTIC < tablename > COLUMN_MAX < columnname > [<filter_expr > ]; 如:

  add statistic tbl_shop column_max revenue;

  • 移除信息项： REMOVE STATISTIC <tablename > COLUMN_MAX <columnname > [< filter_expr >]; 如:

  remove statistic tbl_shop column_max revenue;

注解：

• 该统计项仅能作用于Bigint, Double或Datetime类型的列之上，否则在设置时报错。
• 当列中部分值为NULL时，NULL不做统计。当列全部为NULL的话，则结果为NULL
• filter_expr是一个有效的表达式，表示只统计符合此表达式的行的值，参见 定义统计符合某个表达式的行数： 。

显示示例：

column_max col3=9979

或（对于partition的表）：

(col1='2011-10-25 00:00:00', col2=1)            column_max col3: 3122

定义表的指定列最小值

• 别名：column_min
• 格式：Bigint, Double或Datetime类型，取决于作用于的列的类型

• 设置方法：

  • 添加信息项： ADD STATISTIC <tablename > COLUMN_MIN <columnname > [<filter_expr >]; 如

  add statistic tbl_shop column_min revenue;

  • 移除信息项： REMOVE STATISTIC <tablename > COLUMN_MIN <columnname > [<filter_expr >]; 如:

  remove statistic tbl_shop column_min revenue;

注解：该统计项仅能作用于Bigint, Double或Datetime类型的列之上，否则在设置时报错。当列中部分值为NULL时，NULL不做统计。当列全部为NULL的话，则结果为NULL利用column_max和column_min可以统计某列的值域范围。filter_expr是一个有效的表达式，表示只统计符合此表达式的行的值，参见 定义统计符合某个表达式的行数： 。

显示示例：

column_min col3=101

或（对于partition的表）：

(col1='2011-10-25 00:00:00', col2=1)            column_min col3: 3

定义统计符合某个表达式的行数

• 别名：expression_condition
• 统计项返回值：Bigint类型
• 设置方法：
  • 添加信息项： ADD STATISTIC <tablename > EXPRESSION_CONDITION <expressiondefinition > ; 如:

    add statistic tbl_shop expression_condition tbl_shop='hangzhou';

  • 移除信息项： REMOVE STATISTIC <tablename > EXPRESSION_CONDITION <expressiondefinition > ; 如:

    remove statistic tbl_shop expression_condition tbl_shop='hangzhou';

注解：

• <expressiondefinition > 定义了一个表达式，该表达式允许以列名或常量作为输入，支持“+”, “-”, “*”, “/”, “=”, “>”, “<”, “AND”, “OR”, “NOT”这些运算符的组合。表达式可以是列名与常量的运算，如“mount > 500 AND mount < 1000”，也可以是列名与列名的计算，如“start_date < end_date”。
• 表达式的定义应该满足ODPS SQL对表达式的限制。
• 该统计项统计表中满足此表达式的记录数。
• 当表达式的计算结果为NULL时，不会被统计。
• 利用表达式定义，用户可以灵活的统计所需的统计值，如：
• 某列非负的记录数：col_name >=0。
• 某列为指定值的记录数，如一个字符串类型的字段，值为“BEIJING”的记录数：col_name=”BEIJING”。

• 示例：

  • 添加表达式统计

  add statistic table1 expression_condition col3=”BEIJING”;
  add statistic table1 expression_condition col3=”SHANGHAI”;
  add statistic table1 expression_condition col3=”SHENZHEN”;

  • 显示表达式统计

  expression_condition col3='BEIJING': 1000
  expression_condition col3='SHANGHAI': 500
  expression_condition col3='SHENZHEN': 300

  或（对于partition的表）

  (col1='2011-10-25 00:00:00', col2=1)            expression_condition col3='BEIJING': 1000
  (col1='2011-10-25 00:00:00', col2=1)            expression_condition col3='SHANGHAI': 500
  (col1='2011-10-25 00:00:00', col2=1)            expression_condition col3='SHENZHEN': 300

查看统计数据

当用户为某个表添加了统计项后，可以查看该表的统计值，要注意的是，查看统计数据的命令为

show statistic <TABLENAME> [<PARTITION SPEC>];

如：Show statistic tbl_shop;

其中：

• <TABLENAME > 描述表名。
• < PARTITION SPEC > 可选，描述表中已存在的某个partition名称。当指定了partition spec后，显示该partition的统计信息；否则显示全表的统计信息，一般为所有partition统计信息的加和，或是集合中满足指定条件的某个值。

查看可用统计项

用户可以查询有哪些统计项可用，命令为

show statistic_list;

移除统计数据

用户也可以为某张表移除已经添加的统计项，命令为

remove statistic <TABLENAME> <STATISTICNAME> [<STATISTICRULE>];

如：remove statistic tbl_shop table_count;

需要注意的是：

• 移除未添加的统计项，或移除未添加的统计项规则会报错，如当用户未指定为table1统计count时，执行“remove statistic table1 count;”会报错。当用户仅仅指定“add statistic table2 distinct_value col1, col2;”时，执行”remove statistic table2 distinct_value col3;”会报错。
• 移除的统计项要求跟增加时的统计项完全一致，区分大小写和空格数，例如：对于“add statistic t expression_condition col1 > 1;”，执行“remove statistic t expression_condition COL1 > 1;”和“remove statistic t expression_condition COL1 > 1;”均会找不到移除的项。
• 当移除统计项时，相应已经有的统计信息也会被删除掉。

执行统计过程

根据事先定义的统计项执行统计过程得到相应的统计值, 语法：

analyze table <TABLENAME> <PARTITION SPEC> COMPUTE STATISTICS;

示例：

analyze table tbl_shop compute statistics;
analyze table t partition(pt='1') compute statistics

注解：

• 对于具有分区的表，只能对某个分区执行统计过程
• 对于不具有分区的表，只能对表执行统计过程

使用示例

准备测试表

假设已有数据表stat_pokes(foo int, bar string);

定义统计项

添加行数统计项

add statistic stat_pokes table_count;

添加表达式行数统计项

add statistic stat_pokes expression_condition foo > 100 and foo <= 200;

查看统计项

show statistic_list stat_pokes;
expression_condition foo > 100 and foo <= 200
table_count

计算统计项

analyze table stat_pokes compute statistics;

在计算完成后即可查看统计项的值, 查看统计值

show statistic stat_pokes;
expression_condition foo > 100 and foo <= 200: 105
table_count: 500

注意事项

• 如果修改列名，规则定义包含该列名的统计项会失效
• 对于add statistic操作，要求用户具有SELECT权限

最后更新：2016-11-22 19:11:48

  上一篇： 态势感知能为你做什么___态势感知场景教程_态势感知-阿里云

  下一篇： 态势感知10月功能更新__产品升级更新_态势感知-阿里云