# pig
# 20445 -- 替代役役男訓練人數統計表
- HDFS 完整路徑 `/dataset/20445/20445.csv`
```
欄位名稱
年度 year:int
梯次 phase:chararray
替代役役男訓練人數 trainees:int
原始資料集內容
year phase trainees
100,91,1079
100,92,969
100,92_2,122
```
- 題目 1-1. 哪一年的總替代役人數最多？
- 輸出格式: 年分, 人數
- 答案範例: 107, 87
>> data = load '/dataset/20445/20445.csv' USING PigStorage (',') AS (year:int,phase:chararray,trainees:int);
>> a1 = GROUP data BY year;
>> a2 = FOREACH a1 GENERATE group, SUM(data.trainees);
>> a3 = ORDER a2 BY $1 DESC;
>> a4 = LIMIT a3 1;
>> dump a4;
```
(103,37210)
```
- 題目 1-2. 哪一年的梯次最多？
- 輸出格式: 年分, 數量
- 答案範例: 107, 87
>> data = load '/dataset/20445/20445.csv' USING PigStorage (',') AS (year:int,phase:chararray,trainees:int);
>> a1 = GROUP data BY year;
>> a2 = FOREACH a1 GENERATE group, COUNT(data);
>> a3 = ORDER a2 BY $1 DESC;
>> a4 = LIMIT a3 1;
>> dump a4;
```
(104,23)
```
---
# 5958 -- 各縣(市)警察(分)局暨所屬分駐(派出)所地址資料
- HDFS 完整路徑 `/dataset/5958/5958.csv`
```
欄位名稱
名稱 name:chararray
郵遞區號 zip_code:chararray
地址 address:chararray
原始資料集內容
name zip_code address
臺北市政府警察局,100,臺北市中正區延平南路96號
中山分局,104,臺北市中山區中山北路2段1號
中山一派出所,104,臺北市中山區中山北路1段110號
```
- 題目 2-1. 最多派出所的郵遞區號是幾號？
- 輸出格式: 郵遞區號, 數量
- 答案範例: 100, 87
>> data = load '/dataset/5958/5958.csv' USING PigStorage (',') AS (name:chararray,code:chararray,address:chararray);
>> a1 = FILTER data BY name MATCHES '.*派出所';
>> a2 = GROUP a1 BY zip_code;
>> a3 = FOREACH a2 GENERATE group, COUNT($1);
>> a4 = ORDER a3 BY $1 DESC;
>> a5 = LIMIT a4 1;
>> dump a5;
```
(546,21)
```
- 題目 2-2. 哪個縣市有最多派出所？
>> data = load '/dataset/5958/5958.csv' USING PigStorage (',') AS (name:chararray,code:chararray,address:chararray);
>> a0 = FILTER data BY name MATCHES '.*派出所';
>> a1 = GROUP a0 BY SUBSTRING($2,0,3);
>> a2 = FOREACH a1 GENERATE group, COUNT($1);
>> a3 = ORDER a2 BY $1 DESC;
>> a4 = LIMIT a3 1;
>> dump a4;
```
(新北市,145)
```
---
# 6086 -- 107 年幼兒園名錄
- HDFS 完整路徑 `/dataset/6086/6086.csv`
```
欄位名稱
代碼 code:chararray
學校名稱 school:chararray
縣市名稱 city:chararray
地址 address:chararray
電話 phone:chararray
原始資料集內容
code school city address phone
011K02,新北市私立溫特爾幼兒園,新北市,新北市三峽區龍埔里5鄰三樹路336號1、2、3樓,(02)26718181
011K03,新北市私立新榮富新莊幼兒園,新北市,新北市新莊區中信里13鄰中和街204巷1、3號，218巷2、6號，中信里12鄰中和街1、3、5、7、11號,(02)29922174
011K04,新北市私立崇儒幼兒園,新北市,新北市中和區安樂里16鄰宜安路56之1號1樓,(02)29435789
```
- 題目 3-1. 幼兒園數量最少的是哪個縣市?
- 輸出格式: 縣市, 數量
- 答案範例: 臺北市, 87
> data = load '/dataset/6086/6086.csv' USING PigStorage (',') AS (code:chararray,school:chararray,city:chararray,address:chararray,phone:chararray);
> a1 = GROUP data BY city;
> a2 = FOREACH a1 GENERATE group, COUNT(data);
> a3 = ORDER a2 BY $1 ASC;
> a4 = LIMIT a3 1;
> dump a4;
```
(連江縣,5)
```
- 題目 3-2. 臺北市與新北市的幼兒園各有多少間? 由數量多到少排序。
- 輸出格式: 縣市, 數量
- 答案範例: 臺北市, 87
> data = load '/dataset/6086/6086.csv' USING PigStorage (',') AS (code:chararray,school:chararray,city:chararray,address:chararray,phone:chararray);
> a0 = FOREACH data GENERATE city;
> a1 = FILTER a0 BY $0 MATCHES '臺北市.*' OR $0 MATCHES '新北市.*';
> a2 = FOREACH a1 GENERATE $0, SUBSTRING($0,0,3);
> a3 = GROUP a2 BY $1;
> a4 = FOREACH a3 GENERATE COUNT($1),$0;
> dump a4
```
(新北市,1134)
(臺北市,689)
```
---
# 6087 -- 107年國民小學名錄
- HDFS 完整路徑 `/dataset/6087/6087.csv`
```
欄位名稱
代碼 code:chararray
學校名稱 school:chararray
縣市名稱 city:chararray
地址 address:chararray
電話 phone:chararray
網址 url:chararray
原始資料集內容
code school city address phone url
011601,私立育才國小,新北市,新北市永和區福和路125巷20號,(02)29214630,http://www.ytes.ntpc.edu.tw
011602,私立聖心國小,新北市,新北市八里區龍米路一段261號,(02)26182330,http://lshes.com/school/
011603,私立及人國小,新北市,新北市永和區文化路172號,(02)29212145,http://www.cjps.ntpc.edu.tw
```
- 題目 4-1. 新北市有多少間私立國小？
- 輸出格式: 數量
- 答案範例: 87
> data = LOAD '/dataset/6087/6087.csv' USING PigStorage(',') AS(code:chararray, school:chararray, city:chararray, address:chararray, phone:chararray, url:chararray
);
> a0 = FILTER data BY $1 MATCHES '私立.*' AND $2 MATCHES '新北市.*';
> a1 = FOREACH a0 GENERATE city;
> a2 = FOREACH a1 GENERATE $0, SUBSTRING($0,0,3);
> a3 = GROUP a2 BY $1;
> a4 = FOREACH a3 GENERATE $0, COUNT($1);
> dump a4
```
(新北市,5)
```
---
# 999003 -- 107年6月行政區分齡兒童及少年性別人口統計_縣市
- HDFS 完整路徑 `/dataset/999003/999003.csv`
```
欄位名稱
縣市代碼 country_id:int
縣市名稱 country:chararray
0-5歲兒童人口數 a0a5_cnt:int
6-11歲兒童人口數 a6a11_cnt:int
12-17歲少年人口數 a12a17_cnt:int
原始資料集內容
country_id country a0a5_cnt a6a11_cnt a12a17_cnt
10002,宜蘭縣,20907,21718,27474
10015,花蓮縣,15380,15338,19695
09020,金門縣,6373,4437,5451
```
- 題目 5-1. 新北市的0~17歲兒童總數有多少個?
- 輸出格式: 數量
- 答案範例: 87
> data = LOAD '/dataset/999003/999003.csv' USING PigStorage(',') AS(country_id:int, country:chararray, a0a5_cnt:int, a6a11_cnt:int, a12a17_cnt:int);
> a1 = FILTER data BY country == '新北市';
> a2 = FOREACH a1 GENERATE $1,$2+$3+$4;
> dump a2
```
(新北市,620357)
```
---
# 12197_A1 -- A1 類交通事故資料
- HDFS 完整路徑 `/dataset/12197_A1/12197_A1.csv`
```
欄位名稱
發生時間 time:chararray
死亡人數 dead:chararray
受傷人數 injured:chararray
車種 vehicle:chararray
原始資料集內容
ctime dead injured vehicle
106年01月01日 02時35分00秒,死亡1,受傷0,普通重型-機車
106年01月01日 02時43分00秒,死亡1,受傷0,普通重型-機車
106年01月01日 03時35分00秒,死亡2,受傷0,普通重型-機車
```
- 題目 6-1. 造成事故最多的車種是哪種車? (造成死亡或受傷皆算事故)
- 輸出格式: 車種, 次數
- 答案範例: 普通重型-機車, 87
> data = LOAD '/dataset/12197_A1/12197_A1.csv' USING PigStorage(',') AS(time:chararray, dead:chararray, injured:chararray, vehicle:chararray);
a1 = FOREACH data GENERATE vehicle, (INT) SUBSTRING($1,2,3), (INT) SUBSTRING($2,2,3);
> a2 = FOREACH a1 GENERATE $0,$1+$2;
> a3 = GROUP a2 BY $0;
> a4 = FOREACH a3 GENERATE $0,COUNT($1.$1);
> a5 = ORDER a4 BY $1 DESC;
> a6 = LIMIT a5 1;
> dump a6
```
(普通重型-機車,513)
```
- 題目 6-2. 發生事故最多的是哪年哪月哪日? (造成死亡或受傷皆算事故)
- 輸出格式: 日期, 次數
- 答案範例: 106年01月01日, 87
> data = LOAD '/dataset/12197_A1/12197_A1.csv' USING PigStorage(',') AS(time:chararray, dead:chararray, injured:chararray, vehicle:chararray);
a1 = FOREACH data GENERATE vehicle, (INT) SUBSTRING($1,2,3), (INT) SUBSTRING($2,2,3);
> a1 = FOREACH data GENERATE $3, SUBSTRING($0,0,10);
> b1 = GROUP a1 BY $1;
> c1 = FOREACH b1 GENERATE $0,COUNT($1.$1);
> 1 = ORDER c1 BY $1 DESC;
> dump d1
```
(106年08月13日,10)
```
- 題目 6-3. 造成死亡總數最高的是哪個車種?
- 輸出格式: 車種, 次數
- 答案範例: 普通重型-機車, 87
> data = LOAD '/dataset/12197_A1/12197_A1.csv' USING PigStorage(',') AS(time:chararray, dead:chararray, injured:chararray, vehicle:chararray);
a1 = FOREACH data GENERATE vehicle, (INT) SUBSTRING($1,2,3), (INT) SUBSTRING($2,2,3);
> a1 = FOREACH data GENERATE $3,(int)SUBSTRING($1,2,3);
> b1 = GROUP a1 BY $0;
> c1 = FOREACH b1 GENERATE $0,SUM($1.$1);
> d1 = ORDER c1 BY $1 DESC;
> dump d1
```
(普通重型-機車,517)
```
---
# 12197_A2 – A2 類交通事故資料
- HDFS 完整路徑 `/dataset/12197_A2/12197_A2.csv`
```
欄位名稱
發生時間 time:chararray
死亡人數 dead:chararray
受傷人數 injured:chararray
車種 vehicle:chararray
原始資料集內容
ctime dead injured vehicle
106年01月01日 00時00分00秒,死亡0,受傷1,自用-小客車
106年01月01日 00時00分00秒,死亡0,受傷2,自用-小客車
106年01月01日 00時03分00秒,死亡0,受傷1,自用-小客車
```
- 題目 7-1. 平均造成受傷數最高的是哪個車種? (造成受傷人數總數/事故總數) 平均一樣時依車種字母大到小排序。
- 輸出格式: 車種, 次數
- 答案範例: 普通重型-機車, 87
> data = load '/dataset/12197_A2/12197_A2.csv' USING PigStorage (',') AS (time:chararray,dead:chararray,injured:chararray,vehicle:chararray);
> a1 = FOREACH data GENERATE $3,(int)SUBSTRING($2,2,3);
> a11 = GROUP a1 BY $0;
> b1 = FOREACH a11 GENERATE $0, COUNT($1.$1),SUM($1.$1);
> b2 = FOREACH b1 GENERATE $0,(float)$2/$1;
> c1 = ORDER b2 BY $1 DESC;
> dump c1
>
```
(普通重型-特種車,2.0)
(大客車-軍車,2.0)
```
---
# 24333 -- 「ATM位置」查詢一覽表
- HDFS 完整路徑 ``/dataset/24333/24333.csv`
```
欄位名稱
裝設金融機構代號 code:chararray
裝設金融機構名稱 name:chararray
裝設地點 location:chararray
裝設縣市 city:chararray
裝設地址 address:chararray
原始資料集內容
code name location city address
004,臺灣銀行,士林分行,台北市,台北市士林區中山北路六段197號
004,臺灣銀行,大同公司,台北市,台北市中山區中山北路三段22號
004,臺灣銀行,大安分行,台北市,台北市大安區敦化南路二段69號1樓
```
- 題目 8-1. 哪間銀行的ATM在台中市有最多台?
- 輸出格式: 銀行名稱, 數量
- 答案範例: 元大商業銀行, 87
> data = load '/dataset/24333/24333.csv' USING PigStorage (',');
> a1 = FILTER data BY $3 == '台中市';
> b2 = GROUP a1 BY $1;
> c1 = FOREACH b2 GENERATE $0,COUNT($1.$1);
> d1 = ORDER c1 BY $1 DESC;
> dump d1
```
A: (中國信託商業銀行,642)
```
- 題目 8-2. 哪個縣市擁有最多"臺灣銀行"的ATM? [注] "台"跟"臺"
- 輸出格式: 縣市, 數量
- 答案範例: 台北市, 87
> data = load '/dataset/24333/24333.csv' USING PigStorage (',');
> a2 = FILTER data BY $1 == '臺灣銀行';
> a3 = FOREACH a2 GENERATE $1,SUBSTRING($4,0,3);
> a1 = GROUP a3 BY $1;
> b2 = FOREACH a1 GENERATE $0,COUNT($1.$1);
> c1 = ORDER b2 BY $1 DESC;
> dump c1
```
A: (台北市,95)
```
---
# 8066 -- 農產品交易行情
- HDFS 完整路徑 ``/dataset/8066/8066.csv`
```
欄位名稱
交易日期 date:chararray
作物代號 crop_code:chararray
作物名稱 crop_name:chararray
市場代號 market_code:chararray
市場名稱 market_name:chararray
上價 upper_price:float
中價 middle_price:float
下價 lower_price:float
平均價 average_price:float
交易量 trading_volume:int
原始資料集內容
cdata crop_code crop_name market_code market_name upper_price middle_price lower_price average_price trading_volume
107.09.11,11,椰子,104,台北二,31,20,15.7,21.3,849
107.09.11,129,椰子-進口剝殼,104,台北二,88.3,72.8,60,73.3,60
107.09.11,31,釋迦,104,台北二,114,73.5,46.8,76.3,3581
```
- 題目 9-1. 全台農產品交易量最高的是哪個市場?
- 輸出格式: 市場名稱, 交易量
- 答案範例: 台北二, 87
data = load '/dataset/8066/8066.csv' USING PigStorage (',')AS
(date:chararray,crop_code:chararray,crop_name:chararray,market_code:chararray,market_name:chararray,upper_price:float,middle_price:float,lower_price:float,average_price:float,trading_volume:int);
a1 = GROUP data BY $4;
b1 = FOREACH a1 GENERATE $0,SUM($1.$9);
c1 = ORDER b1 BY $1 DESC;
dump c1
```
A: (台北一,1676906)
```
- 題目 9-2. 全台交易總量最高的是哪種農產品? </br> ("百香果-其他"與"百香果-改良種"皆算是"百香果"，以此類推其他農產品名稱)
- 輸出格式: 農產品名, 交易量
- 答案範例: 椰子, 87
data = load '/dataset/8066/8066.csv' USING PigStorage (',')AS
(date:chararray,crop_code:chararray,crop_name:chararray,market_code:chararray,market_name:chararray,upper_price:float,middle_price:float,lower_price:float,average_price:float,trading_volume:int);
a1 = GROUP data BY $2;
b1 = FOREACH a1 GENERATE $0,SUM($1.$9);
c1 = ORDER b1 BY $1 DESC;
d1 = LIMIT c1 1;
dump d1
```
A: (文旦柚,589354)
```
- 題目 9-3. 平均上價與平均下價平均價差最高的是哪項農產品? </br> ("百香果-其他"與"百香果-改良種"算兩項不同的農產品，以此類推其他農產品名稱)
- 輸出格式: 農產品名, 交易量
- 答案範例: 椰子, 87
data = load '/dataset/8066/8066.csv' USING PigStorage (',')AS
(date:chararray,crop_code:chararray,crop_name:chararray,market_code:chararray,market_name:chararray,upper_price:float,middle_price:float,lower_price:float,average_price:float,trading_volume:int);
a1 = GROUP data BY $2;
b1 = FOREACH a1 GENERATE $0,AVG($1.$5),AVG($1.$7);
c1 = FOREACH b1 GENERATE $0,$1-$2;
c2 = FOREACH c1 GENERATE $0,ROUND_TO()
d1 = ORDER c1 BY $1 DESC;
e1 = LIMIT d1 1;
dump e1
```
A: (葡萄-進口,334.6)
```
---
# 63029 -- 十六縣持卡人前十大國外消費金額及筆數(依簽帳筆數排名)
- HDFS 完整路徑 ``/dataset/63029/63029.csv`
```
欄位名稱
年月 year_month:chararray
國別 country:chararray
城市 tw_city:chararray
金額 amt:int
筆數 count:int
原始資料集內容
year_month country tw_city amt count
2014-01,英國,基隆市,20933333,30063
2014-01,盧森堡,基隆市,2575891,5276
2014-01,日本,基隆市,20238233,3895
```
- 題目 10-1. 2016到2018年國外消費總金額最高的是哪個縣市?
- 輸出格式: 縣市, 金額
- 答案範例: 基隆市, 87
> data = load '/dataset/63029/63029.csv' USING PigStorage (',')AS
(year_month:chararray,country:chararray,tw_city:chararray,amt:int,count:int);
> a0 = FILTER data BY $0 matches '2018.*' or $0 matches '2017.*' or $0 matches '2016.*';
> a1 = GROUP a0 BY $2;
> b1 = FOREACH a1 GENERATE $0,SUM($1.$3);
> c1 = ORDER b1 BY $1 DESC;
> d1 = LIMIT c1 1;
> dump d1;
```
A: (新竹市,10005781918)
```
- 題目 10-2. 哪一個月的國外消費平均金額最高?
- 輸出格式: 月份, 金額
- 答案範例: 一月, 87
> data = load '/dataset/63029/63029.csv' USING PigStorage (',')AS
(year_month:chararray,country:chararray,tw_city:chararray,amt:int,count:int);
> a0 = FOREACH data GENERATE SUBSTRING($0,5,7),$3;
> a1 = GROUP a0 BY $0;
> b1 = FOREACH a1 GENERATE $0,SUM($1.$1)/COUNT($1.$1);
> c1 = ORDER b1 BY $1 DESC;
> d1 = LIMIT c1 1;
> dump d1
```
A: (六月,11158768)
```
[注] 結果為06，我自己將答案改成'六月'
- 題目 10-3. 新竹市2018年在哪個國家的總消費金額最高?
- 輸出格式: 國家, 金額
- 答案範例: 英國, 87
> data = load '/dataset/63029/63029.csv' USING PigStorage (',')AS
(year_month:chararray,country:chararray,tw_city:chararray,amt:int,count:int);
> a1 = FILTER data BY $0 matches '2018.*' and $2 == '新竹市';
> b1 = GROUP a1 BY $1;
> c1 = FOREACH b1 GENERATE $0,SUM($1.$3);
> d1 = ORDER c1 BY $1 DESC;
> e1 = LIMIT d1 1;
> dump e1
```
A: (日本,548333518)
```
---
# 38315 -- 十六縣居民跨縣市消費樣態
- HDFS 完整路徑 ``/dataset/38315/38315.csv`
```
欄位名稱
年月 year:chararray
地區 area:chararray
類別 type:chararray
卡數 cards:int
總交易筆數 total_trans:int
總交易金額[新台幣] total_amt:biginteger
跨縣市交易筆數 inter_city_trans:int
跨縣市交易金額[新台幣] inter_city_amt:biginteger
原始資料集內容
year area type cards total_trans total_amt inter_city_trans inter_city_amt
2014-01,基隆市,ALL,241213,942118,2265105458,714100,1803194168
2014-02,基隆市,ALL,229540,813048,1842401862,641756,1533509566
2014-03,基隆市,ALL,239793,918036,1992896656,722421,1658553554
```
- 題目 11-1. 2018年哪一個縣市的跨縣市交易金額平均最高?
- 輸出格式: 縣市, 金額
- 答案範例: 基隆市, 87
> data = load '/dataset/38315/38315.csv' USING PigStorage (',')AS
(year:chararray,area:chararray,type:chararray,cards:int,total_trans:int,total_amt:biginteger,inter_city_trans:int,inter_city_amt:biginteger);
> a1 = FILTER data BY $0 matches '2018.*';
> b1 = GROUP a1 BY $1;
> c1 = FOREACH b1 GENERATE $0,SUM($1.$7)/SUM($1.$6);
> d1 = ORDER c1 BY $1 DESC;
> e1 = LIMIT d1 5;
> dump e1
```
A: (新竹市,4164)
```
- 題目 11-2. 總交易筆數中平均單筆消費金額最高的是哪個縣市?
- 輸出格式: 縣市, 金額
- 答案範例: 基隆市, 87
> data = load '/dataset/38315/38315.csv' USING PigStorage (',')AS
(year:chararray,area:chararray,type:chararray,cards:int,total_trans:int,total_amt:biginteger,inter_city_trans:int,inter_city_amt:biginteger);
> a1 = GROUP data BY $1;
> b1 = FOREACH a1 GENERATE $0,SUM($1.$5)/SUM($1.$4);
> c1 = ORDER b1 BY $1 DESC;
> d1 = LIMIT c1 5;
> dump d1
```
A: (新竹市,2651)
```
---
# 999001 -- 連線紀錄
- HDFS完整路徑 ``/dataset/999001/999001.tsv`
```
欄位名稱
連線編號 connection:int
連線形式 type:chararray
通訊協定 protocol:chararray
服務程式 service:chararray
時間戳記 timestamp:int
本機位址 local_host:chararray
本機通訊埠 local_port:int
遠端位址 remote_host:chararray
遠端通訊埠 remote_port:int
原始資料集內容
connection type protocol service ctimestamp local _host local _port remote_ host remote_ port
3 accept tcp mssqld 1502196257 192.168.131.6 1433 222.174.114.42 56803
4 accept tcp mssqld 1502196259 192.168.131.6 1433 222.174.114.42 56889
5 accept tcp smbd 1502196284 192.168.131.4 445 218.81.136.101 2671
```
- 題目 12-1. 哪一個本機位址提供的服務程式種類最多？
- 輸出格式: IP, 種類數
- 答案範例: 192.168.131.6, 87
> data = load '/dataset/999001/999001.tsv' USING PigStorage ('\t')AS
(connection:int,type:chararray,protocol:chararray,service:chararray,timestamp:int,local_host:chararray,local_port:int,remote_host:chararray,remote_port:int);
> a1 = FOREACH data GENERATE $5,$3;
> a11 = DISTINCT a1;
> a2 = GROUP a11 BY $0;
> a3 = FOREACH a2 GENERATE $0,COUNT($1.$1);
> a4 = ORDER a3 BY $1 DESC;
> b1 = LIMIT a4 5;
> dump b1
```
A: (192.168.130.1,19)
```
- 題目 12-2. 哪一個服務程式的IP數最多？
- 輸出格式: 服務程式, IP數
- 答案範例: mssqld, 87
> data = load '/dataset/999001/999001.tsv' USING PigStorage ('\t')AS
(connection:int,type:chararray,protocol:chararray,service:chararray,timestamp:int,local_host:chararray,local_port:int,remote_host:chararray,remote_port:int);
> a1 = FOREACH data GENERATE $3,$5;
> b1 = DISTINCT a1;
> c1 = GROUP b1 BY $0;
> d1 = FOREACH c1 GENERATE $0,COUNT($1.$1);
> e1 = ORDER d1 BY $1 DESC;
> dump e1
```
A:
(mysqld,10)
(mssqld,10)
(RtpUdpStream,10)
(SipSession,10)
(Blackhole,10)
(smbd,10)
(Memcache,10)
(httpd,10)
(upnpd,10)
(SipCall,10)
```
- 題目 12-3. 哪一個遠端位址連線次數最多？
- 輸出格式: IP, 次數
- 答案範例: 222.174.114.42, 87
> data = load '/dataset/999001/999001.tsv' USING PigStorage ('\t')AS
(connection:int,type:chararray,protocol:chararray,service:chararray,timestamp:int,local_host:chararray,local_port:int,remote_host:chararray,remote_port:int);
> a1 = FOREACH data GENERATE $7,$4;
> b1 = GROUP a1 BY $0;
> c1 = FOREACH b1 GENERATE $0,COUNT($1.$1);
> d1 = ORDER c1 BY $1 DESC;
> e1 = LIMIT d1 5;
> dump e1
```
A: (35.185.109.141,31564)
```
---
# 999001 -- 連線紀錄
- 題目 13-1. 哪一個網址的 IP:port 被下載檔案最多次？
- 輸出格式: IP:port, 次數
- 答案範例: 118.89.159.61:12347, 87
> data = load '/dataset/999002/999002.tsv' USING PigStorage ('\t')AS
(download:int,connection:int,download_url:chararray,download_md5_hash:chararray);
> a1 = FOREACH data GENERATE $2;
> b1 = FOREACH a1 GENERATE STRSPLIT ($0,':',3);
> c1 = FOREACH b1 GENERATE STRSPLIT ($0.$1,'//',2),STRSPLIT ($0.$2,'/',2);
> d1 = GROUP c1 BY $0;
> e1 = FOREACH d1 GENERATE $0,COUNT($1.$1);
> f1 = ORDER e1 BY $1 DESC;
> g1 = LIMIT f1 5;
> dump g1
```
A: (203.189.234.149:8080,282)
```
---
# JOIN 以下題目資料集 999001 跟 999002 均會使用到
- 題目 14-1. 哪一個遠端位址下載檔案次數最多？ </br> (資料集: 999001 & 999002)
- 輸出格式: IP, 次數
- 答案範例: 222.174.114.42, 87
> data1 = load '/dataset/999001/999001.tsv' USING PigStorage ('\t')AS
(connection:int,type:chararray,protocol:chararray,service:chararray,timestamp:int,local_host:chararray,local_port:int,remote_host:chararray,remote_port:int);
data2 = load '/dataset/999002/999002.tsv' USING PigStorage ('\t')AS
(download:int,connection:int,download_url:chararray,download_md5_hash:chararray);
> data2 = JOIN data1 BY $0,data2 BY $1;
> a1 = GROUP data2 BY $7;
> b1 = FOREACH a1 GENERATE $0,COUNT($1.$1);
> c1 = ORDER b1 BY $1 DESC;
> d1 = LIMIT c1 5;
> dump d1;
```
A: (222.186.21.168,50)
```
- 題目 14-2. 哪一個本機位置被下載檔案次數第三多？ </br> (資料集: 999001、 999002)
- 輸出格式: IP, 次數
- 答案範例: 222.174.114.42, 87
> data1 = load '/dataset/999001/999001.tsv' USING PigStorage ('\t')AS
(connection:int,type:chararray,protocol:chararray,service:chararray,timestamp:int,local_host:chararray,local_port:int,remote_host:chararray,remote_port:int);
> data2 = load '/dataset/999002/999002.tsv' USING PigStorage ('\t')AS
(download:int,connection:int,download_url:chararray,download_md5_hash:chararray);
> data3 = JOIN data1 BY $0,data2 BY $1;
> a1 = GROUP data3 BY $5;
> b1 = FOREACH a1 GENERATE $0,COUNT($1.$1);
> c1 = ORDER b1 BY $1 DESC;
> d1 = LIMIT c1 5;
> dump d1;
```
A: (192.168.130.3, 87)
```
---
# JOIN 以下題目資料集 5989 跟 24333 均會使用到
- 題目15-1. 哪個縣市的警察局與ATM數量差距最大？ </br> [注] "台"跟"臺" </br> (資料集: 5958 & 24333)
- 輸出格式: 縣市, 數量
- 答案範例: 台北市, 87
> data1 = load '/dataset/24333/24333.csv' USING PigStorage (',')AS
(code:chararray,name:chararray,location:chararray,city:chararray,address:chararray);
> data11 = GROUP data1 BY $3;
> data12 = FOREACH data11 GENERATE $0,COUNT($1.$1);
> data13 = FOREACH data12 GENERATE REPLACE($0,'台北市','臺北市'),$1;
> data2 = load '/dataset/5958/5958.csv' USING PigStorage (',')AS
(name:chararray,zip_code:chararray,address:chararray);
> data21 = FOREACH data2 GENERATE SUBSTRING($2,0,3),$0;
> data22 = GROUP data21 BY $0;
> data23 = FOREACH data22 GENERATE $0,COUNT($1.$1);
> data3 = JOIN data13 BY $0,data23 BY $0;
> b1 = FOREACH data3 GENERATE $0,$1/$3;
> c1 = ORDER b1 BY $1 DESC;
> dump c1
```
A: (台北市,37)
```
- 題目 15-2.依內政部行事曆，2018年有最多節日的月份，其節日有哪些? (節日以名稱大到小排序) </br> 十六縣居民該月總消費金額平均是12個月中最高的嗎? (平均為每月平均) </br> 資料集: 26557 & 38315
- 輸出格式: 月份, 節日(不一定一個), 是/否, 金額
- 答案範例: 一月, 是, 87
> data1 = load '/dataset/38315/38315.csv' USING PigStorage (',')AS
(year:chararray,area:chararray,type:chararray,cards:int,total_trans:int,total_amt:biginteger,inter_city_trans:int,inter_city_amt:biginteger);
> e1 = FILTER data1 BY $0 MATCHES '2018-.*';
> f1 = FOREACH e1 GENERATE SUBSTRING($0,5,7),$5;
> g1 = GROUP f1 BY $0;
> h1 = FOREACH g1 GENERATE $0,AVG($1.$1);
> data2 = load '/dataset/26557/26557.csv' USING PigStorage (',')AS
(year:chararray,a:chararray,b:int,c:chararray,d:chararray);
> a1 = FILTER data2 BY $0 MATCHES '2018-.*';
> b1 = FOREACH a1 GENERATE SUBSTRING($0,5,7),$1;
> b2 = DISTINCT b1;
> b3 = ORDER b2 BY $1;
> c1 = GROUP b3 BY $0;
> d1 = FOREACH c1 GENERATE $0,COUNT($1.$1),$1.$1;
> dataJ = JOIN d1 BY $0,h1 BY $0;
> i1 = ORDER dataJ BY $1 DESC;
> dump i1
```
A: (02,3,和平紀念日,農曆除夕,春節,02,1932543150.25)
```
- [注] </br> 原始答案為 (02,3,{(02,和平紀念日),(02,農曆除夕),(02,春節)},02,1932543150.25) 手動刪除不必要的符號與內容
Sign in with Wallet
Connect another wallet