不能改變設計時…

18

VIEW

例一：消除重複資料

無法改變這個
↓

以 檢視表 呈現正規化後的樣子

CREATE VIEW vCustomers AS
SELECT DISTINCT cs.CustFirstName, cs.CustLastName,
    cs.Address, cs.City, cs.Phone
FROM CustomerSales AS cs;

CREATE VIEW vAutomobileModels AS
SELECT DISTINCT cs.ModelYear, cs.Model
FROM CustomerSales AS cs;

CREATE VIEW vEmployees AS
SELECT DISTINCT cs.SalesPerson
FROM CustomerSales AS cs;

挑選欄位 並 DISTINCT，正規化為三個資料表。

便容易發現錯誤，並在原資料表修正。

例二：消除重複群組

無法改變這個
↓

以 檢視表 呈現正規化後的樣子

CREATE VIEW vDrawings AS
SELECT a.ID AS DrawingID, a.DrawingNumber
FROM Assignments AS a;

CREATE VIEW vPredecessors AS
SELECT 1 AS PredecessorID, a.ID AS DrawingID,
    a.Predecessor_1 AS Predecessor
FROM Assignments AS a
WHERE a.Predecessor_1 IS NOT NULL
UNION
…
UNION
SELECT 5, a.ID, a.Predecessor_5
FROM Assignments AS a
WHERE a.Predecessor_5 IS NOT NULL;

挑選重複欄位 並 UNION

檢視表 可以編輯嗎？

CREATE TRIGGER view_insert
    INSTEAD OF INSERT ON my_view
    FOR EACH ROW
    EXECUTE FUNCTION view_insert_row();

PostgreSQL: CREATE TRIGGER

其他使用場合

拿檢視表製作檢視表

有效能疑慮

CREATE VIEW vActiveCustomers AS
SELECT c.CustomerID, c.CustFirstName, c.CustLastName,
    c.CustFirstName + ' ' + c.CustLastName AS CustFullName
FROM Customers AS c
WHERE EXISTS (
    SELECT NULL
    FROM Orders AS o
    WHERE o.CustomerID = c.CustomerID
        AND o.OrderDate > DATEADD(MONTH, -6, GETDATE()));

近半年下單的客人

CREATE VIEW vCustomerStatistics AS
SELECT o.CustomerID, COUNT(o.OrderNumber) AS OrderCount,
    SUM(o.OrderTotal) AS GrandOrderTotal,
    MAX(o.OrderDate) AS LastOrderDate
FROM Orders AS o
GROUP BY o.CustomerID;

客人的統計資料

CREATE VIEW vActiveCustomerStatistics AS
SELECT a.CustomerID, a.CustFirstName, a.CustLastName,
    s.LastOrderDate, s.GrandOrderTotal
FROM vActiveCustomers AS a
    INNER JOIN vCustomerStatistics AS s
        ON a.CustomerID = s.CustomerID;

近半年下單客人的統計資料 (但沒 OrderCount)

SELECT c.CustomerID, c.CustFirstName, c.CustLastName,
    s.LastOrderDate, s.GrandOrderTotal
FROM Customers AS c
    INNER JOIN (
        SELECT o.CustomerID,
            SUM(o.OrderTotal) AS GrandOrderTotal,
            MAX(o.OrderDate) AS LastOrderDate
        FROM Orders AS o
        GROUP BY o.CustomerID) AS s
    ON c.CustomerID = s.CustomerID
WHERE EXISTS (
    SELECT NULL FROM Orders AS o
    WHERE o.CustomerID = c.CustomerID
        AND o.OrderDate > DATEADD(MONTH, -6, GETDATE()));

最佳化程序產生的結果

19

ETL

Access 為例 (1/2)

Access 為例 (2/2)

20

SUMMARY

目的？

土炮做法

建立額外資料表來 匯總 (summary) 原始資料表

DB2 支援 匯總資料表

語法

CREATE SUMMARY TABLE SalesSummary AS (
SELECT
    …
)

DB2

CREATE MATERIALIZED VIEW SalesSummary
    …

Oracle

語法

成本

行內匯總

來避免維護、管理的成本

想像中的樣子 (不確定…)

21

以 UNION 整理資料

舉例討論

從 Excel 來的試算表

1. 先設法匯入

SELECT Category, OctQuantity, OctSales
FROM SalesSummary

不同月份資料要分開查詢

2. 直接以 UNION 將各月份查詢合併

SELECT Category, OctQuantity, OctSales
FROM SalesSummary
UNION
SELECT Category, NovQuantity, NovSales
FROM SalesSummary
UNION
…

分不清是何月

3. ⑴ 於 SELECT 增加月份欄位 ⑵ 善用 AS 更名欄位

SELECT Category, 'Oct' AS SalesMonth,
    OctQuantity AS Quantity, OctSales AS SalesAmt
FROM SalesSummary
UNION
SELECT Category, 'Nov', NovQuantity, NovSales
FROM SalesSummary
UNION
…

4. 在最後加個 ORDER BY 排序資料

SELECT Category, 'Oct' AS SalesMonth,
    OctQuantity AS Quantity, OctSales AS SalesAmt
FROM SalesSummary
UNION
…
UNION
SELECT Category, 'Jan', JanQuantity, JanSales
FROM SalesSummary
UNION
SELECT Category, 'Feb', FebQuantity, FebSales
FROM SalesSummary
ORDER BY SalesMonth, Category;

規則

UNION ALL 不排除重複，
速度較快。

END;