# 【教學筆記 03】awk 指令介紹
內容：awk 指令介紹
---
[目錄](https://hackmd.io/Y7i9O4hCQu6xOJ94__PhNg)
[教學筆記 01：編碼、檔案、斷行字元、Excel、其他基礎知識介紹](https://hackmd.io/7-FajauqT62vXpxOViWzBw)
[教學筆記 02：Linux 指令介紹 ](https://hackmd.io/9D_WXaT3TsCaBrsOQKYdTw)
教學筆記 03：awk 指令介紹
[教學筆記 04：文件圖表表達方式的經驗分享](https://hackmd.io/xVM9lnFBSneA6uhIzdkYyA)
[教學筆記 05：以 C 語言開發資料庫存取程式 (ECPG)](https://hackmd.io/4jh_5A51TfieovVipQqoXA)
[推薦書籍](https://hackmd.io/t7T5FxfmT3Kih-b7PRUZrA)
[補充教材](https://hackmd.io/w-BNpl_TSuysG4_qaGtmFg)
---
awk 為 Linux 的基礎指令之一，也可說是一種程式語言，其特色為處理字串的能力相當強大。在處理資料時，時常需要針對字串進行加工，例如開發人員需要自動產生大量的工作指令、學校的管理人員在新學期時也可能需要大批產生學生的帳號、資料庫管理人員有時也需要大量產生 DDL，或是生成一些測試資料，這些都可以利用 awk 指令來加以處理。
雖然 awk 已經是個面世數十年的老工具，但在資料治理 (Data Governance) 方面，老工具亦有許多發揮的空間。例如檢查原始資料的欄位或格式是否與規格相符、可直接做檔案與檔案之間的 join、在資料尚未進入資料庫之前就協助進行各種檢查。即使在未安裝 Python 這類時常用來進行資料分析的程式語言的情況下，使用 awk 這個老工具也能很輕易做到基本的探索式資料分析 (Exploratory Data Analysis)。對於資料工程師而言，awk 是個值得推薦學習的好工具。
在進行 awk 的介紹之前，我們直接用一些簡單的範例，來看看 awk 可以做些什麼事情：
## 一、awk 的簡單範例：
```shell script
# 如果我們有一個純文字檔 (mart.txt)，內容如下：
$ cat mart.txt
1 新北投溫泉(商圈) 北投區 新北投站 以溫泉業、餐飲業為主
2 行義路溫泉美食(商圈) 北投區 石牌站 溫泉、餐飲
3 石牌捷運(商圈) 北投區 石牌站 餐飲、日常生活用品
4 士林捷運站周邊 士林區 士林站 餐飲、服飾、日用品、家飾、喜餅
5 士林觀光夜市(商圈) 士林區 劍潭站 以餐飲小吃、服飾為主
6 天母(商圈) 士林區 芝山站 餐飲、手工創意商品、百貨
# 印出第2個欄位
$ awk '{print $2}' mart.txt
新北投溫泉(商圈)
行義路溫泉美食(商圈)
石牌捷運(商圈)
士林捷運站周邊
士林觀光夜市(商圈)
天母(商圈)
# 印出第2,3個欄位
$ awk '{print $2,$3}' mart.txt
新北投溫泉(商圈) 北投區
行義路溫泉美食(商圈) 北投區
石牌捷運(商圈) 北投區
士林捷運站周邊 士林區
士林觀光夜市(商圈) 士林區
天母(商圈) 士林區
# 印出第2,3個欄位，但第3欄只印出前2個字
$ awk '{print $2,substr($3,1,2)}' mart.txt
新北投溫泉(商圈) 北投
行義路溫泉美食(商圈) 北投
石牌捷運(商圈) 北投
士林捷運站周邊 士林
士林觀光夜市(商圈) 士林
天母(商圈) 士林
# 印出第2個欄位，以及第2個欄位的長度 (1個中文/英文/符號算1個長度)
$ awk '{print $2,length($2)}' mart.txt
新北投溫泉(商圈) 9
行義路溫泉美食(商圈) 11
石牌捷運(商圈) 8
士林捷運站周邊 7
士林觀光夜市(商圈) 10
天母(商圈) 6
# 印出全部欄位($0)
$ awk '{print $0}' mart.txt
1 新北投溫泉(商圈) 北投區 新北投站 以溫泉業、餐飲業為主
2 行義路溫泉美食(商圈) 北投區 石牌站 溫泉、餐飲
3 石牌捷運(商圈) 北投區 石牌站 餐飲、日常生活用品
4 士林捷運站周邊 士林區 士林站 餐飲、服飾、日用品、家飾、喜餅
5 士林觀光夜市(商圈) 士林區 劍潭站 以餐飲小吃、服飾為主
6 天母(商圈) 士林區 芝山站 餐飲、手工創意商品、百貨
# 只印出北投區的商圈資料
$ awk '{if($3 == "北投區") print $0}' mart.txt
1 新北投溫泉(商圈) 北投區 新北投站 以溫泉業、餐飲業為主
2 行義路溫泉美食(商圈) 北投區 石牌站 溫泉、餐飲
3 石牌捷運(商圈) 北投區 石牌站 餐飲、日常生活用品
# 另一種寫法
$ awk '$3 == "北投區" {print $0}' mart.txt
1 新北投溫泉(商圈) 北投區 新北投站 以溫泉業、餐飲業為主
2 行義路溫泉美食(商圈) 北投區 石牌站 溫泉、餐飲
3 石牌捷運(商圈) 北投區 石牌站 餐飲、日常生活用品
# 只印出北投區且靠近石牌站的商圈資料
$ awk '{if($3 == "北投區" && $4 == "石牌站") print $0}' mart.txt
2 行義路溫泉美食(商圈) 北投區 石牌站 溫泉、餐飲
3 石牌捷運(商圈) 北投區 石牌站 餐飲、日常生活用品
# 如果分割字元不是空白的話：
$ cat test.txt
12,345,6789
ab,cde,fghi
ABCD,EFG,HI
$ awk -F ',' '{print $2}' test.txt # 印出第2個欄位
345
cde
EFG
$ awk -F ',' '{print $2, NF, NR}' test.txt # 印出第2個欄位、欄位數、列數
345 3 1
cde 3 2
EFG 3 3
# 如果分割字元不只一個的話：
$ cat test2.txt # 以 oo 作為分隔字元
12oo345oo6789
aboocdeoofghi
ABCDooEFGooHI
$ awk -F 'oo' '{print $2}' test2.txt
345
cde
EFG
$ cat test3.txt # 以 *| 作為分隔字元
12*|345*|6789
ab*|cde*|fghi
ABCD*|EFG*|HI
$ awk -F '\\*\\|' '{print $2}' test3.txt
345
cde
EFG
```
---
## 二、awk 的特性：
* 是 Linux/UNIX 中的字串處理指令 (跨平台)
* 是一種程式語言 (直譯式、弱型別)，具有變數、函式、條件判斷、迴圈。
* 每列驅動：每讀取一列資料，就執行一次程式碼。適合用來整批、逐筆的處理資料。
※ awk 參考網址：https://www.tutorialspoint.com/awk/index.htm
```shell script
# 每一列皆印出 hello (資料有幾筆，hello 就有幾筆)
$ awk '{print "hello"}' mart.txt
hello
hello
hello
hello
hello
hello
$ awk '{print "hello "$2}' mart.txt
hello 新北投溫泉(商圈)
hello 行義路溫泉美食(商圈)
hello 石牌捷運(商圈)
hello 士林捷運站周邊
hello 士林觀光夜市(商圈)
hello 天母(商圈)
```
---
## 三、awk 的執行方式：
1. 直接在 shell 中執行：
```shell script
$ awk '{print "hello "$2}' mart.txt
hello 新北投溫泉(商圈)
hello 行義路溫泉美食(商圈)
hello 石牌捷運(商圈)
hello 士林捷運站周邊
hello 士林觀光夜市(商圈)
hello 天母(商圈)
```
2. 寫在檔案中再執行
```shell script
$ cat hello.awk
{
print "hello "$2
}
$ awk -f hello.awk mart.txt
hello 新北投溫泉(商圈)
hello 行義路溫泉美食(商圈)
hello 石牌捷運(商圈)
hello 士林捷運站周邊
hello 士林觀光夜市(商圈)
hello 天母(商圈)
```
* awk 執行語法的兩種基本形式：
```awk [-v var=value] [-F delimiter] 'pattern { action }' datafile(s)```
```awk [-v var=value] [-F delimiter] -f scriptfile datafile(s)```
| 練習1 |
| ---- |
| 一、資料集：請先至下列網址下載資料：<br>https://data.gov.tw/dataset/120711 <br>(請下載 csv 檔，並另存檔名為「cov19.csv」)。 <br> 二、題目：請篩選出於 2022 年內，桃園市中壢區的所有資料，並去除「確定病名」、「是否為境外移入」兩個欄位。<br>三、說明：請用上述兩種 awk 方法來作答 (在 shell 中執行，以及寫在檔案執行)，而不要用 grep 方法。<br>[練習1(解答)](#練習1(解答))|
---
## 四、awk 的程式結構：
| 區塊 | 功能 | 例如 |
| ---- | ---- | ---- |
| BEGIN {...} | 前置處理 | 變數宣告、設定、輸出表頭 |
| {...} | 主要處理邏輯 | 主要功能 |
| END {...} | 後續處理 | 輸出表尾 |
※上圖參考來源：https://www.tutorialspoint.com/awk/awk_workflow.htm
```shell script
$ cat hello2.awk
BEGIN {print "hello BEGIN";}
{
print "hello "$2;
}
END {print "hello END";}
$ awk -f hello2.awk mart.txt
hello BEGIN
hello 新北投溫泉(商圈)
hello 行義路溫泉美食(商圈)
hello 石牌捷運(商圈)
hello 士林捷運站周邊
hello 士林觀光夜市(商圈)
hello 天母(商圈)
hello END
```
---
## 五、變數
### (1). 欄位變數
* $1：第 1 欄
* $2：第 2 欄
...
* $n：第 n 欄 (n 的範圍為 0 到 NF 的任何數字)
* $0：指全部欄位
* $NF：最後一欄
### (2). 自訂變數
* 由字母、數字、底線組成，且字母大小寫有差別。
* 變數名稱不可以用數字開頭。
* 變數可以包含字串或數字。
* 變數不須宣告或設定初始值，未設定初始值時，會以空字串(```""```)或 0 作為預設的初始值。awk 會根據程式的運作來決定一個變數要視為字串或數字來處理。
* 設定變數的語法為：```var = expression```
```
x = 1 # 設定變數 x 其值為 1
print x # 讀取變數 x 並列印出來
```
* 從外部輸入的自訂變數：
```shell script
$ awk -v good=不好玩的 '{print good$2}' mart.txt
不好玩的新北投溫泉(商圈)
不好玩的行義路溫泉美食(商圈)
不好玩的石牌捷運(商圈)
不好玩的士林捷運站周邊
不好玩的士林觀光夜市(商圈)
不好玩的天母(商圈)
$ awk -v very=非常 -v good=不好玩的 '{print very""good$2}' mart.txt
非常不好玩的新北投溫泉(商圈)
非常不好玩的行義路溫泉美食(商圈)
非常不好玩的石牌捷運(商圈)
非常不好玩的士林捷運站周邊
非常不好玩的士林觀光夜市(商圈)
非常不好玩的天母(商圈)
```
* 陣列變數 (關聯式陣列)：
* 陣列是一個可以儲存一組值的變數。
* 將一個值設定給陣列某個元素的語法為：```array[index] = value```
* awk中，陣列的索引(index)可以是字串或數字。
* 陣列的元素不會依特定次序儲存。
* 可以用一個 for 迴圈來讀取陣列中的所有元素：```for(item in array)```，並用```array[item]```來取得元素的值。
### (3). 內建/系統變數
所有內建/系統變數名稱都由大寫字母組成
| # | 變數 | 說明 |
| --| ---- | ---- |
| 1 | ARGC | 命令列參數的數目 |
| 2 | ARGV | 一個陣列，包含命令列的參數 |
| 3 | CONVFMT | 由數字轉換成字串時所採取的編碼方式 (預設為%.6g) |
| 4 | ENVIRON | 用來存放環境變數的陣列 |
| 5 | FILENAME | 當前的檔案名稱 |
| 6 | FNR | 如同NF，但為相對於當前的檔案 |
| 7 | FS | 欄位區隔符號 (預設為空格) |
| 8 | NF | 當前紀錄的欄位數目 |
| 9 | NR | 當前紀錄的數目 |
| 10 | OFMT | 數字的輸出編排方式 (預設為%.6g) |
| 11 | OFS | 輸出欄位的區隔符號 (預設為空格) |
| 12 | ORS | 輸出紀錄的區隔符號 (預設為newline) |
| 13 | RLENGTH | 函式 match() 所找到之子字串的長度 |
| 14 | RS | 紀錄的區隔符號 (預設為newline) |
| 15 | RSTART | 函式 match() 所找到之子字串位於字串的第1個位置 |
---
## 六、內建函式
以下僅摘錄一部分本專案之常用內建函式：
| # | 函式 | 語法 | 說明 |
| --| ---- | ---- | ---- |
| 1 | index | index(str, substr) | 回傳 substr 在 str 中出現的位置 (從 1 起算) |
| 2 | int | int(x) | 截掉小數點後面的任何數字，回傳 x 的整數值 |
| 3 | length | length(str) | 回傳字串 str 的長度。如果沒有參數，則回傳 $0 的長度 |
| 4 | printf | printf(format, expr-list) | 依據 format 的格式，並由 expr-list 變數建立字串，並列印出來 |
| 5 | split | split(str, array, sep) | 以欄位區隔符號 sep 剖析字串 str 並將結果存入陣列 array 的元素中。傳回值為元素的總數。如果沒有指定 sep，則會使用系統變數 FS |
| 6 | substr | substr(str, beg, len) | 回傳值為一子字串，也就是在字串 str 中，自位置 beg 開始，最大長度為 len 的子字串。如果沒有指定 len，則會回傳字串 str 中自位置 beg 開始至結尾的部分 |
| 7 | tolower | tolower(str) | 將 str 裡的大寫字元全部轉換成小寫，並回傳新字串 |
| 8 | toupper | toupper(str) | 將 str 裡的小寫字元全部轉換成大寫，並回傳新字串 |
| 9 | gsub | gsub(r, s, t) | 在字串 t 中，凡是與正規表達式 r 相符的地方，都會替換成 s (採用全域替換的方式)。回傳值為替換的次數。若沒有指定 t，則以 $0 為預設值 |
| 10 | sub | sub(r, s, t) | 在字串 t 中，將首次與正規表達式 r 相符的地方替換成 s。如果成功則回傳 1，否則回傳 0。若沒有指定 t，則以 $0 為預設值 |
| 11 | match | match(s, r) | 此函式會回傳與正規表達式 r 相符之子字串，於字串 s 中的開頭位置。若沒有發現與 r 相符的地方則回傳 0。此函式亦會將系統變數 RSTART 與 RLENGTH 分別設定為：相符子字串的第1個位置與其長度 |
```shell script
# printf() 範例：
$ awk 'BEGIN {x = 1024; y = sqrt(x); printf("sqrt(%f) = %f\n", x, y)}'
sqrt(1024.000000) = 32.000000
```
* print 和 printf() 的差異：
* print：列印結束時會自動斷行、輸出時的欄位分割符號預設為OFS。
* printf()：列印結束時不會自動斷行(須自行控制)，用法與 C 語言中的 printf() 很類似。<br>參考網址：https://bl831.als.lbl.gov/~gmeigs/scripting_help/printf_awk_notes.txt
```shell script
$ awk 'BEGIN {print "A","B","C"; print "D","E","F";}'
A B C
D E F
$ awk 'BEGIN {OFS = "|"; print "A","B","C"; print "D","E","F";}'
A|B|C
D|E|F
```
---
## 七、流程控制
### (1). 條件判斷：
```
# 基本形式：
if (expression)
action1
[else if (expression)
action2]
[else
action3]
```
```
# 動作若不只一個，要放在一對大括號中間
if (expression) {
action1
action2
}
```
### (2). 迴圈：
```
# while 迴圈的形式：
while (condition)
action
```
```
# do 迴圈的形式：
do
action
while (condition)
```
```
# for 迴圈的形式：
for (set_counter; test_counter; increment_counter)
action
```
### (3). 其他述句：
* break：跳出迴圈，使迴圈不再重複執行。
* continue：在到達迴圈底部之前，先停止這次的執行，然後由頂部開始下一次的執行。
* next：使 awk 讀取下一個輸入列，並回到 awk script 的頂端繼續執行。
* exit：跳出主輸入迴圈，並將控制權交給 END 規則 (如果存在的話)。若 END 規則未定義，或 exit 述句位於 END 規則中，則會中止執行。
---
## 八、應用：
### (1). 大量產生工作所需的 Script/程式碼片段
```shell script
# 範例1：產生 Trinity 原始碼備份指令
$ cat backup_list.txt
BASE_INIT
BASE
STAGE_INIT
STAGE
UT_BASE_INIT
UT_BASE
UT_STAGE_INIT
UT_STAGE
$ awk '{printf("java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c %s\n", $1);}' backup_list.txt
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c BASE_INIT
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c BASE
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c STAGE_INIT
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c STAGE
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c UT_BASE_INIT
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c UT_BASE
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c UT_STAGE_INIT
java -jar ImportExportCMD.jar -export -host xxx -port xxx -u xxx -pwd xxx -b SBD -c UT_STAGE
```
```shell script
# 範例2：產生 Trinity 作業執行指令
$ cat job_list.txt
SBD_INAT042_STAGE_G0001_INIT 20211205
SBD_INAT020_STAGE_G0001_INIT 20211205
SBD_ICMT011_STAGE_G0001_INIT 20211205
SBD_WIIT800_STAGE_G0001_INIT 20211205
SBD_WIIT820_STAGE_G0001_INIT 20211205
$ awk '{printf("sh /home/trinity/sbd/dic/BD_SH/BD_ETL_TRNTY.sh STAGE_INIT %s %s 00:00:00\n", $1, $2);}' job_list.txt
sh /home/trinity/sbd/dic/BD_SH/BD_ETL_TRNTY.sh STAGE_INIT SBD_INAT042_STAGE_G0001_INIT 20211205 00:00:00
sh /home/trinity/sbd/dic/BD_SH/BD_ETL_TRNTY.sh STAGE_INIT SBD_INAT020_STAGE_G0001_INIT 20211205 00:00:00
sh /home/trinity/sbd/dic/BD_SH/BD_ETL_TRNTY.sh STAGE_INIT SBD_ICMT011_STAGE_G0001_INIT 20211205 00:00:00
sh /home/trinity/sbd/dic/BD_SH/BD_ETL_TRNTY.sh STAGE_INIT SBD_WIIT800_STAGE_G0001_INIT 20211205 00:00:00
sh /home/trinity/sbd/dic/BD_SH/BD_ETL_TRNTY.sh STAGE_INIT SBD_WIIT820_STAGE_G0001_INIT 20211205 00:00:00
```
### (2). 控制輸出格式 (製作報表)
本範例請先至網址下載資料：https://data.gov.tw/dataset/120711
(下載 csv 檔，並另存檔名為「cov19.csv」)。
```shell script
$ cat cov19.awk
BEGIN {
print "---------- ----------";
print " 縣市 確定病例數";
print "---------- ----------";
}
{
sum[$3] += $8; # 各縣市小計
total += $8; # 總計
}
END {
for(key in sum) {
city = key;
if(key == "空值") city = "境外 ";
printf("%-7s %10d\n", city, sum[key]);
}
print "---------- ----------";
printf(" 總計 %10d\n", total);
print "---------- ----------";
}
$ tail -n+2 cov19.csv | awk -F ',' -f cov19.awk
---------- ----------
縣市 確定病例數
---------- ----------
屏東縣 48
高雄市 94
連江縣 4
嘉義市 10
宜蘭縣 99
台東縣 22
台中市 207
新竹市 43
桃園市 891
基隆市 320
彰化縣 274
嘉義縣 32
花蓮縣 68
台北市 4879
新北市 6943
境外 3223
新竹縣 112
南投縣 38
苗栗縣 549
澎湖縣 5
雲林縣 22
台南市 50
---------- ----------
總計 17933
---------- ----------
```
| 練習2 |
| ---- |
| 同上表，但改為以「年齡層」欄位來做彙總 (不須排序)。<br>[練習2(解答)](#練習2(解答)) |
### (3). 彙總計算 (Aggregation)
* count：取筆數
* 全部：```awk '{cnt++} END {print cnt}'```
* 群組：```awk '{cnt[${主鍵}]++} END {for(key in cnt) print key, cnt[key]}'```
* sum：取合計
* 全部：```awk '{sum += ${欄位}} END {print sum}'```
* 群組：```awk '{sum[${主鍵}] += ${欄位}} END {for(key in sum) print key, sum[key]}'```
* min：取最小值
* 全部：```awk '{if(NR == 1 || ${欄位} < min) min = ${欄位}} END {print min}'```
* 群組：```awk '{if(init[${主鍵}] == 0 || ${欄位} < min[${主鍵}]) min[${主鍵}] = ${欄位}; init[${主鍵}] = 1;} END {for(key in min) print key, min[key]}'```
* max：取最大值
* 全部：```awk '{if(NR == 1 || ${欄位} > max) max = ${欄位}} END {print max}'```
* 群組：```awk '{if(init[${主鍵}] == 0 || ${欄位} > max[${主鍵}]) max[${主鍵}] = ${欄位}; init[${主鍵}] = 1;} END {for(key in max) print key, max[key]}'```
```shell script
# 範例：通話紀錄的彙總 (CDR，Call Detail Record)
$ cat cdr.txt
受話號碼,通話日期,通話長度,通話費
0911111111,2022/01/01,100,8
0922222222,2022/01/02,200,16
0933333333,2022/01/03,300,24
0911111111,2022/01/04,400,32
0922222222,2022/01/05,500,40
0933333333,2022/01/06,600,48
0911111111,2022/01/07,700,56
0911111111,2022/01/08,800,64
# 全部筆數
$ tail -n+2 cdr.txt | awk -F ',' '{cnt++} END {print cnt}'
8
# 以受話號碼彙總筆數
$ tail -n+2 cdr.txt | awk -F ',' '{cnt[$1]++} END {for(key in cnt) print key, cnt[key]}'
0933333333 2
0911111111 4
0922222222 2
# 全部通話費
$ tail -n+2 cdr.txt | awk -F ',' '{sum += $4} END {print sum}'
288
# 以受話號碼彙總通話費
$ tail -n+2 cdr.txt | awk -F ',' '{sum[$1] += $4} END {for(key in sum) print key, sum[key]}'
0933333333 72
0911111111 160
0922222222 56
# 全部通話費的最小值
$ tail -n+2 cdr.txt | awk -F ',' '{if(NR == 1 || $4 < min) min = $4} END {print min}'
8
# 以受話號碼彙總通話費的最小值
$ tail -n+2 cdr.txt | awk -F ',' '{if(init[$1] == 0 || $4 < min[$1]) min[$1] = $4; init[$1] = 1;} END {for(key in min) print key, min[key]}'
0933333333 24
0911111111 8
0922222222 16
# 全部通話費的最大值
$ tail -n+2 cdr.txt | awk -F ',' '{if(NR == 1 || $4 > max) max = $4} END {print max}'
64
# 以受話號碼彙總通話費的最大值
$ tail -n+2 cdr.txt | awk -F ',' '{if(init[$1] == 0 || $4 > max[$1]) max[$1] = $4; init[$1] = 1;} END {for(key in max) print key, max[key]}'
0933333333 48
0911111111 64
0922222222 40
```
| 練習3 |
| ---- |
| 利用上述的 cdr.txt 計算：<br>一、全部平均通話長度。<br>二、以受話號碼彙總平均通話長度。<br>[練習3(解答)](#練習3(解答)) |
### (4). 參照其他資料 (reference/join)
```shell script
# 範例：印出各商圈的所屬郵遞區號
$ cat mart.txt
1 新北投溫泉(商圈) 北投區 新北投站 以溫泉業、餐飲業為主
2 行義路溫泉美食(商圈) 北投區 石牌站 溫泉、餐飲
3 石牌捷運(商圈) 北投區 石牌站 餐飲、日常生活用品
4 士林捷運站周邊 士林區 士林站 餐飲、服飾、日用品、家飾、喜餅
5 士林觀光夜市(商圈) 士林區 劍潭站 以餐飲小吃、服飾為主
6 天母(商圈) 士林區 芝山站 餐飲、手工創意商品、百貨
$ cat zip.txt
100 中正區
103 大同區
104 中山區
105 松山區
106 大安區
108 萬華區
110 信義區
111 士林區
112 北投區
114 內湖區
115 南港區
116 文山區
# 方法一：直接於 shell 執行
$ awk '{if(FILENAME == "zip.txt") zip[$2] = $1; else print $2": "zip[$3];}' zip.txt mart.txt
新北投溫泉(商圈): 112
行義路溫泉美食(商圈): 112
石牌捷運(商圈): 112
士林捷運站周邊: 111
士林觀光夜市(商圈): 111
天母(商圈): 111
# 方法二：寫為 awk script 執行
$ cat zip.awk
{
if(FILENAME == "zip.txt")
zip[$2] = $1;
else
print $2": "zip[$3];
}
$ awk -f zip.awk zip.txt mart.txt
新北投溫泉(商圈): 112
行義路溫泉美食(商圈): 112
石牌捷運(商圈): 112
士林捷運站周邊: 111
士林觀光夜市(商圈): 111
天母(商圈): 111
```
| 練習4 |
| ---- |
| 個人身分證號的第一碼英文，通常是代表第一次辦理戶籍的所在縣市。<br>若有下列兩個檔案：<br><br>$ cat id.txt # 姓名, 身分證號<br>陳ＯＯ,M111111111<br>林ＯＯ,D222222222<br>黃ＯＯ,F123456789<br>張ＯＯ,K246813579<br>李ＯＯ,A135792468<br><br>$ cat id_city.txt # 身分證號第一碼與縣市對應<br>A,臺北市<br>B,臺中市<br>C,基隆市<br>D,臺南市<br>E,高雄市<br>F,新北市<br>G,宜蘭縣<br>H,桃園市<br>I,嘉義市<br>J,新竹縣<br>K,苗栗縣<br>M,南投縣<br>N,彰化縣<br>O,新竹市<br>P,雲林縣<br>Q,嘉義縣<br>T,屏東縣<br>U,花蓮縣<br>V,臺東縣<br>W,金門縣<br>X,澎湖縣<br>Z,連江縣<br>L,臺中縣<br>R,臺南縣<br>S,高雄縣<br>Y,陽明山管理局<br><br>問題：請問如何利用 awk script 產生以下的結果？<br>陳ＯＯ M111111111 南投縣<br>林ＯＯ D222222222 臺南市<br>黃ＯＯ F123456789 新北市<br>張ＯＯ K246813579 苗栗縣<br>李ＯＯ A135792468 臺北市<br><br>[練習4(解答)](#練習4(解答)) |
### (5). 由 SDM 文件產生各表格的 PK
假設有一份SDM文件，其內容如下表：
| 資料表名稱 | 資料欄位名稱 | 資料型態 | 主鍵 | 不為空值 | 序號 |
| -------- | ---------- | ------ | ---- | ------- | --- |
| table1 | column1 | int | P | Y |1 |
| table1 | column2 | varchar(10) | P | Y |2 |
| table1 | column3 | decimal(10,2)| | |3 |
| table2 | column1 | varchar(5) | P | Y |1 |
| table2 | column2 | text | | |2 |
| table3 | column1 | char(5) | | |1 |
| table3 | column2 | date | | |2 |
由上述 SDM 中，若欲輸出每張表的 PK，例如：
```
table1: column1, column2
table2: column1
table3:
```
* 設計原則：
* 每「讀取完」同一段的「資料表名稱」再輸出 PK。
* 「讀取完」的定義：當前列的「資料表名稱」不等於前一列。
* 特殊情況：
* 表頭：須排除 (NR 為 1)
* 資料第一列：雖然滿足上述「讀取完」的定義，但尚不須輸出 (可利用 flag 變數來避免)。
* 資料最後一列：awk 讀取至最後一列就會結束，此時會來不及輸出最後一張表格的 PK (利用 END{} 來輸出)。
```shell script
# ------------------------------ #
# 範例1：輸入檔有表頭
# ------------------------------ #
$ cat detail.txt
資料表名稱 資料欄位名稱 資料型態 主鍵 不為空值 序號
table1 column1 int P Y 1
table1 column2 varchar(10) P Y 2
table1 column3 decimal(10,2) 3
table2 column1 varchar(5) P Y 1
table2 column2 text 2
table3 column1 char(5) 1
table3 column2 date 2
$ cat detail.awk
BEGIN {flag = 0; last_table = ""; pk = "";}
{
if(NR != 1) {
if($1 != last_table && flag != 0) {
print last_table": "pk;
pk = ""; # 輸出後將PK重置
}
flag = 1; # 經過資料第一列之後就開啟
if($4 == "P" && pk == "")
pk = $2;
else if($4 == "P" && pk != "")
pk = pk", "$2;
last_table = $1; # 記錄上一列的資料表名稱
}
}
END {print last_table": "pk;}
$ awk -F '\t' -f detail.awk detail.txt
table1: column1, column2
table2: column1
table3:
```
```shell script
# ------------------------------ #
# 範例2：輸入檔有表頭 (利用 next 跳過第一列)
# ------------------------------ #
# 不要用「if(NR != 1) {...}」的寫法，改成用「if(NR == 1) next;」，減少程式碼層次。
$ cat detail2.awk
BEGIN {flag = 0; last_table = ""; pk = "";}
{
if(NR == 1) next;
if($1 != last_table && flag != 0) {
print last_table": "pk;
pk = ""; # 輸出後將PK重置
}
flag = 1; # 經過資料第一列之後就開啟
if($4 == "P" && pk == "")
pk = $2;
else if($4 == "P" && pk != "")
pk = pk", "$2;
last_table = $1; # 記錄上一列的資料表名稱
}
END {print last_table": "pk;}
$ awk -F '\t' -f detail2.awk detail.txt
table1: column1, column2
table2: column1
table3:
```
```shell script
# ------------------------------ #
# 範例3：輸入檔沒有表頭
# ------------------------------ #
$ cat detail_notitle.txt
table1 column1 int P Y 1
table1 column2 varchar(10) P Y 2
table1 column3 decimal(10,2) 3
table2 column1 varchar(5) P Y 1
table2 column2 text 2
table3 column1 char(5) 1
table3 column2 date 2
$ cat detail_notitle.awk
BEGIN {flag = 0; last_table = ""; pk = "";}
{
if($1 != last_table && flag != 0) {
print last_table": "pk;
pk = ""; # 輸出後將PK重置
}
flag = 1; # 經過資料第一列之後就開啟
if($4 == "P" && pk == "")
pk = $2;
else if($4 == "P" && pk != "")
pk = pk", "$2;
last_table = $1; # 記錄上一列的資料表名稱
}
END {print last_table": "pk;}
$ awk -F '\t' -f detail_notitle.awk detail_notitle.txt
table1: column1, column2
table2: column1
table3:
```
```shell script
# ------------------------------ #
# 範例4：利用陣列變數儲存各表格的PK：
# ------------------------------ #
$ cat detail_reverse.txt
資料表名稱 資料欄位名稱 資料型態 主鍵 不為空值 序號
table3 column1 char(5) 1
table3 column2 date 2
table2 column1 varchar(5) P Y 1
table2 column2 text 2
table1 column1 int P Y 1
table1 column2 varchar(10) P Y 2
table1 column3 decimal(10,2) 3
$ cat detail_reverse.awk
{
if(NR == 1) next;
pk[$1] = pk[$1]; # 為各表格的PK做初始化 (如果不加，無PK之表格無法輸出)
if($4 == "P" && pk[$1] == "")
pk[$1] = $2;
else if($4 == "P" && pk[$1] != "")
pk[$1] = pk[$1]", "$2;
}
END {for(table in pk) print table": "pk[table];}
$ awk -F '\t' -f detail_reverse.awk detail_reverse.txt
table1: column1, column2
table2: column1
table3:
# 注意：輸入與輸出順序不相同 (須視情況使用)
```
| 練習5 |
| ---- |
| 請以上述範例2為基礎，寫出 awk script，以計算每張表格的PK數量。<br>輸出的格式為：<br>table1: 2<br>table2: 1<br>table3: 0<br><br>[練習5(解答)](#練習5(解答)) |
### (6). 由 SDM 文件產生 DDL (DDL Generator ，簡易版)
```shell script
$ cat detail.txt
資料表名稱 資料欄位名稱 資料型態 主鍵 不為空值 序號
table1 column1 int P Y 1
table1 column2 varchar(10) P Y 2
table1 column3 decimal(10,2) 3
table2 column1 varchar(5) P Y 1
table2 column2 text 2
table3 column1 char(5) 1
table3 column2 date 2
```
```shell script
$ cat ddl_generator.awk
```
```shellscript=
BEGIN {flag = 0; last_table = ""; pk = ""; col_cnt = 0;}
{
if(NR == 1) next;
if($1 != last_table && flag != 0) {
printf("CREATE TABLE %s.%s (\n", SCH, last_table);
for(i = 1; i <= col_cnt; i++) {
if(i != col_cnt) printf(" %s %s %s,\n", col_name[i], col_type[i], not_null[i]);
else printf(" %s %s %s\n", col_name[i], col_type[i], not_null[i]);
}
printf(");\n");
if(pk != "") printf("ALTER TABLE %s.%s ADD CONSTRAINT %s_PK PRIMARY KEY (%s);\n", SCH, last_table, toupper(last_table), pk);
pk = ""; # 輸出後將PK重置
col_cnt = 0; # 欄位數重置
}
flag = 1; # 經過資料第一列之後就開啟
col_cnt++; # 第幾欄
col_name[col_cnt] = $2; # 資料欄位名稱
col_type[col_cnt] = $3; # 資料型態
if($5 == "Y") not_null[col_cnt] = "NOT NULL"; # 不為空值
else not_null[col_cnt] = "";
if($4 == "P" && pk == "")
pk = $2;
else if($4 == "P" && pk != "")
pk = pk", "$2;
last_table = $1; # 記錄上一列的資料表名稱
}
END {
printf("CREATE TABLE %s.%s (\n", SCH, last_table);
for(i = 1; i <= col_cnt; i++) {
if(i != col_cnt) printf(" %s %s %s,\n", col_name[i], col_type[i], not_null[i]);
else printf(" %s %s %s\n", col_name[i], col_type[i], not_null[i]);
}
printf(");\n");
if(pk != "") printf("ALTER TABLE %s.%s ADD CONSTRAINT %s_PK PRIMARY KEY (%s);\n", SCH, last_table, toupper(last_table), pk);
}
```
```shell script
# schema 作為外部參數(SCH)輸入
$ awk -F '\t' -f ddl_generator.awk -v SCH=base detail.txt
CREATE TABLE base.table1 (
column1 int NOT NULL,
column2 varchar(10) NOT NULL,
column3 decimal(10,2)
);
ALTER TABLE base.table1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (column1, column2);
CREATE TABLE base.table2 (
column1 varchar(5) NOT NULL,
column2 text
);
ALTER TABLE base.table2 ADD CONSTRAINT TABLE2_PK PRIMARY KEY (column1);
CREATE TABLE base.table3 (
column1 char(5) ,
column2 date
);
```
| 練習6 |
| ---- |
| 請依據上述的 DDL Generator，再新增以下的功能：<br>(1). 請在每張表格的DDL最前段，加入「DROP TABLE IF EXISTS [SCH].[表格名稱];」。<br>(2). 請在每張表格的DDL最後段，加入「ALTER TABLE [SCH].[表格名稱] OWNER TO user;」。<br>[練習6(解答)](#練習6(解答)) |
```
※ 提示：產生的結果應為：
DROP TABLE IF EXISTS base.table1;
CREATE TABLE base.table1 (
column1 int NOT NULL,
column2 varchar(10) NOT NULL,
column3 decimal(10,2)
);
ALTER TABLE base.table1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (column1, column2);
ALTER TABLE base.table1 OWNER TO user;
DROP TABLE IF EXISTS base.table2;
CREATE TABLE base.table2 (
column1 varchar(5) NOT NULL,
column2 text
);
ALTER TABLE base.table2 ADD CONSTRAINT TABLE2_PK PRIMARY KEY (column1);
ALTER TABLE base.table2 OWNER TO user;
DROP TABLE IF EXISTS base.table3;
CREATE TABLE base.table3 (
column1 char(5) ,
column2 date
);
ALTER TABLE base.table3 OWNER TO user;
```
### (7). 由 SDM 文件產生測試資料 (insert SQL)
```shell script
$ cat detail.txt
資料表名稱 資料欄位名稱 資料型態 主鍵 不為空值 序號
table1 column1 int P Y 1
table1 column2 varchar(10) P Y 2
table1 column3 decimal(10,2) 3
table2 column1 varchar(5) P Y 1
table2 column2 text 2
table3 column1 char(5) 1
table3 column2 date 2
```
* 產製規則： (每張表格產製一筆測試資料)
* int：填 1。
* varchar(X)、char(X)：填 X 個 'A' (例如：varchar(3) 填 'AAA')。
* text：固定填 'ZZZZZZZZZZ'。
* decimal(X,Y)：整數填 X-Y 個 1，小數填 Y 個 1 (例如：decimal(5,2) 填 111.11)。
* decimal(X)：整數填 X 個 (例如：decimal(5) 填 11111)。
* date：填 2022-01-01。
* timestamp：填 2022-01-01 00:00:00。
```shell script
$ cat insert.awk
```
```shellscript=
BEGIN {flag = 0; last_table = ""; col_cnt = 0;}
{
if(NR == 1) next;
if($1 != last_table && flag != 0) {
printf("insert into %s.%s values (", SCH, last_table);
val = "";
for(i = 1; i <= col_cnt; i++) {
split(col_type[i], temp1, "("); # temp1[1]: 資料型態
split(temp1[2], temp2, ","); # temp2[1]: X temp2[2]: Y
if(tolower(temp1[1]) == "int")
val = val"1";
else if(tolower(temp1[1]) == "varchar" || tolower(temp1[1]) == "char") {
val = val"'";
for(j = 1; j <= int(temp2[1]); j++) val = val"A";
val = val"'";
}
else if(tolower(temp1[1]) == "text")
val = val"'ZZZZZZZZZZ'";
else if(tolower(temp1[1]) == "decimal") {
for(j = 0; j < int(temp2[1]) - int(temp2[2]); j++) val = val"1";
if(int(temp2[2]) != 0) {
val = val".";
for(j = 0; j < int(temp2[2]); j++) val = val"1";
}
}
else if(tolower(temp1[1]) == "date")
val = val"'2022-01-01'";
else if(tolower(temp1[1]) == "timestamp")
val = val"'2022-01-01 00:00:00'";
if(i == col_cnt) val = val");"; # 最後一欄
else val = val", "; # 非最後一欄
}
print val;
col_cnt = 0; # 欄位數重置
}
flag = 1; # 經過資料第一列之後就開啟
col_cnt++; # 第幾欄
col_type[col_cnt] = $3; # 資料型態
last_table = $1; # 記錄上一列的資料表名稱
}
END {
printf("insert into %s.%s values (", SCH, last_table);
val = "";
for(i = 1; i <= col_cnt; i++) {
split(col_type[i], temp1, "("); # temp1[1]: 資料型態
split(temp1[2], temp2, ","); # temp2[1]: X temp2[2]: Y
if(tolower(temp1[1]) == "int")
val = val"1";
else if(tolower(temp1[1]) == "varchar" || tolower(temp1[1]) == "char") {
val = val"'";
for(j = 1; j <= int(temp2[1]); j++) val = val"A";
val = val"'";
}
else if(tolower(temp1[1]) == "text")
val = val"'ZZZZZZZZZZ'";
else if(tolower(temp1[1]) == "decimal") {
for(j = 0; j < int(temp2[1]) - int(temp2[2]); j++) val = val"1";
if(int(temp2[2]) != 0) {
val = val".";
for(j = 0; j < int(temp2[2]); j++) val = val"1";
}
}
else if(tolower(temp1[1]) == "date")
val = val"'2022-01-01'";
else if(tolower(temp1[1]) == "timestamp")
val = val"'2022-01-01 00:00:00'";
if(i == col_cnt) val = val");"; # 最後一欄
else val = val", "; # 非最後一欄
}
print val;
}
```
```shell script
$ awk -F '\t' -f insert.awk -v SCH=base detail.txt
insert into base.table1 values (1, 'AAAAAAAAAA', 11111111.11);
insert into base.table2 values ('AAAAA', 'ZZZZZZZZZZ');
insert into base.table3 values ('AAAAA', '2022-01-01');
```
### ※ 上述 (5).(6).(7). 的共同點：
上述 (5).(6).(7). 程式碼的格式其實都很類似：
```shell script
BEGIN {
flag = 0; last_table = "";
### 段落A：相關變數初始值的宣告 (也可不寫) ###
}
{
if(NR == 1) next; # 如果沒有表頭就不用加這一列
if($1 != last_table && flag != 0) {
### 段落B：上一群組讀取完之後的輸出動作 ###
### 段落C：相關變數重置 ###
}
flag = 1; # 經過資料第一列之後就開啟
#### 段落D：讀取每列的相關變數，並進行必要處理 ###
last_table = $1; # 記錄上一列的資料表名稱
}
END {
### 段落B：上一群組讀取完之後的輸出動作 ###
}
```
### (8). 成品展示：由 SDM 文件產生 Trinity 部分原始碼
(課堂說明)
* **模式一：由開發者依據 SDM 與樣板撰寫程式：**
* **模式二：由開發者複製貼上程式碼：**
* 適用於：程式數量多、處理規則固定的開發情境。
* 優點：利用程式來產生程式，正確、速度快。
* 缺點：(1).開發者無腦複製貼上，較難學習到東西。 (2).若程式碼內容一開始就寫錯，後面的開發者會全錯。 (3).若程式數量不多，採用本方法會浪費時間成本。
### (9). 成品展示：實際的 DDL Generator 與其規則
(課堂說明)
---
## 補充：去除所有本地化的設定：```LC_ALL=C```
範例：(下載下面兩個範例檔案)
[mart_utf8.txt](https://drive.google.com/file/d/1S8QrPYvV3srNI8-BZxJgM-e5be94SyV1/view?usp=sharing)
[mart_big5.txt](https://drive.google.com/file/d/1y803bGuza2QgGa6Z2xZAmgB2QFyLEcTr/view?usp=sharing)
```shell script
# 將 git bash 終端機畫面調整為 UTF-8 編碼
$ awk '{print $2, length($2)}' mart_utf8.txt
新北投溫泉(商圈) 9
行義路溫泉美食(商圈) 11
石牌捷運(商圈) 8
士林捷運站周邊 7
士林觀光夜市(商圈) 10
天母(商圈) 6
$ LC_ALL=C awk '{print $2, length($2)}' mart_utf8.txt
新北投溫泉(商圈) 23
行義路溫泉美食(商圈) 29
石牌捷運(商圈) 20
士林捷運站周邊 21
士林觀光夜市(商圈) 26
天母(商圈) 14
# 將 git bash 終端機畫面調整為 BIG-5 編碼
$ awk '{print $2, length($2)}' mart_big5.txt
新北投溫泉(商圈) 9
行義路溫泉美食(商圈) 11
石牌捷運(商圈) 8
士林捷運站周邊 7
士林觀光夜市(商圈) 10
天母(商圈) 6
$ LC_ALL=C awk '{print $2, length($2)}' mart_big5.txt
新北投溫泉(商圈) 16
行義路溫泉美食(商圈) 20
石牌捷運(商圈) 14
士林捷運站周邊 14
士林觀光夜市(商圈) 18
天母(商圈) 10
```
---
## 作業五：
* **作業檢討時間：預定於 2022/02/18 (五)。**
利用 BASE SDM 文件，以 awk script 完成下列工作：
(非財稅案，但想要交作業的人也可以嘗試使用自己專案的SDM文件來開發 DDL generator)
#### 一、BASE 表的 DDL generator：(輸出：BASE 表 DDL，且每張表格各自輸出一個 sql 檔)
1. 輸入頁籤：
* 總表 (另存為純文字檔(以 tab 分隔)，檔名為： summary.txt)
* base detail表 (另存為純文字檔(以 tab 分隔)，檔名為：base_detail.txt)
2. 執行方式：
```awk -F '\t' -v SCH=base -f homework5-1.awk summary.txt base_detail.txt```
3. 從SDM匯出頁籤時的注意事項：
* 表頭、所有欄位、所有紀錄一律全部匯出，不可篩選之後才匯出。
* 為避免SDM中有斷行字元影響處理，匯出時請先刪除所有斷行字元 (整頁複製到新檔案(純文字) > 按 ctrl-H > 尋找目標按「ctrl-J」> 全部取代 > 另存為以 tab 分隔之純文字檔)。
4. 不須處理的部分：
* 總表與detail表的表頭都不須處理。
* 總表的「Table追蹤註記」若為「X」，則該表格不產製。
* detail表的「Column追蹤註記」($19)若為「X」，則該欄位不產製。
5. DDL的內容必須包含下列段落：
* DROP TABLE IF EXISTS ...
* CREATE TABLE ...
* 資料儲存方式
* 總表的「資料儲存方式」若為「Append」，印出「WITH (APPENDONLY=true, COMPRESSLEVEL=1, COMPRESSTYPE=quicklz, OIDS=FALSE)」
* 總表的「資料儲存方式」若為「AO/CO」，印出「WITH (APPENDONLY=true, ORIENTATION=column, COMPRESSLEVEL=1, COMPRESSTYPE=quicklz, OIDS=FALSE)」
* TABLESPACE sbd
* 分佈鍵
* 若有「分佈鍵」($5)為「Y」的欄位，印出「DISTRIBUTED BY (...)」
* 若有「分佈鍵」($5)為「R」的欄位，印出「DISTRIBUTED RANDOMLY」
* ALTER TABLE ... OWNER TO ap_sbd;
* PK/UK
* 若該表有PK，則印出：ALTER TABLE ... ADD CONSTRAINT XXX_PK PRIMARY KEY (...);
* 若該表有UK，則印出：ALTER TABLE ... ADD CONSTRAINT XXX_UK UNIQUE (...);
**※ 補充：**
* **註解、分割鍵不用寫**
* 若有題意不清的地方，隨時可以詢問，或是參考正式的 DDL Generator。
* 參考用的正式版 DDL：
* sbd_bamt001.sql (分佈鍵為 Y) *<連結已移除>*
* sbd_blqt005.sql (分佈鍵為 R) *<連結已移除>*
* sbd_bddt003.sql (資料儲存方式為 Append) *<連結已移除>*
* sbd_jhqt302_dl.sql (資料儲存方式為 AO/CO) *<連結已移除>*
* 參考用的正式版 DDL Generator：
* [ddlgen_init.awk](https://drive.google.com/file/d/1qBhAu7sXpOV44dRba0ZSMIAyavd4I7PA/view?usp=sharing) (for 初始)
* [ddlgen_base.awk](https://drive.google.com/file/d/1kCc3ErZAbrTNOCQV0QjbiqfHFmUjvvK8/view?usp=sharing) (for 異動、超市)
#### 二、STAGE 表的測試資料：(輸出：STAGE 表 insert SQL)
**產製規則： (每張表格產製 10 筆測試資料)**
1. 輸入頁籤：
* stage detail表 (另存為純文字檔(以 tab 分隔)，檔名為：stage_detail.txt)
2. 執行方式：
```awk -F '\t' -v SCH=stage -f homework5-2.awk stage_detail.txt```
3. 從SDM匯出頁籤時的注意事項：
* 表頭、所有欄位、所有紀錄一律全部匯出，不可篩選之後才匯出。
* 為避免SDM中有斷行字元影響處理，匯出時請先刪除所有斷行字元 (整頁複製到新檔案(純文字) > 按 ctrl-H > 尋找目標按「ctrl-J」> 全部取代 > 另存為以 tab 分隔之純文字檔)。
4. 不須處理的部分：
* 總表與detail表的表頭都不須處理。
* 總表的「Table追蹤註記」若為「X」，則該表格不產製。
* detail表的「Column追蹤註記」($19)若為「X」，則該欄位不產製。
5. 產製規則：
* 第 1~9 筆：
* int：填 1 ~ 9。
* varchar(X)、char(X)：填 X 個 'A' ~ 'I' (例如：varchar(3) 填 'AAA', 'BBB', ..., 'III')。
* text：固定填 ‘ZZZZZZZZZZ’。
* decimal(X,Y)：整數填 X-Y 個 1 ~ 9，小數填 Y 個 1 ~ 9 (例如：decimal(5,2) 填 111.11, 222.22, ..., 999.99)。
* decimal(X)：整數填 X 個 (例如：decimal(5) 填 11111, 22222, ..., 99999)。
* date：填 2021-01-01, 2021-02-01, ..., 2021-09-01。
* timestamp：填 2021-01-01 00:00:00, 2021-02-01 00:00:00, ..., 2021-09-01 00:00:00。
* 第 10 筆：
* 若該欄位允許null：無論任何資料類型都填 null。
* 若該欄位不允許null：
* int：填 0。
* varchar(X)、char(X)：填 X 個 'J' (例如：varchar(3) 填 'JJJ')。
* text：固定填 ‘ZZZZZZZZZZ’。
* decimal(X,Y)：填 0。
* decimal(X)：填 0。
* date：填 2021-10-01。
* timestamp：填 2021-10-01 00:00:00。
---
## 練習解答：
### 練習1(解答)
題目：請篩選出於 2022 年內，桃園市中壢區的所有資料，並去除「確定病名」、「是否為境外移入」兩個欄位。
說明：請用上述兩種 awk 方法來作答 (在 shell 中執行，以及寫在檔案執行)，而不要用 grep 方法。
```shell script
# 1.在 shell 中執行
$ awk -F ',' '{if(substr($2,1,4) == 2022 && $3 == "桃園市" && $4 == "中壢區") print $2,$3,$4,$5,$7,$8}' cov19.csv
# 2.寫在檔案執行
$ cat practice1.awk
{
if(substr($2,1,4) == 2022 && $3 == "桃園市" && $4 == "中壢區")
print $2,$3,$4,$5,$7,$8;
}
$ awk -F ',' -f practice1.awk cov19.csv
```
### 練習2(解答)
同上表，但改為以「年齡層」欄位來做彙總 (不須排序)。
```shell script
$ cat practice2.awk
BEGIN {
print "---------- ----------";
print " 年齡層 確定病例數";
print "---------- ----------";
}
{
sum[$7] += $8; # 年齡層小計
total += $8; # 總計
}
END {
for(key in sum) {
printf("%-10s %10d\n", key, sum[key]);
}
print "---------- ----------";
printf(" 總計 %10d\n", total);
print "---------- ----------";
}
$ tail -n+2 cov19.csv | awk -F ',' -f practice2.awk
---------- ----------
年齡層 確定病例數
---------- ----------
4 67
15-19 508
50-54 1406
70+ 2097
5-9 286
65-69 1541
35-39 1544
20-24 1132
40-44 1498
55-59 1650
10-14 281
25-29 1590
0 66
1 75
2 51
60-64 1600
3 65
30-34 1603
45-49 1304
---------- ----------
總計 18364
---------- ----------
```
### 練習3(解答)
利用上述的 cdr.txt 計算：
一、全部平均通話長度。
二、以受話號碼彙總平均通話長度。
```shell script
# 全部平均通話長度
$ tail -n+2 cdr.txt | awk -F ',' '{cnt++; sum += $3;} END {print sum/cnt}'
450
# 以受話號碼彙總平均通話長度
$ tail -n+2 cdr.txt | awk -F ',' '{cnt[$1]++; sum[$1] += $3;} END {for(key in sum) print key, sum[key]/cnt[key]}'
0933333333 450
0911111111 500
0922222222 350
```
### 練習4(解答)
請問如何利用 awk script 產生以下的結果？
陳ＯＯ M111111111 南投縣
林ＯＯ D222222222 臺南市
黃ＯＯ F123456789 新北市
張ＯＯ K246813579 苗栗縣
李ＯＯ A135792468 臺北市
```shell script
$ awk -F ',' '{if(FILENAME == "id_city.txt") city[$1] = $2; else print $1, $2, city[substr($2,1,1)]}' id_city.txt id.txt
陳ＯＯ M111111111 南投縣
林ＯＯ D222222222 臺南市
黃ＯＯ F123456789 新北市
張ＯＯ K246813579 苗栗縣
李ＯＯ A135792468 臺北市
```
※ 補充：如果兩個檔案改為不同的分隔字元應如何處理？
```shell script
$ cat id2.txt # 將原來 id.txt 的分隔字元改為空格
陳ＯＯ M111111111
林ＯＯ D222222222
黃ＯＯ F123456789
張ＯＯ K246813579
李ＯＯ A135792468
# 利用 -F 指定分隔字元為空格或逗號
$ awk -F '[ ,]' '{if(FILENAME == "id_city.txt") city[$1] = $2; else print $1, $2, city[substr($2,1,1)]}' id_city.txt id2.txt
陳ＯＯ M111111111 南投縣
林ＯＯ D222222222 臺南市
黃ＯＯ F123456789 新北市
張ＯＯ K246813579 苗栗縣
李ＯＯ A135792468 臺北市
```
### 練習5(解答)
請以上述範例2為基礎，寫出 awk script，以計算每張表格的PK數量。
輸出的格式為：
table1: 2
table2: 1
table3: 0
```shell script
$ cat practice5.awk
BEGIN {flag = 0; last_table = ""; pk_cnt = 0;}
{
if(NR == 1) next;
if($1 != last_table && flag != 0) {
print last_table": "pk_cnt;
pk_cnt = 0; # 重置
}
flag = 1; # 經過資料第一列之後就開啟
if($4 == "P") pk_cnt++;
last_table = $1; # 記錄上一列的資料表名稱
}
END {print last_table": "pk_cnt;}
$ awk -F '\t' -f practice5.awk detail.txt
table1: 2
table2: 1
table3: 0
```
### 練習6(解答)
請依據上述的 DDL Generator，再新增以下的功能：
(1). 請在每張表格的DDL最前段，加入「DROP TABLE IF EXISTS [SCH].[表格名稱];」。
(2). 請在每張表格的DDL最後段，加入「ALTER TABLE [SCH].[表格名稱] OWNER TO user;」。
```shell script
$ cat practice6.awk
BEGIN {flag = 0; last_table = ""; pk = ""; col_cnt = 0;}
{
if(NR == 1) next;
if($1 != last_table && flag != 0) {
printf("DROP TABLE IF EXISTS %s.%s;\n", SCH, last_table);
printf("CREATE TABLE %s.%s (\n", SCH, last_table);
for(i = 1; i <= col_cnt; i++) {
if(i != col_cnt) printf(" %s %s %s,\n", col_name[i], col_type[i], not_null[i]);
else printf(" %s %s %s\n", col_name[i], col_type[i], not_null[i]);
}
printf(");\n");
if(pk != "") printf("ALTER TABLE %s.%s ADD CONSTRAINT %s_PK PRIMARY KEY (%s);\n", SCH, last_table, toupper(last_table), pk);
printf("ALTER TABLE %s.%s OWNER TO user;\n", SCH, last_table);
pk = ""; # 輸出後將PK重置
col_cnt = 0; # 欄位數重置
}
flag = 1; # 經過資料第一列之後就開啟
col_cnt++; # 第幾欄
col_name[col_cnt] = $2; # 資料欄位名稱
col_type[col_cnt] = $3; # 資料型態
if($5 == "Y") not_null[col_cnt] = "NOT NULL"; # 不為空值
else not_null[col_cnt] = "";
if($4 == "P" && pk == "")
pk = $2;
else if($4 == "P" && pk != "")
pk = pk", "$2;
last_table = $1; # 記錄上一列的資料表名稱
}
END {
printf("DROP TABLE IF EXISTS %s.%s;\n", SCH, last_table);
printf("CREATE TABLE %s.%s (\n", SCH, last_table);
for(i = 1; i <= col_cnt; i++) {
if(i != col_cnt) printf(" %s %s %s,\n", col_name[i], col_type[i], not_null[i]);
else printf(" %s %s %s\n", col_name[i], col_type[i], not_null[i]);
}
printf(");\n");
if(pk != "") printf("ALTER TABLE %s.%s ADD CONSTRAINT %s_PK PRIMARY KEY (%s);\n", SCH, last_table, toupper(last_table), pk);
printf("ALTER TABLE %s.%s OWNER TO user;\n", SCH, last_table);
}
$ awk -F '\t' -f practice6.awk -v SCH=base detail.txt
DROP TABLE IF EXISTS base.table1;
CREATE TABLE base.table1 (
column1 int NOT NULL,
column2 varchar(10) NOT NULL,
column3 decimal(10,2)
);
ALTER TABLE base.table1 ADD CONSTRAINT TABLE1_PK PRIMARY KEY (column1, column2);
ALTER TABLE base.table1 OWNER TO user;
DROP TABLE IF EXISTS base.table2;
CREATE TABLE base.table2 (
column1 varchar(5) NOT NULL,
column2 text
);
ALTER TABLE base.table2 ADD CONSTRAINT TABLE2_PK PRIMARY KEY (column1);
ALTER TABLE base.table2 OWNER TO user;
DROP TABLE IF EXISTS base.table3;
CREATE TABLE base.table3 (
column1 char(5) ,
column2 date
);
ALTER TABLE base.table3 OWNER TO user;
```
---
## 補充：
### 1. 日期相關函式
```shell script
# 目前時間 (整數格式)
$ awk 'BEGIN{print systime()}'
1644378045
# 特定時間 (整數格式)
$ awk 'BEGIN{print mktime("2022 01 01 00 00 00")}'
1640966400
# 目前時間 (日期時間格式)
$ awk 'BEGIN{print strftime("%Y/%m/%d %H:%M:%S", systime())}'
2022/02/09 11:45:33
# 特定時間 (日期時間格式)
$ awk 'BEGIN{print strftime("%Y/%m/%d %H:%M:%S", mktime("2022 01 01 00 00 00"))}'
2022/01/01 00:00:00
# 取得目前年份
$ awk 'BEGIN{print strftime("%Y", systime())}'
2022
# 取得去年年份
$ awk 'BEGIN{print strftime("%Y", systime())-1}'
2021
# 取得目前時間 (整數 --> 日期時間 --> 整數格式)
$ awk 'BEGIN{print mktime(strftime("%Y %m %d %H %M %S", systime()))}'
1644379037
# 取得目前年份1月1日 (整數格式)
$ awk 'BEGIN{print mktime(strftime("%Y", systime())" 01 01 00 00 00")}'
1640966400
# 取得去年年份1月1日 (整數格式)
$ awk 'BEGIN{print mktime(strftime("%Y", systime())-1" 01 01 00 00 00")}'
1609430400
# 給定固定起始日期(2022/02/01)與長度(31日)，產生範圍內之萬年曆。
$ awk 'BEGIN{for(i=0;i<31;i++) print strftime("%Y%m%d", mktime("2022 02 01 00 00 00") + i * 24 * 60 * 60)}'
# 給定固定起始日期(目前年份1月1日)與長度(10日)，產生範圍內之萬年曆。
$ awk 'BEGIN {for(i=0;i<10;i++) print strftime("%Y%m%d", mktime(strftime("%Y", systime())" 01 01 00 00 00") + i * 24 * 60 * 60)}'
# 給定固定起始日期(目前年份1月1日)與結束日期(目前年份1月31日)，產生範圍內之萬年曆。
$ awk 'BEGIN {for(i=0;mktime(strftime("%Y", systime())" 01 01 00 00 00") + i * 24 * 60 * 60 <= mktime(strftime("%Y", systime())" 01 31 00 00 00");i++) print strftime("%Y%m%d", mktime(strftime("%Y", systime())" 01 01 00 00 00") + i * 24 * 60 * 60)}'
# 給定固定起始日期(去年年份1月1日)與結束日期(目前年份12月31日)，產生範圍內之萬年曆。
$ awk 'BEGIN {for(i=0;mktime(strftime("%Y", systime())-1" 01 01 00 00 00") + i * 24 * 60 * 60 <= mktime(strftime("%Y", systime())" 12 31 00 00 00");i++) print strftime("%Y%m%d", mktime(strftime("%Y", systime())-1" 01 01 00 00 00") + i * 24 * 60 * 60)}'
```
### 2. getline 函式
參考網址：https://www.itread01.com/p/200141.html
範例：**Trinity Decoder**
[trinity_decoder.awk](https://drive.google.com/file/d/1Evq8Mg2mgTut995D-qU4E8ukf_YyOIUc/view?usp=sharing)
[trinity_decoder.c](https://drive.google.com/file/d/1pnPQldlc1hSxqCBZAccjjSeFbFXuTOMM/view?usp=sharing)
Trinity原始碼範例 (密文)：[09-SBD_BASE_INIT_SBD_BAMT001_G0001_INIT.xdl](https://drive.google.com/file/d/1mOTU-231o7hjphm-xWMzGbkQSFisRjKz/view?usp=sharing)
Trinity原始碼範例 (明文)：[09-SBD_BASE_INIT_SBD_BAMT001_G0001_INIT.xdl.xml](https://drive.google.com/file/d/1jRDGXnFsPfrFDxm6t-5eW-AT04VRwlWq/view?usp=sharing)
### 3. graphviz 補充說明
**問題：如何找出彙總表作業相依性總圖之中，每一層的作業清單與數量？**
(2022/5/31 補充)
1. 使用指令介紹：
graphviz 的 dot 指令 (```dot -Tdot```) 可以協助顯示所有物件的詳細屬性 (例如：寬度、高度、位置)。下文利用位置屬性 (pos) 來盤點哪些作業節點位於哪一層。
參考網址：https://graphviz.org/docs/outputs/canon/
2. 假設有一個彙總表作業相依性總圖原始碼 test.gv，內容如下：
```
$ cat test.gv
digraph test {
node [shape=box, style=filled, color=skyblue];
"[XXX] (0001) sts\nSBD_TEST_G0001";
"[XXX] (0002) sts\nSBD_TEST_G0002";
"[XXX] (0003) sts\nSBD_TEST_G0003";
"[XXX] (0004) sts\nSBD_TEST_G0004";
"[XXX] (0005) sts\nSBD_TEST_G0005";
"[XXX] (0006) sts\nSBD_TEST_G0006";
node [shape=box, style=none, color=black];
"[XXX] (0001) sts\nSBD_TEST_G0001" -> "[XXX] (0004) sts\nSBD_TEST_G0004";
"[XXX] (0002) sts\nSBD_TEST_G0002" -> "[XXX] (0004) sts\nSBD_TEST_G0004";
"[XXX] (0002) sts\nSBD_TEST_G0002" -> "[XXX] (0005) sts\nSBD_TEST_G0005";
"[XXX] (0003) sts\nSBD_TEST_G0003" -> "[XXX] (0005) sts\nSBD_TEST_G0005";
"[XXX] (0004) sts\nSBD_TEST_G0004" -> "[XXX] (0006) sts\nSBD_TEST_G0006";
"[XXX] (0005) sts\nSBD_TEST_G0005" -> "[XXX] (0006) sts\nSBD_TEST_G0006";
}
```
其流程圖如下：
```graphviz
digraph test {
node [shape=box, style=filled, color=skyblue];
"[XXX] (0001) sts\nSBD_TEST_G0001";
"[XXX] (0002) sts\nSBD_TEST_G0002";
"[XXX] (0003) sts\nSBD_TEST_G0003";
"[XXX] (0004) sts\nSBD_TEST_G0004";
"[XXX] (0005) sts\nSBD_TEST_G0005";
"[XXX] (0006) sts\nSBD_TEST_G0006";
node [shape=box, style=none, color=black];
"[XXX] (0001) sts\nSBD_TEST_G0001" -> "[XXX] (0004) sts\nSBD_TEST_G0004";
"[XXX] (0002) sts\nSBD_TEST_G0002" -> "[XXX] (0004) sts\nSBD_TEST_G0004";
"[XXX] (0002) sts\nSBD_TEST_G0002" -> "[XXX] (0005) sts\nSBD_TEST_G0005";
"[XXX] (0003) sts\nSBD_TEST_G0003" -> "[XXX] (0005) sts\nSBD_TEST_G0005";
"[XXX] (0004) sts\nSBD_TEST_G0004" -> "[XXX] (0006) sts\nSBD_TEST_G0006";
"[XXX] (0005) sts\nSBD_TEST_G0005" -> "[XXX] (0006) sts\nSBD_TEST_G0006";
}
```
執行指令```dot -Tdot test.gv``` 之後就會顯示：
網址：https://i.imgur.com/Z4xGync.png
3. 詳細說明：
(1). 上圖中，每個節點的 **pos 屬性的第 2 個數字就是我們需要的資訊，代表該節點的 Y 軸座標** (第 1 個數字是該節點的 X 軸座標，但目前不需要)。
(2). Y 軸座標數字越大，代表在流程圖中的位置越高。所以彙總表作業第一層的數字最大，第二層其次...最後一層的數字最小。
(3). 同一層的 Y 軸座標數字會相同，因為都在相同的水平位置。
(4). 根據以上的性質，可以找出彙總表作業總圖每一層的作業清單，指令如下：
```dot -Tdot test.gv | grep -P -B 2 --no-group-separator "^\t\tpos" | grep -Pv "^\t\theight" | grep -Pv "^\t\twidth" | awk -F '"' '{if(NR%2!=0) printf("\"%s\"", $2); else{split($2, x, ","); printf("%10s\n", x[2]);}}' | sort -nr -k4```
輸出結果：
網址：https://i.imgur.com/LY2QaQb.png
(5). 當然也可以用來盤點每一層的作業數量：
```dot -Tdot test.gv | grep -P -B 2 --no-group-separator "^\t\tpos" | grep -Pv "^\t\theight" | grep -Pv "^\t\twidth" | awk -F '"' '{if(NR%2==0) {split($2, x, ","); printf("%s\n", x[2]);}}' | sort -nr | uniq -c```
輸出結果：
網址：https://i.imgur.com/Ix52S2P.png
(6). 補充：以上的方法，無法區分出第一層之中哪些作業有無相依性，但其實應該沒有關係，因為不管有無相依性，只要在第一層都可以一起跑。
---
Sign in with Wallet
Connect another wallet