广州地区职场bi备的三项能力测试题库及答题技巧.docxVIP

第PAGE页共NUMPAGES页

广州地区职场bi备的三项能力测试题库及答题技巧

一、数据建模与ETL设计（共5题，每题10分，总分50分）

1.题目：广州某电商公司需要通过ETL流程整合销售数据、用户行为数据和商品信息数据，以支持BI报表分析。请设计一个ETL逻辑，并说明如何解决数据清洗和关联的问题。

答案与解析：

答案：

1.ETL逻辑设计：

-Extract（抽取）：从MySQL数据库中抽取销售数据（每日订单表、支付表）、HDFS中抽取用户行为数据（日志表，按天分区）、MongoDB中抽取商品信息（商品库）。

-Transform（转换）：

-清洗数据：去除空值、异常值（如订单金额为负数），统一时间格式（如将时间戳转换为`YYYY-MM-DDHH:MM`格式）。

-关联数据：通过订单ID关联销售数据与商品信息，通过用户ID关联用户行为数据与销售数据。

-聚合计算：按天汇总各店铺的销售额、订单量、客单价，按用户维度计算RFM值（最近一次消费、频率、金额）。

-Load（加载）：将处理后的数据加载到数据仓库（如Hive）的星型模型中，事实表包括“销售事实表”，维度表包括“店铺维度表”“用户维度表”“商品维度表”。

2.数据清洗与关联解决方案：

-清洗：使用Python或Spark的`fillna()`、`dropna()`、`filter()`函数处理缺失值，通过规则校验（如金额0）过滤异常数据。

-关联：采用SQL的`JOIN`操作（如`INNERJOIN`）或Spark的`join()`方法，确保关联字段（如订单ID、用户ID）匹配。若存在重ID，需去重处理。

2.题目：广州地铁集团需要分析各线路的客流量变化，要求按小时统计进站和出站人数。现有数据源包括AFC系统（票务交易数据）和传感器数据（站台客流）。如何设计数据模型支持分析？

答案与解析：

答案：

1.数据模型设计：

-采用星型模型，以“小时客流事实表”为核心，关联“线路维度表”“站点维度表”“时间维度表”。

-事实表：记录每小时各站点的进站人数、出站人数、总客流，字段如：`小时ID`、`站点ID`、`进站量`、`出站量`、`总客流`。

-维度表：

-线路维度表（`线路ID`、`线路名称`、`运营时间`）。

-站点维度表（`站点ID`、`站点名称`、`所属线路ID`）。

-时间维度表（`小时ID`、`日期`、`小时`，如`2023-10-2708`）。

2.ETL逻辑：

-抽取AFC系统票务数据（按小时分区），清洗交易类型（区分进站/出站），聚合统计各站点小时客流。

-抽取传感器数据（每5分钟计数），通过站点ID与票务数据匹配，补充客流缺口（如传感器未覆盖的时段）。

-加载到Hive表中，通过SQL或SparkSQL进行联查分析（如计算早高峰7-9点的线路拥堵度）。

3.题目：广州某零售企业使用ODBO（OracleDataIntegrator）做数据同步，但发现销售数据延迟达2小时。如何优化ETL性能？

答案与解析：

答案：

1.性能优化方案：

-并行处理：将销售数据按店铺ID或时间范围拆分为子任务，使用ODBO的并行作业功能（`Parallelism`参数）。

-增量抽取：改为增量抽取（如使用OracleGoldenGate抓取增量日志），避免全量抽取。

-缓存优化：对高频查询的中间结果（如商品分类统计）使用ODBO的物化视图缓存。

-索引优化：在源库（Oracle）中为关联字段（如订单表中的`店铺ID`、`商品ID`）添加索引。

2.具体步骤：

-分析任务瓶颈（使用ODBO的`Log`功能查看CPU/IO占用），定位慢点（如JOIN操作）。

-调整ODBO组件（如`Stage`、`Aggregator`的内存分配）。

-若源库慢，建议通过Kettle抽取，再导入Hive（避免ODBO直接连接数据库）。

4.题目：广州海关需要整合进出口货物数据（CSV文件），并按HS编码统计商品类别占比。请写出ETL步骤和SQL示例。

答案与解析：

答案：

1.ETL步骤：

-抽取：使用Python的`pandas`读取CSV文件，按`HS编码`分桶处理（避免单文件过大）。

-转换：

-清洗：去除重复行（`duplicated()`检测），填充缺失的`HS编码`（通过正则匹配前6位）。

-聚合：按`HS编码`的前6位归类为商品大类（如“0101”归为“水产品”），统计每类的`货物重量`、`金额`。

-加载：加载到MySQL表中，创建索引（`HS编码`、`日期`）。

2.SQL示例（Hive）：

sql

SELECT

SUBSTRING(HS