Created
May 20, 2022 14:55
-
-
Save IsaevDimka/e1f91fce5d11e805b2961284f9b6fbdc to your computer and use it in GitHub Desktop.
clickhouse logs
This file contains hidden or 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 log | |
| ( | |
| click_id UInt64 default 0, | |
| uuid Nullable(UUID) default null, | |
| pub_id UInt64 default 0, | |
| pub_type UInt8 default 0, | |
| campaign_id UInt64 default 0, | |
| offer_id UInt64 default 0, | |
| group_id UInt64 default 0, | |
| affiliate_id UInt64 default 0, | |
| landing_id UInt64 default 0, | |
| prelanding_id UInt64 default 0, | |
| is_unique UInt8 default 0, | |
| is_prelanding UInt8 default 0, | |
| has_prelanding UInt8 default 0, | |
| ip UInt64 default 0, | |
| country_id UInt64 default 0, | |
| host_id UInt64 default 0, | |
| ua_id UInt64 default 0, | |
| device_type UInt8 default 0, | |
| os_id UInt64 default 0, | |
| os_version UInt64 default 0, | |
| browser_id UInt64 default 0, | |
| browser_version UInt64 default 0, | |
| subid String default '', | |
| subid1 String default '', | |
| subid2 String default '', | |
| subid3 String default '', | |
| utm_campaign String default '', | |
| utm_content String default '', | |
| utm_medium String default '', | |
| utm_source String default '', | |
| utm_term String default '', | |
| lead_id UInt64 default 0, | |
| call_count UInt64 default 0, | |
| status UInt64 default 0, | |
| amount UInt64 default 0, | |
| account UInt64 default 0, | |
| currency_id UInt64 default 0, | |
| lead_created_at Nullable(DateTime) default null, | |
| adv_id UInt64 default 0, | |
| is_api UInt8 default 0, | |
| phone String default '', | |
| order_id String default '', | |
| lead_confirmed_date Nullable(Date) default null, | |
| quality UInt64 default 0, | |
| traffic_level UInt64 default 0, | |
| age UInt64 default 0, | |
| gender UInt64 default 0, | |
| fake_approve UInt64 default 0, | |
| failure_reason UInt64 default 0, | |
| adaptive Nullable(UInt64) default null, | |
| subid4 String default '', | |
| subid5 String default '', | |
| subid6 String default '', | |
| subid7 String default '', | |
| subid8 String default '', | |
| subid9 String default '', | |
| subid10 String default '', | |
| created_at DateTime default now() | |
| ) ENGINE = MergeTree() | |
| PARTITION BY toYYYYMM(created_at) ORDER BY (click_id); | |
| CREATE TABLE statistic | |
| ( | |
| click_id UInt64, | |
| uuid AggregateFunction(argMaxIf, Nullable(UUID), DateTime, UInt8), | |
| click_created_at SimpleAggregateFunction(min, DateTime), | |
| click_date SimpleAggregateFunction(min, Date), | |
| pub_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| pub_type AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| campaign_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| offer_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| group_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| affiliate_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| landing_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| prelanding_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| is_unique AggregateFunction(argMaxIf, UInt8, DateTime, UInt8), | |
| is_prelanding AggregateFunction(argMaxIf, UInt8, DateTime, UInt8), | |
| has_prelanding AggregateFunction(argMaxIf, UInt8, DateTime, UInt8), | |
| ip AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| country_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| host_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| ua_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| device_type AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| os_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| os_version AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| browser_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| browser_version AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| subid AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid1 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid2 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid3 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| utm_campaign AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| utm_content AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| utm_medium AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| utm_source AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| utm_term AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| lead_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| call_count AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| status AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| amount AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| account AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| currency_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| lead_created_at AggregateFunction(argMaxIf, Nullable(DateTime), DateTime, UInt8), | |
| adv_id AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| is_api AggregateFunction(argMaxIf, UInt8, DateTime, UInt8), | |
| phone AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| order_id AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| lead_confirmed_date AggregateFunction(argMaxIf, Nullable(Date), DateTime, UInt8), | |
| quality AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| traffic_level AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| age AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| gender AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| fake_approve AggregateFunction(argMaxIf, Nullable(UInt64), DateTime, UInt8), | |
| failure_reason AggregateFunction(argMaxIf, UInt64, DateTime, UInt8), | |
| adaptive AggregateFunction(argMaxIf, Nullable(UInt64), DateTime, UInt8), | |
| subid4 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid5 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid6 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid7 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid8 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid9 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| subid10 AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| _status_title AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| _quality_type AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| _campaign_hash AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| _gender_title AggregateFunction(argMaxIf, String, DateTime, UInt8), | |
| updated_at SimpleAggregateFunction(max, DateTime) | |
| ) ENGINE = AggregatingMergeTree() | |
| PARTITION BY toYYYYMM(click_date) ORDER BY (click_id); | |
| -- | |
| CREATE MATERIALIZED VIEW ` log_to_statistic ` TO statistic | |
| AS | |
| SELECT click_id, | |
| argMaxIfState(uuid, log.created_at, uuid is not null) as uuid, | |
| min(log.created_at) as click_created_at, | |
| min(toDate(log.created_at)) as click_date, | |
| argMaxIfState(pub_id, log.created_at, pub_id > 0) as pub_id, | |
| argMaxIfState(pub_type, log.created_at, pub_type > 0) as pub_type, | |
| argMaxIfState(campaign_id, log.created_at, campaign_id > 0) as campaign_id, | |
| argMaxIfState(offer_id, log.created_at, offer_id > 0) as offer_id, | |
| argMaxIfState(group_id, log.created_at, group_id > 0) as group_id, | |
| argMaxIfState(affiliate_id, log.created_at, affiliate_id > 0) as affiliate_id, | |
| argMaxIfState(landing_id, log.created_at, landing_id > 0) as landing_id, | |
| argMaxIfState(prelanding_id, log.created_at, prelanding_id > 0) as prelanding_id, | |
| argMaxIfState(is_unique, log.created_at, is_unique > 0) as is_unique, | |
| argMaxIfState(is_prelanding, log.created_at, is_prelanding > 0) as is_prelanding, | |
| argMaxIfState(has_prelanding, log.created_at, has_prelanding > 0) as has_prelanding, | |
| argMaxIfState(ip, log.created_at, ip > 0) as ip, | |
| argMaxIfState(country_id, log.created_at, country_id > 0) as country_id, | |
| argMaxIfState(host_id, log.created_at, host_id > 0) as host_id, | |
| argMaxIfState(ua_id, log.created_at, ua_id > 0) as ua_id, | |
| argMaxIfState(device_type, log.created_at, device_type > 0) as device_type, | |
| argMaxIfState(os_id, log.created_at, os_id > 0) as os_id, | |
| argMaxIfState(os_version, log.created_at, os_version > 0) as os_version, | |
| argMaxIfState(browser_id, log.created_at, browser_id > 0) as browser_id, | |
| argMaxIfState(browser_version, log.created_at, browser_version > 0) as browser_version, | |
| argMaxIfState(subid, log.created_at, subid > '') as subid, | |
| argMaxIfState(subid1, log.created_at, subid1 > '') as subid1, | |
| argMaxIfState(subid2, log.created_at, subid2 > '') as subid2, | |
| argMaxIfState(subid3, log.created_at, subid3 > '') as subid3, | |
| argMaxIfState(utm_campaign, log.created_at, utm_campaign > '') as utm_campaign, | |
| argMaxIfState(utm_content, log.created_at, utm_content > '') as utm_content, | |
| argMaxIfState(utm_medium, log.created_at, utm_medium > '') as utm_medium, | |
| argMaxIfState(utm_source, log.created_at, utm_source > '') as utm_source, | |
| argMaxIfState(utm_term, log.created_at, utm_term > '') as utm_term, | |
| argMaxIfState(lead_id, log.created_at, lead_id > 0) as lead_id, | |
| argMaxIfState(call_count, log.created_at, call_count > 0) as call_count, | |
| argMaxIfState(status, log.created_at, status > 0) as status, | |
| argMaxIfState(amount, log.created_at, amount > 0) as amount, | |
| argMaxIfState(account, log.created_at, account > 0) as account, | |
| argMaxIfState(currency_id, log.created_at, currency_id > 0) as currency_id, | |
| argMaxIfState(lead_created_at, log.created_at, | |
| lead_created_at is not null) as lead_created_at, | |
| argMaxIfState(adv_id, log.created_at, adv_id > 0) as adv_id, | |
| argMaxIfState(is_api, log.created_at, is_api > 0) as is_api, | |
| argMaxIfState(phone, log.created_at, phone > '') as phone, | |
| argMaxIfState(order_id, log.created_at, order_id > '') as order_id, | |
| argMaxIfState(lead_confirmed_date, log.created_at, | |
| lead_confirmed_date is not null) as lead_confirmed_date, | |
| argMaxIfState(quality, log.created_at, quality > 0) as quality, | |
| argMaxIfState(traffic_level, log.created_at, traffic_level > 0) as traffic_level, | |
| argMaxIfState(age, log.created_at, age > 0) as age, | |
| argMaxIfState(gender, log.created_at, gender > 0) as gender, | |
| argMaxIfState(fake_approve, log.created_at, fake_approve is not null) as fake_approve, | |
| argMaxIfState(failure_reason, log.created_at, failure_reason > 0) as failure_reason, | |
| argMaxIfState(adaptive, log.created_at, adaptive is not null) as adaptive, | |
| argMaxIfState(subid4, log.created_at, subid4 > '') as subid4, | |
| argMaxIfState(subid5, log.created_at, subid5 > '') as subid5, | |
| argMaxIfState(subid6, log.created_at, subid6 > '') as subid6, | |
| argMaxIfState(subid7, log.created_at, subid7 > '') as subid7, | |
| argMaxIfState(subid8, log.created_at, subid8 > '') as subid8, | |
| argMaxIfState(subid9, log.created_at, subid9 > '') as subid9, | |
| argMaxIfState(subid10, log.created_at, subid10 > '') as subid10, | |
| max(log.created_at) as updated_at, | |
| argMaxIfState(multiIf(log.status = 1, 'В работе', log.status = 2, | |
| 'Подтвержденный', log.status = 3, 'Оплаченный', | |
| log.status = 4, 'Отказ оплаты', log.status = 5, | |
| 'Отклоненный', log.status = 6, 'Треш', | |
| log.status = 7, 'Не оставлял заявку', | |
| log.status = 8, 'Ожидает', ''), log.created_at, log.status > | |
| 0) as status_title, | |
| argMaxIfState(if(log.quality IN (0, 1), 'normal', | |
| if(log.quality = 2, 'trashback', | |
| if(log.quality = 3, 'lastcall', toString(log.quality)))), log.created_at, log.status > | |
| 0) as quality_type, | |
| argMaxIfState(dictGet('dictionary_campaign', 'hash', toUInt64(log.campaign_id)), log.created_at, | |
| log.campaign_id > | |
| 0) as campaign_hash, | |
| argMaxIfState(if(log.gender = 1, 'Мужской', if(log.gender = 2, 'Женский', 'Не известно')), log.created_at, | |
| log.gender > | |
| 0) as gender_title | |
| FROM log | |
| GROUP BY click_id; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment