跳至正文

分析常识-1:数据库、数据表、数据查询

喜乐君:本文为某航空客户培训而准备,在讲解Tableau之前,普及数据常识;三月在客户咨询过程中增补第一部分;五月增补第三部分。后期会增补并列入《大数据业务分析常识》一书中。

V 1.0 Mar 7, 2023 (客户A)
V 1.1 Mar 15, 2023 补充第一部分(客户B)
V1.2 Mar 19, 2023 更新英文素材,调整部分细节。
V1.3 May 11, 2023 补充第三部分计算

理解数据是分析的基础,其中有数据库Database、数据表table、数据查询SQL三个关键。

1、数据库:数据仓储之地

当前主流的数据库是关系型数据库(relational database),关系数据库建立在“集合论”和“谓词逻辑”基础之上。

按照功能来分,数据库可以分为面向运营过程的运营数据库(operational database)和面向分析的分析数据库(analytical database,又称数据仓库data warehouse)两种。两者都是数据库架构,但功能不同、特色迥异,前者为所有公司“必选”,后者是大公司“必选”。

简单的说,业务数据库是支持业务每分每秒的正常运行,用于采集数据,追求高速读取的性能、稳定性(比如零售公司持续一天的收银业务);数据仓库旨在支持大数据量的抽象分析,用于数据加工,不追求实时性能,但查询量较大(比如一次性分析十年数据),对稳定性要求较低,存储和计算要求较高。

从过程来看,数据库的前序环节是业务,后面环节是分析和决策,因此可以用如下的图示理解。

数据库及其前后关系

这里引用“Database Systems_Introduction to Databases and Data Warehouses”书中的几句话,我个人特别喜欢,并翻译如下:

The term operational information (transactional information) refers to the information collected and used in support of the day-to-day operational needs in businesses and other organizations. Any information resulting from an individual transaction, such as performing an ATM withdrawal or purchasing an airline ticket, is operational information. That is why operational information is also sometimes referred to as “transactional information.

“运营信息(交易信息)”指的是企业或其他组织中支持日常运营过程而产生的信息。每一次交易产生的信息都是“运营信息”,比如ATM取款或者购买机票。这也是“运营信息”常被称之为“交易数据”的原因。

Operational databases collect and present operational information in support of daily operational procedures and processes,……“运营型数据库”用于收集和展现日常运营活动和过程中的相关数据。

The term analytical information refers to the information collected and used in support of analytical tasks. An example of analytical information is information showing a pattern of use of ATM machines, such as what hours of the day have the most withdrawals, and what hours of the day have the least withdrawals. The discovered pat- terns can be used to set the stocking schedule for the ATM machines. 

“分析信息”指用于分析工作中收集和用到的信息。通过分析ATM机器的使用规律,比如取款高峰、低谷的时间段,可以用来指导ATM机的现金部署计划。

Note that analytical information is based on operational (transactional) information. 注意,分析数据来自于运营数据。

【增补Mar 15, 2023】昨日在客户处,银行背景的客户总裁被一些错误的信息误导,反复质问数据仓库团队“一些整合计算能否放在业务数据库中、业务数据库的数据有无必要全部复制一遍到数据仓库、数仓是否可以由业务用户搭建”等问题,听得出来,对基础知识缺乏必要的了解。因此,我补充了几句:

  • 业务数据库不能增加复杂计算,数据仓库必须与业务数据库分开,则是原则性要求。数据仓库也必须备份。原因一是数据安全,二是确保业务系统稳定性。
  • 业务数据库的唯一职责是确保系统稳定、客户体验顺畅,历史数据都不必全部留存;保存是数据仓库职责。
  • 数据仓库的必要性,一是数据安全,资产必须备份,二是确保业务系统稳定性,客户操作不能被分析计算影响
  • 数仓是专业领域,不管是数据中间表的整合、处理,还是逻辑表的关系搭建,都具有很高的学习成本。虽然业务用户可以使用敏捷ETL(比如Prep)完成部分任务,但建议放在半年之后BI系统搭建之后考虑。

在结构上,一个数据库通常划分为不同的区域存放不同的主题数据,常常称之为架构(schema),每个区域的数据都分门别类地保存为数据表(table)。

添加图片注释,不超过 140 字(可选)

在分析过程中所,经常会遇到数据表的合并操作——可能是跨数据库的,也可能是跨schema的,甚至可以是自己和自己的聚合表相连的(也就是SQL中的自连接self-join)。

2、数据表:数据存储、查询的基本单位

既然数据库的首要功能是记录业务,所以数据表不断追加的明细通常称之为“记录”(record),每一条记录对应一笔业务过程,比如“谁在何时、何地、以何种方式、给谁、提供了什么产品,并以多个值度量之”。

与明细行对应的是列,每个列对应一个业务对象(比如客户),对于完整的业务过程而言,每个列都称之为一个属性(attribute)。在分析中,我们常常习惯用“字段field”称之。

从业务角度看数据表的构成,数据表就是业务过程、业务对象的交织,其中明细行(record-row)对应业务过程,字段(field- column)列对应业务对象。

添加图片注释,不超过 140 字(可选)

特别注意的是,数据库非常追求规范,从而确保它在超级数据前的性能和稳定性。这和Excel的超级自由、非常灵活形成了鲜明对比。

正因为此,Excel中的大部分技巧、习惯,恰恰是数据库的陷阱,比如合计单元格、多行表头、空格、空行、排序字段、“100元”这样的数据值。

有几个关键,可以结合几个问题来理解。

1)相同属性的业务对象必须 是一列 (column = attribute)

Q:为什么用“2018年销售、2019年销售、2020年销售……”的多列数据表不规范?

2)数据行不得重复,唯一性,因此可以用唯一字段/组合分辨数据表 (PK=详细级别)

Q:客票数据表、订单数据表、客户信息表。每个数据表的标记符,基本就是唯一字段。

3)数据表的每一个值都必须是不可拆分的(最小的业务对象)

Q:Excel中常见的“1000KM”“23岁”是不规范的表达

3、数据查询:一切查询都是计算

SQL(结构化查询语言)是面向数据库的最重要的查询工具。

相比Excel的存取一体,SQL实现了和数据库的存取分离,这是稳定性、安全性的要求,如同银行的金库和我们取款的过程。到了Tableau或者其他可视化工具中,查询又进一步细分为查询、可视化展现两个过程,进一步增强了表现力。

对于查询来说,起点是数据表,终点也是数据表——这一点非常重要。所以SQL查询必然是table到table的过程,为了区分,前者称之为base table底表,后者称之为result table结果表。单表查询有两类基本操作:

  • 查询指定的字段列:投影project
  • 查询指定的明细行:筛选filter

在早期的SQL中,它们分别用project和restrict两个语法来实现,后来逐渐合流,只需要SELECT结合WHERE就可以实现任意一种及其组合。也正因为此,在执行时Where优先级高于SELECT——先要确定查询范围,然后再去查询。

单表查询只是一种简单情形,适用于非常简单的业务场景;由于运营数据库的数据表设计是“反业务”、追求特定数据范式的,多表合并就变得异常普遍,如同Excel的Vlookup合并一样;和vlookup不同,两个不同的数据表Join时,还可以借助连接方式的设置,间接完成筛选,比如Inner join。

  • 多表合并之join:join既是两个数据表合并的方式,也是数据筛选的方式,借助inner join可以减少数据行数。

查询过程中的所有操作都可以称之为“计算”——这就是“计算机”compute名称的部分由来。

为了方便理解计算的类型,我们可以把分析中的计算区分为:以表为对象的计算、以字段为对象的计算。或者称之为“表操作”(table maniplulation)和“字段操作”(filed manipulation),它们二者是统一的。为此,可以用如下的图示来理解:

几种最常见的字段操作:

  • 数据分组聚合:GROUP BY
  • 数据聚合: Aggregation,和分组紧密依赖,又有明显的先后关系——分组聚合是分析计算的精髓,是核心,是查询和分析的分水岭。
  • 聚合后再筛选:having——指定聚合字段设置筛选条件,比如having SUM(sales) >1000
  • 结果排序:order by ——指定字段作为排序对象,并可以设置升序、降序

上述这些操作,就构成最常见、最基本的SQL运算了。

虽然SQL还有很多更强大的功能,比如写入、修改、视图等等,不过对于初学者而言,建立如上的基本知识,就已经是非常好的开始了。 

基于这样的框架继续展开,其实就是计算的完整体系,包括如下的内容:

  • 数据库计算的类型(表操作、字段操作)
  • 数据表的基本类型(物理表、逻辑表)
  • 表操作的基本类型(投影、筛选、合并)
  • 数据合并的基本类型(join、union)
  • 数据筛选的基本类型(行级别筛选、聚合后筛选、执行级别的筛选、TOP筛选等)
  • 字段操作的基本类型(非聚合、聚合;行内计算、跨行计算)
  • ……

上述各种要素的组合,则又构成了更加复杂而完整的体系,比如数据合并的分类矩阵(数据表类型*合并的类型,构成四种可行性)、计算的分类矩阵(非聚合、聚合,与行内、跨行的组合),等等。

喜乐君

Feb 27, 2023
Mar 19, 2023
May 11, 2023 修改,补充最后查询部分

推荐两本个人学习SQL的书:MICK的《SQL基础教程》和《SQL进阶教程》。

了解 喜乐君 的更多信息

立即订阅以继续阅读并访问完整档案。

Continue reading