柚子快報激活碼778899分享：頭歌平臺云計算實驗

http://yzkb.51969.com/

云計算

Hive綜合應(yīng)用案例——用戶學(xué)歷查詢1 查詢每一個用戶從出生到現(xiàn)在的總天數(shù)2 同一個地區(qū)相同的教育程度的最高收入3 統(tǒng)計各級學(xué)歷所占總?cè)藬?shù)百分比

其他部分答案在B站工房 https://gf.bilibili.com/item/detail/1105242061

Hive綜合應(yīng)用案例——用戶學(xué)歷查詢

1 查詢每一個用戶從出生到現(xiàn)在的總天數(shù)

---------- 禁止修改 ----------

drop database if exists mydb cascade;

---------- 禁止修改 ----------

---------- begin ----------

---創(chuàng)建mydb數(shù)據(jù)庫

create database if not exists mydb;

---使用mydb數(shù)據(jù)庫

use mydb;

---創(chuàng)建表user

create table usertab(

id string,

sex string,

time string,

education string,

occupation string,

income string,

area string,

desired_area string,

city_countryside string

)

row format delimited fields terminated by ',';

---導(dǎo)入數(shù)據(jù)：/root/data.txt

load data local inpath '/root/data.txt' into table usertab;

--查詢每一個用戶從出生到2019-06-10的總天數(shù)

select id, datediff('2019-06-10',regexp_replace(time, '/', '-')) from usertab;

2 同一個地區(qū)相同的教育程度的最高收入

---------- 禁止修改 ----------

drop database if exists mydb cascade;

---------- 禁止修改 ----------

---------- begin ----------

--創(chuàng)建mydb數(shù)據(jù)庫

create database if not exists mydb;

---使用mydb數(shù)據(jù)庫

use mydb;

---創(chuàng)建表user

create table usertab1(

id int,

sex string,

time string,

education string,

occupation string,

income string,

area string,

desired_area string,

city_countryside string

)

row format delimited fields terminated by ',';

---導(dǎo)入數(shù)據(jù)：/root/data.txt

load data local inpath '/root/data1.txt' into table usertab1;

--同一個地區(qū)相同的教育程度的最高收入

select area,education,income

from(

select area,education,income,

row_number() over(

partition by area, education order by income desc

) as t1

from usertab1

) as t2

where t2.t1 = 1;

---------- end ----------

3 統(tǒng)計各級學(xué)歷所占總?cè)藬?shù)百分比

---------- 禁止修改 ----------

drop database if exists mydb cascade;

set hive.mapred.mode=nonstrict;

---------- 禁止修改 ----------

---------- begin ----------

--創(chuàng)建mydb數(shù)據(jù)庫

create database if not exists mydb;

---使用mydb數(shù)據(jù)庫

use mydb;

---創(chuàng)建表user

create table usertab2(

id int,

sex string,

time string,

education string,

occupation string,

income string,

area string,

desired_area string,

city_countryside string

)

row format delimited fields terminated by ',';

---導(dǎo)入數(shù)據(jù)：/root/data.txt

load data local inpath '/root/data.txt' into table usertab2;

--統(tǒng)計各級學(xué)歷所占總?cè)藬?shù)百分比(對結(jié)果保留兩位小數(shù))

select concat(round(t1.cnted * 100 / t2.cnt, 2),'%'), t1.education

from

(

select count(*) as cnted,education

from usertab2

group by education

) as t1,

(

select count(*) as cnt from usertab2

) as t2

order by t1.education;

---------- end ----------

柚子快報激活碼778899分享：頭歌平臺云計算實驗

http://yzkb.51969.com/

相關(guān)閱讀