Protected: Oracle資料庫系統管理 – WHERE條件語法與設定參數帶值

Posted on 2017-04-17 by 高聖帆

斜率

Posted on 2017-04-14 by 蕭博元

斜率

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

示意圖：

# coding=utf-8
from __future__ import division
import math, random
import matplotlib.pyplot as plt

print "---斜率&amp;倒數---"
def sum_of_squares(v):
    """computes the sum of squared elements in v"""
    return sum(v_i ** 2 for v_i in v)

# 兩點間斜率
def difference_quotient(f, x, h):
    return (f(x + h) - f(x)) / h

# 兩點斜率算倒數
def plot_estimated_derivative():
    def square(x):
        return x * x
    def derivative(x):
        return 2 * x
    # 倒數
    derivative_estimate = lambda x: difference_quotient(square, x, h=0.00001)
    # plot to show they're basically the same
    x = range(-10,10)
    plt.plot(x, map(derivative, x), 'rx')           # red  x
    plt.plot(x, map(derivative_estimate, x), 'b+')  # blue +
    plt.show()

plot_estimated_derivative()

# coding=utf-8

from __future__ import division

import math, random

import matplotlib.pyplot as plt

print "---斜率&倒數---"

def sum_of_squares(v):

"""computes the sum of squared elements in v"""

return sum(v_i ** 2 for v_i in v)

# 兩點間斜率

def difference_quotient(f, x, h):

return (f(x + h) - f(x)) / h

# 兩點斜率算倒數

def plot_estimated_derivative():

def square(x):

return x * x

def derivative(x):

return 2 * x

# 倒數

derivative_estimate = lambda x: difference_quotient(square, x, h=0.00001)

# plot to show they're basically the same

x = range(-10,10)

plt.plot(x, map(derivative, x), 'rx') # red x

plt.plot(x, map(derivative_estimate, x), 'b+') # blue +

plt.show()

plot_estimated_derivative()

print "---斜率---"
# linear_algebra
def vector_subtract(v, w):
    """subtracts two vectors componentwise"""
    return [v_i - w_i for v_i, w_i in zip(v,w)]

def squared_distance(v, w):
    return sum_of_squares(vector_subtract(v, w))

def distance(v, w):
   return math.sqrt(squared_distance(v, w))

# 找尋最低點
def step(v, direction, step_size):
    """move step_size in the direction from v"""
    return [v_i + step_size * direction_i
            for v_i, direction_i in zip(v, direction)]

def sum_of_squares_gradient(v):
    return [2 * v_i for v_i in v]

v = [random.randint(-10, 10) for i in range(3)]
print "sum of squares &amp; start point :",sum_of_squares(v),v
tolerance = 0.0000001

# 每次都往下找，值到點與點之間差距很小
while True:
    # print v, sum_of_squares(v)
    gradient = sum_of_squares_gradient(v)  # compute the gradient at v
    next_v = step(v, gradient, -0.01)  # 每次掉的幅度
    #print str(sum_of_squares(next_v)) + str(next_v)
    if distance(next_v, v) &lt; tolerance:  # stop if we're converging
        break
    v = next_v  # continue if we're not

print "minimum v", v
print "minimum value", sum_of_squares(v)

print "---斜率---"

# linear_algebra

def vector_subtract(v, w):

"""subtracts two vectors componentwise"""

return [v_i - w_i for v_i, w_i in zip(v,w)]

def squared_distance(v, w):

return sum_of_squares(vector_subtract(v, w))

def distance(v, w):

return math.sqrt(squared_distance(v, w))

# 找尋最低點

def step(v, direction, step_size):

"""move step_size in the direction from v"""

return [v_i + step_size * direction_i

for v_i, direction_i in zip(v, direction)]

def sum_of_squares_gradient(v):

return [2 * v_i for v_i in v]

v = [random.randint(-10, 10) for i in range(3)]

print "sum of squares & start point :",sum_of_squares(v),v

tolerance = 0.0000001

# 每次都往下找，值到點與點之間差距很小

while True:

# print v, sum_of_squares(v)

gradient = sum_of_squares_gradient(v) # compute the gradient at v

next_v = step(v, gradient, -0.01) # 每次掉的幅度

#print str(sum_of_squares(next_v)) + str(next_v)

if distance(next_v, v) < tolerance: # stop if we're converging

break

v = next_v # continue if we're not

print "minimum v", v

print "minimum value", sum_of_squares(v)

print "---v最小值方法二---"
# 用陣列給他step值，讓他一開始跳比較大，之後再慢慢縮小
def safe(f):
    """define a new function that wraps f and return it"""
    def safe_f(*args, **kwargs):
        try:
            return f(*args, **kwargs)
        except:
            return float('inf')         # this means "infinity" in Python
    return safe_f

def minimize_batch(target_fn, gradient_fn, theta_0, tolerance=0.000001):
    """use gradient descent to find theta that minimizes target function"""
    no = 0
    step_sizes = [100, 10, 1, 0.1, 0.01, 0.001, 0.0001, 0.00001]
    theta = theta_0  # set theta to initial value
    target_fn = safe(target_fn)  # safe version of target_fn
    value = target_fn(theta)  # value we're minimizing
    while True:
        gradient = gradient_fn(theta)
        next_thetas = [step(theta, gradient, -step_size)
                       for step_size in step_sizes]

        # choose the one that minimizes the error function
        next_theta = min(next_thetas, key=target_fn)
        next_value = target_fn(next_theta)
        print  str(no)+"  "+str(sum_of_squares(next_theta))+str(next_theta)
        no += 1
        # stop if we're "converging"
        if abs(value - next_value) &lt; tolerance:
            return theta
        else:
            theta, value = next_theta, next_value

v = [random.randint(-10, 10) for i in range(3)]
v = minimize_batch(sum_of_squares, sum_of_squares_gradient, v)

print "minimum v", v
print "minimum value", sum_of_squares(v)

print "---v最小值方法二---"

# 用陣列給他step值，讓他一開始跳比較大，之後再慢慢縮小

def safe(f):

"""define a new function that wraps f and return it"""

def safe_f(*args, **kwargs):

try:

return f(*args, **kwargs)

except:

return float('inf') # this means "infinity" in Python

return safe_f

def minimize_batch(target_fn, gradient_fn, theta_0, tolerance=0.000001):

"""use gradient descent to find theta that minimizes target function"""

no = 0

step_sizes = [100, 10, 1, 0.1, 0.01, 0.001, 0.0001, 0.00001]

theta = theta_0 # set theta to initial value

target_fn = safe(target_fn) # safe version of target_fn

value = target_fn(theta) # value we're minimizing

while True:

gradient = gradient_fn(theta)

next_thetas = [step(theta, gradient, -step_size)

for step_size in step_sizes]

# choose the one that minimizes the error function

next_theta = min(next_thetas, key=target_fn)

next_value = target_fn(next_theta)

print str(no)+" "+str(sum_of_squares(next_theta))+str(next_theta)

no += 1

# stop if we're "converging"

if abs(value - next_value) < tolerance:

return theta

else:

theta, value = next_theta, next_value

v = [random.randint(-10, 10) for i in range(3)]

v = minimize_batch(sum_of_squares, sum_of_squares_gradient, v)

print "minimum v", v

print "minimum value", sum_of_squares(v)

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

# egrep.py
import sys, re

if __name__ == "__main__":
    # sys.argv is the list of command-line arguments
    # sys.argv[0] is the name of the program itself
    # sys.argv[1] will be the regex specfied at the command line
    regex = sys.argv[1]
    # for every line passed into the script
    for line in sys.stdin:
        # if it matches the regex, write it to stdout
        if re.search(regex, line):
            sys.stdout.write(line)

# egrep.py

import sys, re

if __name__ == "__main__":

# sys.argv is the list of command-line arguments

# sys.argv[0] is the name of the program itself

# sys.argv[1] will be the regex specfied at the command line

regex = sys.argv[1]

# for every line passed into the script

for line in sys.stdin:

# if it matches the regex, write it to stdout

if re.search(regex, line):

sys.stdout.write(line)

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

# line_count.py
import sys

if __name__ == "__main__":
    count = 0
    for line in sys.stdin:
        count += 1
    # print goes to sys.stdout
    print count

# line_count.py

import sys

if __name__ == "__main__":

count = 0

for line in sys.stdin:

count += 1

# print goes to sys.stdout

print count

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

# most_common_words.py
import sys
from collections import Counter

if __name__ == "__main__":
    # pass in number of words as first argument
    try:
        num_words = int(sys.argv[1])
    except:
        print "usage: most_common_words.py num_words"
        sys.exit(1)   # non-zero exit code indicates error

    counter = Counter(word.lower()                      
                      for line in sys.stdin            
                      for word in line.strip().split() 
                      if word)                         

    for word, count in counter.most_common(num_words):
        sys.stdout.write(str(count))
        sys.stdout.write("\t")
        sys.stdout.write(word)
        sys.stdout.write("\n")

# most_common_words.py

import sys

from collections import Counter

if __name__ == "__main__":

# pass in number of words as first argument

try:

num_words = int(sys.argv[1])

except:

print "usage: most_common_words.py num_words"

sys.exit(1) # non-zero exit code indicates error

counter = Counter(word.lower()

for line in sys.stdin

for word in line.strip().split()

if word)

for word, count in counter.most_common(num_words):

sys.stdout.write(str(count))

sys.stdout.write("\t")

sys.stdout.write(word)

sys.stdout.write("\n")

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

SSDT計算屬性對話方塊

Posted on 2017-04-11 by 蔡承恩

–SQL Server Data Tools

一、建立Cube

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

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

新增資料來源檢視：

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

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

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

新增維度：

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

建立階層：

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

新增Cube

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

左上「處理」。

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

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

二、計算「平均分數」

在CUBE的「計算」頁籤，上方工具列或「指令碼組合管理區域」右鍵，新增導出成員，

名稱：[平均分數]　(中括號不能刪！)
運算式：[Measures].[分數]/[Measures].[成績單計數]
(可從左下拉進來)

上方工具列「處理」，重新處理CUBE完成後，「瀏覽器」頁籤→到EXCEL查看結果。

欄：科目
列：姓名
值：平均分數

三、建立群組檢視─科目

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

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

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

※方法二：新增命名集

在CUBE「計算」頁籤，新增命名集，

名稱：[語文類]
運算式：{[科目維度].[科目].&[英語], [科目維度].[科目].&[國語]}
(可從左下拉進來，前後加的是大括號)

CUBE處理完成後，到EXCEL，會發現「科目維度」項目下多了「集」，展開後可以看到剛剛建立的「語文類」，放到右下”欄”。

※資料錯誤

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

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

在CUBE「計算」頁籤，新增命名集，

名稱：[觀察學生]
運算式：{ {[學生維度].[姓名].&[王○慈],[學生維度].[姓名].&[吳○慈], [學生維度].[姓名].&[李○瑄]}}
(可從左下拉進來，前後加的是大括號)

CUBE處理完成後，到EXCEL，會發現「學生維度」項目下多了「集」，展開後可以看到剛剛建立的「觀察學生」，放到右下「列」。

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

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

在CUBE「計算」頁籤，新增命名集，

名稱：[前三名]
運算式：topcount([學生表].[姓名].children, 3, [Measures].[分數])
(topcount函數：以分數為標準，從學生表中挑出前三位學生)

CUBE處理完成後，到EXCEL，會發現「學生維度」項目下多了「集」，展開後可以看到剛剛建立的「前三名」，放到右下「列」。

六、建立「成績」的KPI

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

在CUBE「KPI」頁籤，新增KPI，

名稱：[成績]
值運算式：[Measures].[分數]
目標運算式：85
狀態指標：形狀
狀態運算式：

CASE
    WHEN [Measures].[分數] >= 85 THEN 1
    WHEN [Measures].[分數] < 80 THEN -1
    ELSE 0
END

CASE

WHEN [Measures].[分數] >= 85 THEN 1

WHEN [Measures].[分數] < 80 THEN -1

ELSE 0

END

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

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

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

在CUBE「計算」頁籤，新增導出成員，

名稱：[前次分數]
運算式：

(ParallelPeriod([年度段考表].[段考代號].[段考代號], 1,[年度段考表].[段考代號].CurrentMember),[科目表].[科目編號].CurrentMember,[學生表].[座號].CurrentMember, [Measures].[分數])

1	(ParallelPeriod([年度段考表].[段考代號].[段考代號], 1,[年度段考表].[段考代號].CurrentMember),[科目表].[科目編號].CurrentMember,[學生表].[座號].CurrentMember, [Measures].[分數])

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

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

Posted on 2017-04-11 by 蔡承恩

※SQL Server Data Tools

一、建立Cube

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

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

新增資料來源檢視：

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

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

新增維度：

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

建立階層：

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

建立Cube

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

左上「處理」。

「瀏覽器」頁籤，用EXCEL的樞紐分析表檢視此資料，

篩選：
欄：科目
列：座號(不要做小計，以列表方式呈現)、姓名
值：分數

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

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

二、更新異動資料到CUBE

到SQL Server，在[A12345].[學生表.dbo]，新增一個欄位，

資料行名稱：性別
資料類型：nchar(1)

，然後儲存。

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

※檢視方塊

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

一、建立檢視方塊

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

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

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

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

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

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

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

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

※Google Keyword：locale Identifier list，或參考Microsoft Locale ID Values

重新開啟剛剛的.odc檔案：

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

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

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

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

到SQL Server，在[A12345].[學生表.dbo]，新增一個欄位：

資料行名稱：SubjectName
資料類型：nchar(10)

，然後儲存。

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

關聯檢測結果

Posted on 2017-04-11 by 蔡承恩

※比較新舊資料

建立預存程序

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

執行預存程序

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

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

※【SSDT】

開啟SSDT
新增專案→Integration Services專案
左邊工具箱→通用→新增一個「資料分析工作」→點兩下→

「一般」頁籤→Destination：下拉清單「新增檔案連接」→使用類型：建立檔案→命名Data1→確定
「設定檔要求」頁籤→設定檔類型：資料行統計資料設定檔要求，後面的「要求識別碼」會自然產生→點上面檢視：資料行統計資料設定檔要求→下面Connection Manager：下拉清單「新增NET連接」→伺服器：.、資料庫：AdventureWorks2012→OK→TableOrView：[Sales].[SalesOrderHeader]、Column：SubTotal。

※關聯檢測結果

一、資料準備

以系統管理員身分執行SQL Server
到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)

三、建立兩個檢視表

※訂單 & 訂單明細

選取[訂單]、[訂單明細]資料表，並做FULL OUTER JOIN關聯建立兩個檢視表
篩選條件，勾選：

[訂單].[訂單編號]，篩選：IS NULL
[訂單明細].[訂單編號]，或：IS NULL

依序新增資料行

Getdate() AS 日期
‘訂單’ AS 表1
‘訂單編號’ AS 表1欄位
勾選[訂單].[訂單編號]，別名：訂單_訂單編號
‘訂單明細’ AS 表2
‘訂單編號’ AS 表2欄位
勾選[訂單明細].[訂單編號]，別名：明細_訂單編號

SELECT          GETDATE() AS 日期, '訂單' AS 表1, '訂單編號' AS 表1欄位, dbo.訂單.訂單編號 AS 訂單_訂單編號, '訂單明細' AS 表2, 
                            '訂單編號' AS 表2欄位, dbo.訂單明細.訂單編號 AS 明細_訂單編號
FROM              dbo.訂單 FULL OUTER JOIN
                            dbo.訂單明細 ON dbo.訂單.訂單編號 = dbo.訂單明細.訂單編號
WHERE          (dbo.訂單.訂單編號 IS NULL) OR
                            (dbo.訂單明細.訂單編號 IS NULL)

SELECT GETDATE() AS 日期, '訂單' AS 表1, '訂單編號' AS 表1欄位, dbo.訂單.訂單編號 AS 訂單_訂單編號, '訂單明細' AS 表2,

'訂單編號' AS 表2欄位, dbo.訂單明細.訂單編號 AS 明細_訂單編號

FROM dbo.訂單 FULL OUTER JOIN

dbo.訂單明細 ON dbo.訂單.訂單編號 = dbo.訂單明細.訂單編號

WHERE (dbo.訂單.訂單編號 IS NULL) OR

(dbo.訂單明細.訂單編號 IS NULL)

※訂單明細 & 產品資料

選取[訂單明細]、[產品資料]資料表，並做FULL OUTER JOIN關聯
篩選條件，勾選：

[訂單明細].[產品編號]，篩選：IS NULL
[產品資料].[產品編號]，或：IS NULL

依序新增資料行

Getdate() AS 日期
‘訂單明細’ AS 表1
‘明細編號’ AS 表1欄位
勾選[訂單明細].[產品編號]，別名：明細_產品編號
‘產品資料’ AS 表2
‘產品編號’ AS 表2欄位
勾選[產品資料].[產品編號]，別名：產品_產品編號

SELECT          GETDATE() AS 日期, '訂單明細' AS 表1, '明細編號' AS 表1欄位, dbo.訂單明細.產品編號 AS 明細_產品編號, 
                            '產品資料' AS 表2, '產品編號' AS 表2欄位, dbo.產品資料.產品編號 AS 產品_產品編號
FROM              dbo.訂單明細 FULL OUTER JOIN
                            dbo.產品資料 ON dbo.訂單明細.產品編號 = dbo.產品資料.產品編號
WHERE          (dbo.訂單明細.產品編號 IS NULL) OR
                            (dbo.產品資料.產品編號 IS NULL)

SELECT GETDATE() AS 日期, '訂單明細' AS 表1, '明細編號' AS 表1欄位, dbo.訂單明細.產品編號 AS 明細_產品編號,

'產品資料' AS 表2, '產品編號' AS 表2欄位, dbo.產品資料.產品編號 AS 產品_產品編號

FROM dbo.訂單明細 FULL OUTER JOIN

dbo.產品資料 ON dbo.訂單明細.產品編號 = dbo.產品資料.產品編號

WHERE (dbo.訂單明細.產品編號 IS NULL) OR

(dbo.產品資料.產品編號 IS NULL)

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

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

INSERT INTO 關聯檢測結果
SELECT          GETDATE() AS 日期, '訂單明細' AS 表1, '明細編號' AS 表1欄位, dbo.訂單明細.產品編號 AS 明細_產品編號, '產品資料' AS 表2, '產品編號' AS 表2欄位, dbo.產品資料.產品編號 AS 產品_產品編號
FROM              dbo.訂單明細 FULL OUTER JOIN dbo.產品資料 ON dbo.訂單明細.產品編號 = dbo.產品資料.產品編號
WHERE          (dbo.訂單明細.產品編號 IS NULL) OR (dbo.產品資料.產品編號 IS NULL)

INSERT INTO 關聯檢測結果
SELECT          GETDATE() AS 日期, '訂單' AS 表1, '訂單編號' AS 表1欄位, dbo.訂單.訂單編號 AS 訂單_訂單編號, '訂單明細' AS 表2, '訂單編號' AS 表2欄位, dbo.訂單明細.訂單編號 AS 明細_訂單編號
FROM              dbo.訂單 FULL OUTER JOIN dbo.訂單明細 ON dbo.訂單.訂單編號 = dbo.訂單明細.訂單編號
WHERE          (dbo.訂單.訂單編號 IS NULL) OR (dbo.訂單明細.訂單編號 IS NULL)

INSERT INTO 關聯檢測結果

SELECT GETDATE() AS 日期, '訂單明細' AS 表1, '明細編號' AS 表1欄位, dbo.訂單明細.產品編號 AS 明細_產品編號, '產品資料' AS 表2, '產品編號' AS 表2欄位, dbo.產品資料.產品編號 AS 產品_產品編號

FROM dbo.訂單明細 FULL OUTER JOIN dbo.產品資料 ON dbo.訂單明細.產品編號 = dbo.產品資料.產品編號

WHERE (dbo.訂單明細.產品編號 IS NULL) OR (dbo.產品資料.產品編號 IS NULL)

INSERT INTO 關聯檢測結果

SELECT GETDATE() AS 日期, '訂單' AS 表1, '訂單編號' AS 表1欄位, dbo.訂單.訂單編號 AS 訂單_訂單編號, '訂單明細' AS 表2, '訂單編號' AS 表2欄位, dbo.訂單明細.訂單編號 AS 明細_訂單編號

FROM dbo.訂單 FULL OUTER JOIN dbo.訂單明細 ON dbo.訂單.訂單編號 = dbo.訂單明細.訂單編號

WHERE (dbo.訂單.訂單編號 IS NULL) OR (dbo.訂單明細.訂單編號 IS NULL)

資料指標2

Posted on 2017-04-09 by 蔡承恩

一、SQL

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

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

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

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

TABLE_NAME→資料表
COLUMN_NAME→欄位名稱
IS_NULLABLE→NULL
CHARACTER_MAXIMUM_LENGTH→長度
新增一欄：說明

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

查詢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]

用子查詢(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資料庫中各個表格的每個欄位中的字元長度

把vAdv2012AllCharColumns檢視表增加Table_Catalog、vAdv2012Tables.Table_Schema兩個欄位，並移到最上面，IS_NULLABLE欄為增加篩選條件= ‘NO’。

將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

機率

Posted on 2017-04-06 by 蕭博元

機率

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)

# coding=utf-8
from __future__ import division
from collections import Counter
import math, random

print "---課本範例---"
both_girls = 0
older_girl = 0
either_girl = 0

def random_kid():
    return random.choice(["boy", "girl"])

random.seed(0)
for _ in range(10000):
    younger = random_kid()
    older = random_kid()
    if older == "girl":
        older_girl += 1
    if older == "girl" and younger == "girl":
        both_girls += 1
    if older == "girl" or younger == "girl":
        either_girl += 1

print "P(both | older):", both_girls / older_girl      # 0.514 ~ 1/2
print "P(both | either): ", both_girls / either_girl   # 0.342 ~ 1/3

# coding=utf-8

from __future__ import division

from collections import Counter

import math, random

print "---課本範例---"

both_girls = 0

older_girl = 0

either_girl = 0

def random_kid():

return random.choice(["boy", "girl"])

random.seed(0)

for _ in range(10000):

younger = random_kid()

older = random_kid()

if older == "girl":

older_girl += 1

if older == "girl" and younger == "girl":

both_girls += 1

if older == "girl" or younger == "girl":

either_girl += 1

print "P(both | older):", both_girls / older_girl # 0.514 ~ 1/2

print "P(both | either): ", both_girls / either_girl # 0.342 ~ 1/3

print "---修改範例---"
a1=0
a2=0
aboth=0
n=100000
def random_ball():
    return random.choice(["B", "Y"])

random.seed(2)

for _ in range(n):
    get1 = random_ball()
    get2 = random_ball()
    if get1 == "B":
        a1 += 1
    if get1 == "B" and get2 == "B":
        aboth += 1
    if get2 == "B":
        a2 += 1


print "---第一次發生與都發生機率相乘看有沒有獨立---"
print "P(aboth):", aboth / n
print "P(get1): ", a1 / n
print "P(get2): ", a2 / n
print "P(get1,get2): ", a1*a2 / (n*n)
print "P(get1|get2) = p(aboth)/p(get2): ", (aboth / n) / (a2 / n)
print "p(get1|get2)/p(get2) = p(get1)p(get2)/p((get2) = p(get1) : ",a1 / n

print "---修改範例---"

a1=0

a2=0

aboth=0

n=100000

def random_ball():

return random.choice(["B", "Y"])

random.seed(2)

for _ in range(n):

get1 = random_ball()

get2 = random_ball()

if get1 == "B":

a1 += 1

if get1 == "B" and get2 == "B":

aboth += 1

if get2 == "B":

a2 += 1

print "---第一次發生與都發生機率相乘看有沒有獨立---"

print "P(aboth):", aboth / n

print "P(get1): ", a1 / n

print "P(get2): ", a2 / n

print "P(get1,get2): ", a1*a2 / (n*n)

print "P(get1|get2) = p(aboth)/p(get2): ", (aboth / n) / (a2 / n)

print "p(get1|get2)/p(get2) = p(get1)p(get2)/p((get2) = p(get1) : ",a1 / n

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

蒙地卡羅法求 PI

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

The Normal Distribution

<span class="Normal-H"><span style="font-family: Calibri;">print "---</span><span style="font-family: Calibri;">常態機率分配</span><span style="font-family: Calibri;">---"</span></span>
from matplotlib import pyplot as plt
def normal_pdf(x, mu=0, sigma=1):
    sqrt_two_pi = math.sqrt(2 * math.pi)
    return (math.exp(-(x-mu) ** 2 / 2 / sigma ** 2) / (sqrt_two_pi * sigma))

print "---常態機率分配---"

from matplotlib import pyplot as plt

def normal_pdf(x, mu=0, sigma=1):

sqrt_two_pi = math.sqrt(2 * math.pi)

return (math.exp(-(x-mu) ** 2 / 2 / sigma ** 2) / (sqrt_two_pi * sigma))

# sigma越小，越集中

# 機率分配

def plot_normal_pdfs(plt):
    xs = [x / 10.0 for x in range(-50, 50)]
    plt.plot(xs,[normal_pdf(x,sigma=1) for x in xs],'-',label='mu=0,sigma=1')
    plt.plot(xs,[normal_pdf(x,sigma=2) for x in xs],'--',label='mu=0,sigma=2')
    plt.plot(xs,[normal_pdf(x,sigma=0.5) for x in xs],':',label='mu=0,sigma=0.5')
    plt.plot(xs,[normal_pdf(x,mu=-1)   for x in xs],'-.',label='mu=-1,sigma=1')
    plt.legend()
    plt.show()
plot_normal_pdfs(plt)

def plot_normal_pdfs(plt):

xs = [x / 10.0 for x in range(-50, 50)]

plt.plot(xs,[normal_pdf(x,sigma=1) for x in xs],'-',label='mu=0,sigma=1')

plt.plot(xs,[normal_pdf(x,sigma=2) for x in xs],'--',label='mu=0,sigma=2')

plt.plot(xs,[normal_pdf(x,sigma=0.5) for x in xs],':',label='mu=0,sigma=0.5')

plt.plot(xs,[normal_pdf(x,mu=-1) for x in xs],'-.',label='mu=-1,sigma=1')

plt.legend()

plt.show()

plot_normal_pdfs(plt)

print "---累積分配---"
def normal_cdf(x, mu=0,sigma=1):
    return (1 + math.erf((x - mu) / math.sqrt(2) / sigma)) / 2

def plot_normal_cdfs(plt):
    xs = [x / 10.0 for x in range(-50, 50)]
    plt.plot(xs,[normal_cdf(x,sigma=1) for x in xs],'-',label='mu=0,sigma=1')
    plt.plot(xs,[normal_cdf(x,sigma=2) for x in xs],'--',label='mu=0,sigma=2')
    plt.plot(xs,[normal_cdf(x,sigma=0.5) for x in xs],':',label='mu=0,sigma=0.5')
    plt.plot(xs,[normal_cdf(x,mu=-1) for x in xs],'-.',label='mu=-1,sigma=1')
    plt.legend(loc=4) # bottom right
    plt.show()
plot_normal_cdfs(plt)

print "---累積分配---"

def normal_cdf(x, mu=0,sigma=1):

return (1 + math.erf((x - mu) / math.sqrt(2) / sigma)) / 2

def plot_normal_cdfs(plt):

xs = [x / 10.0 for x in range(-50, 50)]

plt.plot(xs,[normal_cdf(x,sigma=1) for x in xs],'-',label='mu=0,sigma=1')

plt.plot(xs,[normal_cdf(x,sigma=2) for x in xs],'--',label='mu=0,sigma=2')

plt.plot(xs,[normal_cdf(x,sigma=0.5) for x in xs],':',label='mu=0,sigma=0.5')

plt.plot(xs,[normal_cdf(x,mu=-1) for x in xs],'-.',label='mu=-1,sigma=1')

plt.legend(loc=4) # bottom right

plt.show()

plot_normal_cdfs(plt)

The Central Limit Theorem

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

Ch07假設檢定

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

-20,-10,0,10,20

標準化standardize

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

把資料處理過才能去比較

print "---假設檢定---"
# Z值
def inverse_normal_cdf(p, mu=0, sigma=1, tolerance=0.00001):
    """find approximate inverse using binary search"""
    # if not standard, compute standard and rescale
    if mu != 0 or sigma != 1:
        return mu + sigma * inverse_normal_cdf(p, tolerance=tolerance)
    low_z, low_p = -10.0, 0  # normal_cdf(-10) is (very close to) 0
    hi_z, hi_p = 10.0, 1  # normal_cdf(10)  is (very close to) 1
    while hi_z - low_z &gt; tolerance:
        mid_z = (low_z + hi_z) / 2  # consider the midpoint
        mid_p = normal_cdf(mid_z)  # and the cdf's value there
        if mid_p &lt; p:
            # midpoint is still too low, search above it
            low_z, low_p = mid_z, mid_p
        elif mid_p &gt; p:
            # midpoint is still too high, search below it
            hi_z, hi_p = mid_z, mid_p
        else:
            break
    return mid_z
# 平均數,變異數
def normal_approximation_to_binomial(n, p):
    """finds mu and sigma corresponding to a Binomial(n, p)"""
    mu = p * n
    sigma = math.sqrt(p * (1 - p) * n)
    return mu, sigma
# 右尾
def normal_upper_bound(probability, mu=0, sigma=1):
    """returns the z for which P(Z &lt;= z) = probability"""
    return inverse_normal_cdf(probability, mu, sigma)
# 左尾
def normal_lower_bound(probability, mu=0, sigma=1):
    """returns the z for which P(Z &gt;= z) = probability"""
    return inverse_normal_cdf(1 - probability, mu, sigma)
# 雙尾
def normal_two_sided_bounds(probability, mu=0, sigma=1):
    """returns the symmetric (about the mean) bounds
    that contain the specified probability"""
    tail_probability = (1 - probability) / 2
    # upper bound should have tail_probability above it
    upper_bound = normal_lower_bound(tail_probability, mu, sigma)
    # lower bound should have tail_probability below it
    lower_bound = normal_upper_bound(tail_probability, mu, sigma)
    return lower_bound, upper_bound


print "---0.5，95%信賴區間---"
mu_0, sigma_0 = normal_approximation_to_binomial(1000, 0.5)
print "mu_0", mu_0
print "sigma_0", sigma_0
print "normal_two_sided_bounds(0.95, mu_0, sigma_0)", normal_two_sided_bounds(0.95, mu_0, sigma_0)

print "---0.47，99%信賴區間---"
mu_0, sigma_0 = normal_approximation_to_binomial(1000, 0.47)
print "mu_0", mu_0
print "sigma_0", sigma_0
print "normal_two_sided_bounds(0.95, mu_0, sigma_0)", normal_two_sided_bounds(0.99, mu_0, sigma_0)

print "---假設檢定---"

# Z值

def inverse_normal_cdf(p, mu=0, sigma=1, tolerance=0.00001):

"""find approximate inverse using binary search"""

# if not standard, compute standard and rescale

if mu != 0 or sigma != 1:

return mu + sigma * inverse_normal_cdf(p, tolerance=tolerance)

low_z, low_p = -10.0, 0 # normal_cdf(-10) is (very close to) 0

hi_z, hi_p = 10.0, 1 # normal_cdf(10) is (very close to) 1

while hi_z - low_z > tolerance:

mid_z = (low_z + hi_z) / 2 # consider the midpoint

mid_p = normal_cdf(mid_z) # and the cdf's value there

if mid_p < p:

# midpoint is still too low, search above it

low_z, low_p = mid_z, mid_p

elif mid_p > p:

# midpoint is still too high, search below it

hi_z, hi_p = mid_z, mid_p

else:

break

return mid_z

# 平均數,變異數

def normal_approximation_to_binomial(n, p):

"""finds mu and sigma corresponding to a Binomial(n, p)"""

mu = p * n

sigma = math.sqrt(p * (1 - p) * n)

return mu, sigma

# 右尾

def normal_upper_bound(probability, mu=0, sigma=1):

"""returns the z for which P(Z <= z) = probability"""

return inverse_normal_cdf(probability, mu, sigma)

# 左尾

def normal_lower_bound(probability, mu=0, sigma=1):

"""returns the z for which P(Z >= z) = probability"""

return inverse_normal_cdf(1 - probability, mu, sigma)

# 雙尾

def normal_two_sided_bounds(probability, mu=0, sigma=1):

"""returns the symmetric (about the mean) bounds

that contain the specified probability"""

tail_probability = (1 - probability) / 2

# upper bound should have tail_probability above it

upper_bound = normal_lower_bound(tail_probability, mu, sigma)

# lower bound should have tail_probability below it

lower_bound = normal_upper_bound(tail_probability, mu, sigma)

return lower_bound, upper_bound

print "---0.5，95%信賴區間---"

mu_0, sigma_0 = normal_approximation_to_binomial(1000, 0.5)

print "mu_0", mu_0

print "sigma_0", sigma_0

print "normal_two_sided_bounds(0.95, mu_0, sigma_0)", normal_two_sided_bounds(0.95, mu_0, sigma_0)

print "---0.47，99%信賴區間---"

mu_0, sigma_0 = normal_approximation_to_binomial(1000, 0.47)

print "mu_0", mu_0

print "sigma_0", sigma_0

print "normal_two_sided_bounds(0.95, mu_0, sigma_0)", normal_two_sided_bounds(0.99, mu_0, sigma_0)

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

資料指標1

Posted on 2017-04-06 by 蔡承恩

一、資料管理(Data Management)

Data Profiling
Data Standardization
Geocoding
Matching or linking
Monitoring
Batch and Real time

二、Data Profiling

確認資料的定義是什麼。

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)

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

Error Discovering

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

三、SQL Server Information_Schema

建立查詢，查詢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建立資料表實體模型

Posted on 2017-04-05 by 蔡承恩

【20170302_建立環境】

一、安裝ERwin：

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

二、開啟erwin程式

左上New→Type：Logical→OK。
按上面工具列，新增下列三個Entity。

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

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

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

點選上面建立「多對多連線」，「學生：學號」與「課程：課程代號」兩個Entity建立關聯。
上面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規則，套用此規則得依其改變)

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

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

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

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

學分：INT

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

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

左上角儲存。
進行Derive：

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

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

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

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

Server：.

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

→Connect

反向工程：Action→Reverse Engineer，只勾選table，New Model Type：Physical，先用AdventureWork2012資料庫→Connect

Artificial Intelligence & Big Data LAB

人工智慧暨巨量資料實驗室

Monthly Archives: April 2017

Protected: Oracle資料庫系統管理 – WHERE條件語法與設定參數帶值

斜率

SSDT計算屬性對話方塊

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

關聯檢測結果

資料指標2

機率

資料指標1

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