Last active
December 5, 2019 08:08
-
-
Save fuCtor/cd51dc0313d6652469c76b8fa6bda939 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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