共計 2506 個字符，預(yù)計需要花費 7 分鐘才能閱讀完成。

自動寫代碼機(jī)器人，免費開通

Quick BI 的模型設(shè)計與生成 SQL 原理剖析

一、  摘要

隨著互聯(lián)網(wǎng)的高速發(fā)展，數(shù)據(jù)量呈現(xiàn)井噴式的增長，如何來分析和使用這些數(shù)據(jù)，使數(shù)據(jù)產(chǎn)生商業(yè)價值，已經(jīng)變得越來越重要。值得高興的是，當(dāng)前越來越多的人已經(jīng)意識到了用數(shù)據(jù)分析決定商業(yè)策略的重要性，也都在進(jìn)行著各行各業(yè)的數(shù)據(jù)分析。眾所周知數(shù)據(jù)分析的核心是數(shù)據(jù)，為了更容易的分析數(shù)據(jù)，數(shù)據(jù)模型的設(shè)計需要遵循一定的規(guī)范。當(dāng)前最流行的聯(lián)機(jī)分析處理 (OLAP) 的規(guī)范為維度建模規(guī)范。本文介紹 Quick BI 如何進(jìn)行維度建模，基于維度模型如何來自動化的生成分析查詢的 SQL 語句，從而使數(shù)據(jù)分析變得更容易。

關(guān)鍵字：Quick BI、OLAP、維度建模、SQL

二、  維度模型的分類

OLAP（On-line Analytical Processing，聯(lián)機(jī)分析處理）根據(jù)存儲數(shù)據(jù)的方式不同可以分為 ROLAP、MOLAP、HOLAP。ROLAP 表示基于關(guān)系數(shù)據(jù)庫存儲的 OLAP 實現(xiàn)（Relational OLAP），以關(guān)系數(shù)據(jù)庫為核心, 以關(guān)系型結(jié)構(gòu)進(jìn)行多維數(shù)據(jù)的表示和存儲；MOLAP 表示基于多維數(shù)據(jù)存儲的 OLAP 實現(xiàn)（Multidimensional OLAP）；HOLAP 表示基于混合數(shù)據(jù)存儲的 OLAP 實現(xiàn)（Hybrid OLAP），如低層用關(guān)系型數(shù)據(jù)庫存儲，高層是多維數(shù)組存儲。接下來主要介紹基于關(guān)系型數(shù)據(jù)庫的 ROLAP 的建模原理。

ROLAP 將多維數(shù)據(jù)庫中的表分為兩類：事實表和維度表。事實表用于存儲維度關(guān)鍵字和數(shù)值類型的事實數(shù)據(jù)，一般是圍繞業(yè)務(wù)過程進(jìn)行設(shè)計，例如：銷售事實表，一般來存儲用戶在什么時間、地點購買了產(chǎn)品，銷量和銷售額等信息。維度表用于存儲維度的詳細(xì)數(shù)據(jù)，例如銷售事實表中存儲了產(chǎn)品維度的 ID，產(chǎn)品維度表中存儲產(chǎn)品的名稱、品牌信息，兩者通過產(chǎn)品 ID 進(jìn)行關(guān)聯(lián)。

ROLAP 根據(jù)事實表、維度表間的關(guān)系，又可分為星型模型(Star Schema)、雪花模型(Snowflake Schema)。

1.  星型模型

星型模型它由事實表（FactTable）和維表（DimensionTable）組成。事實表中的維度外鍵分別與相對應(yīng)的維表中的主鍵相關(guān)聯(lián)，關(guān)聯(lián)之后由于形狀看起來像是一個星星，所以形象的稱為星型模型。以下示例為星型模型：其中 sales_fact_1997 為事實表，存儲客戶在某個時間、某個商店、購買了某個產(chǎn)品，購買量和銷售額的信息，記錄的是一個下單過程。事實表 sales_fact_1997 通過外鍵 product_id、customer_id、time_id、store_id 分別與維度表 product(產(chǎn)品維表)、customer(客戶維表)、time_by_day(時間維表)、store(商店維表)相關(guān)聯(lián)，關(guān)聯(lián)關(guān)系為多對一關(guān)聯(lián)。

2.  雪花模型

雪花模型是當(dāng)有一個或多個維表沒有直接連接到事實表上，而是通過其他維表連接到事實表上時，其圖解就像一個雪花，故稱雪花模型。下面示例 product（產(chǎn)品）維度表與 product_class(產(chǎn)品類別)維度表通過 product_class_id 相關(guān)聯(lián)，關(guān)聯(lián)關(guān)系為多對一。product_class 沒有與 sales_fact_1997 事實表直接關(guān)聯(lián)。

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

模型構(gòu)建好了后，接下來的重點就是針對分析需求來生成滿足分析需要的 SQL 語句，然后將 SQL 語句下發(fā)到 DB 中來查詢數(shù)據(jù)，返回分析結(jié)果。下面通過具體的需求場景來介紹如何生成 SQL 語句。

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

需求場景：

按日期、產(chǎn)品查看總的銷售額、銷售量，日期限定在 1997 年，總銷售額限定在 1000 元以上，結(jié)果按照總的銷售額倒序排列，看前 5 個。

生成 SQL 思路

1.  分析需要用到的字段和表，目標(biāo)是明確查詢需要用到哪些表、表間關(guān)系、表上分組字段、聚合字段，確定 SQL 中 select 和 from 信息。

2.  分析篩選條件，目標(biāo)是明確 SQL 中 where 中需過濾的值。

3.  分析分組維度，目標(biāo)是明確 SQL 中 group by 的字段。

4.  分析聚合后的篩選條件，目標(biāo)是明確 having 中需要過濾的值。

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

6.  生成結(jié)果個數(shù)限制條件

7.  根據(jù)以上信息生成查詢 SQL：

select 分組字段、聚合字段 from 表(含表關(guān)聯(lián)) where 篩選條件 group by 分組維度 having 聚合后的篩選條件 order by 排序信息 結(jié)果條數(shù)限制。

生成 SQL

按照上面的步驟，和本例子中的需求，分析查詢中的關(guān)鍵信息（以下步驟與生成 SQL 思路中的步驟一一對應(yīng)）

1.  用到的分組字段：the_date、product_name, 其中分組字段 the_date 為日粒度，需處理為年粒度：DATE_FORMAT(`the_date` , %Y)

聚合字段：store_sales、unit_sales，聚合方式都為 sum；

用到的表：sales_fact_1997、product、time_by_day；

表間關(guān)系：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.  限制結(jié)果個數(shù)：limit 0,5

7.  生成的 SQL 如下

四、  附錄 - 用到的表

下面羅列出以上示例中用到的表的建表語句，需要在 MySQL 數(shù)據(jù)庫下執(zhí)行，其他類型數(shù)據(jù)庫需要做一些調(diào)整。

1.  sales_fact_1997 表

2.  product 表

3.  product_class 表

4.  time_by_day 表

5.  customer 表

6.  store 表

丸趣 TV 網(wǎng) – 提供最優(yōu)質(zhì)的資源集合！