COUNT_BY_ENUM
更新时间：2025-10-16
描述
将列中数据看作枚举值，统计每个枚举值的个数。返回各个列枚举值的个数，以及非 null 值的个数与 null 值的个数。
语法
SQL
1COUNT_BY_ENUM(<expr1>, <expr2>, ... , <exprN>)参数
| 参数 | 说明 |
|---|---|
<expr1> |
至少填写一个输入。值为字符串（STRING）类型的列 |
返回值
返回一个 JSONArray 字符串。
例如：
JSON
1[{
2 "cbe": {
3 "F": 100,
4 "M": 99
5 },
6 "notnull": 199,
7 "null": 1,
8 "all": 200
9}, {
10 "cbe": {
11 "20": 10,
12 "30": 5,
13 "35": 1
14 },
15 "notnull": 16,
16 "null": 184,
17 "all": 200
18}, {
19 "cbe": {
20 "北京": 10,
21 "上海": 9,
22 "广州": 20,
23 "深圳": 30
24 },
25 "notnull": 69,
26 "null": 131,
27 "all": 200
28}]说明：返回值为一个 JSON array 字符串，内部对象的顺序是输入参数的顺序。
- cbe：根据枚举值统计非 null 值的统计结果
- notnull：非 null 的个数
- null：null 值个数
- all：总数，包括 null 值与非 null 值
举例
SQL
1CREATE TABLE count_by_enum_test(
2 `id` varchar(1024) NULL,
3 `f1` text REPLACE_IF_NOT_NULL NULL,
4 `f2` text REPLACE_IF_NOT_NULL NULL,
5 `f3` text REPLACE_IF_NOT_NULL NULL
6 )
7AGGREGATE KEY(`id`)
8DISTRIBUTED BY HASH(id) BUCKETS 3
9PROPERTIES (
10 "replication_num" = "1"
11);
SQL
1INSERT into count_by_enum_test (id, f1, f2, f3) values
2 (1, "F", "10", "北京"),
3 (2, "F", "20", "北京"),
4 (3, "M", NULL, "上海"),
5 (4, "M", NULL, "上海"),
6 (5, "M", NULL, "广州");
SQL
1SELECT * from count_by_enum_test;
Text
1+------+------+------+--------+
2| id | f1 | f2 | f3 |
3+------+------+------+--------+
4| 2 | F | 20 | 北京 |
5| 3 | M | NULL | 上海 |
6| 4 | M | NULL | 上海 |
7| 5 | M | NULL | 广州 |
8| 1 | F | 10 | 北京 |
9+------+------+------+--------+
SQL
1select count_by_enum(f1) from count_by_enum_test;
Text
1+------------------------------------------------------+
2| count_by_enum(`f1`) |
3+------------------------------------------------------+
4| [{"cbe":{"M":3,"F":2},"notnull":5,"null":0,"all":5}] |
5+------------------------------------------------------+
SQL
1select count_by_enum(f2) from count_by_enum_test;
Text
1+--------------------------------------------------------+
2| count_by_enum(`f2`) |
3+--------------------------------------------------------+
4| [{"cbe":{"10":1,"20":1},"notnull":2,"null":3,"all":5}] |
5+--------------------------------------------------------+
SQL
1select count_by_enum(f1,f2,f3) from count_by_enum_test;
Text
1+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2| count_by_enum(`f1`, `f2`, `f3`) |
3+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4| [{"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}] |
5+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+