MAP_AGG

description

Syntax

MAP_AGG(expr1, expr2)

返回一个 map, 由 expr1 作为键，expr2 作为对应的值。

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`, map_agg(`n_nationkey`, `n_name`) from `nation` group by `n_regionkey`;
33. +-------------+---------------------------------------------------------------------------+
34. | n_regionkey | map_agg(`n_nationkey`, `n_name`)                                          |
35. +-------------+---------------------------------------------------------------------------+
36. |           1 | {1:"ARGENTINA", 2:"BRAZIL", 3:"CANADA", 17:"PERU", 24:"UNITED STATES"}    |
37. |           0 | {0:"ALGERIA", 5:"ETHIOPIA", 14:"KENYA", 15:"MOROCCO", 16:"MOZAMBIQUE"}    |
38. |           3 | {6:"FRANCE", 7:"GERMANY", 19:"ROMANIA", 22:"RUSSIA", 23:"UNITED KINGDOM"} |
39. |           4 | {4:"EGYPT", 10:"IRAN", 11:"IRAQ", 13:"JORDAN", 20:"SAUDI ARABIA"}         |
40. |           2 | {8:"INDIA", 9:"INDONESIA", 12:"JAPAN", 18:"CHINA", 21:"VIETNAM"}          |
41. +-------------+---------------------------------------------------------------------------+

43. MySQL > select n_regionkey, map_agg(`n_name`, `n_nationkey` % 5) from `nation` group by `n_regionkey`;
44. +-------------+------------------------------------------------------------------------+
45. | n_regionkey | map_agg(`n_name`, (`n_nationkey` % 5))                                 |
46. +-------------+------------------------------------------------------------------------+
47. |           2 | {"INDIA":3, "INDONESIA":4, "JAPAN":2, "CHINA":3, "VIETNAM":1}          |
48. |           0 | {"ALGERIA":0, "ETHIOPIA":0, "KENYA":4, "MOROCCO":0, "MOZAMBIQUE":1}    |
49. |           3 | {"FRANCE":1, "GERMANY":2, "ROMANIA":4, "RUSSIA":2, "UNITED KINGDOM":3} |
50. |           1 | {"ARGENTINA":1, "BRAZIL":2, "CANADA":3, "PERU":2, "UNITED STATES":4}   |
51. |           4 | {"EGYPT":4, "IRAN":0, "IRAQ":1, "JORDAN":3, "SAUDI ARABIA":0}          |
52. +-------------+------------------------------------------------------------------------+

keywords

MAP_AGG