全部產品
Search
文件中心

:Clickhouse相容函數

更新時間:Jun 30, 2024

Hologres已支援部分Clickhouse函數或其同語義函數。本文為您介紹Clickhouse函數在Hologres中的支援情況與使用方法,以及部分常見函數的同語義改寫方法。

字串函數

Clickhouse常用字串函數與Hologres文法基本一致,包括length、lower、upper、concat、substring、trim等函數,具體函數及使用方法請參見字串函數

數學函數

Clickhouse常用數學函數與Hologres文法基本一致,包括cbrt、degrees、exp、ln、log、pi、radians、sign、sqrt等函數,具體函數及使用方法請參見數學函數

類型轉換函式

Hologres從V1.3.36版本起支援如下Clickhouse類型轉換函式,暫不支援常量入參。其他資料類型轉換情境請使用CAST(col AS type)col::type文法。

建表及資料插入語句如下,函數用例基於此表進行查詢。

CREATE TABLE public.tb1 (
 id bigint NOT NULL,
 id_text text,
 data_date text,
 data_float text,
 data_text text
);
INSERT INTO public.tb1 VALUES (1234,'123','20190102','1.23','hologres');

toString(anyelement)

  • 描述將任一類型轉換為text類型。

  • 傳回型別:text。

  • 使用樣本:

    SELECT toString(id) from public.tb1;
    
    --返回結果
    1234

toInt64(anyelement)

  • 描述將其他數字類型轉換為bigint類型。

    說明

    如果非數字類型使用此函數進行轉換,資料類型不相容將會導致報錯。

  • 傳回型別:bigint。

  • 使用樣本:

    SELECT toInt64(id_text) from public.tb1;
    
    --返回結果
    1234

toInt32(anyelement)

  • 描述將其他數字類型轉換為int類型。

    說明

    如果非數字類型使用此函數進行轉換,資料類型不相容將會導致報錯。

  • 傳回型別:int。

  • 使用樣本:

    SELECT toInt32(id_text) from public.tb1;
    
    --返回結果
    123

toDate(text)

  • 描述將text類型轉換為date類型。

  • 傳回型別:date。

  • 使用樣本:

    • 轉換成功。

      SELECT toDate(data_date) from public.tb1;
      
      --返回結果
      2019-01-02

    • 資料類型不相容導致報錯。

      SELECT toDate(data_text) from public.tb1;
      
      --返回結果
      ERROR: *** invalid input syntax for type date ***

toFloat64(anyelement)

  • 描述將其他數字類型轉換為double類型。

  • 傳回型別:double precision。

  • 使用樣本:

    SELECT toFloat64(data_float) from public.tb1;
    
    --返回結果
    1.23

時間和日期函數

Hologres更多時間和日期函數及其使用方法請參見時間和日期轉換函式

toYear、toMonth、toQuarter等

  • 描述從時間戳記中擷取年、月、季度等子欄位。

  • Hologres實現:extract(field from timestamp)date_part(text, timestamp),詳情請參見時間日期截取函數

  • 使用樣本:

    • SELECT extract(month FROM timestamp '2001-02-16 20:38:40');
      
      --返回結果
      2

    • SELECT extract(quarter FROM timestamp '2001-02-16 20:38:40');
      
      --返回結果
      1

addDays、addMonths、addYears等

  • 描述將時間戳記增加一段時間間隔。

  • Hologres實現:操作符+

  • 使用樣本:

    SELECT date '2001-09-28' + interval '1 hour';
    
    --返回結果
    2001-09-28 01:00:00

subtractDays、subtractMonths、subtractYears等

  • 描述將時間戳記減去一段時間間隔。

  • Hologres實現:操作符-

  • 使用樣本:

    SELECT date '2001-09-28' - interval '1 day';
    
    --返回結果
    2001-09-27 00:00:00

彙總函式

Hologres更多彙總函式及其使用方法請參見通用彙總函式APPROX_COUNT_DISTINCTUNIQMAX_BY與MIN_BY

argMin(x, y)

  • 描述:計算y列最小值對應x列的值。

  • Hologres實現:min_by(x, y)。

    說明
    • 當y列最小值對應多個不同的x值,輸出其中的最小值。

    • Hologres V1.3.36版本起支援。

  • 使用樣本:test 表建表語句請參見樣本

    SELECT min_by(name, cost) FROM test;
    
    --返回結果
     min_by
    --------
     cc
    (1 row)

argMax(x, y)

  • 描述:計算y列最大值對應x列的值。

  • Hologres實現:max_by(x, y)。

    說明
    • 當y列最大值對應多個不同的x值,輸出其中的最大值。

    • Hologres V1.3.36版本起支援。

  • 使用樣本:test 表建表語句請參見樣本

    SELECT id, max_by(name, cost) FROM test GROUP BY id;
    
    --返回結果
     id | max_by
    ----+--------
      2 | bb
      1 | aaa
      3 | c
    (3 rows)

groupArray(anyelement)

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

  • Hologres實現:array_agg(anyelement)。

  • 使用樣本:

    CREATE TABLE test_array_agg_int (c1 int);
    INSERT INTO test_array_agg_int VALUES (1), (2);
    
    SELECT array_agg (c1) FROM test_array_agg_int;
    
    --返回結果
     array_agg 
    -----------
     {1,2}
    (1 row)

數組函數

Hologres更多數組函數及其使用方法請參見數組函數

arrayJoin(anyarray)

  • 描述:將數組的每個元素擴充到單獨行。

  • Hologres實現:unnest(anyarray) 。

  • 使用樣本:

    SELECT unnest(ARRAY[1,2]);
    
    --返回結果
    1
    2

arrayConcat(anyarray, anyarray...)

  • 描述:合并所有數組。

  • Hologres實現:array_cat(anyarray,anyarray),支援合并2個數組。

  • 使用樣本:

    SELECT array_cat(array_cat(ARRAY[1,2], ARRAY[3,4]), ARRAY[5,6]);
    
    --返回結果
    {1,2,3,4,5,6}

arrayDistinct(anyarray)

  • 描述:返回數組中去重後的元素組成的新數組。

  • Hologres實現:array_distinct(anyarray)。

    說明

    Hologres V1.3.19版本起支援array_distinct函數,暫不支援常量入參。

  • 使用樣本:

    CREATE TABLE test_array_distinct_text ( c1 text[]);
    INSERT INTO test_array_distinct_text
        VALUES (ARRAY['holo', 'hello', 'holo', 'SQL', 'SQL']), (ARRAY[]::text[]);
    
    SELECT c1, array_distinct (c1) FROM test_array_distinct_text;
    
    --返回結果
                c1             |  array_distinct
    ---------------------------+------------------
     {holo,hello,holo,SQL,SQL} | {SQL,hello,holo}
     {}                        | {NULL}
    (2 rows)

arrayMin(anyarray)

  • 描述:返回數組元素的最小值。

  • Hologres實現:array_min(anyarray)。

    說明

    Hologres 1.3.19起支援array_min函數,暫不支援常量入參。

  • 使用樣本:

    CREATE TABLE test_array_min_text ( c1 text[]);
    INSERT INTO test_array_min_text
        VALUES (NULL), (ARRAY['hello', 'holo', 'blackhole', 'array']);
    
    SELECT c1, array_min (c1) FROM test_array_min_text;
    
    --返回結果
                  c1              | array_min
    ------------------------------+-----------
                                  |
     {hello,holo,blackhole,array} | array
    (2 rows)

arrayMax(anyarray)

  • 描述:返回數組元素的最大值。

  • Hologres實現:array_max(anyarray)。

    說明

    Hologres 1.3.19起支援array_max函數,暫不支援常量入參。

  • 使用樣本:

    CREATE TABLE test_array_max_int ( c1 int[]);
    INSERT INTO test_array_max_int VALUES (NULL), (ARRAY[-2, NULL, -3, -12, -7]);
    
    SELECT c1, array_max (c1)FROM test_array_max_int;
    
    --返回結果
            c1        | array_max
    ------------------+-----------
                      |
     {-2,0,-3,-12,-7} |         0
    (2 rows)

arraySum(anyarray)

  • 描述:返回數組元素的和。

  • Hologres實現:

    • uunestFunction Compute。

    • funcFunction Compute。

    • 求和。

  • 使用樣本:

    SELECT sum(ele) FROM (SELECT unnest(ARRAY[1,2,4]) AS ele) a;
    
    --返回結果
    7

Bitmap函數

Hologres更多Bitmap函數及其使用方法請參見Roaring Bitmap函數

bitmapToArray(roaringbitmap)

  • 描述:返回Bitmap對應整型數組。

  • Hologres實現:rb_to_array(roaringbitmap)。

  • 使用樣本:

    SELECT rb_to_array(rb_build('{1,2,3}'));
    
    --返回結果
    {1,2,3}

groupBitmapState(integer)

  • 描述:將運算式的值彙總成Bitmap。

  • Hologres實現:rb_build_agg(integer)。

  • 使用樣本:

    SELECT rb_build_agg(1);
    
    --返回結果
    \x3a3000000100000000000000100000000100

groupBitmap(integer)

  • 描述:計算運算式的值彙總成的bitmap的基數。

  • Hologres實現:先使用rb_build_agg函數將運算式的值彙總成bitmap,再使用rb_cardinalityFunction Compute基數:rb_cardinality(rb_build_agg(integer))

  • 使用樣本:

    SELECT rb_cardinality(rb_build_agg(1));
    
    --返回結果
    1

groupBitmapAndState(roaringbitmap)

  • 描述:Bitmap的And彙總計算。

  • Hologres實現:rb_and_agg(roaringbitmap)。

  • 使用樣本:

    SELECT rb_and_agg(rb_build('{1,2,3}'));
    
    --返回結果
    \x3a300000010000000000020010000000010002000300

groupBitmapOrState(roaringbitmap)

  • 描述:Bitmap的Or彙總計算。

  • Hologres實現:rb_or_agg(roaringbitmap)。

  • 使用樣本:

    SELECT rb_or_agg(rb_build('{1,2,3}'));
    
    --返回結果
    \x3a300000010000000000020010000000010002000300

groupBitmapAnd(roaringbitmap)

  • 描述:Bitmap的And彙總計算並返回其基數。

  • Hologres實現:rb_and_cardinality_agg(roaringbitmap)。

  • 使用樣本:

    SELECT rb_and_cardinality_agg(rb_build('{1,2,3}'));
    
    --返回結果
    3

groupBitmapOr(roaringbitmap)

  • 描述:Bitmap的Or彙總計算並返回其基數。

  • Hologres實現:rb_or_cardinality_agg(roaringbitmap)。

  • 使用樣本:

    SELECT rb_or_cardinality_agg(rb_build('{1,2,3}'));
    
    --返回結果
    3

Hash函數

sipHash64(text)

  • 描述:返回text對應的64位SipHash值,傳回型別為UInt64。

  • Hologres實現:hg_sip_hash_64(text)。

    說明
    • 傳回型別為BIGINT。

    • Hologres V2.0.1版本起支援sipHash64函數。

    • 暫不支援常量入參。

  • 使用樣本:

    CREATE TABLE test_hg_sip_hash_64_text (c1 text);
    INSERT INTO test_hg_sip_hash_64_text VALUES ('abc');
    
    SELECT hg_sip_hash_64 (c1) FROM test_hg_sip_hash_64_text;
    
    --返回結果
    4596069200710135518

其他函數

PostgreSQL相容函數眾多,除字串函數、數學函數外,還有很多函數與Clickhouse文法一致。更多函數及使用方法請參見PostgreSQL