我的编程空间,编程开发者的网络收藏夹
学习永远不晚

Quick BI 的模型设计与生成SQL原理剖析

短信预约 -IT技能 免费直播动态提醒
省份

北京

  • 北京
  • 上海
  • 天津
  • 重庆
  • 河北
  • 山东
  • 辽宁
  • 黑龙江
  • 吉林
  • 甘肃
  • 青海
  • 河南
  • 江苏
  • 湖北
  • 湖南
  • 江西
  • 浙江
  • 广东
  • 云南
  • 福建
  • 海南
  • 山西
  • 四川
  • 陕西
  • 贵州
  • 安徽
  • 广西
  • 内蒙
  • 西藏
  • 新疆
  • 宁夏
  • 兵团
手机号立即预约

请填写图片验证码后获取短信验证码

看不清楚,换张图片

免费获取短信验证码

Quick BI 的模型设计与生成SQL原理剖析

Quick BI 的模型设计与生成SQL原理剖析

一、  摘要

随着互联网的高速发展,数据量呈现井喷式的增长,如何来分析和使用这些数据,使数据产生商业价值,已经变得越来越重要。值得高兴的是,当前越来越多的人已经意识到了用数据分析决定商业策略的重要性,也都在进行着各行各业的数据分析。众所周知数据分析的核心是数据,为了更容易的分析数据,数据模型的设计需要遵循一定的规范。当前最流行的联机分析处理(OLAP)的规范为维度建模规范。本文介绍Quick BI如何进行维度建模,基于维度模型如何来自动化的生成分析查询的SQL语句,从而使数据分析变得更容易。

 

关键字: Quick BI、OLAP、维度建模、SQL

 

二、  维度模型的分类

OLAP(On-line Analytical Processing,联机分析处理)根据存储数据的方式不同可以分为ROLAP、MOLAP、HOLAP。ROLAP表示基于关系数据库存储的OLAP实现(Relational OLAP),以关系数据库为核心,以关系型结构进行多维数据的表示和存储;MOLAP表示基于多维数据存储的OLAP实现(Multidimensional OLAP);HOLAP表示基于混合数据存储的OLAP实现(Hybrid OLAP),如低层用关系型数据库存储,高层是多维数组存储。接下来主要介绍基于关系型数据库的ROLAP的建模原理。

ROLAP将多维数据库中的表分为两类:事实表和维度表。事实表用于存储维度关键字和数值类型的事实数据,一般是围绕业务过程进行设计,例如:销售事实表,一般来存储用户在什么时间、地点购买了产品,销量和销售额等信息。维度表用于存储维度的详细数据,例如销售事实表中存储了产品维度的ID,产品维度表中存储产品的名称、品牌信息,两者通过产品ID进行关联。

ROLAP根据事实表、维度表间的关系,又可分为星型模型(Star Schema)、雪花模型(Snowflake Schema)。

1.    星型模型

星型模型它由事实表(FactTable)和维表(DimensionTable)组成。事实表中的维度外键分别与相对应的维表中的主键相关联,关联之后由于形状看起来像是一个星星,所以形象的称为星型模型。以下示例为星型模型:其中sales_fact_1997为事实表,存储客户在某个时间、某个商店、购买了某个产品,购买量和销售额的信息,记录的是一个下单过程。事实表sales_fact_1997通过外键product_id、customer_id、time_id、store_id分别与维度表product(产品维表)、customer(客户维表)、time_by_day(时间维表)、store(商店维表)相关联,关联关系为多对一关联。

 

Quick BI 的模型设计与生成SQL原理剖析

2.    雪花模型

雪花模型是当有一个或多个维表没有直接连接到事实表上,而是通过其他维表连接到事实表上时,其图解就像一个雪花,故称雪花模型。下面示例product(产品)维度表与product_class(产品类别)维度表通过product_class_id相关联,关联关系为多对一。product_class没有与sales_fact_1997事实表直接关联。

 

Quick BI 的模型设计与生成SQL原理剖析

三、  基于ROLAP模型的SQL生成原理

模型构建好了后,接下来的重点就是针对分析需求来生成满足分析需要的SQL语句,然后将SQL语句下发到DB中来查询数据,返回分析结果。下面通过具体的需求场景来介绍如何生成SQL语句。

1.    基于星型模型(或雪花模型)生成SQL

需求场景:

按日期、产品查看总的销售额、销售量,日期限定在1997年,总销售额限定在1000元以上,结果按照总的销售额倒序排列,看前5个。

Quick BI 的模型设计与生成SQL原理剖析

生成SQL思路

1.        分析需要用到的字段和表,目标是明确查询需要用到哪些表、表间关系、表上分组字段、聚合字段,确定SQL中select和from信息。

2.        分析筛选条件,目标是明确SQL中where中需过滤的值。

3.        分析分组维度,目标是明确SQL中group by的字段。

4.        分析聚合后的筛选条件,目标是明确having中需要过滤的值。

5.        分析需要排序的列和排序类型(升序还是降序)。

6.        生成结果个数限制条件

7.        根据以上信息生成查询SQL:

select 分组字段、聚合字段 from 表(含表关联) where 筛选条件 group by 分组维度 having 聚合后的筛选条件 order by 排序信息 结果条数限制。

生成SQL

按照上面的步骤,和本例子中的需求,分析查询中的关键信息(以下步骤与生成SQL思路中的步骤一一对应)

1.        用到的分组字段:the_date、product_name, 其中分组字段the_date为日粒度,需处理为年粒度:DATE_FORMAT(`the_date` , '%Y')

聚合字段:store_sales、unit_sales,聚合方式都为sum;

用到的表:sales_fact_1997、product、time_by_day;

表间关系:sales_fact_1997. product_id= product. product_id

   sales_fact_1997. time_id= time_by_day .time_id

2.        筛选条件:

the_date`= STR_TO_DATE('1997-01-01 00:00:00' ,'%Y-%m-%d %H:%i:%s')

3.        分组维度:DATE_FORMAT(`the_date` , '%Y')、product_name

4.        聚合后的筛选条件:SUM(`store_sales`) > 1000

5.        排序:order by 聚合后的别名 desc

6.        限制结果个数:limit 0,5

7.        生成的SQL如下

Quick BI 的模型设计与生成SQL原理剖析

 

四、  附录-用到的表

下面罗列出以上示例中用到的表的建表语句,需要在 MySQL数据库下执行,其他类型数据库需要做一些调整。

1.        sales_fact_1997表

Quick BI 的模型设计与生成SQL原理剖析


2.        product表

Quick BI 的模型设计与生成SQL原理剖析

 

3.        product_class表

Quick BI 的模型设计与生成SQL原理剖析

 

4.        time_by_day表

Quick BI 的模型设计与生成SQL原理剖析


5.        customer表

Quick BI 的模型设计与生成SQL原理剖析


6.        store表

 Quick BI 的模型设计与生成SQL原理剖析


免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

Quick BI 的模型设计与生成SQL原理剖析

下载Word文档到电脑,方便收藏和打印~

下载Word文档

编程热搜

目录