BITMAP_AGG

description

Syntax

BITMAP_AGG(expr)

This aggregating function returns a bitmap that aggregates the values of expr, excluding any null values. The type of expr needs to be TINYINT, SMALLINT, INT, or BIGINT.

example

  1. MySQL > select `n_nationkey`, `n_name`, `n_regionkey` from `nation`;
  2. +-------------+----------------+-------------+
  3. | n_nationkey | n_name         | n_regionkey |
  4. +-------------+----------------+-------------+
  5. |           0 | ALGERIA        |           0 |
  6. |           1 | ARGENTINA      |           1 |
  7. |           2 | BRAZIL         |           1 |
  8. |           3 | CANADA         |           1 |
  9. |           4 | EGYPT          |           4 |
  10. |           5 | ETHIOPIA       |           0 |
  11. |           6 | FRANCE         |           3 |
  12. |           7 | GERMANY        |           3 |
  13. |           8 | INDIA          |           2 |
  14. |           9 | INDONESIA      |           2 |
  15. |          10 | IRAN           |           4 |
  16. |          11 | IRAQ           |           4 |
  17. |          12 | JAPAN          |           2 |
  18. |          13 | JORDAN         |           4 |
  19. |          14 | KENYA          |           0 |
  20. |          15 | MOROCCO        |           0 |
  21. |          16 | MOZAMBIQUE     |           0 |
  22. |          17 | PERU           |           1 |
  23. |          18 | CHINA          |           2 |
  24. |          19 | ROMANIA        |           3 |
  25. |          20 | SAUDI ARABIA   |           4 |
  26. |          21 | VIETNAM        |           2 |
  27. |          22 | RUSSIA         |           3 |
  28. |          23 | UNITED KINGDOM |           3 |
  29. |          24 | UNITED STATES  |           1 |
  30. +-------------+----------------+-------------+
  32. MySQL > select n_regionkey, bitmap_to_string(bitmap_agg(n_nationkey)) from nation group by n_regionkey;
  33. +-------------+---------------------------------------------+
  34. | n_regionkey | bitmap_to_string(bitmap_agg(`n_nationkey`)) |
  35. +-------------+---------------------------------------------+
  36. |           4 | 4,10,11,13,20                               |
  37. |           2 | 8,9,12,18,21                                |
  38. |           1 | 1,2,3,17,24                                 |
  39. |           0 | 0,5,14,15,16                                |
  40. |           3 | 6,7,19,22,23                                |
  41. +-------------+---------------------------------------------+
  43. MySQL > select bitmap_count(bitmap_agg(n_nationkey))  from nation;
  44. +-----------------------------------------+
  45. | bitmap_count(bitmap_agg(`n_nationkey`)) |
  46. +-----------------------------------------+
  47. |                                      25 |
  48. +-----------------------------------------+

keywords

BITMAP_AGG