Hologres:General-purpose aggregate functions

Fonctions prises en charge

Le tableau suivant répertorie toutes les fonctions prises en charge, regroupées par catégorie.

Agrégation générale

Agrégation booléenne

Agrégation binaire

Estimation de la cardinalité (à l'aide de HyperLogLog)

Estimation des percentiles

Statistiques

Régression linéaire

Exemple de données

Les exemples suivants utilisent example_table. Exécutez ce script de configuration pour créer la table et insérer des exemples de données.

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

Utilise l'estimation de cardinalité HyperLogLog pour renvoyer un compte distinct approximatif. Le taux d'erreur moyen se situe entre 0,1 % et 1 %, ce qui engendre une surcharge bien inférieure à celle d'un COUNT DISTINCT ( column ) exact.

APPROX_COUNT_DISTINCT ( <column> )

Paramètres

Notes d'utilisation

La précision de l'algorithme HyperLogLog est contrôlée par un paramètre de segmentation :

SET hg_experimental_approx_count_distinct_precision = 20;

Définissez ce paramètre au niveau de la session avec SET ou au niveau de la base de données avec 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;

Pour effectuer un compte distinct exact, utilisez COUNT DISTINCT ( column ) . Cette opération consomme davantage de ressources.

Exemple

Les exemples suivants utilisent la table ORDERS issue du jeu de données public TPC-H fourni par MaxCompute. Exécutez le script de configuration ci-dessous pour créer les tables requises avant d'exécuter les requêtes.

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

Calculez le compte distinct approximatif dans la colonne O_CUSTKEY :

SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

Résultat :

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

Calculez avec une précision globale définie sur 20 :

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

Résultat :

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

Calculez avec une précision au niveau de la session définie sur 20 :

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

Résultat :

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

APPROX_PERCENTILE

Pris en charge dans Hologres V3.1 et versions ultérieures.

Utilise l'échantillonnage par réservoir (jusqu'à 8 192 éléments) pour renvoyer un percentile approximatif d'une colonne numérique.

APPROX_PERCENTILE (fraction) WITHIN GROUP (ORDER BY sort_expression)

Paramètres

Exemple

Calculez Q1, la médiane et Q3 d'une colonne de salaire :

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

Agrège les valeurs de toute expression dans un tableau.

ARRAY_AGG(expression)

Paramètres

Valeur de retour

Type ARRAY.

Exemple

SELECT ARRAY_AGG(c1) FROM example_table;

Résultat :

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

AVG

Renvoie la moyenne des valeurs non NULL.

AVG(expression)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT AVG(c7) FROM example_table;

Résultat :

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

BIT_AND

Effectue un ET binaire sur toutes les valeurs non NULL.

BIT_AND(expression)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT BIT_AND(c1) FROM example_table;

Résultat :

 bit_and
---------
       0

BIT_OR

Effectue un OU binaire sur toutes les valeurs non NULL.

BIT_OR(expression)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT BIT_OR(c1) FROM example_table;

Résultat :

 bit_or
--------
      3

BOOL_AND

Renvoie TRUE si toutes les valeurs d'une expression booléenne sont vraies ; renvoie FALSE sinon.

BOOL_AND(bool)

Paramètres

Valeur de retour

BOOLEAN. Renvoie t si toutes les valeurs sont vraies, f sinon.

Exemple

SELECT BOOL_AND(c2) FROM example_table;

Résultat :

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

BOOL_OR

Renvoie TRUE si au moins une valeur d'une expression booléenne est vraie ; renvoie FALSE sinon.

BOOL_OR(bool)

Paramètres

Valeur de retour

BOOLEAN. Renvoie t si au moins une valeur est vraie, f sinon.

Exemple

SELECT BOOL_OR(c2) FROM example_table;

Résultat :

 bool_or
---------
 t

CORR

Calcule le coefficient de corrélation entre deux variables. Les deux paramètres doivent être de type DOUBLE PRECISION, FLOAT ou FLOAT8.

CORR(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT CORR(c6, c7) FROM example_table;

COUNT

Renvoie le nombre de lignes ou le nombre de valeurs non NULL dans une expression.

• COUNT(*) compte toutes les lignes, y compris celles contenant des valeurs NULL.

• COUNT(expression) ne compte que les valeurs non NULL.

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

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

Paramètres

Valeur de retour

BIGINT.

Exemple

SELECT COUNT(*) FROM example_table;

Résultat :

 count
-------
     2

COVAR_POP

Calcule la covariance de population de deux variables.

COVAR_POP(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT COVAR_POP(c6, c7) FROM example_table;

Résultat :

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

COVAR_SAMP

Calcule la covariance d'échantillon de deux variables.

COVAR_SAMP(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT COVAR_SAMP(c6, c7) FROM example_table;

Résultat :

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

EVERY

Renvoie TRUE si toutes les valeurs d'une expression booléenne sont vraies ; renvoie FALSE sinon.

EVERY(bool)

Paramètres

Valeur de retour

BOOLEAN. Renvoie t si toutes les valeurs sont vraies, f sinon.

Exemple

SELECT EVERY(c2) FROM example_table;

Résultat :

 every
-------
 f

MAX

Renvoie la valeur maximale.

MAX(expression)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT MAX(c1) FROM example_table;

Résultat :

 max
-----
   2

MAX_BY

Pris en charge dans Hologres V1.3.36 et versions ultérieures. Pour les versions antérieures, consultez Comment obtenir davantage de support en ligne ?

Recherche la ligne dont la valeur de la colonne y est maximale et renvoie la valeur correspondante de la colonne x. Par exemple, si une table contient les colonnes name et cost, MAX_BY(name, cost) renvoie le nom de l'article ayant le coût le plus élevé.

Les types numériques sont comparés par valeur numérique. Les types non numériques sont comparés selon l'ordre de tri du dictionnaire.

MAX_BY(x, y)

Paramètres

Valeur de retour

• Renvoie la valeur de x de la ligne où y est maximal.

• Renvoie NULL si toutes les valeurs de y sont NULL.

• Si plusieurs lignes partagent la valeur y maximale, renvoie la valeur maximale des x correspondants.

Exemple

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

Renvoyez le nom correspondant au coût le plus élevé. Comme plusieurs lignes partagent le coût maximal (300), la fonction renvoie la valeur name maximale parmi ces lignes :

SELECT max_by(name, cost) FROM test;

Résultat :

 max_by
--------
 aaa

MIN

Renvoie la valeur minimale.

MIN(expression)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT MIN(c1) FROM example_table;

Résultat :

 min
-----
   1

MIN_BY

Pris en charge dans Hologres V1.3.36 et versions ultérieures. Pour les versions antérieures, consultez Comment obtenir davantage de support en ligne ?

Recherche la ligne dont la valeur de la colonne y est minimale et renvoie la valeur correspondante de la colonne x. Par exemple, si une table contient les colonnes name et cost, MIN_BY(name, cost) renvoie le nom de l'article ayant le coût le plus bas.

Les types numériques sont comparés par valeur numérique. Les types non numériques sont comparés selon l'ordre de tri du dictionnaire.

MIN_BY(x, y)

Paramètres

Valeur de retour

• Renvoie la valeur de x de la ligne où y est minimal.

• Renvoie NULL si toutes les valeurs de y sont NULL.

• Si plusieurs lignes partagent la valeur y minimale, renvoie la valeur minimale des x correspondants.

Exemple

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

Renvoyez le nom correspondant au coût le plus bas :

SELECT MIN_BY(name, cost) FROM test;

Résultat :

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

REGR_AVGX

Calcule la moyenne de la variable indépendante (X).

REGR_AVGX(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT REGR_AVGX(c6, c7) FROM example_table;

Résultat :

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

REGR_AVGY

Calcule la moyenne de la variable dépendante (Y).

REGR_AVGY(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT REGR_AVGY(c6, c7) FROM example_table;

Résultat :

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

REGR_COUNT

Renvoie le nombre de lignes dont aucune entrée n'est NULL.

REGR_COUNT(Y, X)

Paramètres

Valeur de retour

BIGINT.

Exemple

SELECT REGR_COUNT(c6, c7) FROM example_table;

Résultat :

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

REGR_R2

Calcule le carré du coefficient de corrélation (R²).

REGR_R2(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT REGR_R2(c6, c7) FROM example_table;

Résultat :

 regr_r2
---------
       1

REGR_INTERCEPT

Calcule l'ordonnée à l'origine de l'équation linéaire déterminée par la méthode des moindres carrés à partir des paires (Y, X).

REGR_INTERCEPT(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT REGR_INTERCEPT(c6, c7) FROM example_table;

Résultat :

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

REGR_SLOPE

Calcule la pente de l'équation linéaire déterminée par la méthode des moindres carrés.

REGR_SLOPE(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT REGR_SLOPE(c6, c7) FROM example_table;

Résultat :

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

REGR_SXX

Calcule la somme des carrés de la variable indépendante (X), soit sum(X^2) - sum(X)^2/N.

REGR_SXX(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT REGR_SXX(c6, c7) FROM example_table;

Résultat :

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

REGR_SXY

Calcule la somme des produits de la variable indépendante (X) et de la variable dépendante (Y), soit sum(X*Y) - sum(X) * sum(Y)/N.

REGR_SXY(Y, X)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT REGR_SXY(c6, c7) FROM example_table;

Résultat :

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

SUM

Renvoie la somme de toutes les valeurs.

SUM(expression)

Paramètres

Valeur de retour

Même type de données que l'entrée.

Exemple

SELECT SUM(c1) FROM example_table;

Résultat :

 sum
-----
   3

STDDEV

Renvoie l'écart type d'échantillon.

STDDEV(expression)

Paramètres

Valeur de retour

NUMERIC ou FLOAT8.

Exemple

SELECT STDDEV(c1) FROM example_table;

Résultat :

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

STDDEV_POP

Renvoie l'écart type de population.

STDDEV_POP(expression)

Paramètres

Valeur de retour

NUMERIC ou FLOAT8.

Exemple

SELECT STDDEV_POP(c1) FROM example_table;

Résultat :

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

STDDEV_SAMP

Renvoie l'écart type d'échantillon. Équivalent à STDDEV.

STDDEV_SAMP(expression)

Paramètres

Valeur de retour

NUMERIC ou FLOAT8.

Exemple

SELECT STDDEV_SAMP(c1) FROM example_table;

Résultat :

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

STRING_AGG

Concatène les valeurs non NULL en une seule chaîne, séparées par un délimiteur spécifié.

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

Paramètres

Valeur de retour

TEXT.

Exemple

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

Résultat :

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

UNIQ

Pris en charge dans Hologres V1.3 et versions ultérieures. Pour les versions antérieures, consultez Erreurs courantes lors de la préparation d'une mise à niveau ou Comment obtenir davantage de support en ligne ? .

Renvoie le compte distinct pour une colonne. UNIQ offre de meilleures performances que COUNT DISTINCT lorsque la requête inclut une clause GROUP BY et que les valeurs du champ GROUP BY sont uniformément réparties sans skew de données.

UNIQ ( <column> )

Paramètres

Notes d'utilisation

• UNIQ consomme moins de mémoire que COUNT DISTINCT et constitue une alternative utile lorsque COUNT DISTINCT provoque des erreurs de dépassement de mémoire.

• UNIQ offre de meilleures performances que COUNT DISTINCT lorsque la clé GROUP BY présente une cardinalité élevée.

• UNIQ ne surpasse COUNT DISTINCT que lorsque la requête comporte une clause GROUP BY et que les valeurs sont uniformément réparties. Sans clause GROUP BY ou en présence de skew de données, aucun avantage en termes de performances n'est constaté.

• Depuis Hologres V2.1, COUNT DISTINCT a été considérablement optimisé dans divers scénarios, notamment pour les fonctions COUNT DISTINCT simples et multiples, le skew de données et les requêtes sans clause GROUP BY. Il n'est généralement plus nécessaire de réécrire manuellement les requêtes pour utiliser UNIQ. Pour plus de détails, consultez Optimisation de Count Distinct.

Exemple

Les exemples suivants utilisent la table ORDERS issue du jeu de données public TPC-H. Consultez APPROX_COUNT_DISTINCT pour le script de configuration de la table.

Calculez le compte distinct de O_CUSTKEY dans la table ORDERS :

SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;

Résultat :

  uniq
--------
 999982

Calculez le compte distinct de O_CUSTKEY pour chaque O_ORDERSTATUS :

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

Résultat :

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

VARIANCE

Renvoie la variance d'échantillon.

VARIANCE(expression)

Paramètres

Valeur de retour

NUMERIC.

Exemple

SELECT VARIANCE(c1) FROM example_table;

Résultat :

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

VAR_POP

Renvoie la variance de population.

VAR_POP(expression)

Paramètres

Valeur de retour

NUMERIC ou FLOAT8.

Exemple

SELECT VAR_POP(c1) FROM example_table;

Résultat :

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

VAR_SAMP

Renvoie la variance d'échantillon.

VAR_SAMP(expression)

Paramètres

Valeur de retour

NUMERIC ou FLOAT8.

Exemple

SELECT VAR_SAMP(c1) FROM example_table;

Résultat :

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