SQL绕口令(统计潜力客户）

 

        select '202206' as yearmonth,
        c.name,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-A'
        then a.amount else 0 end ) as A去年购进总金额,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-B'
        then a.amount else 0 end ) as B去年购进总金额,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-C'
        then a.amount else 0 end ) as C去年购进总金额,
        sum(case when left(yearmonth,4) = left(to_char(to_date('202206','yyyymm') - interval '12 month','yyyymm'),4) and 
        b.category_name = 'OTC-D'
        then a.amount else 0 end ) as D去年购进总金额,
        d.last_buy_month,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-A' then a.amount else 0 end ) as A上次购进总金额,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-B' then a.amount else 0 end ) as B上次购进总金额,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-C' then a.amount else 0 end ) as C上次购进总金额,
        sum(case when yearmonth = d.last_buy_month and b.category_name = 'OTC-D' then a.amount else 0 end ) as D上次购进总金额,
        case when c.level1 in ('3001','3002','6030') then '连锁'
        when a.label @> '{14}' then '总经理关注'
        when a.label @> '{16}' then '大区样板'
        when a.label @> '{15}' then '大区重点'
        when c.level1 in ('3003') then '单店'
        when c.level1 in ('4001','4002','4003','2032','2033','2086','2087') then '诊所'
        when c.level1 in ('1004') then '非协议'
        end as type,a.newdepartmentid
        from flowmonth a 
        join goods b on a.goodscode = b.code
        join client c on a.clientid = c.id
        -- 最近一次
        left join (
            select 
            clientid,
            max(case when yearmonth <= to_char(to_date('202206','yyyymm') - interval '3 month','yyyymm') then yearmonth end ) as last_buy_month
            from flowmonth a
            where sfcx = '1'
            and a.clientid not in (select distinct clientid from flowmonth where yearmonth > to_char(to_date('202206','yyyymm') - interval '3 month','yyyymm')
            and yearmonth <= '202206'  and sfcx = '1' )
            GROUP BY clientid
        ) d on a.clientid = d.clientid
        
        where sfcx = '1'
        and b.category_name like 'OTC-%'
        and yearmonth <= to_char(to_date('202206','yyyymm') - interval '3 month','yyyymm')
        and a.clientid not in (
        select distinct clientid from flowmonth where yearmonth > to_char(to_date('202206','yyyymm') - interval '3 month','yyyymm')
        and yearmonth <= '202206'  and sfcx = '1')
        and ( c.level1 in ('3001','3002','6030','3003','3003','4001','4002','4003','2032','2033','2086','2087','1004')
        or a.label @> '{14}' or a.label @> '{15}' or a.label @> '{16}')
        GROUP BY c.name,d.last_buy_month,c.level1,a.label,a.newdepartmentid;