Skip to content

Instantly share code, notes, and snippets.

@fuCtor
Last active Dec 5, 2019
Embed
What would you like to do?
select version()
SELECT version()
┌─version()─┐
│ 19.15.3.6 │
└───────────┘
1 rows in set. Elapsed: 0.008 sec.
create table src (
:-] ts DateTime,
:-] foo Int32,
:-] bar Int32
:-] )
:-] ENGINE = MergeTree()
:-] Order BY tuple()
CREATE TABLE src
(
`ts` DateTime,
`foo` Int32,
`bar` Int32
)
ENGINE = MergeTree()
ORDER BY tuple()
Ok.
0 rows in set. Elapsed: 0.092 sec.
create table dst (
:-] ts DateTime,
:-] foo AggregateFunction(sum, Int32),
:-] bar AggregateFunction(sum, Int32)
:-] )
:-] ENGINE = AggregatingMergeTree()
:-] Order BY tuple()
CREATE TABLE dst
(
`ts` DateTime,
`foo` AggregateFunction(sum, Int32),
`bar` AggregateFunction(sum, Int32)
)
ENGINE = AggregatingMergeTree()
ORDER BY tuple()
Ok.
0 rows in set. Elapsed: 0.027 sec.
INSERT INTO src SELECT now() - number, rand() % 10, rand() % 10 FROM system.numbers limit 10000
INSERT INTO src SELECT
now() - number,
rand() % 10,
rand() % 10
FROM system.numbers
LIMIT 10000
Ok.
0 rows in set. Elapsed: 0.002 sec. Processed 10.00 thousand rows, 80.00 KB (4.24 million rows/s., 33.96 MB/s.)
select ts, sumMerge(foo) from dst group by ts
SELECT
ts,
sumMerge(foo)
FROM dst
GROUP BY ts
┌──────────────────ts─┬─sumMerge(foo)─┐
│ 2019-12-05 05:00:00 │ 24014 │
│ 2019-12-05 06:00:00 │ 32310 │
│ 2019-12-05 08:00:00 │ 1100 │
│ 2019-12-05 07:00:00 │ 32508 │
└─────────────────────┴───────────────┘
4 rows in set. Elapsed: 0.003 sec.
optimize table dst final
OPTIMIZE TABLE dst FINAL
Ok.
0 rows in set. Elapsed: 0.015 sec.
select ts, sumMerge(foo) from dst group by ts
SELECT
ts,
sumMerge(foo)
FROM dst
GROUP BY ts
┌──────────────────ts─┬─sumMerge(foo)─┐
│ 2019-12-05 05:00:00 │ 89932 │
└─────────────────────┴───────────────┘
1 rows in set. Elapsed: 0.001 sec.
create table src (
ts DateTime,
foo Int32,
bar Int32
)
ENGINE = MergeTree()
Order BY tuple()
create table dst (
ts DateTime,
foo AggregateFunction(sum, Int32),
bar AggregateFunction(sum, Int32)
)
ENGINE = AggregatingMergeTree()
Order BY tuple()
INSERT INTO src SELECT
now() - number,
rand() % 10,
rand() % 10
FROM system.numbers
limit 10000
INSERT INTO dst SELECT
toStartOfHour(ts) as ts,
sumState(foo) as foo,
sumState(bar) as bar
FROM src
GROUP BY
ts
select ts, sumMerge(foo) from dst group by ts
optimize table dst final
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment