Skip to content

Instantly share code, notes, and snippets.

@fuCtor
Last active December 5, 2019 08:08
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save fuCtor/cd51dc0313d6652469c76b8fa6bda939 to your computer and use it in GitHub Desktop.
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