项目实战：房产行业如何分析？

最新推荐文章于 2025-01-25 14:37:38 发布

15150229228苏州超博金属

最新推荐文章于 2025-01-25 14:37:38 发布

阅读量152

点赞数

原文链接：https://www.szcbjs.com/

版权

【题目】

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

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

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

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

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

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

【解题思路】

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

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

（图片来源贝壳IPO）

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

（图片来源贝壳IPO）

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

1.线上成交占比

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

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

（1）哪些是线上成交的数据？

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

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

利用case来对成交客源渠道字段赋值判断，如果为“线上”就返回1，不是就返回null（表示线下）。

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

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

遇到“每个”这种业务问题，要想到用《猴子从零学会SQL》里讲过的“分组汇总”。按门店分组（签约经纪人门店名称），然后统计线上成交数量（count）。

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

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

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

sql如下：

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

将上面的查询结果当作临时表b。

2.该单应发的贝壳币

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

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

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

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

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

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

sql如下：

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》里讲过的“窗口函数。https://www.szcbjs.com/

用专用窗口函数（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 来判断单量并进行赋值。

sql如下：

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中实现题目的业务需求。

合并的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;
查询结果：