绝对值概念
他们管这个叫累计签单以及绝对值一说法
其实就是我描述的这个意思。
-- 这批线索累计邀约,及累计签单。 绝对值概念 -- 2019年2 月的线索100条, 现在跑20205月看,邀约十条 , 202006六月份再看,可能又多了一条邀约。 即是 11 条。 use jz_daojia; select substr(d.create_time,1,7) ,d.city_name ,d.store_name ,count(d.id) clu_num ,count( if(date_format(d.chance_create_time, 'yyyy-MM-dd') >= '2014-01-01' , c.id ,null) ) yy_num ,count( if(date_format(e.sign_pass_time, 'yyyy-MM-dd') >= '2014-01-01' ,c.id ,null) ) ord_num from ( -- 1最新标签为帮人找线索id select distinct id from jz_daojia.f_jz_clue_common where category_id = 205 and clue_label_text like '%帮人找%' union -- 2 跟进记录里有关键词 select distinct a.id from jz_daojia.f_jz_clue_common a left join jz_daojia.f_jz_clue_t_sale_clue_visit b on a.id = b.clue_id where a.category_id = 205 and regexp_extract(b.visit_summary, '(帮女儿|帮儿子|帮儿媳|帮家人|帮嫂子|帮弟媳|帮姐姐|帮妹妹|帮哥哥|帮弟弟|帮朋友|帮闺蜜)') > '' ) c inner join jz_daojia.f_jz_clue_common d on c.id = d.id left join (select * from jz_daojia.f_jz_order_common where sign_type = 0 ) e on c.id = e.clue_id where substr(d.create_time,1,4) between '2019' and '2020' and d.store_type_text = '自营' group by substr(d.create_time,1,7) ,d.city_name ,d.store_name