Quick BI 的模型設(shè)計與生成SQL原理剖析
創(chuàng)新互聯(lián)是一家專注于網(wǎng)站設(shè)計制作、成都網(wǎng)站制作與策劃設(shè)計,榕城網(wǎng)站建設(shè)哪家好?創(chuàng)新互聯(lián)做網(wǎng)站,專注于網(wǎng)站建設(shè)十多年,網(wǎng)設(shè)計領(lǐng)域的專業(yè)建站公司;建站業(yè)務(wù)涵蓋:榕城等地區(qū)。榕城做網(wǎng)站價格咨詢:13518219792
隨著互聯(lián)網(wǎng)的高速發(fā)展，數(shù)據(jù)量呈現(xiàn)井噴式的增長，如何來分析和使用這些數(shù)據(jù)，使數(shù)據(jù)產(chǎn)生商業(yè)價值，已經(jīng)變得越來越重要。值得高興的是，當前越來越多的人已經(jīng)意識到了用數(shù)據(jù)分析決定商業(yè)策略的重要性，也都在進行著各行各業(yè)的數(shù)據(jù)分析。眾所周知數(shù)據(jù)分析的核心是數(shù)據(jù)，為了更容易的分析數(shù)據(jù)，數(shù)據(jù)模型的設(shè)計需要遵循一定的規(guī)范。當前最流行的聯(lián)機分析處理(OLAP)的規(guī)范為維度建模規(guī)范。本文介紹Quick BI如何進行維度建模，基于維度模型如何來自動化的生成分析查詢的SQL語句，從而使數(shù)據(jù)分析變得更容易。
關(guān)鍵字： Quick BI、OLAP、維度建模、SQL
OLAP（On-line Analytical Processing，聯(lián)機分析處理）根據(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)進行多維數(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ù)過程進行設(shè)計，例如：銷售事實表，一般來存儲用戶在什么時間、地點購買了產(chǎn)品，銷量和銷售額等信息。維度表用于存儲維度的詳細數(shù)據(jù)，例如銷售事實表中存儲了產(chǎn)品維度的ID，產(chǎn)品維度表中存儲產(chǎn)品的名稱、品牌信息，兩者通過產(chǎn)品ID進行關(guān)聯(lián)。
ROLAP根據(jù)事實表、維度表間的關(guān)系，又可分為星型模型(Star Schema)、雪花模型(Snowflake Schema)。
星型模型它由事實表（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)。
雪花模型是當有一個或多個維表沒有直接連接到事實表上，而是通過其他維表連接到事實表上時，其圖解就像一個雪花，故稱雪花模型。下面示例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)。
模型構(gòu)建好了后，接下來的重點就是針對分析需求來生成滿足分析需要的SQL語句，然后將SQL語句下發(fā)到DB中來查詢數(shù)據(jù)，返回分析結(jié)果。下面通過具體的需求場景來介紹如何生成SQL語句。
需求場景：
按日期、產(chǎn)品查看總的銷售額、銷售量，日期限定在1997年，總銷售額限定在1000元以上，結(jié)果按照總的銷售額倒序排列，看前5個。
1. 分析需要用到的字段和表，目標是明確查詢需要用到哪些表、表間關(guān)系、表上分組字段、聚合字段，確定SQL中select和from信息。
2. 分析篩選條件，目標是明確SQL中where中需過濾的值。
3. 分析分組維度，目標是明確SQL中g(shù)roup by的字段。
4. 分析聚合后的篩選條件，目標是明確having中需要過濾的值。
5. 分析需要排序的列和排序類型(升序還是降序)。
6. 生成結(jié)果個數(shù)限制條件
7. 根據(jù)以上信息生成查詢SQL：
select分組字段、聚合字段 from 表(含表關(guān)聯(lián)) where 篩選條件 group by 分組維度 having 聚合后的篩選條件 order by 排序信息 結(jié)果條數(shù)限制。
按照上面的步驟，和本例子中的需求，分析查詢中的關(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表
