引言
事實表
存放路徑成本和次元表的外鍵。
次元表
角度,分類。時間次元,地域次元,狀态次元。
舊的方式
1
2
3
4
5
6
7
8
9
10
<code>select</code>
<code>*</code>
<code>from</code> <code>order</code> <code>o</code>
<code>inner</code> <code>join</code> <code>district d </code><code>on</code> <code>o.discode=d.discode</code>
<code>inner</code> <code>join</code> <code>address a </code><code>on</code> <code>o.addressid=a.addressid</code>
<code>where</code> <code>o.createdate > </code><code>'2012-2-5'</code> <code>and</code> <code>o.createdate < </code><code>'2013-12-5'</code>
<code>and</code> <code>o.isb2c=</code><code>'1'</code>
<code>and</code> <code>o.status=</code><code>'1'</code>
<code>and</code> <code>o.discode=</code><code>'111010000000'</code>
<code>and</code> <code>a.address </code><code>like</code> <code>'北京%'</code>
寫死了存儲過程,增加條件很困難。條件一變化,或者是有新增的字段,往往很多存儲過程都需要修改,都要加上一個and條件,甚至是inner join一張新表,很是痛苦。總是思考有沒有好辦法,但總是沒有想出來好的辦法。
最近看資料倉庫的建設,看到了事實表,次元表這些概念,結合自己做過的項目,有了一點點的感觸。
其實一些标志位,狀态,都可以看做是銷售資訊的一個次元。
通俗的說,就是不在訂單表上條件字段了。以前一出現新的需求,就是直接在訂單表中添加字段,訂單表越來越大,總覺得很多字段和訂單沒有太多直接關系,但是想不出來該怎麼辦,不知道該歸結到那張表中,是建立一張表?還是其他什麼表?總是很糾結,最後的結果往往還是添加在訂單表中。
多條件查詢,動态查詢,不同次元綜合查詢。
其實就是不同次元的連接配接查詢,條件越多,參與的次元越多。每增加一個次元,就連接配接一個次元表,這樣就可以做成動态的,在代碼中寫好條件的拼接,資料庫表的拼接,以後增加字段和表就幾乎不用改動任何代碼,包括程式代碼和SQL代碼,都不用手動維護了。
<code>inner</code> <code>join</code> <code>isb2c i </code><code>on</code> <code>o.</code><code>is</code><code>=i.</code><code>is</code>
<code>inner</code> <code>join</code> <code>status s </code><code>on</code> <code>o.statusid=s.statusid</code>
資料庫設計方式
1 傳統直接映射
在以前的資料設計中都會有一種bool的字段,代表的含義就是:【是】或者【否】。
舉個例子說明一下,下面舉一個産品表的例子。
好辦,在表中加上一個字段,就想下圖一樣HasDiscount代表有無折扣。
插入一些資料,就想下面這樣。
有一個場景就是查詢産品,其中有一個條件就是有無折扣,條件有三種情況:
有折扣。
無折扣。
忽略這個條件,就是不管有無都查詢出來。
11
12
13
14
15
16
17
18
<code>DECLARE</code> <code>@Has </code><code>CHAR</code><code>(1)</code><code>--0無,1有,3忽略</code>
<code>SET</code> <code>@Has=</code><code>'3'</code>
<code>IF(@Has=</code><code>'3'</code><code>)</code>
<code>BEGIN</code>
<code> </code><code>SELECT</code>
<code> </code><code>p.ProductID,</code>
<code> </code><code>p.ProductName</code>
<code> </code><code>FROM</code> <code>SWB_Demo.dbo.T_Products p</code>
<code> </code>
<code>END</code>
<code>ELSE</code>
<code> </code><code>WHERE</code> <code>p.HasDiscount=@Has</code>
上面SQL中的@Has還可以寫成判斷是否為null,反正隻要是差別于0和1的其他值都可以實作同樣的效果。
好像看起來還可以,但是需求變化了,需要再加上一個bool的字段,在查詢場景也有上面的三種情況。
好吧,又是一個分支,繼續添加。
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
<code>DECLARE</code> <code>@HasDiscount </code><code>CHAR</code><code>(1)</code><code>--0無,1有,3忽略</code>
<code>SET</code> <code>@HasDiscount=</code><code>'3'</code>
<code>SET</code> <code>@Has=</code><code>'1'</code>
<code> </code><code>IF(@HasDiscount=</code><code>'3'</code><code>)</code>
<code> </code><code>BEGIN</code>
<code> </code><code>SELECT</code>
<code> </code><code>p.ProductID,</code>
<code> </code><code>p.ProductName</code>
<code> </code><code>FROM</code> <code>SWB_Demo.dbo.T_Products p</code>
<code> </code>
<code> </code><code>END</code>
<code> </code><code>ELSE</code>
<code> </code><code>WHERE</code> <code>p.HasDiscount=@HasDiscount</code>
<code> </code><code>where</code> <code>p.Has=@Has</code>
<code> </code><code>AND</code> <code>p.Has=@Has</code>
好吧,需求被我搞定了,然後說:“再也不要添加這樣的屬性了,否則這段SQL誰也維護不了了,我要逃跑了!”。但是需求是肯定會變的,而且在很多情況這樣的屬性都少不了,那怎麼辦呢,難道大家都是這麼做的嗎?
好像聽說人家都不用寫存儲過程,至少是很少寫的,怎麼我這個地方就成了噩夢了呢。
聽說好像可以用代碼生成SQL,隻要封裝的好,就可以實作添加表,添加字段,不用每次修改SQL,既然有,肯定是可以實作的,至少在某種程度上可以減少開發量,因為這種屬性是沒有辦法窮舉的。
2 事實表加維表
經過這幾天對于事實表和維表的學習,有了一點小的想法,是以分享一下。
首先将産品表的結構修改如下。
産品表隻包含産品相關資訊,是否打折可以看做是一個次元。
建立一個是否打折表,如下圖。
插入下面的兩條資料,一條代表打折,一條代表不打折。
建立一種商品打折關系表,存放商品和是否打折的關系資料。
插入下面的資料。
這時候SQL就可以寫成下面的樣子。
<code>--不考慮是否打折這個條件</code>
<code>SELECT</code>
<code>FROM</code> <code>SWB_Demo.dbo.T_Products p</code>
<code>WHERE</code> <code>p.ProductName </code><code>LIKE</code> <code>'%果%'</code>
<code>--考慮是否打折這個條件</code>
<code>INNER</code> <code>JOIN</code> <code>SWB_Demo.dbo.T_ProductHasDiscount php </code><code>ON</code> <code>p.ProductID=php.ProductID</code>
<code>INNER</code> <code>JOIN</code> <code>SWB_Demo.dbo.T_HasDiscount ph </code><code>ON</code> <code>php.HasID=ph.HasID</code>
<code>AND</code> <code>ph.HasNot=</code><code>'1'</code>
中間的連接配接打折表和打折關系表的部分就可以動态拼接,整個SQL語句的生成用代碼來實作,不用每次變動就修改SQL了,是不是更好一點呢?
本文轉自 virusswb 51CTO部落格,原文連結:http://blog.51cto.com/virusswb/1205589,如需轉載請自行聯系原作者