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_DISTINCT、UNIQ、MAX_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。