斜率

斜率 

從不同的角度看,會有不同的斜率,像是從x角度看的斜率會跟從y的角度的斜率不同,因此要找出最低點,才能算出最小值。

示意圖:

gitnub

 

 

 

 

 

 

使用sys.stdin輸入,並計算有幾次 0-9

 

 

 

計算有幾行文字,記得最要用 Ctrl + Z 兩次離開

 

 

 

統計有幾個字最常出現,並要給他前幾名

 

 

 

 

 備註:2017/04/13 計算方法分析與設計-課堂筆記

SSDT計算屬性對話方塊

–SQL Server Data Tools

一、建立Cube

  1. 到E3下載bak,開啟SQL Server,並還原資料庫
  2. 開啟SQL Server Data Tools 2015,新增→專案→Analysis Services多維度和資料採礦專案
  3. 新增資料來源:

右邊「方案總管」→資料來源→右鍵新增資料來源→下一步→New…→伺服器名稱:.、資料庫名稱:A12345→OK→下一步→勾「繼承」→下一步→完成

  1. 新增資料來源檢視:

右邊「方案總管」→資料來源檢視→右鍵新增資料來源檢視→下一步→選剛剛建立的資料來源(A12345)→下一步→下一步→選取全部資料表移到右邊「包含的物件」→下一步→完成

  1. 設定邏輯主索引鍵、建立關聯

針對[科目表.科目編號]、[學生表.座號]、[年度段考表.段考代號],右鍵→設定邏輯主索引鍵,並建立資料表關聯(如圖)。

  1. 新增維度:

右邊「方案總管」→維度→右鍵新增維度→下一步→勾「使用現有資料表」→下一步→下一步→可用的屬性:全勾→下一步→完成。

  1. 建立階層:

建立「段考階層」:年度>段考。

  1. 新增Cube

右邊「方案總管」→CUBE→右鍵CUBE→下一步→勾”使用現有資料表”→下一步→量值群組資料表:勾選「成績單」→下一步→下一步→下一步→下一步→完成。

左上「處理」。

※如果執行撿角,請回去SQL Server授予目前使用者(as)讀取A12345資料庫的權限(db_owner或db_datareader)。

(安全性→登入→選取目前使用者(as)→右鍵”屬性”→左邊”使用者對應”→右邊勾選資料庫和權限)

二、計算「平均分數」

  1. 在CUBE的「計算」頁籤,上方工具列或「指令碼組合管理區域」右鍵,新增導出成員,
  • 名稱:[平均分數] (中括號不能刪!)
  • 運算式:[Measures].[分數]/[Measures].[成績單 計數]
    (可從左下拉進來)
  1. 上方工具列「處理」,重新處理CUBE完成後,「瀏覽器」頁籤→到EXCEL查看結果。
  • 欄:科目
  • 列:姓名
  • 值:平均分數

三、建立群組檢視─科目

如果科目要做群組檢視的話(例如:語文、文科、理科…)

※方法一:在資料表新增欄位

  1. 到SQL Server「科目表」資料表新增一個欄位:類別,
  2. 回到CUBE的資料來源檢視的視窗(A12345.dsv),上面工具列→資料來源檢視→重新整理,會有提示視窗顯示有哪些變更。
  3. 到科目維度把剛剛建立的「類別」拉進來。
  4. 建立「科目階層」:類別>科目
  5. CUBE處理完成後,到EXCEL「資料」頁籤「全部重新整理」,把剛剛建立的「科目階層」放到右下「篩選」欄。

※方法二:新增命名集

  1. 在CUBE「計算」頁籤,新增命名集,
  • 名稱:[語文類]
  • 運算式:{[科目維度].[科目].&[英語], [科目維度].[科目].&[國語]}
    (可從左下拉進來,前後加的是大括號)
  1. CUBE處理完成後,到EXCEL,會發現「科目維度」項目下多了「集」,展開後可以看到剛剛建立的「語文類」,放到右下”欄”。

※資料錯誤

如果量值資料表出現「載入中繼資料時發生錯誤:使用者沒有資料庫的存取權,或者資料庫不存在。」錯誤的話,重新處理CUBE,到上面工具列按「重新連接」即可。

四、建立「觀察學生」的檢視

  1. 在CUBE「計算」頁籤,新增命名集,
  • 名稱:[觀察學生]
  • 運算式:{ {[學生維度].[姓 名].&[王○慈],[學生維度].[姓   名].&[吳○慈], [學生維度].[姓   名].&[李○瑄]}}
    (可從左下拉進來,前後加的是大括號)
  1. CUBE處理完成後,到EXCEL,會發現「學生維度」項目下多了「集」,展開後可以看到剛剛建立的「觀察學生」,放到右下「列」。

這樣每次要看觀察名單內的學生,直接拉進去就可以看嚕。

五、建立「前三名」的檢視

  1. 在CUBE「計算」頁籤,新增命名集,
  • 名稱:[前三名]
  • 運算式:topcount([學生表].[姓名].children, 3, [Measures].[分數])
    (topcount函數:以分數為標準,從學生表中挑出前三位學生)
  1. CUBE處理完成後,到EXCEL,會發現「學生維度」項目下多了「集」,展開後可以看到剛剛建立的「前三名」,放到右下「列」。

六、建立「成績」的KPI

值運算式去跟目標運算式比較,狀態運算式看要顯示什麼燈號,狀態運算式要傳回三個值:1、0、-1。

  1. 在CUBE「KPI」頁籤,新增KPI,
  • 名稱:[成績]
  • 值運算式:[Measures].[分數]
  • 目標運算式:85
  • 狀態指標:形狀
  • 狀態運算式:

  1. CUBE處理完成後,到EXCEL,會發現多了「KPI」項目,把KPI→成績→狀態,放到右下「列」。

※一個公司通常不建議設定很多KPI,KPI的設定是很嚴謹的,門檻值的設定也是一門學問。

七、建立「前次分數」的檢視

  1. 在CUBE「計算」頁籤,新增導出成員,
  • 名稱:[前次分數]
  • 運算式:

睡著的同學:http://data.bilab.pro:999/bi/_CUBE/_MDX.TXT

SSDT建立不同語系的欄位翻譯

※SQL Server Data Tools

一、建立Cube

  1. 到E3下載bak,開啟SQL Server,還原資料庫
  2. 開啟SQL Server Data Tools 2015,新增→專案→Analysis Services多維度和資料採礦專案
  3. 新增資料來源:

右邊「方案總管」→資料來源→右鍵新增資料來源→下一步→New…→伺服器名稱:.、資料庫名稱:A12345→OK→下一步→勾「繼承」→下一步→完成

  1. 新增資料來源檢視:

右邊「方案總管」→資料來源檢視→右鍵新增資料來源檢視→下一步→選剛剛建立的資料來源(A12345)→下一步→下一步→選取全部資料表移到右邊「包含的物件」→下一步→完成

  1. 設定邏輯主索引鍵、建立關聯

針對[科目表.科目編號]、[學生表.座號]、[年度段考表.段考代號],右鍵→設定邏輯主索引鍵,並建立資料表關聯。

  1. 新增維度:

右邊「方案總管」→維度→右鍵新增維度→下一步→勾「使用現有資料表」→下一步→下一步→可用的屬性:全勾→下一步→完成。

  1. 建立階層:

「段考階層」:段考代號>年度>段考。

  1. 建立Cube

右邊「方案總管」→CUBE→右鍵CUBE→下一步→勾「使用現有資料表」→下一步→量值群組資料表:勾選「成績單」→下一步→下一步→下一步→下一步→完成。

左上「處理」。

  1. 「瀏覽器」頁籤,用EXCEL的樞紐分析表檢視此資料,
  • 篩選:
  • 欄:科目
  • 列:座號(不要做小計,以列表方式呈現)、姓名
  • 值:分數

※如果執行撿角,請回去SQL Server授予目前使用者(as)讀取A12345資料庫的權限(db_owner或db_datareader)。

(安全性→登入→選取目前使用者(as)→右鍵「屬性」→左邊「使用者對應」→右邊勾選資料庫和權限)

二、更新異動資料到CUBE

  1. 到SQL Server,在[A12345].[學生表.dbo],新增一個欄位,
  • 資料行名稱:性別
  • 資料類型:nchar(1)

,然後儲存。

  1. 編輯「學生表」資料表,填入每一位學生的性別(隨機填入即可)。
  2. 到SQL Server Data Tools,「資料來源檢視」→上面工具列「資料來源檢視」→重新整理。
  3. 切換到「學生維度.dim」視窗,把剛剛建立的欄位(性別)從右側資料來源檢視的學生表,拉到左側的屬性。
  4. 將CUBE再「處理」一次,到EXCEL「資料」頁籤→上面工具列「全部重新整理」,就可以看到剛剛學生表新增的欄位。

※檢視方塊

每一個都有各自很多的Fact表、量值,依據不同的資料做分類

一、建立檢視方塊

實作:利用檢視方塊選取不同的量值跟維度的檢視

  1. 切換到cube視窗→「檢視方塊」頁籤
  2. 對著A12345資料表按右鍵→新增檢視方塊,科目維度不勾選,「處理」CUBE。
  3. 開啟新的EXCEL,「資料」頁籤→取得外部資料:從其他來源→從Analsis Server→伺服器名稱:.→下一步→選取專案→選取剛剛建立的檢視方塊→完成。
  4. 檢視此資料的方式:樞紐分析表→確定。

※因為一般來說使用者可能不知道資料的關聯性,如果可以依據不同的檢視方塊,就可以依據不同類別資料做更細部分析。

二、建立翻譯─針對「維度階層名稱」

實作:利用翻譯來讓資料能轉譯成多國語言-先做Cube英譯

  1. 切換到cube視窗→「翻譯」頁籤
  2. 對著A12345資料表按右鍵→新增翻譯→選取新翻譯的語言:「英文(美國)」→OK→輸入對譯英文→「處理」CUBE。
  3. 開啟新的EXCEL,「資料」頁籤→取得外部資料:從其他來源→從Analsis Server→伺服器名稱:.→下一步→選取專案→選取CUBE(A12345.cube)→點擊「瀏覽」→複製資料夾路徑到檔案總管開啟(預設路徑通常是:C:\Users\user\Documents\我的資料來源)→儲存→完成。
  4. 檢視此資料的方式:樞紐分析表→確定。

這時候會發現:資料(量值階層)還是中文資料,因為資料讀取是依據系統語系才做變動,但可以用一個方法去強制修改資料讀取語系。

  1. 到剛剛的資料夾路徑下,對剛剛建立的.odc檔案(A12345.odc)按右鍵→edit with notepad++。
  2. 在<odc:ConnectionString>那行加入粗體字串:
    ;Initial Catalog=MultidimensionalProject1;Locale Identifier=1033</odc:ConnectionString>
    →儲存。

※Google Keyword:locale Identifier list,或參考Microsoft Locale ID Values

  1. 重新開啟剛剛的.odc檔案:

開啟新的EXCEL,「資料」頁籤→取得外部資料:現有連線→選剛剛設定的.odc,就會發現項目名稱變成剛剛設定的英文。

三、建立翻譯─針對「維度的欄位」─以「科目維度」為例

  1. 切換到科目維度.dim視窗→「翻譯」頁籤
  2. 對著科目維度按右鍵→新增翻譯→選取新翻譯的語言:「英文(美國)」→OK→依據下圖輸入對譯英文→「處理」CUBE。
  3. 到EXCEL「資料」頁籤→上面工具列「全部重新整理」,就可以看到剛剛輸入的英文欄位。

四、建立翻譯─針對資料表內容─以「科目表」為例

  1. 到SQL Server,在[A12345].[學生表.dbo],新增一個欄位:
  • 資料行名稱:SubjectName
  • 資料類型:nchar(10)

,然後儲存。

  1. 編輯「科目表」資料表,輸入每個科目的英文翻譯。
  2. 到SQL Server Data Tools,「資料來源檢視」→上面工具列「資料來源檢視」→重新整理。
  3. 切換到「科目維度.dim」視窗,把剛剛建立的欄位(SubjectName)從右側資料來源檢視的科目表,拉到左側的屬性。
  4. 將CUBE再「處理」一次,到EXCEL「資料」頁籤→上面工具列「全部重新整理」。

關聯檢測結果

※比較新舊資料

  1. 建立預存程序

http://data.bilab.pro:999/db/SELECT_DISTINCT%20(2).txt複製語法,新增查詢之後貼上,將ALTER PRODEDURE改成CREATE PRODEDURE,執行

  1. 執行預存程序

AdventureWorks2012資料庫→可程式性→預存程序,找到剛剛建立的預存程序[new_auto_Calc_distinct],右鍵「執行預存程序」→確定,將執行結果複製到EXCEL(執行結果左上角按右鍵「隨標頭一同複製」)。

  1. 重複上述步驟1~2,將兩個步驟執行語法的資料庫改成[AdventureWorks2016CTP3],執行後一樣把結果複製到EXCEL。
  2. 比較兩個資料表差異

※【SSDT】

  1. 開啟SSDT
  2. 新增專案→Integration Services專案
  3. 左邊工具箱→通用→新增一個「資料分析工作」→點兩下→
  • 「一般」頁籤→Destination:下拉清單「新增檔案連接」→使用類型:建立檔案→命名Data1→確定
  • 「設定檔要求」頁籤→設定檔類型:資料行統計資料設定檔要求,後面的「要求識別碼」會自然產生→點上面檢視:資料行統計資料設定檔要求→下面Connection Manager:下拉清單「新增NET連接」→伺服器:.、資料庫:AdventureWorks2012→OK→TableOrView:[Sales].[SalesOrderHeader]、Column:SubTotal。

※關聯檢測結果

一、資料準備

  1. 以系統管理員身分執行SQL Server
  2. 到bilab.pro:999/bi/,下載DB關聯分析.zip,解壓縮後,把「CH07範例資料庫」附加到SQL Server。

二、新增資料表,命名為:關聯檢測結果

資料行名稱  資料類型

測試日期   datetime

表1      nvarchar(50)

表1欄位    nvarchar(50)

表1值     nvarchar(50)

表2      nvarchar(50)

表2欄位    nvarchar(50)

表2值     nvarchar(50)

三、建立兩個檢視表

※訂單 & 訂單明細

  1. 選取[訂單]、[訂單明細]資料表,並做FULL OUTER JOIN關聯建立兩個檢視表
  2. 篩選條件,勾選:
  • [訂單].[訂單編號],篩選:IS NULL
  • [訂單明細].[訂單編號],或:IS NULL
  1. 依序新增資料行
  • Getdate() AS 日期
  • ‘訂單’ AS 表1
  • ‘訂單編號’ AS 表1欄位
  • 勾選[訂單].[訂單編號],別名:訂單_訂單編號
  • ‘訂單明細’ AS 表2
  • ‘訂單編號’ AS 表2欄位
  • 勾選[訂單明細].[訂單編號],別名:明細_訂單編號

※訂單明細 & 產品資料

  1. 選取[訂單明細]、[產品資料]資料表,並做FULL OUTER JOIN關聯
  2. 篩選條件,勾選:
  • [訂單明細].[產品編號],篩選:IS NULL
  • [產品資料].[產品編號],或:IS NULL
  1. 依序新增資料行
  • Getdate() AS 日期
  • ‘訂單明細’ AS 表1
  • ‘明細編號’ AS 表1欄位
  • 勾選[訂單明細].[產品編號],別名:明細_產品編號
  • ‘產品資料’ AS 表2
  • ‘產品編號’ AS 表2欄位
  • 勾選[產品資料].[產品編號],別名:產品_產品編號

四、「關聯檢測結果」資料表

  1. 在兩個檢視表的語法最前面加上:INSERT INTO 關聯檢測結果,一起複製,新增查詢,貼上並執行。

資料指標2

一、SQL

  1. 在AdventureWork2012資料庫建立查詢,查詢COLUMNS、INFORMATION_SCHEMA.TABLES所有資料,並將兩段語法分別複製起來。

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.COLUMNS)

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.TABLES)

  1. 建立一個新資料庫,新增檢視表,將剛剛兩段語法分別貼上,執行後,分別儲存成vAdv2012Columns、vAdv2012Tables兩張檢視表。
  2. 建立新檢視表,加入vAdv2012Columns、vAdv2012Tables兩個資料表,用Table_Name做關聯。
  3. 開啟EXCEL,「資料」頁籤→取得外部資料→從SQL Server→伺服器名稱:.、WINDOWS驗證,下一步,選取剛剛建立的檢視表(vAdv2012AllCharColumns)→下一步→完成執行成儲存成vAdv2012AllCharColumns檢視表。
  4. 修改欄位名稱:
  • TABLE_NAME→資料表
  • COLUMN_NAME→欄位名稱
  • IS_NULLABLE→NULL
  • CHARACTER_MAXIMUM_LENGTH→長度
  • 新增一欄:說明

二、統計Product.Color各種字元長度的資料筆數。

  1. 查詢Product資料表中,Color、Name、ProductNumber三個欄位的平均長度。
SELECT ‘Color’ AS 欄位名稱, avg(Len([Color])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

union

SELECT ‘Name’ AS 欄位名稱, avg(Len([Name])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

union

SELECT ‘ProductNumber’ AS 欄位名稱, avg(Len([ProductNumber])) AS 平均長度

FROM [AdventureWorks2012].[Production].[Product]

  1. 用子查詢(data)的結果,依ColorLength做分組(GROUP BY),再依ColorLength做排序
SELECT ColorLength, count(*) as 資料筆數

FROM

–名為data的子查詢

(

SELECT TOP 1000 [Name]

,[Color]

,Case When [Color] is Null then 0 else Len([Color]) end ColorLength

FROM [AdventureWorks2012].[Production].[Product]

order by Case When [Color] is Null then 0 else Len([Color]) end

) data

GROUP BY ColorLength

ORDER BY ColorLength

三、查詢AdventureWorks2012資料庫中各個表格的每個欄位中的字元長度

  1. 把vAdv2012AllCharColumns檢視表增加Table_Catalog、vAdv2012Tables.Table_Schema兩個欄位,並移到最上面,IS_NULLABLE欄為增加篩選條件= ‘NO’。
  1. http://data.bilab.pro:999/db/Select_Min.txt的語法複製到新的查詢(語法可參考20170309_Cursor)。
    Declare mydata Cursor for

    –因為最後cmd1的指令會超過定義的字串長度,故用top20限制資料來源筆數

    SELECT          top 20 dbo.vAdv2012Tables.TABLE_CATALOG, dbo.vAdv2012Tables.TABLE_SCHEMA, dbo.vAdv2012Columns.TABLE_NAME,

    dbo.vAdv2012Columns.COLUMN_NAME, dbo.vAdv2012Columns.IS_NULLABLE,

    dbo.vAdv2012Columns.CHARACTER_MAXIMUM_LENGTH

    FROM              dbo.vAdv2012Columns INNER JOIN

    dbo.vAdv2012Tables ON dbo.vAdv2012Columns.TABLE_NAME = dbo.vAdv2012Tables.TABLE_NAME

    WHERE          (dbo.vAdv2012Columns.DATA_TYPE = N’nvarchar’) AND (dbo.vAdv2012Tables.TABLE_TYPE = ‘BASE TABLE’) AND

    (dbo.vAdv2012Columns.IS_NULLABLE = ‘NO’)

    declare @cmd1 nvarchar(4000)

    –上面資料來源查詢了6個欄位,所以要宣告6個變數來儲存

    declare @colCatalog nvarchar(100)

    declare @colSchema nvarchar(100)

    declare @colTable nvarchar(100)

    declare @colColumn nvarchar(100)

    declare @colNull nvarchar(100)

    declare @colLen nvarchar(100)

    Open mydata

    Fetch Next from mydata into @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen

    Declare @flag int

    set @flag=0

    set @cmd1=”

    WHILE @@FETCH_STATUS = 0

    BEGIN

    IF @flag=1

    begin

    set @cmd1=@cmd1 + ‘ union ‘;

    end

    SET @cmd1=@cmd1+’ Select ”’ + @colTable + ”’ as ‘+ N’表格名稱’ + ‘, ”’+@colColumn+ ”’ as ‘+N’欄位名稱’+’, avg(Len(‘ + @colLen + ‘)) as ‘ + N’平均長度’ + ‘ from ‘ + @colCatalog + ‘.’ + @colSchema + ‘.’ + @colTable

    set @flag=1

    FETCH NEXT FROM mydata INTO @colCatalog, @colSchema, @colTable, @colColumn, @colNull, @colLen

    END

    print (@cmd1)

    exec (@cmd1)

    CLOSE mydata

    DEALLOCATE mydata

機率

機率

github

Conditional Probability

P(E,F)=P(E)P(F)   IF E,F 獨立

P(E|F)=P(E,F) / P(F)

=P(E)P(F) / P(F)

=P(E)

get1與get2相乘出來的機率會幾乎等於P(aboth)與P(get1,get2)

蒙地卡羅法求 PI

來自 <https://openhome.cc/Gossip/AlgorithmGossip/MathPI.htm>

The Normal Distribution

# sigma越小,越集中

#  機率分配

 

The Central Limit Theorem

當數量夠大時,會非常接近常態分配

 

Ch07假設檢定

40,50,60,70,80 (-60)

-20,-10,0,10,20

標準化standardize

把東西統一標準,EX:都改成台幣EPS

把資料處理過才能去比較

 

 

 備註:2017/04/06 計算方法分析與設計-課程筆記

資料指標1

一、資料管理(Data Management)

  1. Data Profiling
  2. Data Standardization
  3. Geocoding
  4. Matching or linking
  5. Monitoring
  6. Batch and Real time

二、Data Profiling

確認資料的定義是什麼。

  1. Data Domain, Dictionary, Glossary
    1. Field Name
    2. Data Type
    3. Occurrence of Null

確認資料裡面有沒有NULL值,或能不能包含NULL值。EX性別、學號。或者,某個欄位的NULL值比例太高,就要評估這個欄位是否有存在的必要性。

  • 練習:查詢[SalesOrderHeader].[CreditCardID]欄位,空值與非空值的資料筆數。

SELECT SUM(CASE WHEN CreditCardID IS NULL THEN 1 ELSE 0 END) AS NULL_COUNT, SUM(CASE WHEN CreditCardID IS NULL THEN 0 ELSE 1 END) AS NOTNULL_COUNT

FROM [AdventureWorks2012].[Sales].[SalesOrderHeader]

D. Indexing

索引會影響資料處理的速度(效能)。

E. String Pattern

資料裡面的文字描述格式是否一致。EX地址。

F. Constraint, Validation rules

驗證規則

G. Description

  • 練習:用變數儲存語法。查詢salesorderheader資料表的所有資料。

declare @cmd1 nvarchar(100)

set @cmd1 = ‘select * from ‘

set @cmd1 = @cmd1 + ‘sales.salesorderheader’

–print @cmd1

exec(@cmd1)

  1. Relationship Discovery

資料表的關係,去發掘資料表之間有什麼相關性。

A. ER-Diagram/ER-Model

B. Relationship

C. Cardinality

D. Referential Test

3. Data Quality Summary

Minimum, maximum, mean, mode, percentile, standard deviation, frequency, variation, aggregates such as count and sum

(1) 查詢SalesOrderHeader資料表中,銷售金額的相關計算:

建立檢視表,選取[SalesOrderHeader]資料表,先使用COUNT計算全部資料筆數、SalesOrderID筆數、CustomerID筆數,再計算SubTotal的合計、最小值、最大值、平均、變異數

SELECT          COUNT(*) AS TOTAL_COUNT, COUNT(DISTINCT SalesOrderID) AS SALES_COUNT, COUNT(DISTINCT CustomerID) AS CUSTOMER_COUNT, SUM(SubTotal) AS 銷售金額_合計, MIN(SubTotal) AS 銷售金額_最小, MAX(SubTotal) AS 銷售金額_最大, AVG(SubTotal) AS 銷售金額_平均, STDEV(SubTotal) AS 銷售金額_變異

FROM              Sales.SalesOrderHeader

(2) 查詢檢視SalesOrderHeader資料表中,多個欄位的相關計算:

建立檢視表,選取[SalesOrderHeader]資料表,用上一個練習的語法修改,並在最前面補上一個欄位(N’欲查詢欄位名稱’ AS ‘欄位’)。

SELECT          N’銷售金額’ AS ‘欄位’, SUM(SubTotal) AS 合計, MIN(SubTotal) AS 最小, MAX(SubTotal) AS 最大, AVG(SubTotal) AS 平均, STDEV(SubTotal) AS 變異

FROM              Sales.SalesOrderHeader

UNION

SELECT          N’稅金’ AS ‘欄位’, SUM(TaxAmt) AS 合計, MIN(TaxAmt) AS 最小, MAX(TaxAmt) AS 最大, AVG(TaxAmt) AS 平均, STDEV(TaxAmt) AS 變異

FROM              Sales.SalesOrderHeader

  1. Error Discovering

illegal values, misspellings, missing values, varying value representation, and duplicates

三、SQL Server Information_Schema

  1. 建立查詢,查詢COLUMNS中,TABLE_SCHEMA、TABLE_NAME、COLUMN_NAME三個欄位,TABLE_NAME = ‘SALESORDERHEADER’,DATA_TYPE = ‘int’

(AdventureWork2012→檢視→系統檢視表→INFORMATION_SCHEMA.COLUMNS)

SELECT [TABLE_SCHEMA]

,[TABLE_NAME]

,[COLUMN_NAME]

FROM [AdventureWorks2012].[INFORMATION_SCHEMA].[COLUMNS]

WHERE TABLE_NAME = ‘SALESORDERHEADER’ AND DATA_TYPE = ‘int’

四、函數

declare @cmd1 nvarchar(100)

set @cmd1 = ‘select * from ‘

set @cmd1 = @cmd1 + ‘sales.salesorderheader’

print @cmd1

exec(@cmd1)

五、資料指標(Cursor)

練習:查詢SALESORDERHEADER資料表中,資料類型為整數的欄位有哪些,並計算每個欄位的最大最小值、平均、變異。

–宣告Cursor for 迴圈

Declare mydata Cursor for

–for迴圈裡面的資料

SELECT [COLUMN_NAME]

FROM [AdventureWorks2012].[INFORMATION_SCHEMA].[COLUMNS]

WHERE TABLE_NAME = ‘SALESORDERHEADER’ AND DATA_TYPE = ‘int’

–宣告變數

DECLARE @cmd1 nvarchar(2000)

DECLARE @colname nvarchar(100)

–開始run cursor

OPEN mydata

FETCH NEXT FROM mydata INTO @colname  –將第一筆資料填入變數

–宣告變數、設定變數值

DECLARE @flag int

SET @flag = 0

SET @cmd1 = ”

WHILE @@FETCH_STATUS = 0  –檢查是否有讀取到資料;WHILE用來處理迴圈,當為TRUE時則進入迴圈執行

BEGIN  –BEGIN…END表示一個區塊,通常搭配IF…ELSE或WHILE一起使用

IF @flag=1

BEGIN

SET @cmd1 = @cmd1 + ‘ union ‘;

END

SET @cmd1= @cmd1 + ‘Select ”’ + @colname + ”’ AS ‘ + N’欄位名稱’ + ‘, MAX(‘ + @colname + ‘) AS ‘ + N’最大值’ + ‘, MIN(‘ + @colname + ‘) AS ‘ + N’最小值’ + ‘, AVG(‘ + @colname + ‘) AS ‘ + N’平均’ + ‘, STDEV(‘ + @colname + ‘) AS ‘ + N’變異’ + ‘ from Sales.SalesOrderHeader’

SET @flag = 1

FETCH NEXT FROM mydata INTO @colname  –將下一筆資料填入變數

END

print (@cmd1)

exec (@cmd1)

CLOSE mydata  –關閉cursor與參數的關聯

DEALLOCATE mydata  –將cursor物件從記憶體移除

  • 資料庫資料表名稱是否區分大小寫

資料庫右鍵→屬性→「一般」頁籤→定序

CI:不區分大小寫,CS:區分大小寫

使用erwin建立資料表實體模型

【20170302_建立環境】

一、安裝ERwin:

  1. 「130.33.122:8080」或「140.130.33.153:8080」下載erwin64bit.exe和ERwinEvalLicense.lic.zip,將「ERwinEvalLicense.lic.zip」檔名修改成「ERwinEvalLicense.lic」。
  2. 執行exe,下一步 → 同意 → 下一步到底。
  3. 安裝完成後,執行erwin Data Modeler r9.7 (64-bit).exe,User Local licenese→Install License File→選擇lic檔案→OK。

二、開啟erwin程式

  1. 左上New→Type:Logical→OK。
  2. 按上面工具列,新增下列三個Entity。

● 輸入Title:學生→按Tab鍵輸入:學號→按Tab鍵依序輸入:姓名、性別、生日、地址、手機、家裡電話、地址、籍貫、父親、母親、身分證。(按Enter鍵輸入下一筆)

● 輸入Title:課程→按Tab鍵輸入:課程代號→按Tab鍵依序輸入:名稱、學分。

● 輸入Title:老師→按Tab鍵輸入:代號→按Tab鍵依序輸入:姓名、身分證、性別。

  1. 點選上面建立「多對多連線」,「學生:學號」與「課程:課程代號」兩個Entity建立關聯。
  2. 上面Model→Domains…→最上面new→新增:

● Name:身份證型別

● Logical Data Type:CHAR(10)

→下面頁籤

● 「General頁籤」→Null Option:Not Null

● 「Constraint頁籤」→new→

● Name:身分證長度10

● Validation Rule:下拉清單進去新增

● 上面Name:身分證長度10

● 下面「Definition」頁籤:Len(身份證)=10

● 這部分也可以從「學生」Entity右鍵→Attribute Properties進去新增,差別在於:直接從Domains進去新增的話,適用於新增一個廣泛規則,利於未來做一次性規則修改。
(Ex:身分證變成12碼,可直接修改Domains規則,套用此規則得依其改變)

  1. 修改欄位資料型態(Logical Data Type)

● 「學生」Entity右鍵→Attribute Properties:

學號:CHAR(8)、性別:CHAR(2)、地址:CHAR(50)、
生日:DATE、身分證:CHAR(10)

● 「課程」Entity右鍵→Attribute Properties:

學分:INT

● 「老師」Entity右鍵→Attribute Properties:

● 身分證:「Constraint」頁籤→左下規則:選上面建立的身分證型別

  1. 左上角儲存。
  2. 進行Derive:

● 上面Action→Desing Layers→Derive New Model→New Model Type:Physical、Target Database:SQL server→下一步到底→Derive

  1. 在「老師」Entity新增「手機」欄位
  2. 異動Physical Model方法(視窗點擊回Physical,才能選sync Derive with Model:

● 在(Derive之後的Diagram)中,上面Action→Desing Layers→sync Derive With Model,點擊下面「Sync」按鈕,點選「手機」(檢查哪些資料有異動),按上面「→」(更新到SQL Server)

  1. 開啟SQL Server,建立「A+學號」資料庫
  2. 回到erwin,Action→Forward Engineer→Schema→General

Server:.

Database:剛剛建立的「A+學號」資料庫

→Connect

  1. 反向工程:Action→Reverse Engineer,只勾選table,New Model Type:Physical,先用AdventureWork2012資料庫→Connect