房源画像分析
Lincoln ps:阿里云账号激活流程：
1 点击激活链接进入， https://edu.aliyun.com/clouder/my/activateLicense
2 未注册用户需要重新注册，推荐使用支付宝注册；
3 注册成功后需要绑定手机号设定会员信息；
4 一切都准备好之后，粘贴复制”兑换码“，进入如下操作；
国际人学校网校地址：
网址：edu.ooyyee.com
Windows Sublime 下载地址
👇🏻👇🏻👇🏻👇🏻👇🏻👇🏻
提取码: p1s4
- 必须使用Chrome浏览器
- 使用Lincoln发的压缩包
「房源画像2022」https://www.aliyundrive.com/s/MVypsbQ7Bie 提取码: 5x2q
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。
002 不在分享中，群里分享
阿里云房源画像 FAQ
- 本文档由林肯老师首次组织和编辑。你可以通过网址 数据大咖 找到他。
第 1 章：实验目标
1.1 背景介绍
ihouse 是一家二手房交易中介公司,公司数据库中沉淀大量二手房相关房屋楼盘基础信息、业主委托、经纪人运营数据，及公司门户网站、APP应用注册、访问信息。但数据分散在不同的数据库中，不方便产品、运营、分析、算法人员使用。公司希望数据仓库团队能够将散落在各个系统、各个业务环节的房屋、房源及相关经纪人运营数据整合起来，形成完善房源画像信息，以支持产品迭代、提高运营效率、方便数据分析并为算法人员提供完备特征数据，进行深度分析探索。在画像基础上，希望数据仓库团队提供常用维度上的分析报表，满足日常运营报表需求。
1.2 环境架构
实验环境架构：阿里云大数据计算服务MaxCompute、大数据(数加)大数据开发套件 DataWorks及Quick BI
1.3 思路与流程
通过对数据进行分析，可以将数据分为三大类，分别为：房屋信息类、房源信息类、基础字典类，通过对三部分数据进行整合，形成房源分析画像表，以房源画像表的基础之上，从各个维度对房源进行汇总、展现。
数据逻辑关系如下:
本实验整体步骤如下：
(1) 注册阿里云账号并开通本次实验涉及的相关产品服务，分别为MaxCompute、DataWorks、Quick BI服务
(2) 下载并配置MaxCompute命令行工具odpscmd信息，确保客户端能够正常使用
(3) 创建房屋、房源、字典类相关模型，并分别装载数据到相应模型中、验证数据装载的正确性
(4) 分别整合房屋类信息、房源信息、房源相关行为信息，形成最终的房源画像表
(5) 根据生成的房源分析画像表，从不同维度统计房源相关信息，形成不同维度的统计数据
(6) 在统计数据的基础上，使用Quick BI产品进行数据展现
第 2 章：实验环境准备
2.1 申请MaxCompute服务
- 请点击页面左侧的 实验资源 ，在左侧栏中，查看本次实验资源信息。
大数据计算服务 MAXCOMPUTE MAXCOMPUTE - 在弹出的左侧栏中，点击 创建资源 按钮，开始创建实验资源。
- 资源创建过程需要1-3分钟。完成实验资源的创建后，用户可以通过 实验资源 查看实验中所需的资源信息，例如：阿里云账号等。
2.2 进入Maxcompute工作区
本章节目标为注册阿里云账号并开通相关服务、配置实验需要要使用的客户端并配置验证，为后继度正常进行打下良好基础。
通过沙箱环境申请实验资源
本实验默认提供MaxCompute、DataWorks、及Quick BI沙箱实验环境。通过沙箱申请资源成功后，在本地保存下阿里云账号信息，包括企业别名、子用户名称、子用户密码、AK ID 和 AK Secret。点击前往控制台url。
进入登录页面，输入申请资源处提供的账号，@前为子用户名称，@后为企业别名
输入申请资源处提供的子用户密码
登陆后进入管理控制台，依次点击”产品与服务”>DataWorks，进入大数据开发套件DataWorks概览页，使用提供的项目空间”进入工作区。
点击 “进入数据开发” 进入DataWorks 数据开发管理控制台。
2.3 常用功能介绍
本小节通过DataWorks来实现常见数据操作，比如创建表、装载数据到指定表、查询指定表等操作，为后继操作打下良好基础。
脚本及数据准备： 在附件下载中下载并解压附件housedel.zip
1.在数据开发页面的左侧栏中，点击【临时查询】里的新建节点，选择ODPS SQL
2. 给SQL指定名字create_tables_01，类型请选定ODPS SQL。完成后点击提交。
3. 在文本框中输入如下建表语句：
打开压缩文件后 找到 ddl/000000-ddl_all_in_one_by_lioncoln.sql
4.在create_tables_01文件上方菜单中，点击保存。完成后，点击运行。
等待一段时间完成表创建。
5.通过如下步骤，验证刚刚创建的表是否创建完成。
6. 在左侧栏中选择表管理，然后点击页面上方的导入按钮；
7.在 数据导入向导 中，选择要导入数据的表为 ods_house_zoning_district_da，然后点击 下一步：
8.选择文件为下载解压的 housedel\data\ods_house_zoning_district_da.txt ，选择分隔符为 Tab，原始字符集为 UTF-8，首行为标题为 不勾选；在数据预览中将会看到如图的数据，然后，点击 下一步；
9. 点击 导入数据：
至此，完成了表 ods_house_zoning_district_da 的创建和数据导入；
第 3 章：画像构建
3.1 模型创建和数据装载
下载压缩包文件housedel.zip到本地(如上一章节已经下载，在此处无需重复下载了本示例下载至E:盘),并解压,进入housedel目录，存在目录及功能如下：
- ddl:本次实验涉及所有模型的建表脚本,每个模型对应一个以.sql结尾的文件
- data:本次实验涉及所有模型的数据文件，每个模型对应一个以.txt结尾的文件，每一行记录中字段以\t分隔
- load:本次实验涉及所有模型的数据装载脚本，每个模型对应一个以.load结尾的文件
- dml:本次实验涉及所有数据计算处理脚本
3.2 模型创建
实验涉及模型及对应ddl脚本清单如下：
| 模型名称 | ddl文件 | ||
|---|---|---|---|
| 1 | ods_house_zoning_district_da | ods_house_zoning_district_da.sql | 导入 |
| 2 | ods_housedel_basic_info_da | ods_housedel_basic_info_da.sql | 导入 |
| 3 | ods_housedel_credentials_da | ods_housedel_credentials_da.sql | 导入 |
| 4 | ods_housedel_kpi_info_da | ods_housedel_kpi_info_da.sql | 导入 |
| 5 | ods_housedel_real_prospecting_da | ods_housedel_real_prospecting_da.sql | 导入 |
| 6 | ods_house_basic_info_da | ods_house_basic_info_da.sql | 导入 |
| 7 | ods_house_community_info_da | ods_house_community_info_da.sql | 导入 |
| 8 | ods_house_developer_info_da | ods_house_developer_info_da.sql | 导入 |
| 9 | ods_house_property_info_da | ods_house_property_info_da.sql | 导入 |
| 10 | ods_house_school_info_da | ods_house_school_info_da.sql | 导入 |
| 11 | ods_house_station_info_da | ods_house_station_info_da.sql | 导入 |
| 12 | dwa_housedel_housedel_all_info_da | dwa_housedel_housedel_all_info_da.sql | 这个不需要导入 |
按照2.3中表 ods_house_zoning_district_da的创建和数据导入，依次完成上述10个ods开头的表的创建和数据导入。
第 4 章：数据统计
4.1 录入人指标统计
以下每小节的模型创建和数据统计及装载语句的执行均在数加-大数据开发套件（DataWorks）中执行，可以将相关代码粘入DataWorks执行即可。
从房源录入人角度统计每个经纪人录入的房源总数、目前有效房源数、成交房源数信息。从房源分析画像表中统计，并将统计结果保存在一个新表中，具体操作如下：
(1) 创建统计结果表
create table rpt_housedel_creator_da
(
creator_name string comment "录入人姓名"
,housedel_cnt bigint comment "录入房源量"
,housedel_valid_cnt bigint comment "录入有效房源量"
,housedel_contract_cnt bigint comment "录入房源的成交量"
);
(2) 统计数据并定入新创建表中
insert overwrite table rpt_housedel_creator_da
select
creator_name
,count(distinct housedel_id) as housedel_cnt
,count(distinct case when state=1 then housedel_id end) as housedel_valid_cnt
,count(distinct case when state not in (1,9) then housedel_id end) as housedel_contract_cnt
from dwa_housedel_housedel_all_info_da
group by creator_name;
4.2 维护人指标统计
从房源维护人角度统计每个经纪人维护的房源总数、有效房源数、成交房源数信息。从房源分析画像表中统计，并将统计结果保存在一个新表中，具体操作如下：
(1) 创建统计结果表
create table rpt_housedel_holder_da
(
holder_name string comment "维护人姓名"
,housedel_cnt bigint comment "维护房源量"
,housedel_valid_cnt bigint comment "维护有效房源量"
,housedel_contract_cnt bigint comment "维护房源的成交量"
);
(2) 统计数据并定入新创建表中
insert overwrite table rpt_housedel_holder_da
select
holder_name
,count(distinct housedel_id) housedel_cnt
,count(distinct case when state=1 then housedel_id end) housedel_valid_cnt
,count(distinct case when state not in (1,9) then housedel_id end) housedel_contract_cnt
from dwa_housedel_housedel_all_info_da
group by holder_name;
4.3 按城区统计
按城区统计目前所有房源的分布，分别统计每个城区房源总数、有效房源数、成交房源数。从房源分析画像表中统计，并将统计结果保存在一个新表中，具体操作如下：
(1) 创建统计结果表
create table rpt_housedel_district_da
(
district_name string comment "城区名称"
,housedel_cnt bigint comment "城区房源量"
,housedel_valid_cnt bigint comment "城区有效房源量"
,housedel_contract_cnt bigint comment "城区房源的成交量"
);
(2) 统计数据并定入新创建表中
insert overwrite table rpt_housedel_district_da
select
district_name
,count(distinct housedel_id) as housedel_cnt
,count(distinct case when state=1 then housedel_id end) as housedel_valid_cnt
,count(distinct case when state not in (1,9) then housedel_id end) as housedel_contract_cnt
from dwa_housedel_housedel_all_info_da
group by district_name;
4.4 按环线统计
按环线统计目前所有房源的分布，分别统计每个环线房源总数、有效房源数、成交房源数。从房源分析画像表中统计，并将统计结果保存在一个新表中，具体操作如下：
(1) 创建统计结果表
create table rpt_housedel_cycle_line_da
(
cycle_line_name string comment "环线名称"
,housedel_cnt bigint comment "环线房源量"
,housedel_valid_cnt bigint comment "环线有效房源量"
,housedel_contract_cnt bigint comment "环线房源的成交量"
);
(2) 统计数据并定入新创建表中
insert overwrite table rpt_housedel_cycle_line_da
select
resblock_cycle_line_name
,count(distinct housedel_id) as housedel_cnt
,count(distinct case when state=1 then housedel_id end) as housedel_valid_cnt
,count(distinct case when state not in (1,9) then housedel_id end) as housedel_contract_cnt
from dwa_housedel_housedel_all_info_da
group by resblock_cycle_line_name;
4.5 委托来源分布
按房源委托渠道统计目前所有房源的分布，分别统计每个渠道房源总数、有效房源数、成交房源数。从房源分析画像表中统计，并将统计结果保存在一个新表中，具体操作如下：
(1) 创建统计结果表
create table rpt_housedel_source_da
(
source_name string comment "渠道名称"
,housedel_cnt bigint comment "新增房源量"
);
(2) 统计数据并定入新创建表中
insert overwrite table rpt_housedel_source_da
select
case when del_source=1 then '营销活动'
when del_source=2 then '展会'
when del_source=3 then '社区活动'
when del_source=4 then '营销活动'
when del_source=5 then '实体开发'
when del_source=6 then '店面接待'
when del_source=7 then '社区开发'
when del_source=8 then '人际开发'
when del_source=9 then '转介绍'
when del_source=10 then '物业开发'
when del_source=11 then '保安开发'
end
,count(distinct housedel_id)
from dwa_housedel_housedel_all_info_da
group by
case when del_source=1 then '营销活动'
when del_source=2 then '展会'
when del_source=3 then '社区活动'
when del_source=4 then '营销活动'
when del_source=5 then '实体开发'
when del_source=6 then '店面接待'
when del_source=7 then '社区开发'
when del_source=8 then '人际开发'
when del_source=9 then '转介绍'
when del_source=10 then '物业开发'
when del_source=11 then '保安开发'
end;
4.6 每月新增房源统计
按房源录入时间月份统计每月录入的房源量，该数据从房源分析画像表中统计，并保存结果到新建表中。
具体操作如下：
(1) 创建统计结果表
create table rpt_housedel_create_month_da
(
create_month string comment "月份"
,housedel_cnt bigint comment "新增房源量"
);
(2) 统计数据并定入新创建表中
insert overwrite table rpt_housedel_create_month_da
select
substr(creator_time,1,7) create_month
,count(distinct housedel_id) housedel_cnt
from dwa_housedel_housedel_all_info_da
group by substr(creator_time,1,7);
第 5 章：报表开发
5.1 添加数据源
使用阿里云账号登陆，点击“产品与服务”，选择“大数据(数加)”，然后选择“Quick BI”，如下图：
点击“进入Quick BI标准版”如下图所示
根据弹出页面，我们选择MaxCompute云数据库
由于我们已经生成了相关统计报表数据，并存储在MaxCompute平台中，所以这里选择数据源为MaxCompute,点击MaxCompute，弹出对话框如下图：
依照对话框提示，依次输入
显示名称：自定义
项目名称：实验资源列表获取
Access Id/Access Key 信息：实验资源列表获取
上述信息输入完毕后，点击 “连接测试”，如果连接正常，则给出“数据源连通性正常”的提示，然后点击“添加”
至此，数据源添加完毕。在数据源标签页可以看到新增加的数据源
5.2 创建数据集
本小节使用新创建的数据源“housedel”,将6个数据集由MaxCompute同步到Quick BI平台。6个数据集如下：
| 报表 | MaxCompute表名 |
|---|---|
| 维护人指标统计 | rpt_housedel_holder_da |
| 录入人指标统计 | rpt_housedel_creator_da |
| 按城区统计 | rpt_housedel_district_da |
| 按环线统计 | rpt_housedel_cycle_line_da |
| 委托来源分布 | rpt_housedel_source_da |
| 每月新增房源统计 | rpt_housedel_create_month_da |
数据集创建过程如下，如下图，点击想要创建的数据集，按提示点击相应按钮，弹出对话框，接受默认值，直接点击“确认”
重复上述过程，将剩余五个数据集同步至Quick BI下
| 录入人指标统计 | rpt_housedel_creator_da |
|---|---|
| 按城区统计 | rpt_housedel_district_da |
| 按环线统计 | rpt_housedel_cycle_line_da |
| 委托来源分布 | rpt_housedel_source_da |
| 维护人指标统计 | rpt_housedel_holder_da |
如据同步完成后如下图：
5.3 报表创建
如下图，将所需要的数据集添加到个人空间下：
创建仪表盘
1. 维护人指标统计
进入数据集界面，选择“rpt_housedel_holder_da”,点击“新建仪表板”，如下图：
进入仪表板设计界面如下图
按照上图红色框提示，分别为：
常用图表栏，图表设置面板 及 图表展示区
其中，图表设置面板可以完成如下工作：
更改图表类型：选择使用哪一种图表进行展示。如下图 ：
其中“数据”TAB页面可设置内容如下：
轴值/度量 ：即要展示哪些指标
类别轴/维度 ：即展示数据的维度
颜色图例/维度 ：即展示图例的维度
过滤器 ：根据需要过滤一些数据
将上述信息按要求填写后，点击 “更新”按钮，按照最新配置刷新仪表板，结果如下：
调整上图仪表板细节信息，点击“样式”TAB，调整细节
(1) 调整主标题
将主标题调整为“维护人指标统计”
(2) 调整X,Y轴名称
将X轴标题调整为“维护人”, 将Y转名称则“计量”调整为“房源量”
(3) 调整图例信息，调整指标名称，如下图
调整完成后，最终效果如下：
2. 录入人指标统计
在所有仪表板页面，双击柱状图图标，则会在当前页面增加一个新的图表
选择数据源为rpt_housedel_creator_da,配置对应仪表板，具体详细设置参照下面几张截图
选择图表展示使用数据集
设置图表的维度与指标列
调整图表主标题
设置图表的X/Y轴的标签
设置展示指标系列的别名
如上图设置，设置完毕后显示效果如下：
3. 按城区统计
点击图表展示区空白处，然后在常用图表栏中选择新增一个图表，类型为堆积面积图，选择数据集为rpt_housedel_district_da，根据提示选择维度和量度，然后调整样式，具体设置如下截图
设置主标题
设置坐标轴信息
设置系列指标名称
如上图设置后，点击“更新”，最后显示效果如下：
4. 按环线统计
鼠标点击图表展示区空白处，然后选择“雷达”图，增加一个图表。选择数据集为rpt_housedel_cycle_line_da，设置相关的维度和量度，详细设置如下图
** 设置主标题**
设置系列指标别名
设置完毕后，点击“更新”，图表展示如下：
5. 委托来源分布
双击“饼图”，增加一个新的图表，选择数据集为rpt_housedel_source_da，设置相应的维度和量度及样式信息，具体设置如下图：
设置主标题
设置系统别名
设置完毕后刷新数据，最终显示结果如下图：
6. 每月新增房源统计
双击“线图”图标，增加一个仪表板，针对该仪表板，选择数据集为“rpt_housedel_create_month_da”，然后选择对应的维度和量度，如下图，然后刷新数据
设置主标题
设置坐标轴
设置系统别名
数据刷新后，效果如下：
7. 优化图表布局
本小节根据创建的各种图表，调整其在仪表板中的布局，使用其更紧凑、美观。具体操作方法选中所要操作的图表，直接用拖动到目标位置即可。另外，图表大小可以通过调整每个图表右下角来拖动调整。如下图：
6个图表最终调整后的效果如下：
第 6 章：实验总结
通过本次实验，有如下几个方面的收获:
1、 掌握了阿里云账号注册及服务开通流程
2、 掌握了DataWorks的使用，能够熟练的通过客户端或者DataWorks，创建表、装截数据等日常工作
3、 熟练掌握了MaxCompute SQL、常用函数的使用
4、 熟练掌握了Quick BI产品使用，能够根据需求创建炫酷的报表
5、 熟悉了二手经纪业务核心业务数据，及画像建设思路、流程、方法
