mirror of
https://github.com/GreptimeTeam/greptimedb.git
synced 2026-09-13 08:52:15 +00:00
discord9
7252ceb4bb
* feat(function): add internal delta merge aggregates Signed-off-by: discord9 <55937128+discord9@users.noreply.github.com> * test(function): cover delta merge aggregates in SQL Signed-off-by: discord9 <55937128+discord9@users.noreply.github.com> * feat(function): add Welford delta merge aggregate Signed-off-by: discord9 <55937128+discord9@users.noreply.github.com> --------- Signed-off-by: discord9 <55937128+discord9@users.noreply.github.com>
165 lines
6.3 KiB
Plaintext
165 lines
6.3 KiB
Plaintext
CREATE TABLE test_uddsketch (
|
|
`id` INT PRIMARY KEY,
|
|
`value` DOUBLE,
|
|
`ts` timestamp time index default now()
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO test_uddsketch (`id`, `value`) VALUES
|
|
(1, 10.0),
|
|
(2, 20.0),
|
|
(3, 30.0),
|
|
(4, 40.0),
|
|
(5, 50.0),
|
|
(6, 60.0),
|
|
(7, 70.0),
|
|
(8, 80.0),
|
|
(9, 90.0),
|
|
(10, 100.0);
|
|
|
|
Affected Rows: 10
|
|
|
|
select uddsketch_calc(0.1, uddsketch_state(128, 0.01, `value`)) from test_uddsketch;
|
|
|
|
+---------------------------------------------------------------------------------------------+
|
|
| uddsketch_calc(Float64(0.1),uddsketch_state(Int64(128),Float64(0.01),test_uddsketch.value)) |
|
|
+---------------------------------------------------------------------------------------------+
|
|
| 19.886670240866184 |
|
|
+---------------------------------------------------------------------------------------------+
|
|
|
|
select uddsketch_calc(0.5, uddsketch_state(128, 0.01, `value`)) from test_uddsketch;
|
|
|
|
+---------------------------------------------------------------------------------------------+
|
|
| uddsketch_calc(Float64(0.5),uddsketch_state(Int64(128),Float64(0.01),test_uddsketch.value)) |
|
|
+---------------------------------------------------------------------------------------------+
|
|
| 59.745049810145126 |
|
|
+---------------------------------------------------------------------------------------------+
|
|
|
|
select uddsketch_calc(0.75, uddsketch_state(128, 0.01, `value`)) from test_uddsketch;
|
|
|
|
+----------------------------------------------------------------------------------------------+
|
|
| uddsketch_calc(Float64(0.75),uddsketch_state(Int64(128),Float64(0.01),test_uddsketch.value)) |
|
|
+----------------------------------------------------------------------------------------------+
|
|
| 80.648188221533 |
|
|
+----------------------------------------------------------------------------------------------+
|
|
|
|
select uddsketch_calc(0.95, uddsketch_state(128, 0.01, `value`)) from test_uddsketch;
|
|
|
|
+----------------------------------------------------------------------------------------------+
|
|
| uddsketch_calc(Float64(0.95),uddsketch_state(Int64(128),Float64(0.01),test_uddsketch.value)) |
|
|
+----------------------------------------------------------------------------------------------+
|
|
| 100.49456770856492 |
|
|
+----------------------------------------------------------------------------------------------+
|
|
|
|
SELECT
|
|
ROUND(uddsketch_rank(5, `state`), 4) AS below_min_rank,
|
|
ROUND(uddsketch_rank(10, `state`), 4) AS equal_min_rank,
|
|
ROUND(uddsketch_rank(55, `state`), 4) AS middle_rank,
|
|
ROUND(uddsketch_rank(100, `state`), 4) AS equal_max_rank,
|
|
ROUND(uddsketch_rank(110, `state`), 4) AS above_max_rank
|
|
FROM (
|
|
SELECT uddsketch_state(128, 0.01, `value`) AS `state`
|
|
FROM test_uddsketch
|
|
);
|
|
|
|
+----------------+----------------+-------------+----------------+----------------+
|
|
| below_min_rank | equal_min_rank | middle_rank | equal_max_rank | above_max_rank |
|
|
+----------------+----------------+-------------+----------------+----------------+
|
|
| 0.0 | 0.05 | 0.5 | 0.95 | 1.0 |
|
|
+----------------+----------------+-------------+----------------+----------------+
|
|
|
|
CREATE TABLE grouped_uddsketch (
|
|
`state` BINARY,
|
|
id_group INT PRIMARY KEY,
|
|
`ts` timestamp time index default now()
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO grouped_uddsketch (`state`, id_group) SELECT uddsketch_state(128, 0.01, `value`), `id`/5*5 as id_group FROM test_uddsketch GROUP BY id_group;
|
|
|
|
Affected Rows: 3
|
|
|
|
SELECT uddsketch_calc(0.1, uddsketch_merge(128, 0.01, `state`)) FROM grouped_uddsketch;
|
|
|
|
+------------------------------------------------------------------------------------------------+
|
|
| uddsketch_calc(Float64(0.1),uddsketch_merge(Int64(128),Float64(0.01),grouped_uddsketch.state)) |
|
|
+------------------------------------------------------------------------------------------------+
|
|
| 19.886670240866184 |
|
|
+------------------------------------------------------------------------------------------------+
|
|
|
|
SELECT ROUND(uddsketch_rank(55, uddsketch_merge(128, 0.01, `state`)), 4) AS merged_rank
|
|
FROM grouped_uddsketch;
|
|
|
|
+-------------+
|
|
| merged_rank |
|
|
+-------------+
|
|
| 0.5 |
|
|
+-------------+
|
|
|
|
CREATE TABLE delta_uddsketch (
|
|
`id` INT PRIMARY KEY,
|
|
`delta` BINARY,
|
|
`persisted` BINARY,
|
|
`ts` timestamp time index default now()
|
|
);
|
|
|
|
Affected Rows: 0
|
|
|
|
INSERT INTO delta_uddsketch (`id`, `delta`, `persisted`)
|
|
SELECT 1, uddsketch_state(128, 0.01, `value`), NULL
|
|
FROM test_uddsketch WHERE `id` <= 5;
|
|
|
|
Affected Rows: 1
|
|
|
|
INSERT INTO delta_uddsketch (`id`, `delta`, `persisted`)
|
|
SELECT 2, NULL, uddsketch_state(128, 0.01, `value`)
|
|
FROM test_uddsketch WHERE `id` > 5;
|
|
|
|
Affected Rows: 1
|
|
|
|
SELECT
|
|
uddsketch_calc(0.5, __uddsketch_state_delta_merge(128, 0.01, `delta`, `persisted`)) AS merged_p50,
|
|
(SELECT uddsketch_calc(0.5, uddsketch_state(128, 0.01, `value`)) FROM test_uddsketch) AS direct_p50
|
|
FROM delta_uddsketch;
|
|
|
|
+--------------------+--------------------+
|
|
| merged_p50 | direct_p50 |
|
|
+--------------------+--------------------+
|
|
| 59.745049810145126 | 59.745049810145126 |
|
|
+--------------------+--------------------+
|
|
|
|
-- should fail
|
|
SELECT uddsketch_calc(0.1, uddsketch_merge(128, 0.1, `state`)) FROM grouped_uddsketch;
|
|
|
|
Error: 3001(EngineExecuteQuery), Error during planning: Merging UDDSketch with different parameters: arguments=(128, 0.1) vs actual input=(128, 0.01)
|
|
|
|
-- should fail
|
|
SELECT uddsketch_calc(0.1, uddsketch_merge(64, 0.01, `state`)) FROM grouped_uddsketch;
|
|
|
|
Error: 3001(EngineExecuteQuery), Error during planning: Merging UDDSketch with different parameters: arguments=(64, 0.01) vs actual input=(128, 0.01)
|
|
|
|
SELECT uddsketch_rank(55, uddsketch_state(128, 0.01, `value`)) IS NULL AS empty_rank_is_null
|
|
FROM test_uddsketch
|
|
WHERE `id` < 0;
|
|
|
|
+--------------------+
|
|
| empty_rank_is_null |
|
|
+--------------------+
|
|
| true |
|
|
+--------------------+
|
|
|
|
drop table test_uddsketch;
|
|
|
|
Affected Rows: 0
|
|
|
|
drop table grouped_uddsketch;
|
|
|
|
Affected Rows: 0
|
|
|
|
drop table delta_uddsketch;
|
|
|
|
Affected Rows: 0
|
|
|