Hologres:Funções de agregação de uso geral

Funções compatíveis

A tabela a seguir lista todas as funções compatíveis, agrupadas por categoria.

Agregação geral

Agregação booleana

Agregação bit a bit

Estimativa de cardinalidade (usando HyperLogLog)

Estimativa de percentil

Estatísticas

Regressão linear

Dados de exemplo

Os exemplos a seguir utilizam example_table. Execute o script de configuração abaixo para criar a tabela e inserir dados de exemplo.

-- 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

Usa a estimativa de cardinalidade HyperLogLog para retornar uma contagem distinta aproximada. A taxa de erro média é de 0,1% a 1%, com custo operacional muito menor do que um COUNT DISTINCT ( column ) exato.

APPROX_COUNT_DISTINCT ( <column> )

Parâmetros

Notas de uso

A precisão do algoritmo HyperLogLog é controlada por um parâmetro de bucket:

SET hg_experimental_approx_count_distinct_precision = 20;

Defina este parâmetro no nível de sessão com SET ou no nível de banco de dados com 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;

Para calcular uma contagem distinta exata, use COUNT DISTINCT ( column ). Isso consome mais recursos.

Exemplo

Os exemplos a seguir utilizam a tabela ORDERS do conjunto de dados público TPC-H fornecido pelo MaxCompute. Execute o script de configuração abaixo para criar as tabelas necessárias antes de executar as consultas.

-- 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;

Calcule a contagem distinta aproximada na coluna O_CUSTKEY:

SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

Resultado:

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

Calcule com a precisão global definida como 20:

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

Resultado:

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

Calcule com a precisão em nível de sessão definida como 20:

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

Resultado:

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

APPROX_PERCENTILE

Compatível com o Hologres V3.1 e versões posteriores.

Usa amostragem por reservatório (até 8.192 elementos) para retornar um percentil aproximado de uma coluna numérica.

APPROX_PERCENTILE (fraction) WITHIN GROUP (ORDER BY sort_expression)

Parâmetros

Exemplo

Calcule o Q1, a mediana e o Q3 de uma coluna de salários:

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

Agrega valores de qualquer expressão em um array.

ARRAY_AGG(expression)

Parâmetros

Valor de retorno

Tipo ARRAY.

Exemplo

SELECT ARRAY_AGG(c1) FROM example_table;

Resultado:

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

AVG

Retorna a média dos valores não nulos.

AVG(expression)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT AVG(c7) FROM example_table;

Resultado:

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

BIT_AND

Executa um AND bit a bit em todos os valores não nulos.

BIT_AND(expression)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT BIT_AND(c1) FROM example_table;

Resultado:

 bit_and
---------
       0

BIT_OR

Executa um OR bit a bit em todos os valores não nulos.

BIT_OR(expression)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT BIT_OR(c1) FROM example_table;

Resultado:

 bit_or
--------
      3

BOOL_AND

Retorna TRUE se todos os valores de uma expressão booleana forem verdadeiros; retorna FALSE caso contrário.

BOOL_AND(bool)

Parâmetros

Valor de retorno

BOOLEAN. Retorna t se todos os valores forem verdadeiros, f caso contrário.

Exemplo

SELECT BOOL_AND(c2) FROM example_table;

Resultado:

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

BOOL_OR

Retorna TRUE se algum valor de uma expressão booleana for verdadeiro; retorna FALSE caso contrário.

BOOL_OR(bool)

Parâmetros

Valor de retorno

BOOLEAN. Retorna t se algum valor for verdadeiro, f caso contrário.

Exemplo

SELECT BOOL_OR(c2) FROM example_table;

Resultado:

 bool_or
---------
 t

CORR

Calcula o coeficiente de correlação entre duas variáveis. Ambos os parâmetros devem ser DOUBLE PRECISION, FLOAT ou FLOAT8.

CORR(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT CORR(c6, c7) FROM example_table;

COUNT

Retorna o número de linhas ou o número de valores não nulos em uma expressão.

• COUNT(*) conta todas as linhas, incluindo linhas com valores NULL.

• COUNT(expression) conta apenas valores não nulos.

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

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

Parâmetros

Valor de retorno

BIGINT.

Exemplo

SELECT COUNT(*) FROM example_table;

Resultado:

 count
-------
     2

COVAR_POP

Calcula a covariância populacional de duas variáveis.

COVAR_POP(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT COVAR_POP(c6, c7) FROM example_table;

Resultado:

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

COVAR_SAMP

Calcula a covariância amostral de duas variáveis.

COVAR_SAMP(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT COVAR_SAMP(c6, c7) FROM example_table;

Resultado:

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

EVERY

Retorna TRUE se todos os valores de uma expressão booleana forem verdadeiros; retorna FALSE caso contrário.

EVERY(bool)

Parâmetros

Valor de retorno

BOOLEAN. Retorna t se todos os valores forem verdadeiros, f caso contrário.

Exemplo

SELECT EVERY(c2) FROM example_table;

Resultado:

 every
-------
 f

MAX

Retorna o valor máximo.

MAX(expression)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT MAX(c1) FROM example_table;

Resultado:

 max
-----
   2

MAX_BY

Compatível com o Hologres V1.3.36 e versões posteriores. Para versões anteriores, consulte Como obter mais suporte online?

Encontra a linha com o valor máximo na coluna y e retorna o valor correspondente da coluna x. Por exemplo, se uma tabela contiver as colunas name e cost, MAX_BY(name, cost) retorna o nome do item com o maior custo.

Tipos numéricos são comparados pelo valor numérico. Tipos não numéricos são comparados pela ordem de classificação do dicionário.

MAX_BY(x, y)

Parâmetros

Valor de retorno

• Retorna o valor de x da linha em que y está em seu valor máximo.

• Retorna NULL se todos os valores em y forem NULL.

• Se várias linhas compartilharem o valor máximo de y, retorna o máximo dos valores x correspondentes.

Exemplo

-- 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);

Retorna o nome correspondente ao maior custo. Como várias linhas compartilham o custo máximo (300), a função retorna o maior valor de name entre essas linhas:

SELECT max_by(name, cost) FROM test;

Resultado:

 max_by
--------
 aaa

MIN

Retorna o valor mínimo.

MIN(expression)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT MIN(c1) FROM example_table;

Resultado:

 min
-----
   1

MIN_BY

Compatível com o Hologres V1.3.36 e versões posteriores. Para versões anteriores, consulte Como obter mais suporte online?

Encontra a linha com o valor mínimo na coluna y e retorna o valor correspondente da coluna x. Por exemplo, se uma tabela contiver as colunas name e cost, MIN_BY(name, cost) retorna o nome do item com o menor custo.

Tipos numéricos são comparados pelo valor numérico. Tipos não numéricos são comparados pela ordem de classificação do dicionário.

MIN_BY(x, y)

Parâmetros

Valor de retorno

• Retorna o valor de x da linha em que y está em seu valor mínimo.

• Retorna NULL se todos os valores em y forem NULL.

• Se várias linhas compartilharem o valor mínimo de y, retorna o mínimo dos valores x correspondentes.

Exemplo

-- 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);

Retorna o nome correspondente ao menor custo:

SELECT MIN_BY(name, cost) FROM test;

Resultado:

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

REGR_AVGX

Calcula a média da variável independente (X).

REGR_AVGX(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT REGR_AVGX(c6, c7) FROM example_table;

Resultado:

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

REGR_AVGY

Calcula a média da variável dependente (Y).

REGR_AVGY(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT REGR_AVGY(c6, c7) FROM example_table;

Resultado:

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

REGR_COUNT

Retorna o número de linhas em que nenhuma das entradas é NULL.

REGR_COUNT(Y, X)

Parâmetros

Valor de retorno

BIGINT.

Exemplo

SELECT REGR_COUNT(c6, c7) FROM example_table;

Resultado:

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

REGR_R2

Calcula o quadrado do coeficiente de correlação (R²).

REGR_R2(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT REGR_R2(c6, c7) FROM example_table;

Resultado:

 regr_r2
---------
       1

REGR_INTERCEPT

Calcula o intercepto Y da equação linear de mínimos quadrados determinada pelos pares (Y, X).

REGR_INTERCEPT(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT REGR_INTERCEPT(c6, c7) FROM example_table;

Resultado:

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

REGR_SLOPE

Calcula a inclinação da equação linear de mínimos quadrados.

REGR_SLOPE(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT REGR_SLOPE(c6, c7) FROM example_table;

Resultado:

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

REGR_SXX

Calcula a soma dos quadrados da variável independente (X), como sum(X^2) - sum(X)^2/N.

REGR_SXX(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT REGR_SXX(c6, c7) FROM example_table;

Resultado:

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

REGR_SXY

Calcula a soma dos produtos da variável independente (X) e da variável dependente (Y), como sum(X*Y) - sum(X) * sum(Y)/N.

REGR_SXY(Y, X)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT REGR_SXY(c6, c7) FROM example_table;

Resultado:

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

SUM

Retorna a soma de todos os valores.

SUM(expression)

Parâmetros

Valor de retorno

Mesmo tipo de dado da entrada.

Exemplo

SELECT SUM(c1) FROM example_table;

Resultado:

 sum
-----
   3

STDDEV

Retorna o desvio padrão amostral.

STDDEV(expression)

Parâmetros

Valor de retorno

NUMERIC ou FLOAT8.

Exemplo

SELECT STDDEV(c1) FROM example_table;

Resultado:

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

STDDEV_POP

Retorna o desvio padrão populacional.

STDDEV_POP(expression)

Parâmetros

Valor de retorno

NUMERIC ou FLOAT8.

Exemplo

SELECT STDDEV_POP(c1) FROM example_table;

Resultado:

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

STDDEV_SAMP

Retorna o desvio padrão amostral. Equivalente a STDDEV.

STDDEV_SAMP(expression)

Parâmetros

Valor de retorno

NUMERIC ou FLOAT8.

Exemplo

SELECT STDDEV_SAMP(c1) FROM example_table;

Resultado:

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

STRING_AGG

Concatena valores não nulos em uma única string, separados por um delimitador especificado.

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

Parâmetros

Valor de retorno

TEXT.

Example

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

Resultado:

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

UNIQ

Compatível com o Hologres V1.3 e versões posteriores. Para versões anteriores, consulte Erros comuns ao preparar para uma atualização ou Como obter mais suporte online?.

Retorna a contagem distinta de uma coluna. UNIQ supera COUNT DISTINCT especificamente quando a consulta inclui uma cláusula GROUP BY e os valores do campo GROUP BY estão uniformemente distribuídos, sem distorção de dados.

UNIQ ( <column> )

Parâmetros

Notas de uso

• UNIQ usa menos memória do que COUNT DISTINCT e é uma alternativa útil quando COUNT DISTINCT causa erros de falta de memória.

• UNIQ tem melhor desempenho do que COUNT DISTINCT quando a chave GROUP BY tem alta cardinalidade.

• UNIQ supera COUNT DISTINCT apenas quando a consulta tem uma cláusula GROUP BY e os valores estão uniformemente distribuídos. Sem uma cláusula GROUP BY ou com distorção de dados, não há vantagem de desempenho.

• A partir do Hologres V2.1, COUNT DISTINCT foi significativamente otimizado em cenários que incluem funções únicas e múltiplas de COUNT DISTINCT, distorção de dados e consultas sem cláusula GROUP BY. Na maioria dos casos, não é mais necessário reescrever consultas manualmente para usar UNIQ. Para obter detalhes, consulte Otimização de Count Distinct.

Exemplo

Os exemplos a seguir utilizam a tabela ORDERS do conjunto de dados público TPC-H. Consulte APPROX_COUNT_DISTINCT para ver o script de configuração da tabela.

Calcule a contagem distinta de O_CUSTKEY na tabela ORDERS:

SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;

Resultado:

  uniq
--------
 999982

Calcule a contagem distinta de O_CUSTKEY para cada O_ORDERSTATUS:

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

Resultado:

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

VARIANCE

Retorna a variância amostral.

VARIANCE(expression)

Parâmetros

Valor de retorno

NUMERIC.

Exemplo

SELECT VARIANCE(c1) FROM example_table;

Resultado:

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

VAR_POP

Retorna a variância populacional.

VAR_POP(expression)

Parâmetros

Valor de retorno

NUMERIC ou FLOAT8.

Exemplo

SELECT VAR_POP(c1) FROM example_table;

Resultado:

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

VAR_SAMP

Retorna a variância amostral.

VAR_SAMP(expression)

Parâmetros

Valor de retorno

NUMERIC ou FLOAT8.

Exemplo

SELECT VAR_SAMP(c1) FROM example_table;

Resultado:

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