COUNT_BY_ENUM

描述

将列中数据看作枚举值，统计每个枚举值的个数。返回各个列枚举值的个数，以及非 null 值的个数与 null 值的个数。

语法

COUNT_BY_ENUM(<expr1>, <expr2>, ... , <exprN>)

参数

参数	说明
`<expr1>`	至少填写一个输入。值为字符串（STRING）类型的列

返回值

返回一个 JSONArray 字符串。

例如：

[{
  "cbe": {
    "F": 100,
    "M": 99
  },
  "notnull": 199,
  "null": 1,
  "all": 200
}, {
  "cbe": {
    "20": 10,
    "30": 5,
    "35": 1
  },
  "notnull": 16,
  "null": 184,
  "all": 200
}, {
  "cbe": {
    "北京": 10,
    "上海": 9,
    "广州": 20,
    "深圳": 30
  },
  "notnull": 69,
  "null": 131,
  "all": 200
}]

说明：返回值为一个 JSON array 字符串，内部对象的顺序是输入参数的顺序。

cbe：根据枚举值统计非 null 值的统计结果
notnull：非 null 的个数
null：null 值个数
all：总数，包括 null 值与非 null 值

举例

CREATE TABLE count_by_enum_test(
                `id` varchar(1024) NULL,
                `f1` text REPLACE_IF_NOT_NULL NULL,
                `f2` text REPLACE_IF_NOT_NULL NULL,
                `f3` text REPLACE_IF_NOT_NULL NULL
                )
AGGREGATE KEY(`id`)
DISTRIBUTED BY HASH(id) BUCKETS 3 
PROPERTIES ( 
    "replication_num" = "1"
);

INSERT into count_by_enum_test (id, f1, f2, f3) values
                                        (1, "F", "10", "北京"),
                                        (2, "F", "20", "北京"),
                                        (3, "M", NULL, "上海"),
                                        (4, "M", NULL, "上海"),
                                        (5, "M", NULL, "广州");

SELECT * from count_by_enum_test;

+------+------+------+--------+
| id   | f1   | f2   | f3     |
+------+------+------+--------+
| 2    | F    | 20   | 北京   |
| 3    | M    | NULL | 上海   |
| 4    | M    | NULL | 上海   |
| 5    | M    | NULL | 广州   |
| 1    | F    | 10   | 北京   |
+------+------+------+--------+

select count_by_enum(f1) from count_by_enum_test;

+------------------------------------------------------+
| count_by_enum(`f1`)                                  |
+------------------------------------------------------+
| [{"cbe":{"M":3,"F":2},"notnull":5,"null":0,"all":5}] |
+------------------------------------------------------+

select count_by_enum(f2) from count_by_enum_test;

+--------------------------------------------------------+
| count_by_enum(`f2`)                                    |
+--------------------------------------------------------+
| [{"cbe":{"10":1,"20":1},"notnull":2,"null":3,"all":5}] |
+--------------------------------------------------------+

select count_by_enum(f1,f2,f3) from count_by_enum_test;

+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| count_by_enum(`f1`, `f2`, `f3`)                                                                                                                                                   |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| [{"cbe":{"M":3,"F":2},"notnull":5,"null":0,"all":5},{"cbe":{"20":1,"10":1},"notnull":2,"null":3,"all":5},{"cbe":{"广州":1,"上海":2,"北京":2},"notnull":5,"null":0,"all":5}]       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

CORR COUNT