COUNT_BY_ENUM

SinceVersion 1.2.0

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:

  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.     "China": 10,
  21.     "United States": 9,
  22.     "England": 20,
  23.     "Germany": 30
  24.   },
  25.   "notnull": 69,
  26.   "null": 131,
  27.   "all": 200
  28. }]

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

  1. DROP TABLE IF EXISTS count_by_enum_test;
  3. CREATE TABLE count_by_enum_test(
  4.                                    `id` varchar(1024) NULL,
  5.                                    `f1` text REPLACE_IF_NOT_NULL NULL,
  6.                                    `f2` text REPLACE_IF_NOT_NULL NULL,
  7.                                    `f3` text REPLACE_IF_NOT_NULL NULL
  8. )
  9. AGGREGATE KEY(`id`)
  10. DISTRIBUTED BY HASH(id) BUCKETS 3 
  11. PROPERTIES ( 
  12.     "replication_num" = "1"
  13. );
  15. INSERT into count_by_enum_test (id, f1, f2, f3) values
  16.                                                     (1, "F", "10", "China"),
  17.                                                     (2, "F", "20", "China"),
  18.                                                     (3, "M", NULL, "United States"),
  19.                                                     (4, "M", NULL, "United States"),
  20.                                                     (5, "M", NULL, "England");
  22. SELECT * from count_by_enum_test;
  24. +------+------+------+---------------+
  25. | id   | f1   | f2   | f3            |
  26. +------+------+------+---------------+
  27. | 1    | F    | 10   | China         |
  28. | 2    | F    | 20   | China         |
  29. | 3    | M    | NULL | United States |
  30. | 4    | M    | NULL | United States |
  31. | 5    | M    | NULL | England       |
  32. +------+------+------+---------------+
  34. select count_by_enum(f1) from count_by_enum_test;
  36. +------------------------------------------------------+
  37. | count_by_enum(`f1`)                                  |
  38. +------------------------------------------------------+
  39. | [{"cbe":{"M":3,"F":2},"notnull":5,"null":0,"all":5}] |
  40. +------------------------------------------------------+
  42. select count_by_enum(f2) from count_by_enum_test;
  44. +--------------------------------------------------------+
  45. | count_by_enum(`f2`)                                    |
  46. +--------------------------------------------------------+
  47. | [{"cbe":{"10":1,"20":1},"notnull":2,"null":3,"all":5}] |
  48. +--------------------------------------------------------+
  50. select count_by_enum(f1,f2,f3) from count_by_enum_test;
  52. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  53. | count_by_enum(`f1`, `f2`, `f3`)                                                                                                                                                          |
  54. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  55. | [{"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}] |
  56. +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

keywords

COUNT_BY_ENUM