---
# System prepended metadata

title: CH10打造可重複分析用的自訂資料集

---

# CH10打造可重複分析用的自訂資料集

### ad-hoc reporting（即席報表）
接受特定提問 -> 探索資料庫 -> 攥寫SQL提取所需的資料 -> 進行分析得到問題的答案

## 10-1 思考自訂資料集的需求
### 考慮需要用到的度量值與維度
度量值是指可被量化與分析的資料（如銷售額），可以將度量值衍生或轉換出評估用的指標，並以各種維度（例如月份）做分析，如同從不同角度與視野去觀察這些資料

### 根據組成的資料集來確認是否可以回答預期問題
假設常問的問題：
1. 上週市集營業額是多少？
2. 上個星期三與星期六營業額相比
3. 想知道某段時間的總營業額？
4. 針對每週市場的營業額進行定期追蹤
5. 將每週銷售額以供應商為單位匯總

![IMG_8436](https://hackmd.io/_uploads/SygXnfFRT.jpg)
![IMG_8437](https://hackmd.io/_uploads/S1V7nzY0p.jpg)




將各種常用條件加入到sql之中，組成一張表

## 10-2 可重複使用自訂資料集的方法：CTEs和Views

* CTEs(公共表達式，Common Table Expressions)
* Views(視圖)

### 建立CTE的WITH語句
允許為一個查詢結果建立別名，在後續查詢中引用別名去取得該查詢的結果集
![IMG_8438](https://hackmd.io/_uploads/HJaBhMKCT.jpg)

```sql=
WITH CTE名稱 AS (
    -- CTE定義的SQL查询
)
SELECT * FROM CTE名稱;

-- Example
WITH ManagerCTE AS (
    SELECT DISTINCT e.manager_id, m.name
    FROM employees e
    INNER JOIN employees m ON e.manager_id = m.id
)
SELECT * FROM ManagerCTE;
```

* WITH語句內的查詢（如同子查詢一樣）會先執行，每個查詢的結果都會有一個暫存的臨時結果集，我們分別為這些臨時結果集取查詢別名。然後位於WITH語句之後的主查詢SELECT敘述就可以透過別名引用各該臨時結果集

#### 多個CTE
```sql=
WITH CTE名稱1 AS (
    -- CTE1定義的SQL查询
), CTE名稱2 AS (
    -- CTE2定義的SQL查询
)
SELECT * FROM CTE名稱1
JOIN CTE名稱2 ON CTE名稱1.某列 = CTE名稱2.某列;

--- Example
WITH SalesReps AS (
    SELECT id, name
    FROM employees
    WHERE position = 'Sales Rep'
), SalesTotals AS (
    SELECT sales_rep_id, SUM(amount) AS total_sales
    FROM sales
    GROUP BY sales_rep_id
)
SELECT s.name, st.total_sales
FROM SalesReps s
JOIN SalesTotals st ON s.id = st.sales_rep_id;
```

### 將查詢儲存為視圖(Views)
* 將查詢建立視圖存放在資料庫中。視圖是資料庫的一種物件，基本存放的是SQL語句。當引用視圖時，實際上是執行視圖所定義的SQL語句，動態生成一個結果集（虛擬表格），視圖的內容可以動態查出底層表格的當前資料。

```sql=
CREATE VIEW [資料庫結構名稱].[視圖名稱] AS
```
![IMG_8439](https://hackmd.io/_uploads/S1E4nGt0a.jpg)


使用方式
```sql=
SELECT *
FROM farmers_market.vw_sales_by_day_vendow AS s
WHERE s.market_date BETWEEN '2020-04-01' AND '2020-04-30'
    AND s.vendor_id = 7 
ORDER BY market_date
```