编辑代码


-- r_clue_detail_extend_dt

-- 总线索量
select count(distinct merchant_id) all_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%';

-- 下发线索:分配线索
select count(merchant_id) issue_allocate_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 1;

-- 自拓线索
select count(merchant_id) issue_self_allocate_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 2;

-- 不下发线索
-- 终端注册
select count(merchant_id) no_issue_terminal_register_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 3
and plan_name in ('小鹅通助手APP', '商家助手', '安卓市场', 'IOS市场');

-- 运营者邀请短信
select count(merchant_id) no_issue_operator_invite_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 3
and plan_name  = '运营者邀请短信';

-- 其他
select count(merchant_id) no_issue_other_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type in (3, 4)
and plan_name not in ('小鹅通助手APP', '商家助手', '安卓市场', 'IOS市场', '运营者邀请短信');

-- 整个线索表

select '${stat_date}' new_clue_date
coalesce(t1.all_clue, 0) all_clue,
coalesce(t2.issue_allocate_clue, 0) issue_allocate_clue,
coalesce(t3.issue_self_allocate_clue, 0) issue_self_allocate_clue,
coalesce(t4.no_issue_terminal_register_clue, 0) no_issue_terminal_register_clue,
coalesce(t5.no_issue_operator_invite_clue, 0) no_issue_operator_invite_clue,
coalesce(t6.no_issue_other_clue, 0) no_issue_other_clue
from
(select count(merchant_id) all_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%') t1
left join
(select count(merchant_id) issue_allocate_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 1) t2 on t1.statdate = t2.statdate
left join
(select count(merchant_id) issue_self_allocate_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 2) t3 on t1.statdate = t3.statdate
left join
(select count(merchant_id) no_issue_terminal_register_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 3
and plan_name in ('小鹅通助手APP', '商家助手', '安卓市场', 'IOS市场')) t4 on t1.statdate = t4.statdate
left join
(select count(merchant_id) no_issue_operator_invite_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type = 3
and plan_name  = '运营者邀请短信') t5 on t1.statdate = t5.statdate
left join
(select count(merchant_id) no_issue_other_clue, '${statdate}' statdate
from db_result.r_clue_detail_extend_dt
where statdate = '${statdate}' and clue_created_at like '${stat_date}%' and allocation_type in (3, 4)
and plan_name not in ('小鹅通助手APP', '商家助手', '安卓市场', 'IOS市场', '运营者邀请短信')) t6 on t1.statdate = t6.statdate