全部產品
Search
文件中心

Hologres:Hologres通用彙總函式

更新時間:Oct 31, 2024

Hologres相容PostgreSQL,支援使用標準的PostgreSQL文法進行開發,本文為您介紹Hologres已支援的通用彙總函式。

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

函數

功能

APPROX_COUNT_DISTINCT

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

ARRAY_AGG

將運算式的值串聯到數組中。

AVG

計算BIGINT、FLOAT8、FLOAT4或INT類型運算式中非空值的平均值。

BIT_AND

在BIGINT或INT類型的運算式中,對非空值進行按位與運算。

BIT_OR

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

BOOL_AND

計算BOOLEAN類型運算式中值是否均為TRUE。

BOOL_OR

計算BOOLEAN類型運算式中值是否包含TRUE。

CORR

計算兩個變數之間的相關係數。

COUNT

擷取指定表的行數或BIGINT、NUMERIC類型運算式的輸入行數。

COVAR_POP

計算總體共變數。

COVAR_SAMP

計算樣本共變數。

EVERY

判斷BOOLEAN運算式的值是否均為TRUE。

MAX

擷取BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式的最大值。

MAX_BY

用於比較某一列(y列)的最大值,並擷取y列最大值對應其他指定列(x列)的值。

MIN

擷取BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式的最小值。

MIN_BY

用於比較某一列(y列)的最小值,並擷取y列最小值對應其他指定列(x列)的值。

REGR_AVGX

計算自變數(X)的平均值。

REGR_AVGY

計算因變數(Y)的平均值。

REGR_COUNT

計算輸入參數中都不為空白的行數。

REGR_R2

計算相關係數的平方。

REGR_INTERCEPT

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

REGR_SLOPE

計算最小方差擬合的斜率。

REGR_SXX

計算自變數(X)的平方和,即sum(X^2) - sum(X)^2/N

REGR_SXY

計算自變數(X)和因變數(Y)的乘積和,即sum(X*Y) - sum(X) * sum(Y)/N

SUM

擷取BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式所有值的總和。

STDDEV

計算INT、NUMERIC或FLOAT8類型運算式的樣本標準差。

STDDEV_POP

計算INT、NUMERIC或FLOAT8類型運算式的總體標準差。

STDDEV_SAMP

計算INT、NUMERIC、FLOAT8類型運算式的樣本標準差。

STRING_AGG

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

UNIQ

用於計算某一列去重後的行數。

VARIANCE

計算INT、NUMERIC類型運算式的樣本方差。

VAR_POP

計算FLOAT8、INT或NUMERIC類型運算式的總體方差。

VAR_SAMP

計算INT、FLOAT8或NUMERIC類型運算式的樣本方差。

樣本資料

-- 建立表
CREATE TABLE example_table(
    c1 INT,           
    c2 BOOLEAN,      
    c3 VARCHAR(10),  
    c4 DECIMAL(3, 1),
    c5 TEXT,
    c6 FLOAT8,
    c7 FLOAT8
);

-- 插入資料
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

  • 描述:計算某一列去重後的行數,結果只能返回一個值,並且該值為近似值。

    APPROX_COUNT_DISTINCT ( <column> )
  • 參數說明

    column:必填,需要近似計算去重後行數的列。

  • 使用說明

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

    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%不等的誤差率。

    說明

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

  • 樣本

    以下樣本使用MaxCompute提供的公開資料集TPC-H的ORDERS表。您無需進行任何額外準備,只需運行以下SQL語句。

    -- 建立外部表格
    IMPORT FOREIGN SCHEMA public_data
    LIMIT TO (ORDERS_10g)
    FROM SERVER odps_server INTO public options (if_table_exist 'update');
    
    -- 建立內部表
    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 '訂單編號';
    COMMENT ON COLUMN ORDERS.O_CUSTKEY IS '顧客序號';
    COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS '訂單狀態';
    COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS '總價';
    COMMENT ON COLUMN ORDERS.O_ORDERDATE IS '下單日期';
    COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS '訂單優先順序';
    COMMENT ON COLUMN ORDERS.O_CLERK IS '收銀員';
    COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS '發貨優先順序';
    COMMENT ON COLUMN ORDERS.O_COMMENT IS '備忘';
    
    COMMIT;
    
    -- 資料匯入內表
    INSERT INTO ORDERS SELECT * FROM ORDERS_10g;
    • 計算O_CUSTKEY列去重後行數的近似值。

      SELECT APPROX_COUNT_DISTINCT ( O_CUSTKEY ) FROM ORDERS;

      返回結果如下。

      approx_count_distinct
      -----------------------
                     1000422
    • 全域設定精度為20,計算O_CUSTKEY列去重後行數的近似值。

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

      返回結果如下。

      approx_count_distinct
      -----------------------
                     1000422
    • Session層級設定精度為20,計算O_CUSTKEY列去重後行數的近似值。

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

      返回結果如下。

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

ARRAY_AGG

  • 描述:將運算式的值串聯到數組中。

    ARRAY_AGG(expression)
  • 參數說明

    expression:必填,除JSON、JSONB、TIMETZ、INTERVAL、INET、OID、UUID資料類型和數群組類型之外的,其他格式的運算式。

  • 傳回值說明

    返回ARRAY類型。

  • 樣本

    SELECT ARRAY_AGG(c1) FROM example_table;

    返回結果如下。

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

AVG

  • 描述:計算BIGINT、FLOAT8、FLOAT4或INT類型運算式中非空值的平均值。

    AVG(expression)
  • 參數說明

    expression:必填,BIGINT、FLOAT8、FLOAT4或INT類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT AVG(c7) FROM example_table;

    返回結果如下。

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

BIT_AND

  • 描述:在BIGINT或INT類型的運算式中,對非空值進行按位與運算。

    BIT_AND(expression)
  • 參數說明

    expression:必填,BIGINT或INT類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT BIT_AND(c1) FROM example_table;

    返回結果如下。

     bit_and
    ---------
           0

BIT_OR

  • 描述:對BIGINT或INT類型運算式中的非空值進行按位或運算。

    BIT_OR(expression)
  • 參數說明

    expression:必填,BIGINT或INT類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT BIT_OR(c1) FROM example_table;

    返回結果如下。

     bit_or
    --------
          3

BOOL_AND

  • 描述:計算BOOLEAN類型運算式中值是否均為TRUE。

    BOOL_AND(bool)
  • 參數說明

    bool:必填,BOOLEAN類型運算式。

  • 傳回值說明

    返回BOOLEAN類型,若運算式中值均為TRUE,則返回TRUE(t),否則返回FALSE(f)。

  • 樣本

    SELECT BOOL_AND(c2) FROM example_table;

    返回結果如下。

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

BOOL_OR

  • 描述:計算BOOLEAN類型運算式中值是否包含TRUE。

    BOOL_OR(bool)
  • 參數說明

    bool:必填,BOOLEAN類型運算式。

  • 傳回值說明

    返回BOOLEAN類型,若運算式中值包含TRUE,則返回TRUE(t),否則返回FALSE(f)。

  • 樣本

    SELECT BOOL_OR(c2) FROM example_table;

    返回結果如下。

     bool_or
    ---------
     t

CORR

  • 描述:計算兩個變數之間的相關係數。

    CORR(Y, X)
    說明

    參數類型僅支援DOUBLE PRECISION、FLOAT或FLOAT8類型。

  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT CORR(c6, c7) FROM example_table;

    返回結果如下。

     corr
    ------
    

COUNT

  • 描述:擷取指定表的行數或BIGINT、NUMERIC類型運算式的輸入行數。

    • 擷取指定表的行數

      COUNT(*)
    • 擷取BIGINT、NUMERIC類型運算式的輸入行數

      COUNT(expression)

      參數說明:必填,BIGINT、NUMERIC類型的運算式值不為NULL。

  • 傳回值說明

    返回BIGINT類型。

  • 樣本

    SELECT COUNT(*) FROM example_table;

    返回結果如下。

     count
    -------
         2

COVAR_POP

  • 描述:計算總體共變數。

    COVAR_POP(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT COVAR_POP(c6, c7) FROM example_table;

    返回結果如下。

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

COVAR_SAMP

  • 描述:計算樣本共變數。

    COVAR_SAMP(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT COVAR_SAMP(c6, c7) FROM example_table;

    返回結果如下。

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

EVERY

  • 描述:判斷BOOLEAN運算式的值是否均為TRUE。

    EVERY(bool)
  • 參數說明

    bool:必填,BOOLEAN類型運算式。

  • 傳回值說明

    返回BOOLEAN類型,若運算式的值均為TRUE(t),則返回TRUE,否則返回FALSE(f)。

  • 樣本

    SELECT EVERY(c2) FROM example_table;

    返回結果

     every
    -------
     f

MAX

  • 描述:擷取BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式的最大值。

    MAX(expression)
  • 參數說明

    expression:必填,BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT MAX(c1) FROM example_table;

    返回結果如下。

     max
    -----
       2

MAX_BY

  • 描述:用於比較某一列(y列)的最大值,並擷取y列最大值對應其他指定列(x列)的值。

    MAX_BY(x, y);
    說明

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

  • 參數說明

    • y:必填,需要擷取最大值的列名稱。若y列存在NULL值,則該列將不參與計算。

    • x:必填,y列最大值時,需擷取其他列值的列名稱。

  • 使用說明

    僅Hologres V1.3.36及以上版本的執行個體支援使用MAX_BY函數。

    說明

    若執行個體低於該版本,請您通過加入即時數倉Hologres交流群申請升級執行個體,詳情請參見如何擷取更多的線上支援?

  • 傳回值說明

    • 當y列取值為最大值時,返回對應x列的值。

    • 當y列所有值均為NULL時,函數傳回值為NULL。

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

  • 樣本

    --樣本資料
    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);

    查詢cost列最大值(存在多個)對應name列的值。

    SELECT max_by(name, cost) FROM test;

    返回結果如下。

     max_by
    --------
     aaa

MIN

  • 描述:擷取BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式的最小值。

    MIN(expression)
  • 參數說明

    expression:必填,BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型。

  • 傳回值類型

    與參數類型一致。

  • 樣本

    SELECT MIN(c1) FROM example_table;

    返回結果如下。

     min
    -----
       1

MIN_BY

  • 描述:用於比較某一列(y列)的最小值,並擷取y列最小值對應其他指定列(x列)的值。

    MIX_BY(x, y);
    說明

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

  • 參數說明

    • y:必填,需要擷取最小值的列名稱。若y列存在NULL值,則該列將不參與計算。

    • x:必填,y列最小值時,需擷取其他列值的列名稱。

  • 使用說明

    僅Hologres V1.3.36及以上版本的執行個體支援使用MAX_BY與MIN_BY函數。

    說明

    若執行個體低於該版本,請您通過加入即時數倉Hologres交流群申請升級執行個體,詳情請參見如何擷取更多的線上支援?

  • 傳回值說明

    • 當y列取值為最小值時,返回對應x列的值。

    • 當y列所有值均為NULL時,函數傳回值為NULL。

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

  • 樣本

    --樣本資料
    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);

    查詢cost列最小值對應的name列的值。

    SELECT MIX_BY(name, cost) FROM test;

    返回結果如下。

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

REGR_AVGX

  • 描述:計算自變數(X)的平均值。

    REGR_AVGX(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT REGR_AVGX(c6, c7) FROM example_table;

    返回結果如下。

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

REGR_AVGY

  • 描述:計算因變數(Y)的平均值。

    REGR_AVGY(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT REGR_AVGY(c6, c7) FROM example_table;

    返回結果如下。

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

REGR_COUNT

  • 描述:計算輸入參數中都不為空白的行數。

    REGR_COUNT(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    返回BIGINT類型。

  • 樣本

    SELECT REGR_COUNT(c6, c7) FROM example_table;

    返回結果如下。

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

REGR_R2

  • 描述:計算相關係數的平方。

    REGR_R2(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT REGR_R2(c6, c7) FROM example_table;

    返回結果如下。

     regr_r2
    ---------
           1

REGR_INTERCEPT

  • 描述:計算由(Y, X)確定的最小方差擬合的縱軸截距。

    REGR_INTERCEPT(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT REGR_INTERCEPT(c6, c7) FROM example_table;

    返回結果如下。

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

REGR_SLOPE

  • 描述:計算最小方差擬合的斜率。

    REGR_SLOPE(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT REGR_SLOPE(c6, c7) FROM example_table;

    返回結果如下。

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

REGR_SXX

  • 描述:計算自變數(X)的平方和,即sum(X^2) - sum(X)^2/N

    REGR_SXX(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT REGR_SXX(c6, c7) FROM example_table;

    返回結果如下。

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

REGR_SXY

  • 描述:計算自變數(X)和因變數(Y)的乘積和,即sum(X*Y) - sum(X) * sum(Y)/N

    REGR_SXY(Y, X)
  • 參數說明

    Y和X:必填,DOUBLE PRECISION、FLOAT或FLOAT8類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT REGR_SXY(c6, c7) FROM example_table;

    返回結果如下。

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

SUM

  • 描述:擷取BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式所有值的總和。

    SUM(expression)
  • 參數說明

    expression:必填,BIGINT、FLOAT8、FLOAT4、INT或NUMERIC類型運算式。

  • 傳回值說明

    與參數類型一致。

  • 樣本

    SELECT SUM(c1) FROM example_table;

    返回結果如下。

     sum
    -----
       3

STDDEV

  • 描述:計算INT、NUMERIC或FLOAT8類型運算式的樣本標準差。

    STDDEV(expression)
  • 參數說明

    expression:必填,INT、NUMERIC或FLOAT8類型運算式。

  • 傳回值說明

    返回NUMERIC或FLOAT8類型。

  • 樣本

    SELECT STDDEV(c1) FROM example_table;

    返回結果如下。

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

STDDEV_POP

  • 描述:計算INT、NUMERIC或FLOAT8類型運算式的總體標準差。

    STDDEV_POP(expression)	
  • 參數說明

    expression:必填,INT、NUMERIC或FLOAT8類型運算式。

  • 傳回值說明

    返回NUMERIC或FLOAT8類型。

  • 樣本

    SELECT STDDEV_POP(c1) FROM example_table;

    返回結果如下。

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

STDDEV_SAMP

  • 描述:計算INT、NUMERIC、FLOAT8類型運算式的樣本標準差。

    STDDEV_SAMP(expression)	
  • 參數說明

    expression:必填,INT、NUMERIC、FLOAT8類型運算式。

  • 傳回值說明

    返回NUMERIC或FLOAT8類型。

  • 樣本

    SELECT STDDEV_SAMP(c1) FROM example_table;

    返回結果如下。

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

STRING_AGG

  • 描述:將TEXT類型運算式中的非空值使用指定分隔字元串聯成字串。

    STRING_AGG(<expression> TEXT, <delimiter> TEXT)
  • 參數說明

    • expression:必填,原始字串。

    • delimiter:必填,分隔字元。

  • 傳回值說明

    返回TEXT類型。

  • 樣本

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

    返回結果如下。

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

UNIQ

  • 描述:用於計算某一列去重後的行數。

    UNIQ ( < column > );
  • 參數說明

    column:必填,需要計算去重後行數的列。支援SMALLINT、INTEGER、BIGINT、REAL、DOUBLE PRECISION、TEXT、VARCHAR、TIMESTAMP、TIMESTAMPTZ、DATE、TIMETZ、UUID類型。

  • 使用說明

    • 僅Hologres V1.3及以上版本支援使用UNIQ函數。

      說明

      如果您的執行個體是V1.3以下版本,請您使用自助升級或加入HologresDingTalk交流群反饋,詳情請參見如何擷取更多的線上支援?

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

    • 正常情況下,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最佳化

  • 樣本

    以下樣本使用MaxCompute提供的公開資料集TPC-H的ORDERS表。您無需進行任何額外準備,只需運行以下SQL語句。

    -- 建立外部表格
    IMPORT FOREIGN SCHEMA public_data
    LIMIT TO (ORDERS_10g)
    FROM SERVER odps_server INTO public options (if_table_exist 'update');
    
    -- 建立內部表
    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 '訂單編號';
    COMMENT ON COLUMN ORDERS.O_CUSTKEY IS '顧客序號';
    COMMENT ON COLUMN ORDERS.O_ORDERSTATUS IS '訂單狀態';
    COMMENT ON COLUMN ORDERS.O_TOTALPRICE IS '總價';
    COMMENT ON COLUMN ORDERS.O_ORDERDATE IS '下單日期';
    COMMENT ON COLUMN ORDERS.O_ORDERPRIORITY IS '訂單優先順序';
    COMMENT ON COLUMN ORDERS.O_CLERK IS '收銀員';
    COMMENT ON COLUMN ORDERS.O_SHIPPRIORITY IS '發貨優先順序';
    COMMENT ON COLUMN ORDERS.O_COMMENT IS '備忘';
    
    COMMIT;
    
    -- 資料匯入內表
    INSERT INTO ORDERS SELECT * FROM ORDERS_10g;
    • 計算ORDERS表的O_CUSTKEY列去重後行數。

      SELECT UNIQ ( O_CUSTKEY ) FROM ORDERS;

      返回結果如下。

        uniq
      --------
       999982
    • 計算ORDERS表的O_CUSTKEY列,不同O_ORDERSTATUS下O_CUSTKEY去重後行數。

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

      返回結果如下。

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

VARIANCE

  • 描述:計算INT、NUMERIC類型運算式的樣本方差。

    VARIANCE(expression)
  • 參數說明

    expression:必填,INT、NUMERIC類型運算式。

  • 傳回值說明

    返回NUMERIC類型。

  • 樣本

    SELECT VARIANCE(c1) FROM example_table;

    返回結果如下。

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

VAR_POP

  • 描述:計算FLOAT8、INT或NUMERIC類型運算式的總體方差。

    VAR_POP(expression)
  • 參數說明

    expression:必填,FLOAT8、INT或NUMERIC類型運算式。

  • 傳回值說明

    返回NUMERIC或FLOAT8類型。

  • 樣本

    SELECT VAR_POP(c1) FROM example_table;

    返回結果如下。

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

VAR_SAMP

  • 描述:計算INT、FLOAT8或NUMERIC類型運算式的樣本方差。

    VAR_SAMP(expression)
  • 參數說明

    expression:必填,INT、FLOAT8或NUMERIC類型運算式。

  • 傳回值說明

    返回NUMERIC或FLOAT8類型。

  • 樣本

    SELECT VAR_SAMP(c1) FROM example_table;

    返回結果如下。

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