全部產品
Search

搜尋本產品

    Hologres：Hologres通用彙總函式

    文件中心

    Hologres：Hologres通用彙總函式

    更新時間：Jun 30, 2024

    Hologres相容PostgreSQL，支援使用標準的PostgreSQL文法進行開發。

    相容PostgreSQL彙總函式

    Hologres已支援的通用彙總函式列表如下。當前Hologres版本支援的函數是PostgreSQL的一個子集，函數的使用方法請參見通用彙總函式。

    函數名

    描述

    用例

    結果

    array_agg(anyelement)

    將運算式的值串聯到數組中。暫不支援JSON、JSONB、TIMETZ、INTERVAL、INET、OID、UUID資料類型和數群組類型。

    array_agg(c1)

    {1,2}
{true,false}
{a,b}
{1.1,2.2}

    avg(bigint)

    求BIGINT類型運算式中非空值的平均值。

    avg(c1)

    2.000000

    avg(float8)

    求FLOAT8類型運算式中非空值的平均值。

    avg(c1)

    2

    avg(float4)

    求FLOAT4類型運算式中非空值的平均值。

    avg(c1)

    2

    avg(int)

    求INT類型運算式中非空值的平均值。

    avg(c1)

    2.000000

    bit_and(bigint)

    對BIGINT類型運算式中的非空值執行按位與運算。

    bit_and(c1)

    0

    bit_and(int)

    對INT類型運算式中的非空值執行按位與運算。

    bit_and(c1)

    0

    bit_or(bigint)

    對BIGINT類型運算式中的非空值執行按位或運算。

    bit_or(c1)

    3

    bit_or(int)

    對INT類型運算式中的非空值執行按位或運算。

    bit_or(c1)

    3

    bool_and(bool)

    如果BOOL運算式的值均為TRUE，則函數結果返回TRUE，否則返回FALSE。

    bool_and(c1)

    f

    bool_or(bool)

    如果BOOL運算式的值包含TRUE，則函數結果返回TRUE，否則返回FALSE。

    bool_or(c1)

    t

    count(*)

    返回指定表的行數。

    count(*)

    3

    count(bigint)

    求BIGINT類型運算式的輸入行數。

    說明

    BIGINT類型運算式的值不為NULL。

    count(c1)

    3

    count(numeric)

    求NUMERIC類型運算式的輸入行數。

    說明

    NUMERIC類型運算式的值不為NULL。

    count(c1)

    3

    every(bool)

    如果BOOL運算式的值均為TRUE，則函數結果返回TRUE，否則返回FALSE。

    無

    無

    max(bigint)

    求BIGINT類型運算式的最大值。

    max(c1)

    3

    max(float8)

    求FLOAT8類型運算式的最大值。

    max(c1)

    3

    max(float4)

    求FLOAT4類型運算式的最大值。

    max(c1)

    3

    max(int)

    求INT類型運算式的最大值。

    max(c1)

    3

    max(numeric)

    求NUMERIC類型運算式的最大值。

    max(c1)

    3

    min(bigint)

    求BIGINT類型運算式的最小值。

    min(c1)

    1

    min(float8)

    求FLOAT8類型運算式的最小值。

    min(c1)

    1

    min(float4)

    求FLOAT4類型運算式的最小值。

    min(c1)

    1

    min(int)

    求INT類型運算式的最小值。

    min(c1)

    1

    min(numeric)

    求NUMERIC類型運算式的最小值。

    min(c1)

    1

    sum(bigint)

    求BIGINT類型運算式所有值的總和。

    sum(c1)

    6

    sum(float8)

    求FLOAT8類型運算式所有值的總和。

    sum(c1)

    6

    sum(float4)

    求FLOAT4類型運算式所有值的總和。

    sum(c1)

    6

    sum(int)

    求INT類型運算式所有值的總和。

    sum(c1)

    6

    sum(numeric)

    求NUMERIC類型運算式所有值的總和。

    sum(c1)

    6.0

    string_agg(expression, delimiter)

    使用指定分隔字元將指定運算式的非空值串聯成字串。

    string_agg(c1, '-')

    a-b-c

    corr(Y, X)

    求相關係數。

    corr(c1, c2)

    無

    covar_pop(Y, X)

    求總體共變數。

    covar_pop(c1, c2)

    無

    covar_samp(Y, X)

    求樣本共變數。

    covar_samp(c1, c2)

    無

    regr_avgx(Y, X)

    求自變數的平均值。

    reg_avgx(c1, c2)

    無

    regr_avgy(Y, X)

    求因變數的平均值。

    reg_avgy(c1, c2)

    無

    regr_count(Y, X)

    求兩個輸入參數中都不為空白的行數。

    regr_count(c1, c2)

    無

    regr_intercept(Y, X)

    求由(X,Y)確定的最小方差擬合的縱軸截距。

    reg_intercept(c1, c2)

    無

    regr_r2(Y, X)

    求相關係數的平方。

    regr_r2(c1, c2)

    無

    regr_slope(Y, X)

    求由(X,Y)確定的最小方差擬合的斜率。

    regr_slope(c1, c2)

    無

    regr_sxx(Y, X)

    求自變數的平方和sum(X^2) - sum(X)^2/N 。

    regr_sxx(c1, c2)

    無

    regr_sxy(Y, X)

    求自變數和因變數的乘積和sum(X*Y) - sum(X) * sum(Y)/N。

    regr_sxy(c1, c2)

    無

    regr_syy(Y, X)

    求因變數的平方和sum(Y^2) - sum(Y)^2/N。

    regr_syy(c1, c2)

    無

    stddev(int)

    求INT類型運算式的樣本標準差。

    stddev(c1)

    無

    stddev(numeric)

    求NUMERIC類型運算式的樣本標準差。

    stddev(c1)

    無

    stddev(float8)

    求FLOAT8類型運算式的樣本標準差。

    stddev(c1)

    無

    stddev_pop(int)

    求INT類型運算式的總體標準差。

    stddev_pop(c1)

    無

    stddev_pop(numeric)

    求NUMERIC類型運算式的總體標準差。

    stddev_pop(c1)

    無

    stddev_pop(float8)

    求FLOAT8類型運算式的總體標準差。

    stddev_pop(c1)

    無

    stddev_samp(int)

    求INT類型運算式的樣本標準差。

    stddev_samp(c1)

    無

    stddev_samp(numeric)

    求NUMERIC類型運算式的樣本標準差。

    stddev_samp(c1)

    無

    stddev_samp(float8)

    求FLOAT8類型運算式的樣本標準差。

    stddev_samp(c1)

    無

    variance(int)

    求INT類型運算式的樣本方差。

    variance(c1)

    無

    variance(numeric)

    求NUMERIC類型運算式的樣本方差。

    variance(c1)

    無

    var_pop(float8)

    求FLOAT8類型運算式的總體方差。

    var_pop(c1)

    無

    var_pop(int)

    求INT類型運算式的總體方差。

    var_pop(c1)

    無

    var_pop(numeric)

    求NUMERIC類型運算式的總體方差。

    var_pop(c1)

    無

    var_samp(float8)

    求FLOAT8類型運算式的樣本方差。

    var_samp(c1)

    無

    var_samp(int)

    求INT類型運算式的樣本方差。

    var_samp(c1)

    無

    var_samp(numeric)

    求NUMERIC類型運算式的樣本方差。

    var_samp(c1)

    無

    其他彙總函式

    APPROX_COUNT_DISTINCT

    • 文法

      APPROX_COUNT_DISTINCT函數用於計算某一列去重後的行數，結果只能返回一個值，並且該值為近似值。

      APPROX_COUNT_DISTINCT ( <column> )

      參數說明如下表所示。

      參數

      描述

      column

      需要近似計算去重後行數的列。

      APPROX_COUNT_DISTINCT採用HyperLogLog基數估計的方式進行非精確的COUNT DISTINCT計算。非精確的COUNT DISTINCT計算能提升查詢效能，尤其是對於column的離散值比較大的情況，誤差率平均可以控制在0.1%-1%以內。該函數適用於對效能敏感並且可以接受誤差的情境。

      同時，您也可以通過COUNT DISTINCT ( column )的方式進行精確的COUNT DISTINCT計算，使用時資源開銷會比較大。

    • 調整誤差率

      通過使用以下參數調整誤差率。

      SET hg_experimental_approx_count_distinct_precision = 20;

      • 支援取值範圍為[12,20]，預設值為17。

      • 精度參數含義為HyperLogLog演算法的分桶bit位個數，參數越大，代表分桶越多，理論精度越高。

      • 精度參數取值越高，計算時間和記憶體開銷也會相應增大，但都遠遠小於精確的COUNT DISTINCT ( column )語句帶來的開銷，因此，推薦選用APPROX_COUNT_DISTINCT替換COUNT DISTINCT ( column )。

      • 當精度參數設定為17以上時，Hologres採用HyperLogLog++演算法，會對傳回值做誤差修正，以進一步降低誤差、穩定誤差。例如hg_experimental_approx_count_distinct_precision取值為20時，多數情況下，可以降低到0.01-0.2%不等的誤差率。

    • 樣本

      計算O_CUSTKEY列去重後行數的近似值，樣本語句如下。

      SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

--全域設定精度20，計算O_CUSTKEY列去重後行數的近似值
ALTER DATABASE dbname SET hg_experimental_approx_count_distinct_precision = 20;
SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

--在Session層級設定精度20
SET hg_experimental_approx_count_distinct_precision = 20;
SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

    UNIQ

    • 使用限制

      • 僅Hologres V1.3及以上版本支援使用UNIQ函數，如果您的執行個體是V1.3以下版本，請您使用自助升級或加入HologresDingTalk交流群反饋，詳情請參見如何擷取更多的線上支援？。

      • SQL中必須包含GROUP BY，且GROUP BY的欄位比較均勻（不傾斜），UNIQ才能發揮比COUNT DISTINCT更好的效能。

    • 文法

      UNIQ函數用於計算某一列去重後的行數，結果返回一個去重值。

      UNIQ ( < column > );

      • 參數說明如下。

        參數

        描述

        column

        需要計算去重後行數的列。

        支援SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、TEXT、VARCHAR、TIMESTAMP、TIMESTAMPTZ、DATE、TIMETZ、UUID類型。

      • 使用說明如下。

        • 一般來說，UNIQ在GROUP BY KEY的KEY基數較高時，比COUNT DISTINCT效能更好，同時UNIQ比COUNT DISTINCT更節省記憶體，如果使用COUNT DISTINCT遇到記憶體超限時，可以換用UNIQ。

        • 從Hologres V2.1版本開始，Hologres針對COUNT DISTINCT情境做了非常多的效能最佳化（包括單個COUNT DISTINCT、多個COUNT DISTINCT、資料扭曲、SQL沒有GROUP BY欄位等情境），無需再手動改寫成UNIQ實現，即可實現更好的效能。詳情請參見Count Distinct最佳化。

    • 樣本

      計算ORDERS表的O_CUSTKEY列去重後行的數值語句如下。

      SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;

--不同O_ORDERSTATUS下O_CUSTKEY去重數
SELECT O_ORDERSTATUS, UNIQ ( O_CUSTKEY ) FROM ORDERS GROUP BY O_ORDERSTATUS;

    MAX_BY與MIN_BY

    • 使用限制

      該函數適用於Hologres V1.3.36及以上版本的執行個體，若執行個體低於該版本，請您通過加入即時數倉Hologres交流群申請升級執行個體，詳情請參見如何擷取更多的線上支援？。

    • 文法

      MAX_BY與MIN_BY函數用於比較某一列（y列）取值的大小，當y列取值為最大值或最小值時，返回對應著的另外一列（x列）的取值。文法如下。

      說明

      數字類型按數字大小進行比較，非數字類型按照字典排序進行比較。

      MAX_BY(x, y);
MIN_BY(x, y);

      • 參數說明如下。

        參數

        描述

        y

        需要比較大小的列。

        x

        另外一列的欄位名。

        • 當y列取值為最大值時，MAX_BY函數返回此列的取值結果。

        • 當y列取值為最小值時，MIN_BY函數返回此列的取值結果。

      • 使用說明如下。

        • 當MAX_BY函數中y列的最大值存在多個時，則返回對應的多個x值中的最大值。

        • 當MIN_BY函數中y列的最小值存在多個時，則返回對應的多個x值中的最小值。

        • y列的NULL值不參與計算。當y列所有值均為NULL時，函數傳回值為NULL。

    • 樣本

      • 使用MAX_BY函數，通過GROUP BY對id進行分組，查詢每組cost最大值對應的name值。樣本語句如下。

        SELECT id, max_by(name, cost) FROM test GROUP BY id;

 id | max_by
----+--------
  2 | bb
  1 | aaa
  3 | c
(3 rows)

      • 使用MAX_BY函數，查詢cost最大值（存在多個）對應的name值。樣本語句如下。

        select max_by(name, cost) from test;

 max_by
--------
 bb
(1 row)

      • 使用MIN_BY函數，查詢cost最小值對應的name值。樣本語句如下。

        SELECT min_by(name, cost) FROM test;

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