Skip to main content
Skip to main content

COUNT_BY_ENUM

COUNT_BY_ENUM​

COUNT_BY_ENUM

Description​

Syntax​

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

Treats the data in a column as an enumeration and counts the number of values in each enumeration. Returns the number of enumerated values for each column, and the number of non-null values versus the number of null values.

Arguments​

expr1 β€” At least one input must be specified. The value is a column of type STRING.

Returned value​

Returns a JSONArray string.

For example:

[{
"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": {
"China": 10,
"United States": 9,
"England": 20,
"Germany": 30
},
"notnull": 69,
"null": 131,
"all": 200
}]

Description: The return value is a JSON array string and the order of the internal objects is the order of the input parameters.

  • cbe: count of non-null values based on enumeration values
  • notnull: number of non-null values.
  • null: number of null values
  • all: total number, including both null and non-null values.

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", "China"),
(2, "F", "20", "China"),
(3, "M", NULL, "United States"),
(4, "M", NULL, "United States"),
(5, "M", NULL, "England");

SELECT * from count_by_enum_test;

+------+------+------+---------------+
| id | f1 | f2 | f3 |
+------+------+------+---------------+
| 1 | F | 10 | China |
| 2 | F | 20 | China |
| 3 | M | NULL | United States |
| 4 | M | NULL | United States |
| 5 | M | NULL | England |
+------+------+------+---------------+

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":{"England":1,"United States":2,"China":2},"notnull":5,"null":0,"all":5}] |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

keywords​

COUNT_BY_ENUM