1. 內(nèi)部表應(yīng)用
   (1) 創(chuàng)建內(nèi)部表Student，要求的字段和類(lèi)型： id int, name string, sex string, age int，執(zhí)行insert語(yǔ)句錄入以下數(shù)據(jù)：

create table Student(id int,name string,sex string,age int);
insert into Student values(1,"Tom","M",18),(2,"Trill","M",19),(3,"Bell","M",20),(4,"Lisa","F",19),(5,"Willy","F",18);

(2) 執(zhí)行查詢(xún)
查詢(xún)所有男生

select * from Student where sex="M";

查詢(xún)大于18歲的學(xué)生，并按年齡升序排列

select * from Student where sex="M" and age>18 order by age asc;

2.外部表應(yīng)用

(1) 將本實(shí)驗(yàn)數(shù)據(jù)上傳至HDFS，數(shù)據(jù)為stock-daily30d2.txt，此數(shù)據(jù)修改了實(shí)驗(yàn)4中stock-daily數(shù)據(jù)中的日期格式以支持Hive中的Date數(shù)據(jù)類(lèi)型：

https://download.csdn.net/download/m0_69488210/87961142

stock-daily數(shù)據(jù)說(shuō)明：

[‘code’,‘isst’,open’, ‘close’, ‘high’, ‘low’, ‘volume’, ‘money’, ‘factor’, ‘high_limit’, ‘low_limit’, ‘a(chǎn)vg’, ‘paused’, ‘date’]

code 股票代碼

isst 是否ST

open 開(kāi)盤(pán)價(jià)

close 收盤(pán)價(jià)

high 當(dāng)日最高價(jià)

low 當(dāng)日最低價(jià)

volume 交易量（手）

money 交易量（萬(wàn)元）

factor 除權(quán)比例

high_limit 漲停價(jià)

low_limit 跌停價(jià)

avg 每日均價(jià)

paused 是否停牌

date 日期

在hdfs中新建/hivedata/stock-daily目錄，并把stock-daily30d.txt上傳到/hivedata/stock-daily目錄中

hadoop fs -mkdir /hivedata
hadoop fs -mkdir /hivedata/stock-daily
hadoop fs -put stock-daily30d.txt /hivedata/stock-daily

(2) 創(chuàng)建外部表Stock，具體的字段名稱(chēng)和類(lèi)型為：code string, isst boolean, open float, close float, high float, low float, volume float, money float, factor float, high_limit float, low_limit float, avg float, paused boolean, trade_date date；注意日期字段不要用date或time，可使用trade_date以避免語(yǔ)法錯(cuò)誤。

create external table Stock(code string, isst boolean, open float, close float, high float, low float, volume float, money float, factor float, high_limit float, low_limit float, avg float, paused boolean, trade_date date) row format delimited fields terminated by '\t' location '/hivedata/stock-daily';

(3) 執(zhí)行查詢(xún)
查詢(xún)上證所的股票數(shù)量，代碼以.SH結(jié)尾的股票

select count(*) from Stock where code like "%.SH";

參考SQL語(yǔ)句group語(yǔ)句，查詢(xún)每日市場(chǎng)總交易量(萬(wàn)元)

• 結(jié)果須包含日期和對(duì)應(yīng)的中交易量數(shù)據(jù)

• 結(jié)果按時(shí)間做降序排列

• 市場(chǎng)總交易量為當(dāng)天所有股票交易量(萬(wàn)元)之和

select trade_date,sum(money) from Stock group by trade_date order by trade_date desc;

(4) 學(xué)習(xí)與思考：計(jì)算實(shí)驗(yàn)4中，股票5日滾動(dòng)收益率
_1_?搜索所有交易日，并對(duì)交易日編號(hào)（保存中間數(shù)據(jù)到tradedate表）

CREATE TABLE TradeDate as (select row_number() over(order by 1) as date_id, s.trade_date from (select distinct(trade_date) trade_date from Stock where trade_date is not NULL order by trade_date) s);

_2_?利用連接查詢(xún)，搜索提取計(jì)算所需股票代碼和收盤(pán)價(jià)，并將交易日標(biāo)記上對(duì)應(yīng)編號(hào)（保存中間數(shù)據(jù)到closewithid表）

CREATE TABLE CloseWithDateId as (select s.code, s.close, s.trade_date, t.date_id from Stock as s join TradeDate as t on s.trade_date=t.trade_date);

_3_?利用自連接，讓股票當(dāng)日收益與改股票前第五日收益連接在同一行中，并計(jì)算五日收益率，此計(jì)算對(duì)每個(gè)股票和交易日都生效（保存中間數(shù)據(jù)到rollreturn表）

CREATE TABLE RollEarns as (select c2.code, c1.close prev_close, c2.close curr_close, round(((c2.close-c1.close)/c1.close)*100,2) earn_rate, c2.trade_date from CloseWithDateId as c1 join CloseWithDateId as c2 on (c1.date_id == c2.date_id + 4) and c1.code == c2.code order by c2.trade_date);

_4_?查詢(xún)某只股票的五日滾動(dòng)收益率

select * from RollEarns where code=='688800.SH' order by trade_date desc;