绝对值概念

-- 这批线索累计邀约，及累计签单。 绝对值概念
-- 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