Hologres:General-purpose aggregate functions

Supported functions

The following table lists all supported functions, grouped by category.

General aggregation

Boolean aggregation

Bitwise aggregation

Cardinality estimation (using HyperLogLog)

Percentile estimation

Statistics

Linear regression

Sample data

The following examples use example_table. Run this setup script to create the table and insert sample data.

-- Create a table.
CREATE TABLE example_table(
    c1 INT,
    c2 BOOLEAN,
    c3 VARCHAR(10),
    c4 DECIMAL(3, 1),
    c5 TEXT,
    c6 FLOAT8,
    c7 FLOAT8
);

-- Insert data.
INSERT INTO example_table (c1, c2, c3, c4, c5, c6, C7) VALUES
(1, true, 'a', 1.1, 3, 6, 7),
(2, false, 'b', 2.2, 4, 6, 2.6);

APPROX_COUNT_DISTINCT

Uses HyperLogLog cardinality estimation to return an approximate distinct count. The average error rate is between 0.1% and 1%, which is much lower overhead than an exact COUNT DISTINCT ( column ).

APPROX_COUNT_DISTINCT ( <column> )

Parameters

Usage notes

The precision of the HyperLogLog algorithm is controlled by a bucketing parameter:

SET hg_experimental_approx_count_distinct_precision = 20;

Set this parameter at the session level with SET or at the database level with ALTER DATABASE:

-- Session level
SET hg_experimental_approx_count_distinct_precision = 20;

-- Database level
ALTER DATABASE dbname SET hg_experimental_approx_count_distinct_precision = 20;

To perform an exact distinct count, use COUNT DISTINCT ( column ). This consumes more resources.

Example

The following examples use the ORDERS table from the TPC-H public dataset provided by MaxCompute. Run the setup script below to create the required tables before running the queries.

-- Create a foreign table.
IMPORT FOREIGN SCHEMA public_data
LIMIT TO (ORDERS_10g)
FROM SERVER odps_server INTO public options (if_table_exist 'update');

-- Create an internal table.
BEGIN;
CREATE TABLE ORDERS (
    O_ORDERKEY bigint NOT NULL PRIMARY KEY,
    O_CUSTKEY int NOT NULL,
    O_ORDERSTATUS text NOT NULL,
    O_TOTALPRICE DECIMAL(12, 2) NOT NULL,
    O_ORDERDATE timestamptz NOT NULL,
    O_ORDERPRIORITY text NOT NULL,
    O_CLERK text NOT NULL,
    O_SHIPPRIORITY int NOT NULL,
    O_COMMENT text NOT NULL
);

CALL set_table_property ('ORDERS', 'segment_key', 'O_ORDERDATE');
CALL set_table_property ('ORDERS', 'distribution_key', 'O_ORDERKEY');
CALL set_table_property ('ORDERS', 'bitmap_columns', 'O_ORDERKEY,O_CUSTKEY,O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_SHIPPRIORITY,O_COMMENT');
CALL set_table_property ('ORDERS', 'dictionary_encoding_columns', 'O_ORDERSTATUS,O_ORDERPRIORITY,O_CLERK,O_COMMENT');

COMMENT ON COLUMN ORDERS.O_ORDERKEY IS 'Order ID';
COMMENT ON COLUMN ORDERS.O_CUSTKEY IS 'Customer ID';
COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS 'Order status';
COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS 'Total price';
COMMENT ON COLUMN ORDERS.O_ORDERDATE IS 'Order date';
COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS 'Order priority';
COMMENT ON COLUMN ORDERS.O_CLERK IS 'Cashier';
COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS 'Shipping priority';
COMMENT ON COLUMN ORDERS.O_COMMENT IS 'Comment';

COMMIT;

-- Import data to the internal table.
INSERT INTO ORDERS SELECT * FROM ORDERS_10g;

Calculate the approximate distinct count in the O_CUSTKEY column:

SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

Result:

approx_count_distinct
-----------------------
               1000422

Calculate with global precision set to 20:

ALTER DATABASE dbname SET hg_experimental_approx_count_distinct_precision = 20;
SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

Result:

approx_count_distinct
-----------------------
               1000422

Calculate with session-level precision set to 20:

SET hg_experimental_approx_count_distinct_precision = 20;
SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

Result:

 approx_count_distinct
-----------------------
                998854

APPROX_PERCENTILE

Supported in Hologres V3.1 and later.

Uses reservoir sampling (up to 8,192 elements) to return an approximate percentile of a numeric column.

APPROX_PERCENTILE (fraction) WITHIN GROUP (ORDER BY sort_expression)

Parameters

Example

Calculate the Q1, median, and Q3 of a salary column:

SELECT
  APPROX_PERCENTILE(0.25) WITHIN GROUP (ORDER BY salary) AS q1,
  APPROX_PERCENTILE(0.5)  WITHIN GROUP (ORDER BY salary) AS median,
  APPROX_PERCENTILE(0.75) WITHIN GROUP (ORDER BY salary) AS q3
FROM employees;

ARRAY_AGG

Aggregates values of any expression into an array.

ARRAY_AGG(expression)

Parameters

Return value

ARRAY type.

Example

SELECT ARRAY_AGG(c1) FROM example_table;

Result:

 array_agg
-----------
 {2,1}

AVG

Returns the average of non-NULL values.

AVG(expression)

Parameters

Return value

Same data type as the input.

Example

SELECT AVG(c7) FROM example_table;

Result:

   avg
----------
 1.500000

BIT_AND

Performs a bitwise AND on all non-NULL values.

BIT_AND(expression)

Parameters

Return value

Same data type as the input.

Example

SELECT BIT_AND(c1) FROM example_table;

Result:

 bit_and
---------
       0

BIT_OR

Performs a bitwise OR on all non-NULL values.

BIT_OR(expression)

Parameters

Return value

Same data type as the input.

Example

SELECT BIT_OR(c1) FROM example_table;

Result:

 bit_or
--------
      3

BOOL_AND

Returns TRUE if all values of a Boolean expression are true; returns FALSE otherwise.

BOOL_AND(bool)

Parameters

Return value

BOOLEAN. Returns t if all values are true, f otherwise.

Example

SELECT BOOL_AND(c2) FROM example_table;

Result:

 bool_and
----------
 f

BOOL_OR

Returns TRUE if any value of a Boolean expression is true; returns FALSE otherwise.

BOOL_OR(bool)

Parameters

Return value

BOOLEAN. Returns t if any value is true, f otherwise.

Example

SELECT BOOL_OR(c2) FROM example_table;

Result:

 bool_or
---------
 t

CORR

Calculates the correlation coefficient between two variables. Both parameters must be DOUBLE PRECISION, FLOAT, or FLOAT8.

CORR(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT CORR(c6, c7) FROM example_table;

COUNT

Returns the number of rows or the number of non-NULL values in an expression.

• COUNT(*) counts all rows, including rows with NULL values.

• COUNT(expression) counts only non-NULL values.

-- Count all rows, including rows with NULL values.
COUNT(*)

-- Count only non-NULL input values.
COUNT(expression)

Parameters

Return value

BIGINT.

Example

SELECT COUNT(*) FROM example_table;

Result:

 count
-------
     2

COVAR_POP

Calculates the population covariance of two variables.

COVAR_POP(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT COVAR_POP(c6, c7) FROM example_table;

Result:

      covar_pop
---------------------
 3.5527136788005e-15

COVAR_SAMP

Calculates the sample covariance of two variables.

COVAR_SAMP(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT COVAR_SAMP(c6, c7) FROM example_table;

Result:

     covar_samp
--------------------
 7.105427357601e-15

EVERY

Returns TRUE if all values of a Boolean expression are true; returns FALSE otherwise.

EVERY(bool)

Parameters

Return value

BOOLEAN. Returns t if all values are true, f otherwise.

Example

SELECT EVERY(c2) FROM example_table;

Result:

 every
-------
 f

MAX

Returns the maximum value.

MAX(expression)

Parameters

Return value

Same data type as the input.

Example

SELECT MAX(c1) FROM example_table;

Result:

 max
-----
   2

MAX_BY

Supported in Hologres V1.3.36 and later. For earlier versions, see How to obtain more online support?

Finds the row with the maximum value in column y and returns the corresponding value from column x. For example, if a table contains name and cost columns, MAX_BY(name, cost) returns the name of the item with the highest cost.

Numeric types are compared by numeric value. Non-numeric types are compared by dictionary sort order.

MAX_BY(x, y)

Parameters

Return value

• Returns the value of x from the row where y is at its maximum.

• Returns NULL if all values in y are NULL.

• If multiple rows share the maximum y value, returns the maximum of the corresponding x values.

Example

-- Sample data
DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (
    id INT,
    name TEXT,
    cost INT
);
INSERT INTO test
    VALUES (1, 'a', 100), (1, 'aa', 200), (1, 'aaa', 300), (2, 'b', 150), (2, 'bb', 300), (3, 'c', 150), (3, 'cc', 50);

Return the name corresponding to the highest cost. Because multiple rows share the maximum cost (300), the function returns the maximum name value among those rows:

SELECT max_by(name, cost) FROM test;

Result:

 max_by
--------
 aaa

MIN

Returns the minimum value.

MIN(expression)

Parameters

Return value

Same data type as the input.

Example

SELECT MIN(c1) FROM example_table;

Result:

 min
-----
   1

MIN_BY

Supported in Hologres V1.3.36 and later. For earlier versions, see How to obtain more online support?

Finds the row with the minimum value in column y and returns the corresponding value from column x. For example, if a table contains name and cost columns, MIN_BY(name, cost) returns the name of the item with the lowest cost.

Numeric types are compared by numeric value. Non-numeric types are compared by dictionary sort order.

MIN_BY(x, y)

Parameters

Return value

• Returns the value of x from the row where y is at its minimum.

• Returns NULL if all values in y are NULL.

• If multiple rows share the minimum y value, returns the minimum of the corresponding x values.

Example

-- Sample data
DROP TABLE IF EXISTS test;
CREATE TABLE IF NOT EXISTS test (
    id INT,
    name TEXT,
    cost INT
);
INSERT INTO test
    VALUES (1, 'a', 100), (1, 'aa', 200), (1, 'aaa', 300), (2, 'b', 150), (2, 'bb', 300), (3, 'c', 150), (3, 'cc', 50);

Return the name corresponding to the lowest cost:

SELECT MIN_BY(name, cost) FROM test;

Result:

min_by
--------
 cc
(1 row)

REGR_AVGX

Calculates the average of the independent variable (X).

REGR_AVGX(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT REGR_AVGX(c6, c7) FROM example_table;

Result:

 regr_avgx
-----------
       4.8

REGR_AVGY

Calculates the average of the dependent variable (Y).

REGR_AVGY(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT REGR_AVGY(c6, c7) FROM example_table;

Result:

 regr_avgy
-----------
         6

REGR_COUNT

Returns the number of rows where neither input is NULL.

REGR_COUNT(Y, X)

Parameters

Return value

BIGINT.

Example

SELECT REGR_COUNT(c6, c7) FROM example_table;

Result:

 regr_count
------------
          2

REGR_R2

Calculates the square of the correlation coefficient (R²).

REGR_R2(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT REGR_R2(c6, c7) FROM example_table;

Result:

 regr_r2
---------
       1

REGR_INTERCEPT

Calculates the y-intercept of the least-squares-fit linear equation determined by the (Y, X) pairs.

REGR_INTERCEPT(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT REGR_INTERCEPT(c6, c7) FROM example_table;

Result:

 regr_intercept
----------------
              6

REGR_SLOPE

Calculates the slope of the least-squares-fit linear equation.

REGR_SLOPE(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT REGR_SLOPE(c6, c7) FROM example_table;

Result:

      regr_slope
----------------------
 7.34031751818285e-16

REGR_SXX

Calculates the sum of squares of the independent variable (X), as sum(X^2) - sum(X)^2/N.

REGR_SXX(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT REGR_SXX(c6, c7) FROM example_table;

Result:

 regr_sxx
----------
     9.68

REGR_SXY

Calculates the sum of products of the independent variable (X) and dependent variable (Y), as sum(X*Y) - sum(X) * sum(Y)/N.

REGR_SXY(Y, X)

Parameters

Return value

Same data type as the input.

Example

SELECT REGR_SXY(c6, c7) FROM example_table;

Result:

      regr_sxy
--------------------
 7.105427357601e-15

SUM

Returns the sum of all values.

SUM(expression)

Parameters

Return value

Same data type as the input.

Example

SELECT SUM(c1) FROM example_table;

Result:

 sum
-----
   3

STDDEV

Returns the sample standard deviation.

STDDEV(expression)

Parameters

Return value

NUMERIC or FLOAT8.

Example

SELECT STDDEV(c1) FROM example_table;

Result:

    stddev
--------------
 0.7071067811

STDDEV_POP

Returns the population standard deviation.

STDDEV_POP(expression)

Parameters

Return value

NUMERIC or FLOAT8.

Example

SELECT STDDEV_POP(c1) FROM example_table;

Result:

  stddev_pop
--------------
 0.5000000000

STDDEV_SAMP

Returns the sample standard deviation. Equivalent to STDDEV.

STDDEV_SAMP(expression)

Parameters

Return value

NUMERIC or FLOAT8.

Example

SELECT STDDEV_SAMP(c1) FROM example_table;

Result:

 stddev_samp
--------------
 0.7071067812

STRING_AGG

Concatenates non-NULL values into a single string, separated by a specified delimiter.

STRING_AGG(<expression> TEXT, <delimiter> TEXT)

Parameters

Return value

TEXT.

Example

SELECT STRING_AGG(c5, '-') FROM example_table;

Result:

 string_agg
------------
 3-4

UNIQ

Supported in Hologres V1.3 and later. For earlier versions, see Common errors when preparing for an upgrade or How to obtain more online support?.

Returns the distinct count for a column. UNIQ outperforms COUNT DISTINCT specifically when the query includes a GROUP BY clause and the GROUP BY field values are evenly distributed without data skew.

UNIQ ( <column> )

Parameters

Usage notes

• UNIQ uses less memory than COUNT DISTINCT and is a useful alternative when COUNT DISTINCT causes out-of-memory errors.

• UNIQ performs better than COUNT DISTINCT when the GROUP BY key has high cardinality.

• UNIQ only outperforms COUNT DISTINCT when the query has a GROUP BY clause and values are evenly distributed. Without a GROUP BY clause, or with data skew, there is no performance advantage.

• Since Hologres V2.1, COUNT DISTINCT has been significantly optimized across scenarios including single and multiple COUNT DISTINCT functions, data skew, and queries without a GROUP BY clause. Manually rewriting queries to use UNIQ is no longer necessary in most cases. For details, see Count Distinct optimization.

Example

The following examples use the ORDERS table from the TPC-H public dataset. See APPROX_COUNT_DISTINCT for the table setup script.

Calculate the distinct count of O_CUSTKEY in the ORDERS table:

SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;

Result:

  uniq
--------
 999982

Calculate the distinct O_CUSTKEY count for each O_ORDERSTATUS:

SELECT O_ORDERSTATUS, UNIQ ( O_CUSTKEY ) FROM ORDERS GROUP BY O_ORDERSTATUS;

Result:

 o_orderstatus |  uniq
---------------+--------
 P             | 313478
 F             | 996258
 O             | 996400

VARIANCE

Returns the sample variance.

VARIANCE(expression)

Parameters

Return value

NUMERIC.

Example

SELECT VARIANCE(c1) FROM example_table;

Result:

   variance
--------------
 0.5000000000

VAR_POP

Returns the population variance.

VAR_POP(expression)

Parameters

Return value

NUMERIC or FLOAT8.

Example

SELECT VAR_POP(c1) FROM example_table;

Result:

   var_pop
--------------
 0.2500000000

VAR_SAMP

Returns the sample variance.

VAR_SAMP(expression)

Parameters

Return value

NUMERIC or FLOAT8.

Example

SELECT VAR_SAMP(c1) FROM example_table;

Result:

   var_samp
--------------
 0.5000000000