一条复杂SQL实现思路

1. 定义

城市等级(city_rank)小于3且GMV大于6000或者城市等级大与3且GMV大于5000定义为高消费(gq)

城市等级(city_rank)小于3且广告收入大于360或者城市等级大与3且广告收入大于300定义为高收入(pq)

flow_rank: 0低流量 1中流量 2高流量

合作商跨多个城市，选择city_rank最小值为其city_rank，毛收入多城市取和，广告毛收入按合作商收取

2. 要求查询

• GMV城市最高小于10000、总和小于30000、非首次合作、如GMV有量毛利大于0.02

• 高消费额、低收入、非高流量；低消费额、高收入、非高流量；低消费额、低收入、中流量；低消费额、低收入、低流量，且合作商gmv超过2000

3. 实现

select distinct    tc.partner_id as partnerId,    tc.contract_id as contractId,    tc.contract_num as contractNum,    tc.bd_id as bdId,    tc.org_id as orgId,    if(tc.org_scale='NULL','0',tc.org_scale) as orgScalefrom table tcjoin (    select        tc.partner_id,        case            when min(city_rank)<=3 and avg(t.gmv)>=6000 then 1            when min(city_rank)>3 and avg(t.gmv)>=5000 then 1            else 0        end as gq,        case            when min(city_rank)<=3 and avg(t.gross_profit+t.advertisement_gross_profit)>=360 then 1            when min(city_rank)>3 and avg(t.gross_profit+t.advertisement_gross_profit)>=300 then 1            else 0        end as pq,        max(tc.flow_rank) as fq,        sum(t.gmv) as gmv    from (        select partner_id,poi_id,min(city_rank) as city_rank,            max(tc.flow_rank) as flow_rank,            sum(is_old) as is_old        from table tc        where tc.partner_id>=#{start} and tc.partner_id<=#{end}        group by partner_id,poi_id    ) tc    join  (        select poi_id,            sum(gmv) as gmv,            sum(gross_profit) as gross_profit,            avg(advertisement_gross_profit) as advertisement_gross_profit        from table tc        where tc.partner_id>=#{start} and tc.partner_id<=#{end}        group by poi_id    ) t on tc.poi_id=t.poi_id    group by tc.partner_id    having max(t.gmv)<10000 and sum(t.gmv)<30000  and sum(tc.is_old)>0 and    case        when sum(gmv)>0 then  sum(gross_profit)/sum(gmv)>0.02        else 1=1    end) t2 on t.partner_id=tc.partner_idwhere]]>    ((t2.gq=1 and t2.pq=0 and fq!=2) or (t2.gq=0 and t2.pq=1 and fq!=2) or (t2.gq=0 and t2.pq=0 and fq=1) or (t2.gq=0 and t2.pq=0 and fq=0 and t2.gmv>2000))

4. 关键

4.1 t2

a) tc子查询计算流量、是否首次合作

b) t子查询计算毛利、毛收入

c) 计算合作商消费额、流量、收入类型，查询满足要求1的合作商

d) having子查询过滤，case子句限制毛利率

4.2 要求2

where过滤

5. 总结

大量使用了聚合计算、过滤，业务功能使用一条SQL实现

如果用代码实现类似的功能，复杂程度可以想象，每个聚合都会对应一大坨代码