版本：V1.0

第0題
表結(jié)構(gòu)：uid,subject_id,score
求：找出所有科目成績(jī)都大于某一學(xué)科平均成績(jī)的用戶
select
uid
from
( select t3.uid from test00_score t3 left join
( select t.uid, t.subjet_id, t.score, t1.avgScore from test00_score t left join
( select subjet_id, avg(score) as avgScore from test00_score group by subjet_id)t1
on t.subjet_id = t1.subjet_id where t.score < t1.avgScore)t2 on t2.uid = t3.uid where t2.uid is null) t4 group by uid;
第1題
我們有如下的用戶訪問數(shù)據(jù)

select
userid,
months,
sum(visitcount) as sumcount,
sum(sum(visitcount)) over(partition by userid order by months) as sum_count
from
(
select
lower(userid) as userid ,
date_format(from_unixtime(unix_timestamp(visitdate,'yyyy/MM/dd'),'yyyy-MM-dd'),'yyyy-MM') as months,
visitcount
from
test01_visit
)t group by months ,userid order by userid asc

userId visitDate visitCount
u01 2017/1/21 5
u02 2017/1/23 6
u03 2017/1/22 8
u04 2017/1/20 3
u01 2017/1/23 6
u01 2017/2/21 8
U02 2017/1/23 6
U01 2017/2/22 4
要求使用SQL統(tǒng)計(jì)出每個(gè)用戶的累積訪問次數(shù)，如下表所示：
用戶id 月份 小計(jì) 累積
u01 2017-01 11 11
u01 2017-02 12 23
u02 2017-01 12 12
u03 2017-01 8 8
u04 2017-01 3 3

第2題
有50W個(gè)京東店鋪，每個(gè)顧客訪客訪問任何一個(gè)店鋪的任何一個(gè)商品時(shí)都會(huì)產(chǎn)生一條訪問日志，訪問日志存儲(chǔ)的表名為Visit，訪客的用戶id為user_id，被訪問的店鋪名稱為shop，請(qǐng)統(tǒng)計(jì)：
1）每個(gè)店鋪的UV（訪客數(shù)）
select shop, count(distinct user_id) as uv from test02_visit group by shop;
2）每個(gè)店鋪訪問次數(shù)top3的訪客信息。輸出店鋪名稱、訪客id、訪問次數(shù)
select * from (
select t1.shop, t1.user_id, t1.count_visit,
row_number() over(partition by t1.shop order by t1.count_visit desc) rk
from (
select shop, user_id, count(*) as count_visit
from test02_visit
group by shop, user_id
)
t1
) t2 where t2.rk <= 3;
第3題
已知一個(gè)表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。請(qǐng)給出sql進(jìn)行統(tǒng)計(jì):數(shù)據(jù)樣例:2017-01-01,10029028,1000003251,33.57。
1）給出 2017年每個(gè)月的訂單數(shù)、用戶數(shù)、總成交金額。
select
date_format(date,'yyyy-MM') as month,
count(order_id) as countOrder,
count(distinct user_id) as countUser,
cast(sum(amount) as decimal) as countAmount
from
test03_order
group by date_format(date,'yyyy-MM')
2）給出2017年11月的新客數(shù)(指在11月才有第一筆訂單)
第4題
有一個(gè)5000萬的用戶文件(user_id，name，age)，一個(gè)2億記錄的用戶看電影的記錄文件(user_id，url)，統(tǒng)計(jì)各年齡段觀看電影的次數(shù)

select age,count() sumcount from
(
select
l.user_id,url,name,age
from
test04_log l
join
test04_user u
on u.user_id=l.user_id
)t group by age order by sumcount desc
select age,count() sumcount from
(
select
l.user_id,url,name,
case
when age>0 and age<10 then '0-10'
when age>=10 and age < 20 then '10-20'
when age >= 20 and age <30 then '20-30'
when age >=30 and age <40 then '30-40'
when age >=40 and age <50 then '40-50'
when age >=50 and age <60 then '50-60'
when age >=60 and age <70 then '60-70'
when age >=70 and age <80 then '70-80'
else '80-100'
end as age
from
test04_log l
join
test04_user u
on u.user_id=l.user_id
)t group by age order by sumcount desc

第5題
有日志如下，請(qǐng)寫出代碼求得所有用戶和活躍用戶的總數(shù)及平均年齡。（活躍用戶指連續(xù)兩天都有訪問記錄的用戶）
select count(),age from (
select
user_id,count(),age
from
(
select
user_id,
avg(age) age,
dt,
rank() over(partition by user_id order by dt) rank
from test05_active
group by user_id,dt
)t group by date_sub(dt,rank),user_id,age having count(*)>=2
)t1 group by user_id,age
;日期 用戶 年齡
11,test_1,23
11,test_2,19
11,test_3,39
11,test_1,23
11,test_3,39
11,test_1,23
12,test_2,19
13,test_1,23
第6題
請(qǐng)用sql寫出所有用戶中在今年10月份第一次購買商品的金額，表ordertable字段（購買用戶：userid，金額：money，購買時(shí)間：paymenttime(格式：2017-10-01)，訂單id：orderid）

select
userid,
money
from
(
select
userid,
money,
paymenttime
from
test06_ordertable
where date_format(paymenttime,'yyyy-MM')='2017-10'
order by paymenttime
)t
第7題
有一個(gè)線上服務(wù)器訪問日志格式如下（用sql答題）
時(shí)間 接口 ip地址
2016-11-09 11：22：05 /api/user/login 110.23.5.33
2016-11-09 11：23：10 /api/user/detail 57.3.2.16
.....
2016-11-09 23：59：40 /api/user/login 200.6.5.166
求11月9號(hào)下午14點(diǎn)（14-15點(diǎn)），訪問api/user/login接口的top10的ip地址

第8題
有一個(gè)賬號(hào)表如下，請(qǐng)寫出SQL語句，查詢各自區(qū)組的money排名前十的賬號(hào)（分組取前10）
CREATE TABIE account
(
dist_id int（11）
DEFAULT NULL COMMENT '區(qū)組id'，
account varchar（100）DEFAULT NULL COMMENT '賬號(hào)' ,
gold int（11）DEFAULT NULL COMMENT '金幣'
PRIMARY KEY （dist_id，account_id），
）ENGINE=InnoDB DEFAULT CHARSET-utf8

select
dist_id,
account,
rk
from
(
select
dist_id,
account,
rank() over(partition by dist_id order by gold) rk
from
test08_account
)t
where rk <10
第9題
1）有三張表分別為會(huì)員表（member）銷售表（sale）退貨表（regoods）
（1）會(huì)員表有字段memberid（會(huì)員id，主鍵）credits（積分）；
（2）銷售表有字段memberid（會(huì)員id，外鍵）購買金額（MNAccount）；
（3）退貨表中有字段memberid（會(huì)員id，外鍵）退貨金額（RMNAccount）；
2）業(yè)務(wù)說明：
（1）銷售表中的銷售記錄可以是會(huì)員購買，也可是非會(huì)員購買。（即銷售表中的memberid可以為空）
（2）銷售表中的一個(gè)會(huì)員可以有多條購買記錄
（3）退貨表中的退貨記錄可以是會(huì)員，也可是非會(huì)員
4、一個(gè)會(huì)員可以有一條或多條退貨記錄
查詢需求：分組查出銷售表中所有會(huì)員購買金額，同時(shí)分組查出退貨表中所有會(huì)員的退貨金額，把會(huì)員id相同的購買金額-退款金額得到的結(jié)果更新到表會(huì)員表中對(duì)應(yīng)會(huì)員的積分字段（credits）

with tmp_test09_sale as(
select
memberid,
sum(mnaccount) MNAccount
from
test09_sale
group by
memberid
),
tmp_test09_regoods as(
select
memberid,
sum(rmnaccount) RMNAccount
from
test09_regoods
group by
memberid
)

insert into table test09_credits
select
memberid,
sum(MNAccount) MNAccount,
sum(RMNAccount) RMNAccount
from(
select
memberid,
0 credits,
MNAccount,
0 RMNAccount
from
tmp_test09_sale
union all
select
memberid,
0 credits,
0 MNAccount,
RMNAccount
from
tmp_test09_regoods
)t
group by t.memberid

第10題
現(xiàn)在有三個(gè)表student（學(xué)生表）、course(課程表)、score（成績(jī)單），結(jié)構(gòu)如下：
create table student
(
id bigint comment ‘學(xué)號(hào)’，
name string comment ‘姓名’,
age bigint comment ‘年齡’
);
create table course
(
cid string comment ‘課程號(hào)，001/002格式’,
cname string comment ‘課程名’
);
Create table score
(
Id bigint comment ‘學(xué)號(hào)’,
cid string comment ‘課程號(hào)’,
score bigint comment ‘成績(jī)’
) partitioned by(event_day string)

其中score中的id、cid，分別是student、course中對(duì)應(yīng)的列請(qǐng)根據(jù)上面的表結(jié)構(gòu)，回答下面的問題
1）請(qǐng)將本地文件（/home/users/test/20190301.csv）文件，加載到分區(qū)表score的20190301分區(qū)中，并覆蓋之前的數(shù)據(jù)
2）查出平均成績(jī)大于60分的學(xué)生的姓名、年齡、平均成績(jī)
select
name ,
age,
cid,
avg_score
from
test10_student t
join
(
select
id,
cid,
avg(score) avg_score
from
test10_score
group by id,cid having avg(score) >60
) t1
on t.id=t1.id
2）查出沒有‘001’課程成績(jī)的學(xué)生的姓名、年齡
select
name,
age
from
(
select
name,
age,
cid
from
test10_student t1
left join
test10_score t2
on t1.id=t2.id

)t3 where cid!='001'
3）查出有‘001’\’002’這兩門課程下，成績(jī)排名前3的學(xué)生的姓名、年齡

select
name,
age,
sum(score) as score
from
(
select
name,
age,
cid,
score
from
test10_student t1
left join
test10_score t2
on t1.id=t2.id where cid='001' or cid='002'

)t3 group by name,age order by score desc limit 3
5）創(chuàng)建新的表score_20190317，并存入score表中20190317分區(qū)的數(shù)據(jù)
6）描述一下union和union all的區(qū)別，以及在mysql和HQL中用法的不同之處？
7）簡(jiǎn)單描述一下lateral view語法在HQL中的應(yīng)用場(chǎng)景，并寫一個(gè)HQL實(shí)例