COUNT_BY_ENUM

COUNT_BY_ENUM

description

Syntax

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

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

Arguments

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

Returned value

返回一个 JSONArray 字符串。

例如:

  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 值

example

  1. DROP TABLE IF EXISTS count_by_enum_test;
  2. CREATE TABLE count_by_enum_test(
  3. `id` varchar(1024) NULL,
  4. `f1` text REPLACE_IF_NOT_NULL NULL,
  5. `f2` text REPLACE_IF_NOT_NULL NULL,
  6. `f3` text REPLACE_IF_NOT_NULL NULL
  7. )
  8. AGGREGATE KEY(`id`)
  9. DISTRIBUTED BY HASH(id) BUCKETS 3
  10. PROPERTIES (
  11. "replication_num" = "1"
  12. );
  13. INSERT into count_by_enum_test (id, f1, f2, f3) values
  14. (1, "F", "10", "北京"),
  15. (2, "F", "20", "北京"),
  16. (3, "M", NULL, "上海"),
  17. (4, "M", NULL, "上海"),
  18. (5, "M", NULL, "广州");
  19. SELECT * from count_by_enum_test;
  20. +------+------+------+--------+
  21. | id | f1 | f2 | f3 |
  22. +------+------+------+--------+
  23. | 2 | F | 20 | 北京 |
  24. | 3 | M | NULL | 上海 |
  25. | 4 | M | NULL | 上海 |
  26. | 5 | M | NULL | 广州 |
  27. | 1 | F | 10 | 北京 |
  28. +------+------+------+--------+
  29. select count_by_enum(f1) from count_by_enum_test;
  30. +------------------------------------------------------+
  31. | count_by_enum(`f1`) |
  32. +------------------------------------------------------+
  33. | [{"cbe":{"M":3,"F":2},"notnull":5,"null":0,"all":5}] |
  34. +------------------------------------------------------+
  35. select count_by_enum(f2) from count_by_enum_test;
  36. +--------------------------------------------------------+
  37. | count_by_enum(`f2`) |
  38. +--------------------------------------------------------+
  39. | [{"cbe":{"10":1,"20":1},"notnull":2,"null":3,"all":5}] |
  40. +--------------------------------------------------------+
  41. select count_by_enum(f1,f2,f3) from count_by_enum_test;
  42. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  43. | count_by_enum(`f1`, `f2`, `f3`) |
  44. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  45. | [{"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}] |
  46. +-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

keywords

COUNT_BY_ENUM