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月功能更新__產品升級更新_態勢感知-阿裏雲