create_query.sql

DROP DATABASE IF EXISTS yg_projection;

CREATE DATABASE yg_projection;

USE yg_projection;

CREATE TABLE app_flow_entrance_data_detail_local
(
   `hour` Int32,
   `minute` Int32,
   `region_id` Int64,
   `province_id` Int64,
   `city_id` Int64,
   `entrance2_name` String,
   `entrance_cname` String,
   `entrance2_id` String,
   `entrance1_id` String,
   `dt` Int32,
   `entrance_cid` String,
   `entrance1_name` String,
   `flow_source` String,
   `cooperation_type_code` Int32,
   `view_cnt` Int64,
   `view_union_id` String,
   `click_union_id` String,
   `click_cnt` Int64,
   `addcar_union_id` String,
   `addcar_cnt` Int64
)
ENGINE = MergeTree
PARTITION BY dt
ORDER BY (hour, minute, region_id, province_id, city_id, entrance2_name, entrance_cname, entrance2_id, entrance1_id, dt, entrance_cid, entrance1_name, flow_source, cooperation_type_code, view_cnt, view_union_id, click_union_id, click_cnt, addcar_union_id, addcar_cnt)
SETTINGS index_granularity = 8192, storage_policy='all_disks';

ALTER TABLE app_flow_entrance_data_detail_local ADD PROJECTION p_agg
(
SELECT
   hour,
   minute,
   flow_source,
   entrance_cname,
   entrance1_name,
   entrance1_id,
   region_id,
   province_id,
   city_id,
   entrance2_name,
   entrance2_id,
   dt,
   entrance_cid,
   cooperation_type_code,
   sum(view_cnt),
   uniqHLL12(view_union_id),
   uniqHLL12(click_union_id),
   sum(click_cnt),
   uniqHLL12(addcar_union_id),
   sum(addcar_cnt)
GROUP BY
   hour,
   minute,
   flow_source,
   entrance_cname,
   entrance1_name,
   entrance1_id,
   region_id,
   province_id,
   city_id,
   entrance2_name,
   entrance2_id,
   dt,
   entrance_cid,
   cooperation_type_code
);

ALTER TABLE app_flow_entrance_data_detail_local MATERIALIZE PROJECTION p_agg;

CREATE TABLE app_flow_entrance_data_detail AS app_flow_entrance_data_detail_local ENGINE=Distributed('yg-data-olap-ck', 'yg_projection', 'app_flow_entrance_data_detail_local', rand());

set allow_experimental_projection_optimization=1;

SELECT
    multiIf(empty(entrance1_id), '-100', entrance1_id) AS entrance1_id_res,
    multiIf(empty(entrance_cname), '-100', entrance_cname) AS entrance_cname_res,
    multiIf(empty(entrance1_name), '-100', entrance1_name) AS entrance1_name_res,
    exposure_uv,
    sku_view_cnt,
    sku_view_cnt / exposure_uv AS per_exposure_uv,
    click_uv / exposure_uv AS click_ratio,
    click_pv / sku_view_cnt AS pv_click_ratio,
    click_uv,
    click_pv,
    add_cart,
    addcar_pv
FROM
(
    SELECT
        entrance1_id,
        entrance_cname,
        entrance1_name,
        countDistinct(view_union_id) AS exposure_uv,
        sum(view_cnt) AS sku_view_cnt,
        countDistinct(click_union_id) AS click_uv,
        sum(click_cnt) AS click_pv,
        countDistinct(addcar_union_id) AS add_cart,
        sum(view_cnt) AS addcar_pv
    FROM yg_projection.app_flow_entrance_data_detail
    WHERE (dt = 20210717) AND ((hour < 20) OR ((hour = 20) AND (minute <= 6))) AND (flow_source = 'wxapp') AND isNotNull(entrance_cname) AND isNotNull(entrance1_name)
    GROUP BY
        entrance_cname,
        entrance1_id,
        entrance1_name
        WITH ROLLUP
) AS t
WHERE (notEmpty(entrance_cname) AND empty(entrance1_id) AND empty(entrance1_name)) OR (notEmpty(entrance_cname) AND notEmpty(entrance1_id) AND notEmpty(entrance1_name))
ORDER BY exposure_uv DESC;