硬核数据分析(一)——数据库选型及一个简单案例

变废        2019-08-13   来源:豆豆君大世界

作为硬核数据分析系列的正篇第一节,主要介绍数据库工具的选型,并给出一个简单的案列,案列中涉及的内容将在以后各节进行展开。


从数据库到大数据

 

其实数据库的主要目的是用于记录信息并处理业务逻辑的,可以看做是“图书馆管理”这门高深学问在现代科技下的延伸。记得作为券商资管大佬的某研究生室友当年拿到某行工作Offer以后跟我们惊呼“天啦,居然有个同事的专业是图书馆……”,当然后来大家都改名叫管理信息系统(MIS)了。



按照目前通行的说法,数据处理大致可以分成两大类:联机事务处理OLTPon-line transaction processing)、联机分析处理OLAPOn-Line Analytical Processing),“联机”这个词有点费解,我的理解就是——“要快”。数据库主要就是用于OLTP的,特长是很快地标准化处理各类信息,例如银行的存款、转账、外汇交易等,由于数据量不断积累,往往不能很快地进行分析;OLAP则注重大量数据的汇总分析与展示,主要供经营决策使用,典型的运用场景就是数据仓库。

 

上述应用主要还是在关系型数据(Relational Data)领域,建立在关系模型基础上,其数学基础主要是集合代数。数据的具体载体是“表”(Table,表中的“字段”(Field,又可以更粗暴的直接叫做“列”,column)需要预先定义。各位交易大佬在输机界面上哼哧哼哧选择或者敲击一堆信息的时候,就是在填写一堆相关联的表。这些信息,我们都叫做“结构化信息”(Structured Information

 

但是毕竟有很多信息不是能够完全标准化的,比如各类图片、文章等等。还有一类情况就是:大家都建立了自己的数据库,按照自己的定义整理数据,字段起的名字都不一样,要把这些信息合在一起,预定义一个兼容所有格式的表来似乎有点麻烦。随着这种情况越来越多,为了处理这些高速积累的信息,所谓“非机构化数据”处理,“大数据”应用的工具就应运而生了。


上面这些东西,不管我们熟悉不熟悉,都已经大规模地在日程工作和生活中运用了,就如同我们不知道APP后面是啥,但是也点得很欢一样。在这个过程中,技术人员与业务人员或者说用户之间产生了一条巨大的鸿沟——反正你也弄不明白,我就把一切都做到背后,你们能点过去就行了。

 

我们的目标

 

不管数据怎么被分析和运用,对于数据源的管理永远是最重要的——所谓Garbage in,garbage out. 基础数据一团糟,所谓的人工智能也只能学成弱智。阿拉法狗之所以那么强,跟它的训练材料有直接关系——那可都是日本等国长期积攒下来的真实案例。

 

所以在数据分析中首要要解决的问题就是,如何有效地去管理数据源,这涉及到三个问题:获取数据、更新数据、分工协作。在一个团队内部,如果大家能够按照既定标准,有条不紊地去获取和更新所分派的数据,那么,这个团队的工作意识和工作成效就真正地向着现代化的数据治理迈进了一大步。更进一步,如果我们能够稍微多一点地了解数据在当今社会是怎么被组织和使用的,就能更有效率地和技术人员配合开发出真正能用的分析工具,省时省心省钱。

 

作为金融从业人员,比如说资管业搬砖人,每天都在跟各类数据打交道,宏观的,微观的,交易的,管理的……随着市场变化和监管动态,各类分析口径花样翻新,如果每一次分析都是东拼西凑,标准不统一,方法不规范,势必浪费感情浪费时间浪费生命,没准还要跟同事、跟领导、跟合作伙伴互相伤害。

 

为了更顺畅地分析数据,把更多的宝贵时间用于真正地分析业务趋势和市场走势,我们可以提升的空间还很大,特别是在工具的使用方面。这其中残酷的潜台词是,即使数据得到了优化,真正能够分析好市场还是……很难……。但是能花点时间和精力去实现切切实实改善的事儿都不做,其他的估计也好不到哪儿去把。

 

根据个人的实践,针对大多数资管和中小银行资金业务的场景,建立一个分析数据库,结合适当的前端分析语言,能够解决大多数数据管理和数据分析上的痛点。如果数据量进一步增长,或许可以考虑求助于数据仓库(其实通过定期跑批存储分析结果的形式,也可以在数据库中很好地处理分析效率的问题),至于大数据,我个人是看不到有那么迫切的需求。银行业务那么大的数据量都没敢号称大数据,某些放在云端跑个小服务器的应用就敢大言不惭,可能也是欺负我们不懂吧。


所以短期内的目标就是——建立一个数据库,并不断丰富完善相关的分析应用。


数据库选型

 

目前各类数据库软件可谓琳琅满目,DB-Engines的排名里面就含有343个。我个人这些年试用过的包括微软Access,微软SQL ServerMySQLPostgreSQL,各种特点和比较我应该能够说上半天,这里就不展开了。

 

直接给结论——简单体验一下可以考虑Access,新手想深入选MySQL,严谨的老司机可以考虑PostgreSQL,不怕一直被微软绑定也可以SQL Server(开发版免费)Oracle太贵一般人别碰。


数据来源:DB-Engines Ranking


从发展趋势看,排名前三位的大佬地位不可撼动,近年来PostgreSQL在海外非常火,属于学院派的开源软件(克利主导开发),可以使用各类编程语言编写函数。一度想把主要使用的数据库切换到PostgreSQL,但是这玩意儿对于新手不是那么友好,有一些奇怪的坚持,比如不允许更换数据表各列的位置,在修改函数和视图的时候得把握好各类依赖关系,严谨是严谨,但是麻烦啊。


数据来源:DB-Engines Ranking


作为一个有点追求的新手,我选择MySQL,这玩意儿有一些对于新手来说不得不说的优点:

 

  1. 不要钱,开源。

  2. 由于电商去IOE的时候大量采用MySQL,在国内用户群体庞大,学习资料多。

  3. 数据库对象结构简单清爽,建立了database之后直接就可以建表了。

  4. 对中文支持到位。

  5. 更新表结构容易(很重要!比如可以调整字段的顺序……),第三方支持多,连接各类数据源方便。

  6. 官方和第三方的图形化管理界面凑合能用。

 

当然,作为一个小型的开源数据库,MySQL弱项也不少,比如写入速度啥的明显比PostgreSQL慢。但从实践结果来看,除了备份恢复的速度会慢一些,在其他方面足够满足要求。


一个简单案例——股票质押数据


基于MySQL给一个简单的分析案例,近一年来股票质押业务实在是搞得一言难尽,我们使用数据库加SQL来简要分析一下存量业务数据。

 

明确分析目标


所有A股在中登公司的存量质押信息(包含了公开披露的和不需要披露的,口径最全),并检索到相关公司已披露的股权质押信息明细信息,最后按照企业属性等信息对质押情况进行分类汇总分析。


准备数据来源


我们讲究能一手信息就一手,比如中登公司的股票质押信息就直接去网站下载。


 

公开披露的明细信息wind资讯估计是通过文本分析的手段给扒下来了,先凑合用一下,举个列子,就下载最近一年的。



然后为了分析口径,从wind上扒出全量的A股列表,以及发行人、公司属性、实际控制人等信息。按照时间序列扒出股价信息。


建立数据库

 

连上MySQL数据库,新建一个amazing database(或者叫schema),使用gbk编码和排序(默认的是utf8,但是这个在按照中文读音排序时不准)。



设计数据表

 

Stock_pledge表存储中登的截面数据信息,因为是每周发布一次,带一个日期字段就可以组成时间序列了。



Stock_pledge_detail表存储wind扒出来的明细交易。



另外还有stock_list表存储股票列表和分类分析口径信息,stock_price存储股票价格信息,可以使用wind公式或者量化借口扒下来。

 

上述表设计是参照了数据库设计范式的,主要原则就是各表之间通过关键字段想联系,除此之外尽量不要有重复信息,使得各表都可以方便地被分别增、删、改、查。可能有些朋友会问为什么没有建立表之间的关系(relationship),这是个人习惯问题,为了一些不必要的麻烦,我一般不建立外键和关系,有必要的话通过前端语言进行控制。这些话题留待以后再具体讲。


装载数据

 

既然是要提高数据管理的效率和标准化程度,理想的做法是下载源文件之后由程序自动完成数据的增加和更新。这个其实有很多工具,使用VBAPython或者一些第三方的数据库管理软件都挺好。

 

至于我们,就稍微硬核一点了,自己写了一个Excel插件,如图,很美观吧,还可以加上一些自吹自擂的标题,够鸡血。更新数据的原则很简单——与原数据不冲突则新增数据,冲突则更新内容。以后会将相关的源代码和使用方法分享给大家。



分析与展示

 

于是数据很快就集中了,由于是演示用,一些细节还不是很完备,实际上要清晰、靠谱地展示证券、发行人、交易相关的各类信息,是需要做很多标准化工作的,比如,发行人就经常改名字吧……后来发现证券代码都是可以改得。

 

对于数据库的展示现在也是越来越方便的,比如2016版的Excel,非常清新,直接就连上去了,可以导入数据库中的表和“视图”。



上面提到的 “视图”(view)可以理解为一类虚拟表,使用实体表计算生成。我这里就写了两个简单的查询,用于分类股票质押的存量信息。下面几个图表都是Excel的数据透视表生成的,其数据源直接使用了数据库的查询结果。如果配合上Power BI一类的工具,会更加美观。

 

更重要的是,这些事情在标准化之后,剩下的事情就是更新数据库和刷新图表了,以及在此基础上做更加广泛和深入的数据分析。





至于明细项查询功能,方式也很多,可以使用Excel,Access作为前端界面,也可以使用一些网页类的展示工具。

 

Excel的例子,还是我们的万能插件,直接在单元格里写SQL语句,相当硬核。



Access的例子,几分钟就可以做一个窗体,使用链接表读取MySQL数据库

的表和查询,还可以把文本框信息传过去做筛选。



这一节的内容先到这里,前面提到的内容,我都会以一定的方式进行总结。这些年的数据整理和分析工作耗费了我不少精神,趁这次机会记录下来。由于大多数时间都是一个人瞎琢磨,所以很多地方都有血的教训。希望在分享的过程中,能够让部分朋友对相关的概念甚至事件提起一些兴趣来。

 

毕竟数据的整理和分析,在展示过去的基础上,更多的还是要面向未来。我今后的学习和实践,也会更多地朝那个方向去努力。令人欣喜的是,随着各种工具的发展,不仅思维方式和工作方法不会过时,就连积累的各种工具技能也能不断地在新的场景中得到应用。比如,对SQL掌握略微熟练以后,忽然找到了一个网页BI平台就是基于SQL的,让人觉得自己所做的事情并不是那么非主流。



本人不是专业IT人员,只是出于个人兴趣学习过一些工具和方法,如有错误及不当之处,请随时指正。


相关阅读