【数据挖掘】恒生金融有限公司2023届秋招数据ETL工程师笔试题解析

公司：恒生科技

笔试时间：2022年9月24号

岗位：数据ETL工程师

时间：120分钟

1 不定项选择题

1、SQL 修改表结构的关键词是

A. alter
B. create
C. desc
D. rename

答案：A

2、正确的是

A. 一个表中，主键可以唯标识一行记录
B. 一个事务中的某条sql 命提交时，其他sql命令可以不提交
C. 一个关系表的外键必定是另一个表的主键
D. 回滚可以在任务失败时，回到操作是五千的数据状态

答案：A，C，D

解析：

B错误

4、数据集市和数据仓库的区别

解析：

（1）数据仓库：基于整个企业的数据模型建立，面向企业范围内的主题；
数据集市：按照某一特定部门的数据模型建立的，由于每个部门有自己特定的需求，因此，他们对数据集市的期望也不同。 部门主题与企业主题之间可能存在关联，也可能不存在。

（2）数仓存储整个企业内非常详细的数据；数据集市数据详细程度低一些，包含概要和综合数据多一些。
（3）数据集市的数据组织一般采用星形模型。大型数仓的数据组织，星形或雪花形都可以。
（4）数据集市较少保留历史数据。

6、正确的是

A. 事务特性为原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）
B. 可重复读会导致脏读
C. 序列化级别最高
D. SQL可以动态调整事务隔离级别

答案：A，C，D

7、为了检查查询，而又不增加数据的存储空间，常用的方法是创建

答案：视图

9、数据仓库常见的模型分为

A. 星型模型
B. 雪花型模型

答案：A，B

10、典型的元数据包括

A. 数据仓库的表结构
B. 数据仓库表的属性
C. 数据仓库的数据源
D. 源数据到数据仓库的映射
E. 数据模型的规格说明
F. 抽取日志
G. 访问数据的公共例行程序

答案：ABCDE是正确的，F，G不知道是否正确

解析：

元数据（Metadata）是关于数据的数据。在数据仓库系统中，元数据是描述数据仓库内数据的结构和建立方法的数据，可将其按用途的不同分为两类：技术元数据（Technical Metadata）；业务元数据（Business Metadata）

技术元数据：
  技术元数据是存储关于数据仓库系统技术细节的数据，是用于开发和管理数据仓库使用的数据，它主要包括以下信息：数据仓库结构的描述，包括仓库模式、视图、维、层次结构和导出数据的定义，以及数据集市的位置和内容；业务系统、数据仓库和数据集市的体系结构和模式，汇总用的算法，包括度量和维定义算法，数据粒度、主题领域、聚集、汇总、预定义的查询与报告；由操作环境到数据仓库环境的映射，包括源数据和它们的内容、数据分割、数据提取、清理、转换规则和数据刷新规则、安全（用户授权和存取控制）。

业务元数据：
  业务元数据从业务角度描述了数据仓库中的数据，它提供了介于使用者和实际系统之间的语义层，使得不懂计算机技术的业务人员也能够“读懂”数据仓库中的数据。业务元数据主要包括以下信息：使用者的业务术语所表达的数据模型、对象名和属性名；访问数据的原则和数据的来源；系统所提供的分析方法以及公式和报表的信息；具体包括以下信息：

企业概念模型：这是业务元数据所应提供的重要的信息，它表示企业数据模型的高层信息、整个企业的业务概念和相互关系。以这个企业模型为基础，不懂数据库技术和SQL语句的业务人员对数据仓库中的数据也能做到心中有数。
多维数据模型：这是企业概念模型的重要组成部分，它告诉业务分析人员在数据集市当中有哪些维、维的类别、数据立方体以及数据集市中的聚合规则。这里的数据立方体表示某主题领域业务事实表和维表的多维组织形式。业务概念模型和物理数据之间的依赖：以上提到的业务元数据只是表示出了数据的业务视图，这些业务视图与实际的数据仓库或数据库、多维数据库中的表、字段、维、层次等之间的对应关系也应该在元数据知识库中有所体现。

2 SQL编程题

1、连接查询

答案：

SELECT * FROM A JOIN B ON A.ID = B .ID

2、删除重复行，只保留一条记录

deletefrom 表名
where 
重复字段名 in(SELECT a.重复字段名from(select 重复字段名
     from 表名
     groupby 重复字段名 havingcount(1)>1) a
 )and 
 id(只保留id最小的一个)notin(SELECT b.id from(selectmin(id)as id
     from 表名 
     groupby 重复字段名 havingcount(1)>1) b
 )

3、将竖表Table_A转换为横表Table_B

（1）方法一:聚合函数[max或sum]配合case语句

select 姓名,sum(case 课程 when'语文'then 成绩 else0end)as 语文,sum(case 课程 when'数学'then 成绩 else0end)as 数学,sum(case 课程 when'英语'then 成绩 else0end)as 英语
from Table_A
groupby 姓名

（2）方法二:使用pivot

select*from Table_A pivot(max(成绩)for 课程 in(语文,数学,英语)) 临时表

PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

参考：https://www.cnblogs.com/liushen/p/3333936.html

3 业务应用SQL编程题

1、 理财数据库，表结构如下

说明：除了Fund表的余额Fbalance字段和Investment表的投资金额Iaccount字段为number,其他字段均视为字符串

（1）查询投资过600571的这个产品且单笔投资金额不少于10000的客户的客户号和名字且按姓名降序排列

（2）给Product表新增字段Pvalue，类型未字符串，长度为32位，不允许为空

（3）在投资流水表Investment中，新建客户号、经理人工号和产品代码的唯一索引

（4）为表彰业绩优秀的经理人，请查询投资600571这个产品投资金额在平均值以上的客户的经理人的工号和姓名，现在投在600571这个产品的客户要从自己的资金账户统一追加投资5000元，请操作相关的表。

解析：我自己写的答案，不保证正确性

1、SELECT  client.Cno,cilent.Cname FROM cilent JOIN Investment ON client.Cno = Client.Cno WHERE Investment.Iaccount>10000AND Investment.Pno ='600571'ORDERBY Client.Cname DESC2、ALTERTABLE Product add Pvalue char(32)NOTNULL3、ALTERTABLE Investment ADDUNIQUEINDEX Investment_index (Cno,Mno,Pno)4、SELECT Manager.Mno,Manager.Mname FROM Manager JOIN Investment WHERE Investment.Pnp ='600571'and Iaccount >(selectAVG(Iaccount)FROM Investment GROUPBY Cno)UPDATE Investment  SET Iaccount =  Iaccount+5000WHERE Pno ='600571'UPDATE Found INNERJOIN Client ON Client.Fno = Found.Fno SET Fbalance  = Fbalance -5000WHERE Clinent.Cno IN(SELECT Cno FROM Investment  WHERE Pno='600571')

2、表结构如下

说明：TradeType:B–申购，S–赎回

TradeStatus:T–交易成功个，F–交易失败

TradeDate:格式为YYYYMMDD

回答下面问题

（1）请给用户表的用户ID增加一个唯一索引

（2）请按日统计2016年10月份各产品的交易金额（含失败），并按交易日期进行降序排序。

（3）将2016年10月31日的所有交易金额大于1000的交易状态更新为交易成功

（4）请列出所有购买过货币基金产品的客户姓名

（5）请列出2016年10鱼粉申购交易前10的产品

解析：我自己写的答案，不保证正确性

1、CREATEUNIQUEINDEX ID_index ON Users(ID)2、SELECT Amount FROM TradeDetail WHEREmonth(TradeDate)='10'andyear(TradeDate)='2016'ORDERBY Amount DESC3、UPDATE TradeDetail SET TradeStatus='T'WHERE TradeDate ='20161031'and Amount >10004、SELECT UserName FROM Users WHERE UserId in(SELECT UserId FROM TradeDetail JOIN Product ON TradeDetail.PrdNo = Product.PrdNo WHERE Product.PrdType='C')5、SELECT PrdName FROM Product JOIN TradeDetail ON Product.PrdNo = TradeDate.PrdNo WHEREmonth(TradeDate)='10'andyear(TradeDate)='2016'