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

truncate table logs;
truncate table agg_table;


CREATE TABLE logs(
    date Date,
    ts DateTime,
    groupId Int8,
    subGroupId Int8) ENGINE = MergeTree() PARTITION BY date ORDER BY (groupId);

den-crane

# 103 buckets (10007 buckets do not work).

create table foo_h(user_id String, date Date, val UInt64, h UInt16) engine=MergeTree order by (h, date);

insert into foo_h select toString(cityHash64(toString(number))) user_id, '2018-12-14', number, cityHash64(toString(number)) % 103  from numbers(50000000);
insert into foo_h select toString(cityHash64(toString(number))) user_id, '2018-12-13', number, cityHash64(toString(number)) % 103  from numbers(50000000);
insert into foo_h select toString(cityHash64(toString(number))) user_id, '2018-12-12', number, cityHash64(toString(number)) % 103  from numbers(5);

select count() from foo_h prewhere user_id = '10408321403207385874';
1 rows in set. Elapsed: 0.695 sec. Processed 100.00 million rows, 

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

drop table fact;
drop table animals;
drop table colors;

create table fact(id Int64, animal_key Int64, color_key Int64) Engine = MergeTree order by tuple();
insert into fact values (1,1,1),(2,2,2);

create table animals(animal_key UInt64, animal_name String) Engine = MergeTree order by tuple();
insert into animals values (0, 'unknown');

den-crane

create table tableA (A String) Engine=MergeTree order by tuple();
create table tableB (B String) Engine=MergeTree order by tuple();
create table tableC (C String) Engine=Null;
create table storeABC(ABC String) Engine=MergeTree order by tuple();
create materialized view MVA to storeABC as select A ABC from tableA;
create materialized view MVB to storeABC as select B ABC from tableB;
create materialized view MVC to storeABC as select C ABC from tableC;
insert into tableA values('A');
insert into tableB values('B');
insert into tableC values('C');

den-crane

create table BX(I Int64, S String) Engine=MergeTree order by I;
insert into BX select number, toString(rand()) from numbers(10000000);
ALTER TABLE  BX ADD INDEX idx0 S TYPE minmax GRANULARITY 10;
optimize table BX final;

select count() from BX prewhere S = '666';
0 rows in set. Processed 10.00 million rows,

select count() from BX prewhere S = 'A';
0 rows in set. Elapsed: 0.001 sec.

den-crane

CREATE TABLE fff ( tx Int64) ENGINE = MergeTree() ORDER BY (intHash32(tx)) SAMPLE BY intHash32(tx)
insert into fff select number from numbers(10);

select groupArray(tx)  from fff sample 0.33 offset 0
[9,6,3,1]

select groupArray(tx)  from fff sample 0.33 offset 0.33
[8,2,5]

select groupArray(tx)  from fff sample 0.33 offset 0.66

den-crane

SELECT grp, ga.1 key, ga.2 value, vsum, value / vsum*100 percent
FROM (SELECT grp, groupArray(tuple (key,value)) ga, SUM(value) vsum
      FROM (SELECT 1 grp,'k1' key,33 value UNION ALL SELECT 1, 'k2', 11 UNION ALL SELECT 2,'k1', 0 UNION ALL SELECT 2,'k2',1)
      GROUP BY grp) Array JOIN ga

den-crane

# создаем таблицу заранее

CREATE TABLE kostya_test.view_result_table
  date Date,
  customer_id UInt32,
  val AggregateFunction (sum, UInt32)) 
ENGINE = ReplicatedAggregatingMergeTree('/clickhouse/tables/kostya_test.view_table', 
'{replica}', date, (date, prom_customer_id, val), 8192);