【题目】
“成交订单表”里记录了某房产平台（类似链家、贝壳等）每日房屋成交的明细。（贝壳面试题）
字段“成交客源渠道”中的值是“客源角色人”、“业主线上委托”、“”表示线下渠道，其余的成交客源渠道是线上。
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*,(casewhen成交客源渠道 ="客源角色人"or成交客源渠道 ="业主线上委托"or成交客源渠道 =""thennullelse1end)as是否线上from成交订单表;
查询结果：
（2）每个门店的线上成交量
遇到“每个”这种业务问题，要想到用《猴子 从零学会SQL》里讲过的“分组汇总”。按门店分组（签约经纪人门店名称），然后统计线上成交数量（count）。
select*,count(casewhen成交客源渠道 ="客源角色人"or成交客源渠道 ="业主线上委托"or成交客源渠道 =""thennullelse1end)as是否线上from成交订单表groupby签约经纪人门店名称;
（3）线上成交占比=线上成交单量/总成交单量
上面已经得到了每个门店的线上成交量。而总成交量可以用count(*)得到。
所以，线上成交占比就是：
sql如下：
select*,count(casewhen成交客源渠道 ="客源角色人"or成交客源渠道 ="业主线上委托"or成交客源渠道 =""thennullelse1end) /count(*)as经纪人所在门店的线上占比from成交订单表groupby签约经纪人门店名称;
查询结果：
将上面的查询结果当作临时表b。
2.该单应发的贝壳币
符合获奖条件的门店的第1单线上成交可获得200贝壳币（可以用于兑换奖金），第2单可获400贝壳币，第3单及以上可获800贝壳币，但车库不奖励（字段“房屋用途”中的值是”车位”、”车库”认为是车库）
（1）哪些是符合条件的门店？
根据题目的业务说明：当月截止昨天二手线上成交单量占比（含车位）>=50%的门店可获奖。
各门店的线上单量占比已经在上面的临时表b中得出。
本月截止昨天用函数 month (签约时间)=month(now()) 得出。
字段“房屋用途”中的值是”车位”、”车库”认为是车库，用where 来筛选。
sql如下：
selecta.*,b.经纪人所在门店的线上占比from成交订单表asaleftjoinbona.签约经纪人门店名称=b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 >0.5andmonth(a.签约时间)=month(now())anda.房屋用途 =""；
查询结果：
（2）订单如何排名？
题目的业务需求是：符合获奖条件的门店的第1单线上成交可获得200贝壳币（可以用于兑换奖金），第2单可获400贝壳币，第3单及以上可获800贝壳币。
这里的第1单、第2单、第3单，是按每个门店的签约时间对订单进行排名。涉及到排名问题，要想到《猴子 从零学会SQL》里讲过的“窗口函数。
用专用窗口函数（row_number）来排名，按签约时间排序（order by）
sql如下：
selecta.*,row_number()over(partitionbya.签约经纪人门店名称orderbya.签约时间 )as单量from成交订单表asaleftjoinbona.签约经纪人门店名称=b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 >0.5andmonth(a.签约时间)=month(now())anda.房屋用途 ="";
查询结果：
把上面的查询结果记为临时表c。
（3）根据单量得出对应的贝壳币
现在根据单量进行奖励，第1单线上成交可获得200贝壳币，第2单可获400贝壳币，第3单及以上可获800贝壳币，利用case when 来判断单量并进行赋值。
sql如下：
selectc.*,casewhenc.单量>=3then800whenc.单量>=2then400else200endas该单应发的贝壳币from(select*,row_number() over(partitionby签约经纪人门店名称orderby签约时间 )as单量from成交订单表)asc;
查询结果：
3.合并字段
得出 是否线上，经纪人所在门店的线上占比，该单应发的贝壳币 3个字段的代码之后，我们合并在一个连续的SQL中实现题目的业务需求。
合并的sql如下：
selectc.*,casewhenc.单量>=3then800whenc.单量>=2then400else200endas该单应发的贝壳币from(selecta.*, b.经纪人所在门店的线上占比,casewhen成交客源渠道 ="客源角色人"or成交客源渠道 ="业主线上委托"or成交客源渠道 =""thennullelse1endas是否线上, row_number () over(partitionbya.签约经纪人门店名称orderbya.签约时间)as单量from成交订单表asaleftjoin(select签约经纪人门店名称,count(casewhen成交客源渠道 ="客源角色人"or成交客源渠道 ="业主线上委托"or成交客源渠道 =""thennullelse1end)/count(*)as经纪人所在门店的线上占比from成交订单表groupby签约经纪人门店名称)asbona.签约经纪人门店名称 = b.签约经纪人门店名称whereb.经纪人所在门店的线上占比 >0.5andmonth(a.签约时间) =month(now())anda.房屋用途 ="")asc;
查询结果：
【本题考点】
1.用多维度拆解分析方法，将复杂的业务问题拆解为可以解决的简单问题。
2.遇到多条件判断的问题，要想到用case语句来实现。
3.排名问题，要想到用窗口函数来实现。
4.遇到只有一个表且只能用一条SQL语句完成，可以联想到用多表联结，来实现复杂的业务。
推荐：如何从零学会sql？
网友评论