den-crane

drop table replacing_test;

create table replacing_test(userid Int64, propery0 String, property1 Int8, deleted UInt8 default 0)
ENGINE = ReplacingMergeTree  ORDER BY (userid);

insert into replacing_test select  number, concat('propery',toString(number)), number+4,0 from system.numbers limit 500000000;
insert into replacing_test select  number, concat('propery',toString(number)), number+4,0 from system.numbers limit 5000;

OPTIMIZE table replacing_test PARTITION tuple() final;

den-crane

DROP TABLE IF EXISTS requests;

CREATE TABLE requests (
  request_date Date, 
  request_time DateTime, 
  response_time Int, 
  request_uri String) 
ENGINE  = MergeTree(request_date, (request_time, request_uri), 8192);

den-crane

# cat /etc/metrika.xml
<yandex>
      <dictmysql1>
             <port>3306</port>
	     <connect_timeout>3600</connect_timeout>
	     <rw_timeout>3600</rw_timeout>
             <user>userx</user>
             <password>passwordx</password>
             <host>hostx</host>
             <priority>1</priority>

den-crane

create table test(a Int64, b Int64, c Int64, d String) 
engine=MergeTree partition by tuple() order by (a,b,c);

insert into test select 1, 0, number, toString(number) from numbers(1000000);
insert into test select 2, 2, number, toString(number) from numbers(100);
insert into test select 3, 3, number, toString(number) from numbers(1000000);

select count() from test where a=2 and c=1;
1 rows in set. Elapsed: 0.002 sec.

den-crane

drop table tg
create table tg (a Int64, b Int64, c Int64) Engine = TinyLog;


insert into tg values (1,1433419200,15),(1,1433332800,23),(1,1433246400,41),(1,1433160000,55),
(1,1432900800,24),(2,1433419200,52),(2,1433332800,23),(2,1433246400,39),
(2,1433160000,22),(3,1433419200,11),(3,1433246400,58)

select a,tupleElement(x,1) b, tupleElement(x,2) c from (
select a,arrayJoin(arraySlice(groupArray(tuple(b,c)),1,3)) x from tg group by a)

den-crane

drop table z; 
drop table mvz;
create table z(d Date, u String) Engine=MergeTree partition by tuple() order by tuple();
CREATE MATERIALIZED VIEW mvz ENGINE = AggregatingMergeTree(d, (d), 8192) as select d, uniqState(u) as us from z group by d
insert into z select today()-number%571, concat('usr',toString(rand()%664579)) from numbers(100000000);
optimize table mvz final;
optimize table z final;

select  (uniqMerge(us)) as unique from mvz group by d order by d;
571 rows in set. Elapsed: 0.300 sec.

den-crane

create table z(a date, b Int64) Engine=MergeTree Partition by toYYYYMM(a) order by a;
insert into z select today(), number from numbers(1000000000);
insert into z select yesterday(), number from numbers(1000);

create table mv_z_store(a date, max_b AggregateFunction(MAX,Int64)) ENGINE = AggregatingMergeTree Partition by toYYYYMM(a) order by a;
create table temp(a date, b Int64) Engine=Null;
create MATERIALIZED VIEW mv_z to mv_z_store AS SELECT a, maxState(b) AS max_b FROM temp GROUP BY a;
insert into temp select * from z;
drop table mv_z;
drop table temp;

den-crane

DROP TABLE IF EXISTS states_raw;
DROP TABLE IF EXISTS final_states_by_day;
DROP TABLE IF EXISTS final_states_by_day_mv;

CREATE TABLE states_raw
(
    process String,
    state String,
    stateint Int64,
    statevalue Float64,

den-crane

ClickHouse server version 18.14.12 revision 54409.


create table data(K Int64, V String) engine=MergeTree order by K;
insert into data select number, toString(number) from numbers(100,100000000);
optimize table data final;
create table buffer(K Int64, V String) engine=Memory;
insert into buffer select number, toString(number) from numbers(0,1000);

den-crane

create table test (p Int64, t Int64, x String, y Int64) engine=MergeTree order by (p,t)
insert into test select intDiv(number,10000), rand() r, toString(r), number from numbers(10000000);

select count(), min(x) from (
select * from test order by p, t limit 1 by p
)

┌─count()─┬─min(x)─┐
│    1000 │ 100420 │
└─────────┴────────┘