SQL 强化练习

 

 

2. 现有经纪人表：rpt.rpt_uc_agent_all_info_teach_da，字段如下：
字段名    字段中文名    数据类型    枚举值
uc_id    用户Id    bigint    
user_name    姓名    string    
office_address_name    城市名称    string    北京市、天津市......
status    状态    tinyint    -99：未知 、1：在职、2：离职
pt    分区    string    
请统计截止2017-08-31，北京在职经纪人人数以及天津离职人数。10分


select
     office_address_name                  --城市名称
     ,count(distinct uc_id) as zaizhi_cnt --在职经纪人人数
from rpt.rpt_uc_agent_all_info_teach_da
where pt='20170831000000'
and status=1
and office_address_name='北京市'
group by office_address_name
union
select
     office_address_name                 --城市名称
     ,count(distinct uc_id) as lizhi_cnt --离职经纪人人数
from rpt.rpt_uc_agent_all_info_teach_da
where pt='20170831000000'
and status=2
and office_address_name='天津市'
group by office_address_name





3. 统计北京市2017年8月31日成交的房源，以及每套房源维护人陪看总次数，字段包括：房源ID，签约人姓名，维护人陪看次数                --15分
rpt.rpt_housedel_housedel_all_info_teach_da
字段名    字段中文名    数据类型    枚举值
housedel_id    房源id    bigint    
sign_name    签约人姓名    string    
sign_time    签约时间    string    
pt    分区    string    
rpt.rpt_evt_showing_detail_all_info_teach_da
字段名    字段中文名    数据类型    枚举值
id    带看id    bigint    
housedel_id    房源编号    bigint    
is_owner_accompany    是否维护人陪看    Tinyint    1：是；0：否
pt    分区    string    
select
    t1.housedel_id --房源ID
    ,t1.sign_name  --签约人姓名
    ,if(t2.peikan_cnt is null,0,t2.peikan_cnt) --维护人陪看次数
from
    (
    select
        housedel_id --房源ID
        ,sign_name --签约人姓名
    from rpt.rpt_housedel_housedel_all_info_teach_da
    where pt='20170831000000'
    and to_date(sign_time)='2017-08-31'
    )t1
    left join
    (
    select
         housedel_id --房源ID
         ,count(distinct id) as peikan_cnt --维护人陪看次数
    from rpt.rpt_evt_showing_detail_all_info_teach_da
    where pt='20170831000000'
    and is_owner_accompany=1
    group by housedel_id
    )t2
    on t1.housedel_id=t2.housedel_i;

4. 截止到2017年8月31日各城市的有效房源量，在职经纪人数以及平均每个经纪人的可售房源数量，字段包括：城市名称，有效房源量，在职经纪人量，平均每个经纪人可售房源量                --15分
rpt.rpt_housedel_housedel_all_info_teach_da
字段名    字段中文名    数据类型    枚举值
housedel_id    房源id    bigint    
state_name    房源状态名称    string    有效，无效，签约，
city_name    城市名称    String    
pt    分区    string    
rpt.rpt_uc_agent_all_info_teach_da
字段名    字段中文名    数据类型    枚举值
uc_id    用户Id    bigint    
user_name    姓名    string    
office_address_name    城市名称    string    北京市、天津市......
status    状态    tinyint    -99：未知 、1：在职、2：离职
pt    分区    string    
select
    nvl(t1.city_name,t2.office_address_name) --城市名称
    ,yxfy_cnt      --有效房源量
    ,zaizhi_cnt    --在职经纪人量
    ,yxfy_cnt/nvl(zaizhi_cnt,0)  --平均每个经纪人可售房源量
from
    (
    select
         city_name --城市名称
         ,count(distinct housedel_id) as yxfy_cnt --有效房源量
    from rpt.rpt_housedel_housedel_all_info_teach_da
    where pt='20170831000000'
    and state_name='有效'
    group by city_name
    )t1
    full join
    (
    select
         office_address_name --城市名称
        ,count(distinct uc_id) as zaizhi_cnt --用户id
    from rpt.rpt_uc_agent_all_info_teach_da
    where pt='20170831000000'
    and status=1
    group by office_address_name
    )t2
    on t1.city_name=t2.office_address_name
5. 截止2017年8月31日有效的买卖房源中，各城市按挂牌价(0,50万],(50,100]以及100万以上，统计建筑面积分别是80（含）平米以下，大于80小于等于90平米，大于90平米以上的房源量。字段包括：城市，价格区间，80(含)平米以下房源量，大于80小于等于90平米，大于90平米以上的房源量。                                 --15分
rpt.rpt_housedel_housedel_all_info_teach_da
字段名    字段中文名    数据类型    枚举值
housedel_id    房源id    bigint    
biz_type    业务类型    Bigint    200200000001:买卖,200200000002:出租
state_name    房源状态名称    string    有效，无效，签约，
city_name    城市名称    String    
build_area    建筑面积    decimal(24,2)    
total_price    挂牌价    Decimal(24,12)    单位：元
pt    分区    string    

select
    t1.city_name
    ,t1.jiagequjian
    ,count(case when t1.build_area<80 then 1 end) as 80_cnt
    ,count(case when t1.build_area>80 and t1.build_area<=90 then 1 end) as 80_90_cnt
    ,count(case when t1.build_area>90 then 1 end) as 90_cnt
from
    (
    select
        city_name
        ,case when total_price>0 and total_price/10000<=50 then '(0,50万]'
              when total_price/10000>50 and total_price/10000<=100 then '(50,100万]'
              else '100万以上'
        end as jiagequjian
        ,build_area
        ,housedel_id
    from rpt.rpt_housedel_housedel_all_info_teach_da
    where pt='20170831000000'
    and biz_type=200200000001
    and state_name='有效'
    )t1
group by t1.city_name,t1.jiagequjian
order by t1.city_name
6. 截止2017年8月31日有效的买卖房源中，各城市挂牌的面积最大的前十和面积最小的前十的平均单价。字段包括：城市、类型（面积最大前十/面积最小前十）、挂牌价、建筑面积、平均挂牌单价（精确到元） --15分
rpt.rpt_housedel_housedel_all_info_teach_da
字段名    字段中文名    数据类型    枚举值
housedel_id    房源id    bigint    
biz_type    业务类型    Bigint    200200000001:买卖,200200000002:出租
state_name    房源状态名称    string    有效，无效，签约，
city_name    城市名称    String    
build_area    建筑面积    decimal(24,2)    
total_price    挂牌价    Decimal(24,12)    单位：元
pt    分区    string    
select
    t1.city_name
    ,t1.type
    ,t1.total_price
    ,t1.build_area
    ,round(t1.avg,0)
from
    (
    select
         city_name
        ,'面积最大前十' as type
        ,build_area
        ,total_price
        ,total_price/build_area as avg
        ,row_number() over (partition by city_name order by build_area desc) as rn
    from rpt.rpt_housedel_housedel_all_info_teach_da
    where pt='20170831000000'
    and state_name='有效'
    and biz_type='200200000001'
    having rn<=10
    union all
    select
        city_name
        ,'面积最小前十' as type
        ,build_area
        ,total_price
        ,total_price/build_area as avg
        ,row_number() over (partition by city_name order by build_area) as rn
    from rpt.rpt_housedel_housedel_all_info_teach_da
    where pt='20170831000000'
    and state_name='有效'
    and biz_type='200200000001'
    having rn<=10
    )t1