hologres でサポートされている clickhouse 関数とその使用方法 - Hologres

Hologres は、一部の ClickHouse 関数と、同じセマンティクスを持つ他の関数をサポートしています。このトピックでは、サポートされている ClickHouse 関数、その使用方法、および同等のセマンティクスを持つ関数を使用して共通関数を再書き込みする方法について説明します。

文字列関数

length、lower、upper、concat、substring、trim などの一般的な ClickHouse 文字列関数の構文は、Hologres でもほぼ同じです。詳細については、「文字列関数」をご参照ください。

数学関数

cbrt、degrees、exp、ln、log、pi、radians、sign、sqrt などの一般的な ClickHouse 数学関数の構文は、Hologres でもほぼ同じです。詳細については、「数学関数」をご参照ください。

型変換関数

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 precision 型に変換します。
戻り値の型: double precision。

使用例:

SELECT toFloat64(data_float) from public.tb1;

-- 結果:
1.23

時間と日付の関数

Hologres の時間と日付の関数とその使用方法の詳細については、「時間と日付の変換関数」をご参照ください。

toYear、toMonth、toQuarter など

説明: タイムスタンプから年、月、四半期などのサブフィールドを抽出します。
Hologres の実装: Hologres V4.0 以降では、同じ名前の ClickHouse 関数が直接サポートされています。V4.0 より前のバージョンでは、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 の複数の異なる値に対応する場合、関数はそれらの x 値の最小値を返します。
- Hologres V1.3.36 以降でサポートされています。
使用例: test テーブルの CREATE TABLE 文については、「例」をご参照ください。
```
SELECT min_by(name, cost) FROM test;

-- 結果:
 min_by
--------
 cc
(1 行)
```

argMax(x, y)

説明: 列 y の最大値に対応する列 x の値を計算します。
Hologres の実装: max_by(x, y)。
説明
- 列 y の最大値が列 x の複数の異なる値に対応する場合、関数はそれらの x 値の最大値を返します。
- Hologres V1.3.36 以降でサポートされています。

使用例: test テーブルの CREATE TABLE 文については、「例」をご参照ください。

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

-- 結果:
 id | max_by
----+--------
  2 | bb
  1 | aaa
  3 | c
(3 行)

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

配列関数

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

arrayMin(anyarray)

説明: 配列要素の最小値を返します。
Hologres の実装: array_min(anyarray)。
説明
Hologres V1.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 行)

arrayMax(anyarray)

説明: 配列要素の最大値を返します。
Hologres の実装: array_max(anyarray)。
説明
Hologres V1.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 行)

arraySum(anyarray)

説明: 配列要素の合計を返します。
Hologres での実装:
- unnest 関数
- func 関数
- Sum。

使用例:

SELECT sum(ele) FROM (SELECT unnest(ARRAY[1,2,4]) AS ele) a;

-- 戻り値:
7

ビットマップ関数

Hologres の Roaring Bitmap 関数の詳細とその使用方法については、「Roaring Bitmap 関数」をご参照ください。

bitmapToArray(roaringbitmap)

説明: ビットマップに対応する整数配列を返します。
Hologres の実装: rb_to_array(roaringbitmap)。

使用例:

SELECT rb_to_array(rb_build('{1,2,3}'));

-- 結果:
{1,2,3}

groupBitmapState(integer)

説明: 式の値をビットマップに集約します。
Hologres の実装: rb_build_agg(integer)。

使用例:

SELECT rb_build_agg(1);

-- 結果:
\x3a3000000100000000000000100000000100

groupBitmap(integer)

説明: 式の値から集約されたビットマップのカーディナリティを計算します。
Hologres の実装: まず、rb_build_agg 関数を使用して式の値をビットマップに集約します。次に、rb_cardinality 関数を使用してカーディナリティを計算します: rb_cardinality(rb_build_agg(integer))。

使用例:

SELECT rb_cardinality(rb_build_agg(1));

-- 結果:
1

groupBitmapAndState(roaringbitmap)

説明: ビットマップに対して AND 集約を実行します。
Hologres の実装: rb_and_agg(roaringbitmap)。

使用例:

SELECT rb_and_agg(rb_build('{1,2,3}'));

-- 結果:
\x3a300000010000000000020010000000010002000300

groupBitmapOrState(roaringbitmap)

説明: ビットマップに対して OR 集約を実行します。
Hologres の実装: rb_or_agg(roaringbitmap)。

使用例:

SELECT rb_or_agg(rb_build('{1,2,3}'));

-- 結果:
\x3a300000010000000000020010000000010002000300

groupBitmapAnd(roaringbitmap)

説明: ビットマップに対して AND 集約を実行し、カーディナリティを返します。
Hologres の実装: rb_and_cardinality_agg(roaringbitmap)。

使用例:

SELECT rb_and_cardinality_agg(rb_build('{1,2,3}'));

-- 結果:
3

groupBitmapOr(roaringbitmap)

説明: ビットマップに対して OR 集約を実行し、カーディナリティを返します。
Hologres の実装: rb_or_cardinality_agg(roaringbitmap)。

使用例:

SELECT rb_or_cardinality_agg(rb_build('{1,2,3}'));

-- 結果:
3

ハッシュ関数

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 は多くの関数と互換性があります。文字列関数と数学関数に加えて、PostgreSQL は ClickHouse の関数と同じ構文を使用する他の多くの関数を提供します。関数とその使用方法の詳細については、「PostgreSQL」をご参照ください。