SQL项目实战：房产行业如何分析？（步骤具体且明确）

【题目】

“成交订单表”里记录了某房产平台（类似链家、贝壳等）每日房屋成交的明细。（贝壳面试题）

字段“成交客源渠道”中的值是“客源角色人”、“业主线上委托”、“”表示线下渠道，其余的成交客源渠道是线上。

1．当月截止昨天二手线上成交单量占比（含车位）>=50%的门店可获奖；

（线上成交占比=线上成交单量/总成交单量）

2．符合获奖条件的门店的第1单线上成交可获得200贝壳币（可以用于兑换奖金），第2单可获400贝壳币，第3单及以上可获800贝壳币，但车库不奖励（字段“房屋用途”中的值是”车位”、”车库”认为是车库）；

3.在一个连续的SQL中实现以上需求，不能拆分成多个SQL，必须输出表格字段如下（可增加）；

 

【解题思路】

房产平台贝壳的业务是什么？

贝壳业务的实质，是搭建了一个基于房地产服务的协作平台（ACN，agent cooperation network 经纪人协作网络）。房产经纪人、门店、品牌和其他服务提供者在这个平台上进行协同合作。

 

目前贝壳已成为国内最大的线上线下房产交易服务平台，覆盖了103所城市，连接265家地产经纪品牌，4.2万家门店，45.6万经纪人，2019年平台交易总额超2.12万亿，成交量超220万，手机APP月活达3900万。

 对比原始数据表，可以发现本题要求输出表格中增加了3个字段，分别是：是否线上、经纪人所在门店的线上占比、该单应发的贝壳币。

1.线上成交占比

只有线上成交占比大于50%的门店订单才可以获得奖励，所以需要先得出每个门店的线上占比。

线上成交占比=线上成交单量/总成交单量

字段“成交客源渠道”中的值是“客源角色人”、“业主线上委托”、“”表示线下渠道，其余的成交客源渠道是线上。

这种多条件判断的业务问题，要想到用《猴子 从零学会SQL》里讲过的case表达式。

select *,
(case when 成交客源渠道 ="客源角色人" 
           or 成交客源渠道 ="业主线上委托" 
           or 成交客源渠道 ="" 
      then null 
      else 1 
end) as 是否线上
from 成交订单表;

（2）每个门店的线上成交量

按门店分组（签约经纪人门店名称），然后统计线上成交数量（count）。

 
select *,
count(case when 成交客源渠道 ="客源角色人" 
                or  成交客源渠道 ="业主线上委托" 
                or 成交客源渠道 ="" 
           then null 
           else 1 
       end
) as 是否线上
from 成交订单表
group by  签约经纪人门店名称;

（3）线上成交占比=线上成交单量/总成交单量

上面已经得到了每个门店的线上成交量。而总成交量可以用count(*)得到。

所以，线上成交占比就是：

 
select *,
count(case when 成交客源渠道 ="客源角色人" 
                   or  成交客源渠道 ="业主线上委托" 
                   or 成交客源渠道 ="" 
          then null 
          else 1 
          end
) / count(*)  as 经纪人所在门店的线上占比
from 成交订单表
group by  签约经纪人门店名称;

2.该单应发的贝壳币

符合获奖条件的门店的第1单线上成交可获得200贝壳币（可以用于兑换奖金），第2单可获400贝壳币，第3单及以上可获800贝壳币，但车库不奖励（字段“房屋用途”中的值是”车位”、”车库”认为是车库）

（1）哪些是符合条件的门店？

根据题目的业务说明：当月截止昨天二手线上成交单量占比（含车位）>=50%的门店可获奖。

各门店的线上单量占比已经在上面的临时表b中得出。

本月截止昨天用函数 month (签约时间)=month(now()) 得出。

字段“房屋用途”中的值是”车位”、”车库”认为是车库，用where 来筛选。

 
select a.*,b.经纪人所在门店的线上占比
from 成交订单表 as a
left join b   
on
a.签约经纪人门店名称=b.签约经纪人门店名称
where
b.经纪人所在门店的线上占比 >0.5 
and month (a.签约时间)=month(now()) 
and  a.房屋用途 =""；

 

（2）订单如何排名？

题目的业务需求是：符合获奖条件的门店的第1单线上成交可获得200贝壳币（可以用于兑换奖金），第2单可获400贝壳币，第3单及以上可获800贝壳币。

这里的第1单、第2单、第3单，是按每个门店的签约时间对订单进行排名。涉及到排名问题，要想到《猴子 从零学会SQL》里讲过的“窗口函数。

用专用窗口函数（row_number）来排名，按签约时间排序（order by）

sql如下：

 
select a.*,
row_number() over (partition by a.签约经纪人门店名称 
                   order by a.签约时间 ) as 单量
from 成交订单表 as a
left join b
on
a.签约经纪人门店名称=b.签约经纪人门店名称
where
b.经纪人所在门店的线上占比 >0.5 
and month (a.签约时间)=month(now()) 
and  a.房屋用途 ="";

 

把上面的查询结果记为临时表c。

（3）根据单量得出对应的贝壳币

现在根据单量进行奖励，第1单线上成交可获得200贝壳币，第2单可获400贝壳币，第3单及以上可获800贝壳币，利用case when 来判断单量并进行赋值。

 
select c.*,
case when c.单量>= 3  then 800
     when c.单量>= 2  then 400
     else 200 end  as  该单应发的贝壳币   
from 
(select *,
row_number() 
     over  (partition by 签约经纪人门店名称 
            order by 签约时间 ) as 单量
from 成交订单表) as c;

 

3.合并字段

得出 是否线上，经纪人所在门店的线上占比，该单应发的贝壳币 3个字段的代码之后，我们合并在一个连续的SQL中实现题目的业务需求。

 
select  c.*, 
      case when c.单量>= 3  then 800
      when c.单量>= 2  then 400
      else 200 end  as  该单应发的贝壳币 
from
(select a.*, b.经纪人所在门店的线上占比,
        case when  成交客源渠道 ="客源角色人" 
                   or  成交客源渠道 ="业主线上委托" 
                   or 成交客源渠道 ="" 
        then  null else 1  end as  是否线上,
  row_number () 
   over (partition by  a.签约经纪人门店名称
        order by a.签约时间) as 单量
from  成交订单表 as a
left join
(select 签约经纪人门店名称,
      count(case when  成交客源渠道 ="客源角色人" 
                   or  成交客源渠道 ="业主线上委托" 
                   or 成交客源渠道 ="" 
        then  null else 1  end )
 / count(*) as 经纪人所在门店的线上占比
from
成交订单表
group by 签约经纪人门店名称) as b 
on a.签约经纪人门店名称 = b.签约经纪人门店名称
where
b.经纪人所在门店的线上占比 > 0.5
and month  (a.签约时间) = month(now())
and a.房屋用途 = "") as c;

 

【本题考点】

1.用多维度拆解分析方法，将复杂的业务问题拆解为可以解决的简单问题。

2.遇到多条件判断的问题，要想到用case语句来实现。

3.排名问题，要想到用窗口函数来实现。

4.遇到只有一个表且只能用一条SQL语句完成，可以联想到用多表联结，来实现复杂的业务。

转载于公众号：猴子数据分析