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

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 aleft join b ona.签约经纪人门店名称=b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 >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 aleft join bona.签约经纪人门店名称=b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 >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 aleft join(select 签约经纪人门店名称, count(case when 成交客源渠道 ="客源角色人" or 成交客源渠道 ="业主线上委托" or 成交客源渠道 ="" then null else 1 end ) / count(*) as 经纪人所在门店的线上占比from成交订单表group by 签约经纪人门店名称) as b on a.签约经纪人门店名称 = b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 > 0.5and month (a.签约时间) = month(now())and a.房屋用途 = "") as c;

【本题考点】

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

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

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

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。