淺談數(shù)據(jù)庫(kù)開發(fā)（四）——從寬表到存儲(chǔ)過(guò)程（之三）

roych

上回說(shuō)到，咱們的數(shù)據(jù)開發(fā)攻城獅想到了寬表的建立。但寬表該如何建立，心里沒個(gè)譜。如果直接按結(jié)果表來(lái)建立，似乎達(dá)不到優(yōu)化腳本的目的。但除此之外，我們并沒有更多的字段了。那么，是否可以考慮把用戶ID作為主鍵來(lái)建立寬表，然后再進(jìn)行統(tǒng)計(jì)呢？

于是，咱決定建立起這么一個(gè)表（圖1）：

我們只需要對(duì)日期右邊的列進(jìn)行sum統(tǒng)計(jì)，然后group by日期就可以得到以下結(jié)果了（圖2）：


同樣地，我們也留意到，圖1中，1月8日（含）以后再無(wú)新數(shù)據(jù)。這也從另一方面驗(yàn)證了圖2的準(zhǔn)確性。代碼如下：

1. create table log_result(
   
2. userID int,min_Date date
   
3. ,day0 int,day1 int,day2 int,day3 int
   
4. ,day4 int,day5 int,day6 int,day7 int,day8 int)

復(fù)制代碼

之所以設(shè)置day0~day8為int，是考慮到數(shù)值更便于統(tǒng)計(jì)，0表示沒登錄，1表示登錄。當(dāng)然，你也可以設(shè)置為varchar，用“是/否”來(lái)統(tǒng)計(jì)，又或者設(shè)置為bit。但我的印象中，bit只有0和1兩個(gè)值，如果某天要統(tǒng)計(jì)當(dāng)天登錄次數(shù)，可能就需要更改表字段結(jié)構(gòu)了。從這一點(diǎn)來(lái)看，設(shè)置為int可能更具有擴(kuò)展性�！�—這是后話了，這里暫時(shí)表過(guò)。
建立好表之后，接下來(lái)就是寫存儲(chǔ)過(guò)程填充數(shù)據(jù)了。在寫存儲(chǔ)過(guò)程之前，我覺得有必要講下流程步驟。按我的習(xí)慣來(lái)說(shuō)，一般有以下幾個(gè)步驟：

1、清空舊數(shù)據(jù)
2、插入基礎(chǔ)數(shù)據(jù)（必要的地方可以把某些字段的默認(rèn)值追加上去，例如，“0”，“其它”等等）
3、更新其他字段（必要的地方可以先把目標(biāo)字段先初始化為默認(rèn)值，例如，“0”，“其它”等等）
4、數(shù)據(jù)清洗（包括刪除不符合條件的數(shù)據(jù)，清理一些特殊字符之類，具體看實(shí)際情況）
很多初學(xué)者往往會(huì)忘記第一步。結(jié)果每運(yùn)行一次存儲(chǔ)過(guò)程，就生成一批數(shù)據(jù)，最后導(dǎo)致數(shù)據(jù)重復(fù)。這里到底用truncate table還是delete from+where，需要看實(shí)際需求，前者效率比較高，但如需考慮到數(shù)據(jù)的可追溯性，或者不希望一次性插入大量數(shù)據(jù)，則應(yīng)該選擇用后者。

此外，也有人不喜歡預(yù)先建表，喜歡用生成表語(yǔ)句（select * into 結(jié)果表 from 源表）這樣的話，好像聽起來(lái)好像是減少了步驟（沒有插入數(shù)據(jù)和更新數(shù)據(jù)），而且很靈活，需要哪些字段再手動(dòng)alter table add column進(jìn)去。在確認(rèn)需求階段，這樣寫無(wú)可厚非。但固化存儲(chǔ)過(guò)程時(shí)則不應(yīng)該再這樣寫了。因?yàn)槊看紊�成一個(gè)表，然后需要幾個(gè)字段，你后面還得add幾個(gè)column，步驟更繁瑣。

我們這個(gè)需求很明確了，所以按上面的create語(yǔ)句建表即可。接下來(lái)，就是存儲(chǔ)過(guò)程的編寫了。根據(jù)前面我們的思路，需要傳入一個(gè)日期參數(shù)，然后根據(jù)這個(gè)日期參數(shù)確定后面的day0~day8。依次打開數(shù)據(jù)庫(kù)/可編程性/存儲(chǔ)過(guò)程，右擊選擇“新建存儲(chǔ)過(guò)程”。我們可以看到SQL server已經(jīng)給我們寫了一個(gè)模板：

我們把尖括號(hào)里的內(nèi)容替換為我們自己的東西，一個(gè)存儲(chǔ)過(guò)程就寫好了。

1. SET ANSI_NULLS ON
   
2. GO
   
3. SET QUOTED_IDENTIFIER ON
   
4. GO
   
5. -- =============================================
   
6. -- Author:                Roych
   
7. -- Create date: 2019-11-16
   
8. -- Description:        用于完成用戶留存率數(shù)據(jù)源的計(jì)算
   
9. -- =============================================
   
10. CREATE PROCEDURE sp_user_count @min_date date=null
    
11. AS
    
12. BEGIN
    
13.         SET NOCOUNT ON;
    
14.         --定義9個(gè)日期變量
    
15.         declare @day0 date,@day1 date,@day2 date,@day3 date,@day4 date
    
16.                         ,@day5 date,@day6 date,@day7 date,@day8 date
    
17.         /*
    
18.         另一種變量聲明的寫法：
    
19.         declare @day0 date
    
20.         declare @day1 date
    
21.         ……………………
    
22.         declare @dayn date
    
23.         */
    
24.         --如果使用默認(rèn)值（即無(wú)輸入），則預(yù)設(shè)為系統(tǒng)日期前8天。
    
25.         if @min_date is null
    
26.                 set @min_date=dateadd(day,-8,GETDATE())
    
27.         set @day0=@min_date
    
28.         set @day1=DATEADD(day,1,@min_date)
    
29.         set @day2=DATEADD(day,2,@min_date)
    
30.         set @day3=DATEADD(day,3,@min_date)
    
31.         set @day4=DATEADD(day,4,@min_date)
    
32.         set @day5=DATEADD(day,5,@min_date)
    
33.         set @day6=DATEADD(day,6,@min_date)
    
34.         set @day7=DATEADD(day,7,@min_date)
    
35.         set @day8=DATEADD(day,8,@min_date)
    
36. --清空舊數(shù)據(jù)
    
37. truncate table log_result
    
38. --插入新數(shù)據(jù)（這里只插入day0~day8的數(shù)據(jù)，并初始化登錄天數(shù)為0）
    
39. insert into log_result
    
40. select 用戶ID,min(登錄時(shí)間),0,0,0,0,0,0,0,0,0
    
41. from log_info where 登錄時(shí)間 between @day0 and @day8
    
42. group by 用戶ID
    
43. 
    
44. --更新字段day0~day8
    
45. update log_result set log_result.day0=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day0) b
    
46. where log_result.userID=b.用戶ID
    
47. 
    
48. update log_result set log_result.day1=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day1) b
    
49. where log_result.userID=b.用戶ID
    
50. 
    
51. update log_result set log_result.day2=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day2) b
    
52. where log_result.userID=b.用戶ID
    
53. 
    
54. update log_result set log_result.day3=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day3) b
    
55. where log_result.userID=b.用戶ID
    
56. 
    
57. update log_result set log_result.day4=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day4) b
    
58. where log_result.userID=b.用戶ID
    
59. 
    
60. update log_result set log_result.day5=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day5) b
    
61. where log_result.userID=b.用戶ID
    
62. 
    
63. update log_result set log_result.day6=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day6) b
    
64. where log_result.userID=b.用戶ID
    
65. 
    
66. update log_result set log_result.day7=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day7) b
    
67. where log_result.userID=b.用戶ID
    
68. 
    
69. update log_result set log_result.day8=1 from (select 用戶ID from log_info where 登錄時(shí)間=@day8) b
    
70. where log_result.userID=b.用戶ID
    
71. END
    
72. GO

復(fù)制代碼

存儲(chǔ)過(guò)程寫完之后，我們可以執(zhí)行一下：
exec sp_user_count '2019-1-1'
然后去查看log_result，看看結(jié)果是否與我們預(yù)想的一致：

看起來(lái)似乎已經(jīng)達(dá)到了我們的預(yù)期。你以為這樣就結(jié)束了？然鵝并沒有。請(qǐng)看下回分解。

GOODWIN

學(xué)習(xí)