COUNT_BY_ENUM

描述

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

语法

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

参数

参数	说明
<expr1>	至少填写一个输入，至多支持 1024 个输入，支持类型为 String。

返回值

返回 JSONArray 格式的结果。 返回类型为 String。

例如：

[{
	"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}]       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+