COUNT_BY_ENUM

COUNT_BY_ENUM

COUNT_BY_ENUM

description

Syntax

count_by_enum(expr1, expr2, ... , exprN);

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

Arguments

expr1 — 至少填写一个输入。值为字符串（STRING）类型的列。

Returned value

返回一个 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 值

example

DROP TABLE IF EXISTS count_by_enum_test;

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}]       |
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

keywords

COUNT_BY_ENUM