HISTOGRAM - MaxCompute - Alibaba Cloud Documentation Center

Returns a map that contains the number of times each input value appears. The keys in the map are the input values. Each value in the map is the number of times an input value appears. The null value is ignored.

Usage notes

MaxCompute V2.0 provides extension functions. If the functions that you use involve new data types, you must run one of the following SET commands to enable the MaxCompute V2.0 data type edition. The new data types include TINYINT, SMALLINT, INT, FLOAT, VARCHAR, TIMESTAMP, and BINARY.

Session level: To use the MaxCompute V2.0 data type edition, you must add set odps.sql.type.system.odps2=true; before the SQL statement that you want to execute, and commit and execute them together.
Project level: The project owner can enable the MaxCompute V2.0 data type edition for the project based on the project requirements. The configuration takes effect after 10 to 15 minutes. Run the following command:
```
setproject odps.sql.type.system.odps2=true;
```
For more information about setproject, see Project operations. For more information about the precautions that you must take when you enable the MaxCompute V2.0 data type edition at the project level, see Data type editions.

If you use an SQL statement that includes multiple aggregate functions and the project resources are insufficient, memory overflow may occur. We recommend that you optimize the SQL statement or purchase computing resources based on your business requirements.

Syntax

map<K, bigint> histogram(K input);

Parameters

input: input values, which are used as the keys in the map.

Return value

A map that contains the number of times each input value appears is returned.

Examples

select histogram(a) from values
    ('hi'), (null), ('apple'), ('pie'), ('apple') t(a);

The following result is returned:

+----------------------------+
| _c0                        |
+----------------------------+
| {"pie":1,"hi":1,"apple":2} |
+----------------------------+

Related functions

HISTOGRAM is an aggregate function. For more information about the functions that are used to calculate the average value of multiple input records and to aggregate parameters, see Aggregate functions.