如何合理和有效的进行数据库设计

你那些极限参数我都没有去给你查，有兴趣自己到MYSQL网站查询，或者给开发团队发MAIL。因为我认为开发团队设计的极限参数，肯定可以应对绝大多数情况，即使有点变态。比如表的个数，我认为几千甚至万把个表，MYSQL也能转起来，字段数也是如此。

表太多的库，一般情况下没什么大的问题，也不是很有必要划分为多个库。除非这个库里面，许多表是平时基本上不使用的，少数表是需要反复频繁使用的，那么有必要把频繁使用的表移到一个库里面。因为操作系统一般对目录下的文件是不排序的，数据库请求操作系统打开某文件的时候，操作系统实际上是顺序搜索目录表（FDT），这样当目录下的文件数太多的时候，效率会很差。经过我自己的实验，在WINDOWS下当文件个数达到5000左右的时候我无法忍受，在FREEBSD下也差不多。

字段数较多的表，在数据库理论里面称为宽表。它是有的情况下必须要有的，比如我什么零件库，零件的参数就有上千个，是正常的。一般我们不需要把宽表划分为几个表，因为程序会变得复杂，而每次查询都需要关联多表，更新更是麻烦。

但是在某些情况下，把宽表进行划分是有效的。比如一部分字段是比较固定的，而另外一些字段是需要反复更新的。把需要反复更新的字段单独分为一个表，能提高更新的效率，也能减少更新期间系统故障时带来的风险。

对宽表的划分，应该照顾逻辑属性。比如对论坛的表进行划分，可以把论坛ID、标题等固定属性分为一个表，而把论坛状态、最后回复者、帖子数等状态属性分为一个表。

对于基本上不需要修改的表，无论字段有好多，也无论文件有多大，都没有必要划分为多个表。

1、各数据分区的模型设计思路：

       数据架构部分中提到了在数据仓库中主要分为以下区域，那各数据区域的主要设计原则如下：

       （1）主数据区：主数据区是全行最全的基础数据区，保留历史并作为整个数据仓库的数据主存储区，后续的数据都可以从主数据区数据加工获得，因此主数据区的数据天然就要保留所有历史数据轨迹。

        1) 近源模型区：主要是将所有入数据仓库的数据表按历史拉链表或事件表（APPEND算法）的方式保留所有历史数据，因此模型设计较简单，只需要基于源系统表结构，对字段进行数据标准化后，增加保留历史数据算法所需要的日期字段即可。

        2)整合模型区：该模型区域按主题方式对数据进行建模，需要对源系统表字段按主题分类划分到不同的主题区域中，并主要按3范式的方式设计表结构，通过主题模型的设计并汇总各系统数据，可以从全行及集团角度进行客户、产品、协议（账户、合同）分析，获得统一视图。比如说，全行有多少客户、有多少产品？通过主题模型事先良好的设计和梳理，可以很快获得相关统计数据。

       主数据区的模型设计按顶层设计（自上而下）为主，兼顾应用需求（自下而上）的方式，即需要有全局视角，也要满足应用需求。那顶层设计主要是需要从全行数据角度对源系统的主要业务数据进行入仓，获得全行客户、业务数据的整体视角，同时又保存所有交易明细数据，满足后续的数据分析需求；应用需求指源系统数据的入仓也需要考虑当前集市、数据应用系统的数据需求，因为数据需求是千变万化的，但是只要保留全面的基础的业务数据，就有了加工的基础，当前的数据需求只是考虑的一部分，更多的需要根据业务经验以及主题模型进行数据入仓和模型设计。

        主数据模型的设计主要自上而下，近源模型层虽然比较简单，但设计步骤和整合模型类型，分为以下几个步骤：

       步骤1：系统信息调研，筛选入仓的系统并深入了解业务数据；

       步骤2：对入仓系统进行表级筛选和字段筛选，并将字段进行初步映射；

        步骤3：根据入仓字段按一定规范设计逻辑模型；

       步骤4：对逻辑模型进行物理化；

       （2）集市区：集市区的设计表结构设计主要按维度模型（雪花模型、星形模型）进行设计，主要是为了方便应用分析，满足数据应用需求，集市区一般以切片的形式保留结果历史数据，但保留期限不会太长，比如只保留月末数据以及当前月份的每日切片数据。

       数据集市需要从数据仓库获得基础数据，对于仓内集市，可以直接访问或通过视图访问，减少数据存储，仓外集市则需要从数据仓库获得批量数据作为基础数据进行存储加工。因此仓外集市还需要设计基础数据的保留策略。

      集市区的设计步骤如下：

       （3）接口区：接口区的设计完全根据数据应用系统的接口方式来进行，一般也是维度模型（事实表+维度表）方式，接口区之前也提到过，不做复杂计算，只做简单关联，可以将复杂计算放到集市或指标汇总层加工。

        （4）指标汇总区：作为集市接口区和主数据区的中间层，主要是提供基于各集市和接口数据的共性需求，基于主模型区数据进行统一加工。即面向所有的应用需求来设计，那中间层一般采用维度模型，按从细粒度到粗粒度的方式逐步汇总。由于各数据应用及集市的需求不断变化，指标汇总区也是不断进行完善，许多一开始在集市的加工由于其它集市或应用也需要，则会从集市转移到指标汇总层。常见的数据就是客户、账户、合同等常用的数据实体的宽表（事实表），统一进行汇总后供各数据应用使用。

        另外指标汇总层也包括共性指标的加工，指标可以通过基础指标配置指标计算加工方式获得衍生指标，那这些基础指标和衍生指标的定义、口径以及加工方式可以由指标管理系统来维护并集成到数据标准系统和元数据管理系统中。

        指标汇总区设计步骤如下：

        （5）非结构化数据存储区：非结构化存储区的设计不仅需要考虑非结构化数据本身的存储，同时需要考虑非结构化数据所带有的结构化属性，因此在设计时主要考虑以下几点：

         1）存储路径规划：是需要将非结构化数据按源系统、类型、日期、外部来源等角度进行存储路径的规划，分门别类，便于管理。

         2）对非结构化数据的元数据建立索引：比如对于凭证的影像，需要有账户、流水号、客户名等相关结构化数据，以便完整描述影像图片的来源，通过对这些结构化数据建立索引，方便查找。

         3）对部分文档内容建立索引：对于部分文档如合同电子版、红头文件PDF需要建立内容索引，以便快速搜索查找文件内容，一般可用支持HADOOP的ElasticSearch来实现。

         4）设立计算区和结果区：由于非结构化数据往往需要使用MAPREDUCE或程序化语言进行处理，也会产生中间临时文件和结果数据，因此需要规划计算区和结果区来存放这些数据。

        （6）历史数据存储区：历史数据区作为历史数据的归档，即包括结构化数据，也包括非结构化数据，对于历史数据除了存储也需要方便查找，历史数据区的规划设计需要考虑非结构化数据存储区的存储、索引设计外，还需要考虑以下几点：

        1）压缩，由于历史数据使用频率低，可以选择压缩率较高的算法，降低存储空间。

         2）容量规划：由于历史数据归档会越来越大，因此需要提前进行容量规划以及历史数据清理。比如10年以上的数据进行删除。

         3）可设计一个管理系统对历史数据进行归档、查找以及管理。

        （7）实时数据区：实时数据区需要使用部分批量数据来和实时流数据进行关联加工，因此可从主数据区获得所需要的数据后进行存放在实时数据区的关联数据区，同时对于加工结果不仅可以推送到KAFKA等消息中间件，同时也可输出到实时数据区的结果区进行保留。

        （8）在线查询区：在线查询区主要在线提供计算结果查询，常用HBASE来实现，设计按照接口来分别存放到不同的HBASE表，字段内容也主要是接口字段内容。HBASE表可以根据应用或者接口类型进行分目录和分用户。由于在线查询区和实时数据区考虑到作业的保障级别以及资源竞争，往往会单独建立一套集群，与批量作业集群进行隔离，在线查询的结果计算可以在批量集群计算后加载到在线查询区。

        后续将分别对主数据区、集市及汇总指标层模型设计进行介绍，敬请关注。

这篇文章较为完整、清晰的讲述了数仓建模分层理论，要点如下：

1、分层的意义：清晰结构体系、数据血缘跟踪、减少重复开发、复杂问题简单化及统一数据口径

2、ODS：用作缓冲，可以存一周左右，跟DWD大多重复，留存的目的还在于保持跟源端一致，方便追溯

3、DWD：针对ODS做数据的清洗和整合，在DWD层会根据维度模型，设计事实表和维度表，DWD层是一个非常规范的、高质量的、可信的数据明细层

4、DWS：基于DWD层形成某一主题的轻度汇总表或分析宽表，DWS形成大量维度退化的事实表以提高易用性，DWS层应覆盖80%的应用场景

5、TDM：标签层，通过统一的ID-Mapping 把各个业务板块，各个业务过程中同一对象的数据打通，形成对象的全域数据标签体系，方便深度分析、挖掘、应用，大家注意，这个ID不仅仅指客户或用户ID，也包括其它的主数据ID，其是全流程分析的基础

6、ADS：数据应用层ApplicationDataService面向业务定制的应用数据，主要提供给数据产品和数据分析使用的数据，一般会放在ES，MYSQL，Redis等前端系统供线上系统使用，也可以放在Hive中供数据分析和数据挖掘使用

7、DM：主要是提供数据产品和数据分析的数据，主要解决部门用户报表和分析需求而建立数据库，数据集市就代表数据仓库的主题域。DM 是面向单个主题的，所以它不会从全局考虑进行建设。

强烈推荐阅读！

正文开始

简单点儿，直接ODS+DM就可以了，将所有数据同步过来，然后直接开发些应用层的报表，这是最简单的了；当DM层的内容多了以后，想要重用，就会再拆分一个公共层出来，变成3层架构,这个过程有点类似代码重构，就是在实践中不断的进行抽象、总结。

数仓的建模或者分层，其实都是为了更好的去组织、管理、维护数据,所以当你站在更高的维度去看的话，所有的划分都是为了更好的管理。小到JVM 内存区域的划分，JVM 中堆空间的划分(年轻代、老年代、方法区等)，大到国家的省市区的划分，无一例外的都是为了更好的组织管理 。

所以数仓分层是数据仓库设计中十分重要的一个环节， 优秀的分层设计能够让整个数据体系更容易理解和使用 。

这一节，我们主要是从整体上出发进行分析和介绍，就和上一节数仓建模方法论一样，进度对比分析，更多细节的东西我们后面会单独拆分出来，用案例进行演示，例如维度建模，维度表的设计，事实表的设计、以及如何设计标签、如何管理标签等等。

每一个数据分层都有它的作用域，这样在使用表的时候能更方便的定位和理解。

由于最终给业务呈现的是一个能直接使用的业务表，但是表的数据来源有很多，如果有一张来源表出问题了，我们希望能够 快速准确的定位到问题，并清楚它的影响范围，从而及时给到业务方反馈，从而将损失降到最低 。

将一个复杂的任务分解成多个步骤来完成，每一层只处理单一的步骤，比较简单和容易理解。而且便于维护数据的准确性，当数据出现问题之后，可以不用修复所有的数据，只需要从有问题的步骤开始修复。

过数据分层提供统一的数据出口，统一对外输出的数据口径，这往往就是我们说的数据应用层。

前面我们说到分层其实是为了更好更快更准的组织管理，但是这个是从宏观上来说的，接下来我们从微观上也来看一下分层。

越靠上的层次，对应用越友好,比如ADS层，基本是完全为应用设计,从数据聚合程度来讲，越上层的聚合程度越高，当然聚合程度越高可理解程度就越低。

数仓层内部的划分不是为了分层而分层， 分层是为了解决 ETL 任务及工作流的组织、数据的流向、读写权限的控制、不同需求的满足等各类问题 ，当然我们常说的分层也是面向行业而言的，也是我们常用分层方法，但是你需要注意的是分层仅仅是手段而已。

ODS 全称是 OperationalDataStore， 操作数据层存储的是面向业务系统的数据 ，也是最接近数据源中数据的一层，数据源中的数据，经过抽取、洗净、传输，也就说传说中的 ETL 之后，装入本层。

本层的数据，总体上大多是 按照源头业务系统的分类方式而分类的 ，前面我们说到为什么在数仓主要用维度建模的情况下，我们依然要学习范式建模呢，因为我们的数据源是范式建模的，所以学习范式建模可以帮助我们更好的理解业务系统，理解业务数据，所以你可以认为我们的ODS 层其实就是用的实范式建模。

这里的数据处理，并不涉及业务逻辑，仅仅是针对数据完整性以及重复值和空值的处理，其实就是做的是数据规约，数据清洗，但是为了考虑后续可能追溯数据源问题，因此 对这一层不建议做过多的数据清洗工作 ，原封不动接入源数据即可，至于数据的去噪，去重，异常值处理等过程可以放在后面的DW层

表名的设计 ODS_业务系统_表名_标记 ，这样的设计可以保持与业务表名一致，又可以有清晰的层次，还可以区分来源。标记一般指的是其他数仓特有的属性，例如表是天级的还是小时的，是全量的还是增量的。

ods 的设计可以保证所有的数据按照统一的规范进行存储。

DW是数据仓库的核心，从ODS层中获得的数据按照主题建立各种数据模型。DW又细分数据明细层DWD 和轻度汇总层DWS

这一层和维度建模会有比较深的联系，业务数据是按照 业务流程方便操作的角度 来组织数据的，而统一数仓层是 按照业务易理解的角度或者是业务分析的角度 进行数据组织的，定义了一致的指标、维度，各业务板块、数据域都是按照统一的规范来建设，从而形成统一规范的 标准业务数据体系 ，它们通常都是基于Kimball的维度建模理论来构建的， 并通过一致性维度和数据总线来保证各个子主题的维度一致性 。

公共层的维度表中相同维度属性在不同物理表中的字段名称、数据类型、数据内容必须保持一致，因为这样可以降低我们在使用过程中犯错误的概率，例如使用了不正确的字段，或者因为数据类型的原因导致了一些奇怪的错误

将维度所描述业务相关性强的字段在一个物理维表实现。相关性强是指经常需要一起查询或进行报表展现、两个维度属性间是否存在天然的关系等。例如，商品基本属性和所属品牌。

公告明细数据层，可以说是我们数仓建设的核心了。

DWD层要做的就是将 数据清理、整合、规范化、脏数据、垃圾数据、规范不一致的、状态定义不一致的、命名不规范的数据都会被处理 。然后加工成面向数仓的基础明细表，这个时候可以加工一些面向分析的大宽表。

DWD层应该是覆盖所有系统的、完整的、干净的、具有一致性的数据层。在DWD层会根据维度模型，设计事实表和维度表，也就是说DWD层是一个非常规范的、高质量的、可信的数据明细层。

DWS层为 公共汇总层 ，这一层会进行轻度汇总，粒度比明细数据稍粗， 基于DWD层上的基础数据，整合汇总成分析某一个主题域的服务数据 ，一般是也是面向分析宽表或者是面向某个注意的汇总表。DWS层应覆盖80%的应用场景，这样我们才能快速响应数据需求，否则的话，如果很多需求都要从ods开始做的话，那说明我们的数仓建设是不完善的。

例如按照业务划分，例如流量，订单，用户等，生成字段比较多的宽表，用于后续的业务查询，OLAP分析，数据分析等。

一般采用维度模型方法作为理论基础，更多的采用一些维度退化手法，将维度退化至事实表中，减少维度表与事实表的关联，提高明细数据表的易用性；同时在汇总数据层要加强指标的维度退化，采用更多的宽表化手段构建公共指标数据层，提升公共指标的复用性，减少重复加工 。

维表层，所以其实维度层就是大量维表构成的，为了统一管理这些维度表，所以我们就建设维度层，维度表本身也有很多类型，例如稳定维度维表，渐变维度维表。

维度指的是观察事物的角度，提供某一业务过程事件涉及用什么过滤和分类的描述属性 ，"谁、什么时候、什么地点、为什么、如何"干了什么，维度表示维度建模的基础和灵魂。

所以可以看出，维度表包含了业务过程记录的业务过程度量的上下文和环境。维度表都包含单一的主键列， 维度表设计的核心是确定维度字段，维度字段是查询约束条件(where)、分组条件(group)、排序(order)，与报表标签的基本来源 。

维度表一般为 单一主键 ，在ER模型中，实体为客观存在的事务，会带有自己的描述性属性，属性一般为文本性、描述性的，这些描述被称为维度。维度建模的核心是 数据可以抽象为事实和维度 ，维度即观察事物的角度，事实某一粒度下的度量词， 维度一定是针对实体而言的 。

每个维度表都 包含单一的主键列 。维度表的主键可以作为与之关联的任何事实表的外键，当然，维度表行的描述环境应与事实表行完全对应。维度表通常比较宽，是扁平型非规范表，包含大量的低粒度的文本属性。例如customer（客户表）、goods(商品表)、d_time(时间表)这些都属于维度表，这些表都有一个唯一的主键，然后在表中存放了详细的数据信息。

维度表通常比较宽 ，包含多个属性、是扁平的规范表 ，实际应用中包含几十个或者上百个属性的维度并不少见，所以 维度表应该包括一些有意义的描述，方便下游使用 。

维度表的维度属性，应该尽可能的丰富，所以维度表中，经常出现一些反范式的设计，把其他维度属性并到主维度属性中， 达到易用少关联的效果。

维度表的设计包括维度选择，主维表的确定，梳理关联维度，定义维度属性的过程。

维度的选择一般从报表需求和从业务人员的交谈中发现，主要用于过滤、分组、排序，主维度表一般从业务库直接同步，比如用户表，但是数仓的本身也会有自己的维度，这是因为数仓是面向分析的，所以会有很多从分析的角度出发的维度。

关联维度主要是不同业务系统或者同一业务系统的表之间存在关联性(范式建模)，根据对业务表的梳理，确定哪些表和主维度表之间存在关联关系，并选择其中的某些表用于生成维度属性。

随着互联网的普及，获客成本越来越高，这也使得公司对用户运营提出了更高的要求，不仅需要精细化更需要个性化。解决这一问题的办法之一就是建立相对完备的标签系统，而数仓的标签层对于标签系统而言就像数据仓库对于数据系统一样，有着举足轻重的地位，这样的标签系统需要与业务进行紧密结合， 从业务中获取养分—用户标签，同时也要服务于业务—给用户提供更加精准和个性的服务 。

底层的标签系统就像一个索引，层层展示大千世界，而用户就从这大千世界中不断选择一些东西表明自己的身份和喜好，也不断反哺，使得这个大千世界更加丰富多彩。 其实到最后用户就是一些标签的集合。

对跨业务板块、跨数据域的特定对象进行数据整合，通过统一的ID-Mapping 把各个业务板块，各个业务过程中 同一对象的数据打通 ，形成对象的全域数据标签体系，方便深度分析、挖掘、应用。ID-Mapping 可以认为是通过对象的标识对不同数据体系下相同对象进行关联和识别。对象的标识可以标识一个对象，一般是对象的ID,比如手机号，身份证，登录账号

完成对象的ID 打通需要给对象设置一个超级ID,需要根据对象当前业务体系的ID和获取得到或者计算得到超级ID,进而完成所有业务标识的ID打通一般来说ID打通是建设标签体系的前提，如果没有ID打通就无法收集到一个对象的全面信息，也就无法对这个对象进行全面的标签刻画。

传统的计算方法要有 ID-ID之间的两两关系，例如邮箱和手机号可以打通，手机号和身份证号可以打通，那么邮箱就和身份证号可以打通，但是当数据量非常大，且业务板块非常多的时候，例如有上一个对象，每个对象有数十种ID,这个时候打通就需要非常漫长的计算

那么什么是标签呢，利用原始数据，通过一定的逻辑加工产出直接能被业务所直接使用的、可阅读的，有价值的数据。标签类目，是标签的分类组织方式，是标签信息的一种结构化描述，目的是管理、查找，一般采用多级类目，一般当一个对象的标签个数超过50个的时候，业务人员查找标签就会变得非常麻烦，这个时候我们往往会通过标签类目进行组织管理

标签按照产生和计算方式的不同可分为属性标签，统计标签，算法标签，关联标签。

对象本身的性质就是属性标签，例如用户画像的时候打到用户身上的标签。

对象在业务过程中产生的原子指标，通过不同的计算方法可以生成统计标签。

对象在多个业务过程中的特征规律通过一定的算法产出的标签。

对象在特定的业务过程会和其他对象关联，关联对象的标签也可以打在主对象上。

我们的标签一定是针对用户的，而不是一些虚假、高大上、无用的标签，一定要真实反映用户行为喜好的，所以我们不能只依赖人工智能算法的分析，来完成对一个用户标签的建立与定期维护，我们需要走出去和用户交互，引导用户使用，要抓住用户痛点，及时获取用户反馈，形成闭环。

如何引导使用呢？这个方式有很多我们就不再这里介绍了，后面我们会专门介绍这一层的建设细节。

数据应用层ApplicationDataService面向业务定制的应用数据，主要提供给数据产品和数据分析使用的数据，一般会放在ES，MYSQL，Redis等系统供线上系统使用，也可以放在Hive中供数据分析和数据挖掘使用，或者使用一下其他的大数据工具进行存储和使用。

数仓层，DIM 层，TDM 层是相对稳定的，所以无法满足灵活多变业务需求 ，所以这和数仓层的规范和划分相矛盾，所以我们在此基础上建立了另外一个层，这就是ADS 层，解决了规划稳定和灵活多变之间的矛盾。其实到这里你也就慢慢的看明白了，分层和分类其实没多大差别，其实就是相似的放在一起，有点代码重构的意味啊。

数据应用层，按照业务的需要，然后从统一数仓层和DIM进行取数，并面向业务的特殊需求对数据进行加工,以满足业务和性能的需求。ADS 层因为面向的实众多的需求，所以这一层没有太多的规范，只需要按照命名规范来进行就可以了。

前面也说了，ADS 层因为面向的实众多的需求，所以这一层没有太多的规范，但是ADS 层的建设是强业务推动的，业务部门需要参与到ADS 的建设中来，至少我们得了解用户的痛点才能对症施药啊。

理清需求，了解业务方对数据内容、使用方式(怎么交互的，报表、接口、即席查询、在线查询、指标查询、搜索)、性能的要求。

盘点现有的数仓表是否可以支持，看以前有没有类似的需求，有没有可以复用的接口、报表什么的。

代码实现，选择合适的存储引擎和查询引擎，配置线上监控然后交付。

主要是提供数据产品和数据分析的数据，一般会存放在ES、Mysql、也可能直接存储在hive中或者druid供数据分析和数据挖掘使用。主要 解决部门用户报表和分析需求 而建立数据库，数据集市就代表数据仓库的主题域。

DM 是面向单个主题的，所以它不会从全局考虑进行建设，只专注于自己的数据、往往是某个业务线，例如流量主题、社交主题、电商主题等等。

还有其他的

2 为集群搭大数据环境（一般公司招大数据工程师环境都已经搭好了，公司内部会有现成的大数据平台，但我这边会私下搞一套测试环境，毕竟公司内部的大数据系统权限限制很多，严重影响开发效率）

3 维护大数据平台（这个应该是每个大数据工程师都做过的工作，或多或少会承担“运维”的工作）

4 数据迁移（有部分公司需要把数据从传统的数据库 Oracle、MySQL 等数据迁移到大数据集群中，这个是比较繁琐的工作，吃力不讨好）

5 应用迁移（有部分公司需要把应用从传统的数据库 Oracle、MySQL 等数据库的存储过程程序或者SQL脚本迁移到大数据平台上，这个过程也是非常繁琐的工作，无聊，高度重复且麻烦，吃力不讨好）

6 数据采集（采集日志数据、文件数据、接口数据，这个涉及到各种格式的转换，一般用得比较多的是 Flume 和 Logstash）

7 数据处理

7.1 离线数据处理（这个一般就是写写 SQL 然后扔到 Hive 中跑，其实和第一点有点重复了）

7.2 实时数据处理（这个涉及到消息队列，Kafka，Spark，Flink 这些，组件，一般就是 Flume 采集到数据发给 Kafka 然后 Spark 消费 Kafka 的数据进行处理）

8 数据可视化（这个我司是用 Spring Boot 连接后台数据与前端，前端用自己魔改的 echarts）

9 大数据平台开发（偏Java方向的，大概就是把开源的组件整合起来整成一个可用的大数据平台这样，常见的是各种难用的 PaaS 平台）

10 数据中台开发（中台需要支持接入各种数据源，把各种数据源清洗转换为可用的数据，然后再基于原始数据搭建起宽表层，一般为了节省开发成本和服务器资源，都是基于宽表层查询出业务数据）

11 搭建数据仓库（这里的数据仓库的搭建不是指 Hive ，Hive 是搭建数仓的工具，数仓搭建一般会分为三层 ODS、DW、DM 层，其中DW是最重要的，它又可以分为DWD,DWM,DWS，这个层级只是逻辑上的概念，类似于把表名按照层级区分开来的操作，分层的目的是防止开发数据应用的时候直接访问底层数据，可以减少资源，注意，减少资源开销是减少 内存 和 CPU 的开销，分层后磁盘占用会大大增加，磁盘不值钱所以没什么关系，分层可以使数据表的逻辑更加清晰，方便进一步的开发操作，如果分层没有做好会导致逻辑混乱，新来的员工难以接手业务，提高公司的运营成本，还有这个建数仓也分为建离线和实时的）

总之就是离不开写 SQL ...

【案例】恒丰银行——基于大数据的精准营销模型应用 https://mp.weixin.qq.com/s?src=3&timestamp=1500159788&ver=1&signature=pCHfpePVrKXUGp39JEg577lopIPT9KCdx9FqIL2LbRmunZMQ-86itFcexY XKcX3Vb1ypwGo8v0IU6fkNgcs QIafGAccsZFmMb6yBYcuPdqH63EKBvL88BGFaUrBBPQl0v*mpgzYxrTCkcaJGaX2iIFRHZEDNCmuM0qhqqN294=

本篇案例为数据猿推出的大型 “金融大数据主题策划” 活动 （查看详情） 第一部分的系列案例/征文；感谢** 恒丰银行** 的投递

作为整体活动的第二部分，2017年6月29日，由数据猿主办，上海金融信息行业协会、互联网普惠金融研究院合办，中国信息通信研究院、大数据发展促进委员会、上海大数据联盟、首席数据官联盟、中国大数据技术与应用联盟协办的 《「数据猿·超声波」之金融科技·商业价值探索高峰论坛》 还将在上海隆重举办 【论坛详情】 【上届回顾（点击阅读原文查看）】

在论坛现场，也将颁发 “技术创新奖”、“应用创新奖”、“最佳实践奖”、“优秀案例奖” 四大类案例奖

本文长度为 6000 字，建议阅读 12 分钟

如今，商业银行信息化的迅速发展，产生了大量的业务数据、中间数据和非结构化数据，大数据随之兴起。要从这些海量数据中提取出有价值的信息，为商业银行的各类决策提供参考和服务，需要结合大数据和人工智能技术。国外的汇丰、花旗和瑞士银行是数据挖掘技术应用的先行者。在国内的商业银行中，大数据的思想和技术逐步开始在业务中获得实践和尝试。

面对日趋激烈的行业内部竞争及互联网金融带来的冲击，传统的上门营销、电话营销，甚至是扫街营销等方式跟不上时代的节奏。利用精准营销可节约大量的人力物力、提高营销精准程度，并减少业务环节，无形中为商业银行节约了大量的营销成本。

虽然恒丰银行内部拥有客户的基本信息和交易等大量数据，但是传统的营销系统并没有挖掘出行内大量数据的价值，仍然停留在传统的规则模型。当下，恒丰银行接入了大量的外部数据，有着更多的维度，如果将内部数据与外部数据进行交叉，则能产生更大的价值。客户信息收集越全面、完整，数据分析得到的结论就越趋向于合理和客观。利用人工智能技术，建立精准营销系统变得可能且必要。

恒丰银行基于大数据的精准营销方案是利用大数据平台上的机器学习模型深入洞察客户行为、客户需求，客户偏好，挖掘潜出在客户，实现可持续的营销计划。

周期/节奏

2016.4-2016.5 完成需求梳理和业务调研，并在此基础上进行总体方案设计。

2016.5-2016.8 整理银行内、外部数据，根据营销需求制定客户标签和设计文档，实施用户画像。

2016.8-2016.10 在用户画像的基础上，构建理财产品个性化推荐系统。其中包括个性化推荐算法调研，模型对比等一系列工作。

2016.10-2017.1 客户需求预测并对客户价值进行建模，并完善整合精准营销应用模型。

2017.1-2017.3 用户画像、个性化推荐、客户价值预测等精准营销模型上线。

客户名称/所属分类

恒丰银行/客户管理

任务/目标

根据零售业务营销要求，运用多种数据源分析客户行为洞察客户需求，实现精准营销与服务，提高银行客户满意度和忠诚度。

针对不同的客户特征、产品特征和渠道特征，制定不同市场推广策略。为了完成以上任务，主要从以下几个方面构建精准营销系统：

1.用户画像： 结合用户的历史行为和基本属性给用户打标签。

2.精准推荐系统： 给用户推荐个性化理财产品， 例如在微信银行中给每个客户推荐他喜欢的产品，帮客户找到其最适合的产品，增加产品的购买率。

3.需求预测和客户价值： 新产品发售的时候，找到最有可能购买该产品的客户，进行短信营销，进而提高产品响应率。客户价值精准定位，根据客户价值水平制定不同的推荐策略。银行通过计算客户使用其产品与服务后所形成的实际业务收益，充分了解每一个客户的贡献度，为管理层提供决策支撑。

挑战

项目实施过程由用户画像，精准推荐系统，需求预测和客户价值建模三部分组成，采用TDH机器学习平台Discover所提供的算法和模型库进行开发和验证。

（一）用户画像的建立

客户标签主要包含客户基本属性，客户等级标签，客户偏好标签，客户交易特征，客户流失特征，客户信用特征，客户终身价值标签，客户潜在需求标签。

（二）精准推荐系统的建立

由于系统复杂，且篇幅有限，仅对其中最重要的理财推荐系统做详细阐述。精准推荐系统架构图如下。

2.1业务问题转化为机器学习问题

业务问题

银行理财产品个性化推荐给客户。 例如在微信银行中给每个客户推荐此客户喜欢的产品，帮客户找到其最适合的产品，增加产品的购买率。

将业务问题转化为机器学习问题

理财产品种类繁多，产品迭代速度很快，客户在繁多的产品中不能快速找到适合自己的产品，因此有必要建立一个自动化推荐模型，建立客户理财偏好，给客户推荐最适合的产品。

将银行理财产品推荐业务问题转化为机器学习问题，进而利用人工智能技术提高推荐产品的点击率和购买率。例如在恰当的时间，通过用户偏好的渠道给用户推荐产品，推荐的结果为用户购买或者未购买。这个问题可以看作一个典型机器学习二分类问题：基于历史营销数据来训练模型，让模型自动学到客户购买的产品偏好，并预测客户下次购买理财产品的概率。对模型预测出所有客户对所有产品的响应概率进行排序，可选择客户购买概率最高的topN个产品推荐给客户。

下面将叙述如何构建该推荐预测模型。

2.2数据源准备

在建立的一个理财推荐模型之前，可以预见到相似的客户可能会喜好相似的产品（需要表征客户和产品的数据），同一个人的喜好可能具有连续性（购买历史交易数据，包括基金国债等），他的存款、贷款资金可能决定了他能购买什么档次的理财等等。因此，我们需要准备以下数据。

客户基本属性：客户性别，年龄，开户时间，评估的风险等级等等。

产品基本属性：产品的逾期收益率，产品周期，保本非保本，风险等级等。

客户购买理财产品的历史：在什么时候购买什么产品以及购买的金额。

客户的存款历史： 客户历史存款日均余额等。

客户的贷款历史： 客户历史贷款信息等。

客户工资：客户工资的多少也决定了客户购买理财的额度和偏好。

用户画像提取的特征：用户的AUM等级，贡献度，之前购买基金，国债的金额等。

2.3特征转换和抽取

有了这么多数据，但是有一部分特征是算法不能直接处理的，还有一部分数据是算法不能直接利用的。

特征转换

把不能处理的特征做一些转换，处理成算法容易处理的干净特征。举例如下：

开户日期。就时间属性本身来说，对模型来说不具有任何意义，需要把开户日期转变成到购买理财时的时间间隔。

产品特征。从理财产品信息表里面可以得到风险等级，起点金额等。但是并没有标志这款产品是否是新手专属，是否是忠诚客户专属。这就需要我们从产品名字抽取这款产品的上述特征。

客户交易的时间信息。同客户的开户日期，孤立时间点的交易信息不具有任何意义，我们可以把交易时间转变为距离上次购买的时间间隔。

特征抽取

还有一部分数据算法不能直接利用，例如客户存款信息，客户交易信息。我们需用从理财交易和存款表中抽取可能有用的信息。

用户存款信息：根据我们的经验，客户购买理财之前的存款变动信息更能表明客户购买理财的真实想法，因此我们需要从客户历史存款数据抽取客户近三个月，近一个月，近一周的日均余额，以体现客户存款变化。

客户交易信息：客户最近一次购买的产品、购买的金额、及其相关属性，最近一个月购买的产品、购买的金额及其相关属性等等。

以上例举的只是部分特征。

2.4构造、划分训练和测试集

构造

以上说明了如何抽取客户购买理财的相关特征，只是针对正样本的，即客户购买某种理财时候的特征。隐藏着的信息是，此客户当时没有购买其他在发售的产品。假设把客户购买了产品的标签设为1，没有购买的产品样本设为0，我们大致有如下训练样本（只列举部分特征）。

其中客户是否购买产品是我们在有监督训练的标签，也就是我们建立的是一个预测客户是否会购买产的模型。

划分训练集和测试集

考虑到最终模型会预测将来的某时间客户购买某种产品的概率，为了更真实的测试模型效果，以时间来切分训练集和测试集。具体做法如下。假设我们有2016-09-01 ~ 2017-03-20 的理财购买相关数据。以2016-09-01 ~ 2017-03-19的理财交易数据作为训练，2017-03-20这一天的客户对每个产品是否购买的数据作为测试。以2016-09-01 ~ 2017-03-18的理财交易数据作为训练，2017-03-19这一天的客户对每个产品是否购买的数据作为测试，以此类推。

2.5模型训练

根据提取的特征，组成样本宽表，输入到分类模型，这里选择了TDH平台机器学习组件Discover所提供的近百个分布式算法进行建模和训练，同时我们还使用了特征的高阶交叉特性进行推荐的预测和分析。

2.6模型评估

评价推荐好坏的指标很多，比较常用的有

1.ROC曲线下面积（AUC）

2.logloss

3.推荐产品第一次命中rank的倒数（MRR）

4.TopN

针对银行的理财推荐实际业务，客户当天绝大多数是只购买了某一款理财，MRR(Mean Average Precision 的特殊情况)能反应这种情况下推荐的好坏。另一种直观的评价指标是TopN，假定我们只推荐N个模型认为客户最有可能购买的产品，并和真实情况比较，就能得到当天推荐的结果的混淆矩阵，TN，TP，FN，FP，recall，precision等。

我们在生产上验证了最近十天的推荐效果，即测试了2017-03-20， 2017-03-19，…… , 2017-03-11等十天的推荐效果，以下是这些结果的评价。

AUC

Logloss

MRR

0.89

0.45

0.78

也可以把新客户（之前没有购买理财）和老客户（至少购买过一次）分开评估效果。 新客户的购买占了整个理财购买的1/3 以上。

测试新客户的预测效果，可以看出模型对冷启动问题解决的好坏。

对新客户的预测效果

AUC

Logloss

MRR

0.80

0.73

0.32

对老客户的预测效果

AUC

Logloss

MRR

0.92

0.38

0.88

2.7模型优化

1.上线之前的优化：特征提取，样本抽样，参数调参

2.上线之后的迭代，根据实际的A/B testing和业务人员的建议改进模型

（三）需求预测和客户价值

“顾客终生价值”(Customer Lifetime Value)指的是每个购买者在未来可能为企业带来的收益总和。研究表明，如同某种产品一样，顾客对于企业利润的贡献也可以分为导入期、快速增长期、成熟期和衰退期。

经典的客户终身价值建模的模型基于客户RFM模型。模型简单的把客户划分为几个状态，有一定意义但不一定准确，毕竟RFM模型用到的特征不全面，不能很好的表征客户的价值以及客户银行关系管理。

为了方便的对客户终身价值建模，有几个假定条件。其一把客户的购买价值近似为客户为企业带来的总收益，其二把未来时间定义在未来一个季度、半年或者一年。也就是我们通过预测客户在下一个时间段内的购买价值来定义客户的终身价值。因此，我们将预测的问题分为两个步骤：第一步预测这个客户在下一个阶段是否会发生购买（需求预测）。第二步对预测有购买行为的客户继续建模预测会购买多大产品价值。

3.1需求预测

提取客户定活期存款、pos机刷卡、渠道端查询历史等特征，以这些特征作为输入预测用户在当前时间节点是否有购买需求，训练和测试样本构造如下：

1.历史用户购买记录作为正样本。

2.抽样一部分从未购买的理财产品的用户作为负样本集合Un，对于每一个正样本Un中随机选取一个用户构造负样本。

3.选取2016.04-201610 的购买数据作为训练样本，2016.11的数据作为测试样本。

使用机器学习算法进行分类训练和预测，重复上述实验，得到下列结果:

AUC: 0.930451274

precision: 0.8993963783

recall: 0.8357507082

fmeasure: 0.8664062729

进一步对客户分群之后，可以更好的对新客户进行建模，对于老客户我们可以进一步提取他们的历史购买特征，预测他们在下一段时间内购买的产品价值（数量，金额等），对于新客户，可以进根据他的存款量预测其第一次购买的产品价值，把存款客户变成理财客户。通过分析客户存款变动于客户购买理财的关系，我们发现客户购买理财的前一段时间内定活期的增加的有不同的模式，如下图。

根据需求预测模型，我们给出新客户最有可能购买的top N 列表，然后由业务人员进行市场推广。

3.2客户价值预测

进一步预测有购买需求的客户的购买价值高低。这是个回归问题，但是预测变量从二分类变量变为预测连续的金额值。训练的时候预测值取训练周期内（一个月或者季度）客户所购买的总金额。

算出客户的当前价值（即当前阶段购买的产品价值）和未来价值（预测的下一个阶段的客户价值）可以帮助我们鉴定客户处于流失阶段，或者上升阶段，或者是稳定阶段。当前价值取的是当前时间前三个月的交易量。对流失阶段高价值客户可以适当给予营销优惠，对于有购买意向的客户适当引导。如下图所示。

结果/效果

一是提高银行营销准确性。随着客户不断增加，理财产品也在不断推陈出新，在实时精准营销平台的帮助下，银行从以前盲目撒网式的营销方式转变到对不同客户精准触达，提高了理财产品的营销成功率，降低销售和运作成本。理财产品推荐的上线以来，产品推荐成功率比专家经验排序模型最高提升10倍。

二是增加银行获客数量。精准营销系统洞察客户潜在需求和偏好，提高了银行获取目标客户群的准确率。从数百万客户中,通过机器学习模型,找到最有可能购买产品的客户群，通过渠道营销，实现响应率提升。相比传统盲发模式，发送原38%的短信即可覆盖80%的客户。

通过构建基于大数据的精准营销方案，恒丰银行深入洞察客户行为、需求、偏好，帮助银行深入了解客户，并打造个性化推荐系统和建立客户价值预测模型，实现可持续的营销计划。

近期成为月入两万的数据分析师的广告遍地都是，可能会对一些未入行的同学造成错觉。我个人感觉数据分析师这个岗位，可能近几年会消亡。

这不意味着这份工作本身不重要，而是说这份工作本身可能会转化为产品运营的一些必备技能，而不再需要单独特设人力去做这件事。或者说，不是再需要你学习SQL或者学习python，只是为了成为一名数据分析师。作为一名数据分析师，职业自身的壁垒正在不断消减，更加主动的拥抱业务，解决真正的产品和用户需求，或将成为未来的发展趋势。

数据分析师的日常工作

我们来看下预设中的分析师的一些工作场景，看看数据分析师核心的工作价值。

取数

数据清洗

数据可视化

统计分析

数据方向建设和规划

数据报告

取数 — SQL

很多人对数据分析师的预设是SQL达人，包括现在很多数据分析师的核心工作其实就是进行SQL取数。

这项工作的痛点和难点在于，我们为了得到一个结果，通常需要join很多的数据集，然后整个SQL语句就会写的特别长，而且可能会出现一些问题：比如join的表可能会出现key是重复的情况，造成最终的SQL结果因为重复而变得不可用。所以我们需要专人去专门维护各种各样的数据集，他们知道每张表应该怎么用。

但这个其实是关系型数据库遗留下来的产物——我们完全可以不需要join那么多的表。现在的分布式计算的框架，已经完全可以支持我们只保留一张大宽表，有需要的所有字段，然后所有的操作都在这张大宽表上进行，而且可以保证查询速度。这样数据分析最大的痛点已经没有了。至于你说大宽表里面存了很多重复的数据，是不是很浪费资源(关系型数据库之所以不用大宽表就是从存储空间和性能的trade-off角度考虑的)：放心，分布式存储本身是不贵的，而计算效率则是由分布式计算框架进行专门优化的。现在的计算框架计算的响应速度，已经可以在大宽表上可以很快的得到结果了。相比之下，多次join操作反而可能会更慢一些。

同时，现在很多公司的NB框架，其实都已经支持拖拽取数了，也根本不需要写SQL了。

此外，不得不说的一点是，SQL语句本身真的不难。可能如果你自己静下心来想学，一个周末的时间肯定能搞定。而资历老的数据分析师，并不会比资历轻的数据分析师，在SQL语句的写作上有什么本质的区别。以前可能还有一些小表join大表的trick，但现在计算框架大多都已经优化过这些了。所以即使是需要写SQL的场景，本身也是没有什么难度的。

所以，通过大宽表来解放数据分析工作的生产力。即使在一定要写SQL做join操作的时候，本身也不是一件壁垒特别高的事情。取数这件事儿，对于其他岗位的同学，就已经没那么复杂了。

数据清洗 — Python

数据清洗其实是很多强调python进行数据分析课程中，python部分的主要卖点。包括但不限于，怎么处理异常值，怎么从一些原始的数据中，得到我们想要的数据。

在日常产品需求过程中，这种需求的场景其实很小。因为数据大部分都是自己产生的，很少会出现没有预设到的极端值或者异常情况。如果有的话，一般就是生产数据的同学代码写的有bug，这种发现了之后修复代码bug就行。

数据清洗在工作场景的应用在于落表——就是把原始数据变成上面提到的，可以通过SQL提取的hive表。这个工作是需要懂代码的同学去支持的，他们负责数据的产出，包括数据的准确性，数据的延时性(不能太晚产出)等等。前文提到的生成大宽表，其实也可以是他们的工作。这其中就涉及到一些代码的效率优化问题，这个就不是简单懂一点python可以搞定的了，可能涉及到一些数据压缩格式的转化，比如Json/Proto buffer到hive表的转化，还有一些计算框架层面的调优，比如spark设置什么样的参数，以及怎么样存储可以更好的提升查询速度。

所以这部分工作一般是由懂代码的同学完成的。可能数据团队会有比较少数的同学，管理支持全公司的基础表的生成。

数据可视化 — Tableau

很多之前在数据分析做实习的同学，主要的工作内容就是在一个商业化的软件(比如Tableau)上，做一些统计报表。这样可以通过这些数据报表，可以很方便的查看到所属业务的一些关键指标。这些商业软件通常都比较难用，比如可能需要先预计算一下才能输出结果；而且不太好做自定义功能的开发。稍微复杂一点的需求场景，可能就需要一个专门的同学捣鼓一阵，才能输出最终的统计报表。

现在有更先进的套路了。

首先可视化。很多公司打通了前端和后端的数据，这样就可以通过网页查询原始的数据库得到数据结果。而现在很多优秀的前端可视化插件，已经可以提供非常丰富的统计图形的支持。而且因为代码是开源的，可以根据公司的需求场景进行针对性的开发，公司可以再辅以配置一些更加用户友好的操作界面，这样一些复杂需求也有了简单拖拽实现的可能。而且这些前端js代码都是免费的！对于公司来说也能省去一笔商业公司的采买成本。

其次很多商业软件，都是针对小数据集场景设计的。在一些大数据集的场景，一般需要先预计算一些中间表。而如果自己公司定制化开发的前端展示结果，就可以根据需要自主设置计算逻辑和配置计算资源，先在后端进行预计算，前端最终只是作为一个结果展示模块，把结果展示和需要的预计算进行解耦。这样就省去了很多中间表的产出，也会更加快速的得到想要的业务指标，快速迭代。

所以可视化数据的工作量也会大大减少。而且会变成一个人人都可以操作，快速得到结果的场景。

统计分析

对于一名数据分析师而言，统计学分析可能是一块知识性的壁垒。尤其是在现在ab实验成为互联网公司迭代标配的今天。需要把实验设计的那套理论应用起来：比如ab实验进行后的显著性检验，多少样本量的数据才能让这个结论有效可信呢。

但是，你我都知道，经典的统计分析其实是一个非常套路性的工作。其实就是套公式，对应到代码层面，可能也就一两行就搞定了。这个代码的统计分析结果可以作为ab平台的指标展示在最终的ab结果上，大家看一眼就能明白。即使是对那些可能不知道显著性是什么意思的人，你可以跟他简单说，显著了才有效，不显著就别管。

这么一想是不是其实不怎么需要投入额外的人力进行分析？

其他数据相关的工作

数据层面的规划和设计。移动互联网刚刚兴起的时候，可能那时候数据分析师需要对每一个数据怎么来设计一套方案，包括原始的埋点怎么样，又要怎么统计出想要的结果。但现在大部分已经过了快速迭代的时代了，新产品的埋点添加可以参考老产品，这就意味着形成套路了。而一旦形成套路，其实就意味着可以通过程序直接完成或者辅助完成。

数据报告。那就真的是一件人人都能做的事情了，试想谁没在大学期间做过数据报告呢？以前只是因为数据都是从分析师产出的，而如果人人都能取到数据的话，数据报告是不是也不是一个真需求呢？

在我看来，数据分析师这个岗位的天花板和其他岗位相比起来是比较低的。可能工作一两年之后，从岗位本身就已经学不到什么额外的工作知识了。主要的工作内容技术含量不是特别高，技能性的更多的是一些可以简单上手的东西，而且做的时间长了，在这些技能性的事情上得到的积累并不是很多。

数据分析师更像是一个在时代变迁过程中的一个中间岗位：我们从一个基本没有数据的时代，突然进入了一个数据极大丰富的时代，在这个过程中，我们都知道重视数据。那怎么能够利用这个数据呢？可能之前的那一帮人并没有太多的经验，于是老板就招一些人专门来研究一下它，同时做一些底层数据的优化。

经过多年的迭代，现在互联网行业的每个人都知道数据的价值，也大概知道了什么样的数据是重要的，怎样可以更好的挖掘数据背后的价值。同时底层的基础设施也已经支持可以让一个之前没有经验的同学可以快速的上手得到自己想要的关键数据。这时候对于一个职业数据分析师来说，他的任务就已经完成了。就如同当人人都会讲英语的时候，翻译其实也就没有存在的价值了。

此后的数据分析工作，可能不再是一些单独的人做的工作。它会变成一个产品和运营的基础工具，而且足够简单，没有取数的门槛。只是产品运营怎么样可以更好的认识数据，通过数据本身更好的配合产品运营的工作，这已经超脱我们一般理解的数据分析师的工作了，而是一个产品运营分内的工作。

对于那些已经在从事数据分析师岗位的同学来说，建议不要把心思全部投入到数据分析的本职工作上，以完成任务为核心KPI。而是不要给自己设置边界，多从用户的角度思考问题，不要因为是产品运营的工作就不去做了。数据分析师这个职业发展到这个阶段，要么做更加底层的数据建设，要么拥抱业务，最大化的发掘数据背后背后的价值。不要再死守着数据分析的“固有技能”沾沾自喜了。

数据本身的价值是无穷的，作为数据分析师，你们已经先人一步的掌握它了，要有先发优势。你们最接近数据的人，是最可能发现用户的宝藏的人。