Aggregate Functions
hll_add_agg(hll_hashval)
Description: Groups hashed data into HLL.
Return type: HLL
Example:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- Prepare data: gaussdb=# CREATE TABLE t_id(id int); gaussdb=# INSERT INTO t_id VALUES(generate_series(1,500)); gaussdb=# CREATE TABLE t_data(a int, c text); gaussdb=# INSERT INTO t_data SELECT mod(id,2), id FROM t_id; -- Create a table and specify an HLL column: gaussdb=# CREATE TABLE t_a_c_hll(a int, c hll); -- Use GROUP BY on column a to group data, and insert the data to the HLL: gaussdb=# INSERT INTO t_a_c_hll SELECT a, hll_add_agg(hll_hash_text(c)) FROM t_data GROUP BY a; -- Calculate the number of distinct values for each group in the HLL: gaussdb=# SELECT a, #c AS cardinality FROM t_a_c_hll ORDER BY a; a | cardinality ---+------------------ 0 | 247.862354346299 1 | 250.908710610377 (2 rows) |
hll_add_agg(hll_hashval, int32 log2m)
Description: Groups hashed data into HLL and specifies the log2m parameter. The value ranges from 10 to 16. If the input is –1 or NULL, the built-in default value is used.
Return type: HLL
Example:
1 2 3 4 5 |
gaussdb=# SELECT hll_cardinality(hll_add_agg(hll_hash_text(c), 12)) FROM t_data; hll_cardinality ------------------ 497.965240179228 (1 row) |
hll_add_agg(hll_hashval, int32 log2m, int32 log2explicit)
Description: Groups hashed data into HLL and specifies the log2m and log2explicit parameters in sequence. The value of log2explicit ranges from 0 to 12. The value 0 indicates that the explicit mode is skipped. This parameter is used to set the threshold of the explicit mode. When the length of the data segment reaches 2log2explicit, the mode is switched to the sparse or full mode. If the input is –1 or NULL, the built-in default value of log2explicit is used.
Return type: HLL
Example:
1 2 3 4 5 |
gaussdb=# SELECT hll_cardinality(hll_add_agg(hll_hash_text(c), NULL, 1)) FROM t_data; hll_cardinality ------------------ 498.496062953313 (1 row) |
hll_add_agg(hll_hashval, int32 log2m, int32 log2explicit, int64 log2sparse)
Description: Groups hashed data into HLL and sets the log2m, log2explicit, and log2sparse parameters in sequence. The value of log2sparse ranges from 0 to 14. The value 0 indicates that the sparse mode is skipped. This parameter is used to set the threshold of the sparse mode. When the length of the data segment reaches 2log2sparse, the mode is switched to the full mode. If the input is –1 or NULL, the built-in default value of log2sparse is used.
Return type: HLL
Example:
1 2 3 4 5 |
gaussdb=# SELECT hll_cardinality(hll_add_agg(hll_hash_text(c), NULL, 6, 10)) FROM t_data; hll_cardinality ------------------ 498.496062953313 (1 row) |
hll_add_agg(hll_hashval, int32 log2m, int32 log2explicit, int64 log2sparse, int32 duplicatecheck)
Description: Groups hashed data into HLL and specifies the log2m, log2explicit, log2sparse, and duplicatecheck parameters. The value of duplicatecheck can be 0 or 1, indicating whether to enable this mode. By default, this mode is disabled. If the input is –1 or NULL, the built-in default value of duplicatecheck is used.
Return type: HLL
Example:
1 2 3 4 5 |
gaussdb=# SELECT hll_cardinality(hll_add_agg(hll_hash_text(c), NULL, 6, 10, -1)) FROM t_data; hll_cardinality ------------------ 498.496062953313 (1 row) |
hll_union_agg(hll)
Description: Performs the UNION operation on multiple pieces of data of the HLL type to obtain one HLL.
Return type: HLL
Example:
1 2 3 4 5 6 7 8 9 10 11 |
-- Perform the UNION operation on data of the HLL type in each group to obtain one HLL, and calculate the number of distinct values: gaussdb=# SELECT #hll_union_agg(c) AS cardinality FROM t_a_c_hll; cardinality ------------------ 498.496062953313 (1 row) -- Drop tables. gaussdb=# DROP TABLE t_id; gaussdb=# DROP TABLE t_data; gaussdb=# DROP TABLE t_a_c_hll; |

To perform the UNION operation on data in multiple HLLs, ensure that the HLLs have the same precision. Otherwise, UNION cannot be performed. This constraint also applies to the hll_union(hll, hll) function.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot