Shulou(Shulou.com)06/01 Report--

1. Define

High consumption (gq) is defined as city_rank less than 3 and GMV greater than 6000 or city rating greater than 3 and GMV greater than 5000.

High income is defined as city_rank less than 3 and advertising revenue greater than 360or city rating greater than 3 and advertising revenue greater than 300 (pq).

Flow_rank: 0 low traffic 1 medium traffic 2 high traffic

The partner spans multiple cities, chooses the minimum value of city_rank as its city_rank, the gross income is summed up by many cities, and the advertising gross revenue is collected by the partner.

two。 Request inquiry

GMV city maximum less than 10000, sum less than 30000, non-first cooperation, such as GMV volume gross margin greater than 0.02,

High consumption, low income, non-high flow; low consumption, high income, non-high flow; low consumption, low income, medium flow; low consumption, low income, low flow, and partner gmv more than 2000

3. Realize

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) = 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) = 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=# {start} and tc.partner_id0.02 else 1: 1 end) T2 on t.partner_id=tc.partner_idwhere]] > ((t2.gq=1 and t2.pq=0 and fqroomroom2) or (t2.gq=0 and t2.pq=1 and fqroomroom2) 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. Key

4.1 t2

A) TC subquery calculates traffic and whether it cooperates for the first time

B) t subquery to calculate gross profit and gross income

C) calculate the partner's consumption, flow and income type, and query the partner who meets requirement 1

D) filtering of hawing subqueries, and case clause limits gross profit margin

4.2 requirement 2

Where filtering

5. Summary

A large number of aggregate computing and filtering are used, and business functions are implemented using a SQL.

If you implement similar functions in code, you can imagine the complexity of each aggregation corresponding to a large pile of code.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.