usain_bolt.sql

-- run it with: cat usain_bolt.sql | clickohuse-client -mn

drop database deleteme_alrocar;
create database deleteme_alrocar;

CREATE TABLE deleteme_alrocar.deleteme
(
    `number` UInt64,
    `key` LowCardinality(String),
    `timestamp` DateTime
)
ENGINE = MergeTree
ORDER BY timestamp AS
SELECT
    number,
    toString(number % 10000) as key,
    toDateTime64(now(), 3) - (number /10)
FROM numbers(1000000000);

CREATE TABLE deleteme_alrocar.deleteme_landing
(
    `number` UInt64,
    `key` LowCardinality(String),
    `timestamp` DateTime
)
ENGINE = Null;

CREATE TABLE deleteme_alrocar.deleteme_agg_day
(
    `avg_number` AggregateFunction(avg, UInt64),
    `c` AggregateFunction(count),
    `sum_number` AggregateFunction(sum, UInt64),
    `date` Date,
    `key` LowCardinality(String)

)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (key, date);

create materialized view deleteme_alrocar.deleteme_agg_mv TO deleteme_alrocar.deleteme_agg_day as select toDate(timestamp) date, key, avgState(number) as avg_number, countState() as c, sumState(number) as sum_number from deleteme_alrocar.deleteme_landing group by date, key;

CREATE TABLE deleteme_alrocar.deleteme_agg_month
(
    `avg_number` AggregateFunction(avg, UInt64),
    `c` AggregateFunction(count),
    `sum_number` AggregateFunction(sum, UInt64),
    `date` Date,
    `key` LowCardinality(String)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (key, date);

create materialized view deleteme_alrocar.deleteme_agg_month_mv TO deleteme_alrocar.deleteme_agg_month as select toStartOfMonth(timestamp) date, key, avgState(number) as avg_number, countState() as c, sumState(number) as sum_number from deleteme_alrocar.deleteme_landing group by date, key;


CREATE TABLE deleteme_alrocar.deleteme_sum_day
(
    `avg_number` UInt64,
    `c` UInt64,
    `sum_number` UInt64,
    `date` Date,
    `key` LowCardinality(String)
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (key, date);

create materialized view deleteme_alrocar.deleteme_sum_mv TO deleteme_alrocar.deleteme_sum_day as select toDate(timestamp) date, key, avg(number) as avg_number, count() as c, sum(number) as sum_number from deleteme_alrocar.deleteme_landing group by date, key;

CREATE TABLE deleteme_alrocar.deleteme_sum_month
(
    `avg_number` UInt64,
    `c` UInt64,
    `sum_number` UInt64,
    `date` Date,
    `key` LowCardinality(String)
)
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(date)
ORDER BY (key, date);

create materialized view deleteme_alrocar.deleteme_sum_month_mv TO deleteme_alrocar.deleteme_sum_month as select toStartOfMonth(timestamp) date, key, avg(number) as avg_number, count() as c, sum(number) as sum_number from deleteme_alrocar.deleteme_landing group by date, key;

insert into deleteme_alrocar.deleteme_landing select * from deleteme_alrocar.deleteme order by timestamp asc;