10個經(jīng)典Hive-SQL面試題

點(diǎn)擊上方“數(shù)據(jù)管道”，選擇“置頂星標(biāo)”公眾號

干貨福利，第一時間送達(dá)

第一題

需求

我們有如下的用戶訪問數(shù)據(jù)
????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ì)出每個用戶的累積訪問次數(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

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test1?(?
????????userId?string,?
????????visitDate?string,
????????visitCount?INT?)
????ROW?format?delimited?FIELDS?TERMINATED?BY?"\t";
????INSERT?INTO?TABLE?test_sql.test1
????VALUES
????????(?'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

SELECT?t2.userid,
???????t2.visitmonth,
???????subtotal_visit_cnt,
???????sum(subtotal_visit_cnt)?over?(partition?BY?userid?ORDER?BY?visitmonth)?AS?total_visit_cnt
FROM
??(SELECT?userid,
??????????visitmonth,
??????????sum(visitcount)?AS?subtotal_visit_cnt
???FROM
?????(SELECT?userid,
?????????????date_format(regexp_replace(visitdate,'/','-'),'yyyy-MM')?AS?visitmonth,
?????????????visitcount
??????FROM?test_sql.test1)?t1
???GROUP?BY?userid,
????????????visitmonth)t2
ORDER?BY?t2.userid,
?????????t2.visitmonth

第二題

需求

有50W個京東店鋪，每個顧客訪客訪問任何一個店鋪的任何一個商品時都會產(chǎn)生一條訪問日志，
訪問日志存儲的表名為Visit，訪客的用戶id為user_id，被訪問的店鋪名稱為shop，數(shù)據(jù)如下：

????????????????u1??a
????????????????u2??b
????????????????u1??b
????????????????u1??a
????????????????u3??c
????????????????u4??b
????????????????u1??a
????????????????u2??c
????????????????u5??b
????????????????u4??b
????????????????u6??c
????????????????u2??c
????????????????u1??b
????????????????u2??a
????????????????u2??a
????????????????u3??a
????????????????u5??a
????????????????u5??a
????????????????u5??a
請統(tǒng)計(jì)：
(1)每個店鋪的UV（訪客數(shù)）
(2)每個店鋪訪問次數(shù)top3的訪客信息。輸出店鋪名稱、訪客id、訪問次數(shù)

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test2?(?
?????????????????????????user_id?string,?
?????????????????????????shop?string?)
????????????ROW?format?delimited?FIELDS?TERMINATED?BY?'\t';?
????????????INSERT?INTO?TABLE?test_sql.test2?VALUES
????????????(?'u1',?'a'?),
????????????(?'u2',?'b'?),
????????????(?'u1',?'b'?),
????????????(?'u1',?'a'?),
????????????(?'u3',?'c'?),
????????????(?'u4',?'b'?),
????????????(?'u1',?'a'?),
????????????(?'u2',?'c'?),
????????????(?'u5',?'b'?),
????????????(?'u4',?'b'?),
????????????(?'u6',?'c'?),
????????????(?'u2',?'c'?),
????????????(?'u1',?'b'?),
????????????(?'u2',?'a'?),
????????????(?'u2',?'a'?),
????????????(?'u3',?'a'?),
????????????(?'u5',?'a'?),
????????????(?'u5',?'a'?),
????????????(?'u5',?'a'?);??????????

查詢SQL實(shí)現(xiàn)

(1)方式1：
????????SELECT?shop,
???????????????count(DISTINCT?user_id)
????????FROM?test_sql.test2
????????GROUP?BY?shop
方式2：
????????SELECT?t.shop,
???????????????count(*)
????????FROM
??????????(SELECT?user_id,
??????????????????shop
???????????FROM?test_sql.test2
???????????GROUP?BY?user_id,
????????????????????shop)?t
????????GROUP?BY?t.shop
(2)????
SELECT?t2.shop,
???????t2.user_id,
???????t2.cnt
FROM
??(SELECT?t1.*,
??????????row_number()?over(partition?BY?t1.shop
????????????????????????????ORDER?BY?t1.cnt?DESC)?rank
???FROM
?????(SELECT?user_id,
?????????????shop,
?????????????count(*)?AS?cnt
??????FROM?test_sql.test2
??????GROUP?BY?user_id,
???????????????shop)?t1)t2
WHERE?rank?<=?3????????????????????

第三題

需求

已知一個表STG.ORDER，有如下字段:Date，Order_id，User_id，amount。
數(shù)據(jù)樣例:2017-01-01,10029028,1000003251,33.57。
請給出sql進(jìn)行統(tǒng)計(jì):
(1)給出 2017年每個月的訂單數(shù)、用戶數(shù)、總成交金額。
(2)給出2017年11月的新客數(shù)(指在11月才有第一筆訂單)

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test3?(?
????????????dt?string,
????????????order_id?string,?
????????????user_id?string,?
????????????amount?DECIMAL?(?10,?2?)?)
ROW?format?delimited?FIELDS?TERMINATED?BY?'\t';
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2017-01-01','10029028','1000003251',33.57);
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2017-01-01','10029029','1000003251',33.57);
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2017-01-01','100290288','1000003252',33.57);
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2017-02-02','10029088','1000003251',33.57);
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2017-02-02','100290281','1000003251',33.57);
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2017-02-02','100290282','1000003253',33.57);
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2017-11-02','10290282','100003253',234);
INSERT?INTO?TABLE?test_sql.test3?VALUES?('2018-11-02','10290284','100003243',234);

查詢SQL

(1)
SELECT?t1.mon,
???????count(t1.order_id)?AS?order_cnt,
???????count(DISTINCT?t1.user_id)?AS?user_cnt,
???????sum(amount)?AS?total_amount
FROM
??(SELECT?order_id,
??????????user_id,
??????????amount,
??????????date_format(dt,'yyyy-MM')?mon
???FROM?test_sql.test3
???WHERE?date_format(dt,'yyyy')?=?'2017')?t1
GROUP?BY?t1.mon
(2)
SELECT?count(user_id)
FROM?test_sql.test3
GROUP?BY?user_id
HAVING?date_format(min(dt),'yyyy-MM')='2017-11';

第四題

需求

有一個5000萬的用戶文件(user_id，name，age)，一個2億記錄的用戶看電影的記錄文件(user_id，url)，根據(jù)年齡段觀看電影的次數(shù)進(jìn)行排序？????????

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test4user
???????????(user_id?string,
????????????name?string,
????????????age?int);

CREATE?TABLE?test_sql.test4log
????????????????????????(user_id?string,
????????????????????????url?string);

INSERT?INTO?TABLE?test_sql.test4user?VALUES('001','u1',10);
INSERT?INTO?TABLE?test_sql.test4user?VALUES('002','u2',15);???
INSERT?INTO?TABLE?test_sql.test4user?VALUES('003','u3',15);???
INSERT?INTO?TABLE?test_sql.test4user?VALUES('004','u4',20);???
INSERT?INTO?TABLE?test_sql.test4user?VALUES('005','u5',25);???
INSERT?INTO?TABLE?test_sql.test4user?VALUES('006','u6',35);???
INSERT?INTO?TABLE?test_sql.test4user?VALUES('007','u7',40);
INSERT?INTO?TABLE?test_sql.test4user?VALUES('008','u8',45);??
INSERT?INTO?TABLE?test_sql.test4user?VALUES('009','u9',50);??
INSERT?INTO?TABLE?test_sql.test4user?VALUES('0010','u10',65);??
INSERT?INTO?TABLE?test_sql.test4log?VALUES('001','url1');
INSERT?INTO?TABLE?test_sql.test4log?VALUES('002','url1');???
INSERT?INTO?TABLE?test_sql.test4log?VALUES('003','url2');???
INSERT?INTO?TABLE?test_sql.test4log?VALUES('004','url3');???
INSERT?INTO?TABLE?test_sql.test4log?VALUES('005','url3');???
INSERT?INTO?TABLE?test_sql.test4log?VALUES('006','url1');???
INSERT?INTO?TABLE?test_sql.test4log?VALUES('007','url5');
INSERT?INTO?TABLE?test_sql.test4log?VALUES('008','url7');??
INSERT?INTO?TABLE?test_sql.test4log?VALUES('009','url5');??
INSERT?INTO?TABLE?test_sql.test4log?VALUES('0010','url1');?

查詢SQL

SELECT?
t2.age_phase,
sum(t1.cnt)?as?view_cnt
FROM

(SELECT?user_id,
??count(*)?cnt
FROM?test_sql.test4log
GROUP?BY?user_id)?t1
JOIN
(SELECT?user_id,
??CASE?WHEN?age?<=?10?AND?age?>?0?THEN?'0-10'?
??WHEN?age?<=?20?AND?age?>?10?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'
??ELSE?'70以上'?END?as?age_phase
FROM?test_sql.test4user)?t2?ON?t1.user_id?=?t2.user_id?
GROUP?BY?t2.age_phase

第五題

需求

有日志如下，請寫出代碼求得所有用戶和活躍用戶的總數(shù)及平均年齡。（活躍用戶指連續(xù)兩天都有訪問記錄的用戶）
日期?用戶?年齡
2019-02-11,test_1,23
2019-02-11,test_2,19
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-11,test_3,39
2019-02-11,test_1,23
2019-02-12,test_2,19
2019-02-13,test_1,23
2019-02-15,test_2,19
2019-02-16,test_2,19

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test5(
dt?string,
user_id?string,
age?int)
ROW?format?delimited?fields?terminated?BY?',';
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-11','test_1',23);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-11','test_2',19);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-11','test_3',39);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-11','test_1',23);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-11','test_3',39);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-11','test_1',23);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-12','test_2',19);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-13','test_1',23);
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-15','test_2',19);????????????????????????????????????????
INSERT?INTO?TABLE?test_sql.test5?VALUES?('2019-02-16','test_2',19);????????????????

查詢SQL

SELECT?sum(total_user_cnt)?total_user_cnt,
???????sum(total_user_avg_age)?total_user_avg_age,
???????sum(two_days_cnt)?two_days_cnt,
???????sum(avg_age)?avg_age
FROM
??(SELECT?0?total_user_cnt,
??????????0?total_user_avg_age,
??????????count(*)?AS?two_days_cnt,
??????????cast(sum(age)?/?count(*)?AS?decimal(5,2))?AS?avg_age
???FROM
?????(SELECT?user_id,
?????????????max(age)?age
??????FROM
????????(SELECT?user_id,
????????????????max(age)?age
?????????FROM
???????????(SELECT?user_id,
???????????????????age,
???????????????????date_sub(dt,rank)?flag
????????????FROM
??????????????(SELECT?dt,
??????????????????????user_id,
??????????????????????max(age)?age,
??????????????????????row_number()?over(PARTITION?BY?user_id
????????????????????????????????????????ORDER?BY?dt)?rank
???????????????FROM?test_sql.test5
???????????????GROUP?BY?dt,
????????????????????????user_id)?t1)?t2
?????????GROUP?BY?user_id,
??????????????????flag
?????????HAVING?count(*)?>=2)?t3
??????GROUP?BY?user_id)?t4
???UNION?ALL?SELECT?count(*)?total_user_cnt,
????????????????????cast(sum(age)?/count(*)?AS?decimal(5,2))?total_user_avg_age,
????????????????????0?two_days_cnt,
????????????????????0?avg_age
???FROM
?????(SELECT?user_id,
?????????????max(age)?age
??????FROM?test_sql.test5
??????GROUP?BY?user_id)?t5)?t6

第六題

需求

請用sql寫出所有用戶中在今年10月份第一次購買商品的金額，
表ordertable字段:
(購買用戶：userid，金額：money，購買時間：paymenttime(格式：2017-10-01)，訂單id：orderid ???????????

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test6?(
????????userid?string,
????????money?decimal(10,2),
????????paymenttime?string,
????????orderid?string);

INSERT?INTO?TABLE?test_sql.test6?VALUES('001',100,'2017-10-01','123');
INSERT?INTO?TABLE?test_sql.test6?VALUES('001',200,'2017-10-02','124');
INSERT?INTO?TABLE?test_sql.test6?VALUES('002',500,'2017-10-01','125');
INSERT?INTO?TABLE?test_sql.test6?VALUES('001',100,'2017-11-01','126');????????????????

查詢SQL

SELECT
userid,
paymenttime,
money,
orderid
from
(SELECT?userid,
???????money,
???????paymenttime,
???????orderid,
???????row_number()?over?(PARTITION?BY?userid
??????????????????????????ORDER?BY?paymenttime)?rank
FROM?test_sql.test6
WHERE?date_format(paymenttime,'yyyy-MM')?=?'2017-10')?t
WHERE?rank?=?1

第七題

需求

現(xiàn)有圖書管理數(shù)據(jù)庫的三個數(shù)據(jù)模型如下：
圖書（數(shù)據(jù)表名：BOOK）
????序號??????字段名稱????字段描述????字段類型
????1???????BOOK_ID?????總編號?????????文本
????2???????SORT????????分類號?????????文本
????3???????BOOK_NAME???書名??????????文本
????4???????WRITER??????作者??????????文本
????5???????OUTPUT??????出版單位????文本
????6???????PRICE???????單價??????????數(shù)值（保留小數(shù)點(diǎn)后2位）
讀者（數(shù)據(jù)表名：READER）
????序號??????字段名稱????字段描述????字段類型
????1???????READER_ID???借書證號????文本
????2???????COMPANY?????單位??????????文本
????3???????NAME????????姓名??????????文本
????4???????SEX?????????性別??????????文本
????5???????GRADE???????職稱??????????文本
????6???????ADDR????????地址??????????文本
借閱記錄（數(shù)據(jù)表名：BORROW LOG）
????序號??????字段名稱????????字段描述????字段類型
????1???????READER_ID???????借書證號????文本
????2???????BOOK_ID?????????總編號?????????文本
????3???????BORROW_DATE?????借書日期????日期
（1）創(chuàng)建圖書管理庫的圖書、讀者和借閱三個基本表的表結(jié)構(gòu)。請寫出建表語句。
（2）找出姓李的讀者姓名（NAME）和所在單位（COMPANY）。
（3）查找“高等教育出版社”的所有圖書名稱（BOOK_NAME）及單價（PRICE），結(jié)果按單價降序排序。
（4）查找價格介于10元和20元之間的圖書種類(SORT）出版單位（OUTPUT）和單價（PRICE），結(jié)果按出版單位（OUTPUT）和單價（PRICE）升序排序。
（5）查找所有借了書的讀者的姓名（NAME）及所在單位（COMPANY）。
（6）求”科學(xué)出版社”圖書的最高單價、最低單價、平均單價。
（7）找出當(dāng)前至少借閱了2本圖書（大于等于2本）的讀者姓名及其所在單位。
（8）考慮到數(shù)據(jù)安全的需要，需定時將“借閱記錄”中數(shù)據(jù)進(jìn)行備份，請使用一條SQL語句，在備份用戶bak下創(chuàng)建與“借閱記錄”表結(jié)構(gòu)完全一致的數(shù)據(jù)表BORROW_LOG_BAK.井且將“借閱記錄”中現(xiàn)有數(shù)據(jù)全部復(fù)制到BORROW_L0G_ BAK中。
（9）現(xiàn)在需要將原Oracle數(shù)據(jù)庫中數(shù)據(jù)遷移至Hive倉庫，請寫出“圖書”在Hive中的建表語句（Hive實(shí)現(xiàn)，提示：列分隔符|；數(shù)據(jù)表數(shù)據(jù)需要外部導(dǎo)入：分區(qū)分別以month＿part、day＿part 命名）
（10）Hive中有表A，現(xiàn)在需要將表A的月分區(qū)　201505　中　user＿id為20000的user＿dinner字段更新為bonc8920，其他用戶user＿dinner字段數(shù)據(jù)不變，請列出更新的方法步驟。（Hive實(shí)現(xiàn)，提示：Hlive中無update語法，請通過其他辦法進(jìn)行數(shù)據(jù)更新）

實(shí)現(xiàn)

(1)

--?創(chuàng)建圖書表book

CREATE?TABLE?test_sql.book(book_id?string,
???????????????????????????`SORT`?string,
???????????????????????????book_name?string,
???????????????????????????writer?string,
???????????????????????????OUTPUT?string,
???????????????????????????price?decimal(10,2));
INSERT?INTO?TABLE?test_sql.book?VALUES?('001','TP391','信息處理','author1','機(jī)械工業(yè)出版社','20');
INSERT?INTO?TABLE?test_sql.book?VALUES?('002','TP392','數(shù)據(jù)庫','author12','科學(xué)出版社','15');
INSERT?INTO?TABLE?test_sql.book?VALUES?('003','TP393','計(jì)算機(jī)網(wǎng)絡(luò)','author3','機(jī)械工業(yè)出版社','29');
INSERT?INTO?TABLE?test_sql.book?VALUES?('004','TP399','微機(jī)原理','author4','科學(xué)出版社','39');
INSERT?INTO?TABLE?test_sql.book?VALUES?('005','C931','管理信息系統(tǒng)','author5','機(jī)械工業(yè)出版社','40');
INSERT?INTO?TABLE?test_sql.book?VALUES?('006','C932','運(yùn)籌學(xué)','author6','科學(xué)出版社','55');


--?創(chuàng)建讀者表reader

CREATE?TABLE?test_sql.reader?(reader_id?string,
??????????????????????????????company?string,
??????????????????????????????name?string,
??????????????????????????????sex?string,
??????????????????????????????grade?string,
??????????????????????????????addr?string);
INSERT?INTO?TABLE?test_sql.reader?VALUES?('0001','阿里巴巴','jack','男','vp','addr1');
INSERT?INTO?TABLE?test_sql.reader?VALUES?('0002','百度','robin','男','vp','addr2');
INSERT?INTO?TABLE?test_sql.reader?VALUES?('0003','騰訊','tony','男','vp','addr3');
INSERT?INTO?TABLE?test_sql.reader?VALUES?('0004','京東','jasper','男','cfo','addr4');
INSERT?INTO?TABLE?test_sql.reader?VALUES?('0005','網(wǎng)易','zhangsan','女','ceo','addr5');
INSERT?INTO?TABLE?test_sql.reader?VALUES?('0006','搜狐','lisi','女','ceo','addr6');

--?創(chuàng)建借閱記錄表borrow_log

CREATE?TABLE?test_sql.borrow_log(reader_id?string,
?????????????????????????????????book_id?string,
?????????????????????????????????borrow_date?string);

INSERT?INTO?TABLE?test_sql.borrow_log?VALUES?('0001','002','2019-10-14');
INSERT?INTO?TABLE?test_sql.borrow_log?VALUES?('0002','001','2019-10-13');
INSERT?INTO?TABLE?test_sql.borrow_log?VALUES?('0003','005','2019-09-14');
INSERT?INTO?TABLE?test_sql.borrow_log?VALUES?('0004','006','2019-08-15');
INSERT?INTO?TABLE?test_sql.borrow_log?VALUES?('0005','003','2019-10-10');
INSERT?INTO?TABLE?test_sql.borrow_log?VALUES?('0006','004','2019-17-13');

(2)
????SELECT?name,
???????????company
????FROM?test_sql.reader
????WHERE?name?LIKE?'李%';
(3)
????SELECT?book_name,
???????????price
????FROM?test_sql.book
????WHERE?OUTPUT?=?"高等教育出版社"
????ORDER?BY?price?DESC;
(4)
????SELECT?sort,
???????????output,
???????????price
????FROM?test_sql.book
????WHERE?price?>=?10?and?price?<=?20
????ORDER?BY?output,price?;
(5)
????SELECT?b.name,
???????????b.company
????FROM?test_sql.borrow_log?a
????JOIN?test_sql.reader?b?ON?a.reader_id?=?b.reader_id;
(6)
????SELECT?max(price),
???????????min(price),
???????????avg(price)
????FROM?test_sql.book
????WHERE?OUTPUT?=?'科學(xué)出版社';
(7)
????SELECT?b.name,
???????????b.company
????FROM
??????(SELECT?reader_id
???????FROM?test_sql.borrow_log
???????GROUP?BY?reader_id
???????HAVING?count(*)?>=?2)?a
????JOIN?test_sql.reader?b?ON?a.reader_id?=?b.reader_id;

(8)
????CREATE?TABLE?test_sql.borrow_log_bak?AS
????SELECT?*
????FROM?test_sql.borrow_log;
(9)
????CREATE?TABLE?book_hive?(?
????book_id?string,
????SORT?string,?
????book_name?string,
????writer?string,?
????OUTPUT?string,?
????price?DECIMAL?(?10,?2?)?)
????partitioned?BY?(?month_part?string,?day_part?string?)
????ROW?format?delimited?FIELDS?TERMINATED?BY?'\\|'?stored?AS?textfile;
(10)
????方式1：配置hive支持事務(wù)操作，分桶表，orc存儲格式
????方式2：第一步找到要更新的數(shù)據(jù)，將要更改的字段替換為新的值，第二步找到不需要更新的數(shù)據(jù)，第三步將上兩步的數(shù)據(jù)插入一張新表中。

第八題

需求

有一個線上服務(wù)器訪問日志格式如下（用sql答題）
時間????????????????????接口?????????????????????????ip地址
2016-11-09?14:22:05????????/api/user/login?????????????110.23.5.33
2016-11-09?14:23:10????????/api/user/detail????????????57.3.2.16
2016-11-09?15:59:40????????/api/user/login?????????????200.6.5.166
…?…
求11月9號下午14點(diǎn)（14-15點(diǎn)），訪問/api/user/login接口的top10的ip地址????????????????

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test8(`date`?string,
????????????????interface?string,
????????????????ip?string);

INSERT?INTO?TABLE?test_sql.test8?VALUES?('2016-11-09?11:22:05','/api/user/login','110.23.5.23');
INSERT?INTO?TABLE?test_sql.test8?VALUES?('2016-11-09?11:23:10','/api/user/detail','57.3.2.16');
INSERT?INTO?TABLE?test_sql.test8?VALUES?('2016-11-09?23:59:40','/api/user/login','200.6.5.166');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?11:14:23','/api/user/login','136.79.47.70');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?11:15:23','/api/user/detail','94.144.143.141');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?11:16:23','/api/user/login','197.161.8.206');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?12:14:23','/api/user/detail','240.227.107.145');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?13:14:23','/api/user/login','79.130.122.205');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:14:23','/api/user/detail','65.228.251.189');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:15:23','/api/user/detail','245.23.122.44');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:17:23','/api/user/detail','22.74.142.137');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:19:23','/api/user/detail','54.93.212.87');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:20:23','/api/user/detail','218.15.167.248');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:24:23','/api/user/detail','20.117.19.75');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?15:14:23','/api/user/login','183.162.66.97');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?16:14:23','/api/user/login','108.181.245.147');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:17:23','/api/user/login','22.74.142.137');
INSERT?INTO?TABLE?test_sql.test8?VALUES('2016-11-09?14:19:23','/api/user/login','22.74.142.137');

查詢SQL

SELECT?ip,
???????count(*)?AS?cnt
FROM?test_sql.test8
WHERE?date_format(date,'yyyy-MM-dd?HH')?>=?'2016-11-09?14'
??AND?date_format(date,'yyyy-MM-dd?HH')?'2016-11-09?15'
??AND?interface='/api/user/login'
GROUP?BY?ip
ORDER?BY?cnt?desc
LIMIT?10;?????????????

第九題

需求

有一個充值日志表credit_log，字段如下：

`dist_id`?int??'區(qū)組id',
`account`?string??'賬號',
`money`?int???'充值金額',
`create_time`?string??'訂單時間'

請寫出SQL語句，查詢充值日志表2019年01月02號每個區(qū)組下充值額最大的賬號，要求結(jié)果：
區(qū)組id，賬號，金額，充值時間????????

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test9(
????????????dist_id?string?COMMENT?'區(qū)組id',
????????????account?string?COMMENT?'賬號',
???????????`money`?decimal(10,2)?COMMENT?'充值金額',
????????????create_time?string?COMMENT?'訂單時間');

INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','11',100006,'2019-01-02?13:00:01');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','22',110000,'2019-01-02?13:00:02');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','33',102000,'2019-01-02?13:00:03');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','44',100300,'2019-01-02?13:00:04');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','55',100040,'2019-01-02?13:00:05');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','66',100005,'2019-01-02?13:00:06');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','77',180000,'2019-01-03?13:00:07');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','88',106000,'2019-01-02?13:00:08');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','99',100400,'2019-01-02?13:00:09');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','12',100030,'2019-01-02?13:00:10');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','13',100003,'2019-01-02?13:00:20');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','14',100020,'2019-01-02?13:00:30');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','15',100500,'2019-01-02?13:00:40');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','16',106000,'2019-01-02?13:00:50');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('1','17',100800,'2019-01-02?13:00:59');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('2','18',100800,'2019-01-02?13:00:11');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('2','19',100030,'2019-01-02?13:00:12');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('2','10',100000,'2019-01-02?13:00:13');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('2','45',100010,'2019-01-02?13:00:14');
INSERT?INTO?TABLE?test_sql.test9?VALUES?('2','78',100070,'2019-01-02?13:00:15');????????????????

查詢SQL

WITH?TEMP?AS
??(SELECT?dist_id,
??????????account,
??????????sum(`money`)?sum_money
???FROM?test_sql.test9
???WHERE?date_format(create_time,'yyyy-MM-dd')?=?'2019-01-02'
???GROUP?BY?dist_id,
????????????account)
SELECT?t1.dist_id,
???????t1.account,
???????t1.sum_money
FROM
??(SELECT?temp.dist_id,
??????????temp.account,
??????????temp.sum_money,
??????????rank()?over(partition?BY?temp.dist_id
??????????????????????ORDER?BY?temp.sum_money?DESC)?ranks
???FROM?TEMP)?t1
WHERE?ranks?=?1?????????????

第十題

需求

有一個賬號表如下，請寫出SQL語句，查詢各自區(qū)組的money排名前十的賬號（分組取前10）
dist_id?string??'區(qū)組id',
account?string??'賬號',
gold?????int????'金幣'????????????????

實(shí)現(xiàn)

數(shù)據(jù)準(zhǔn)備

CREATE?TABLE?test_sql.test10(
????`dist_id`?string?COMMENT?'區(qū)組id',
????`account`?string?COMMENT?'賬號',
????`gold`?int?COMMENT?'金幣'
);

INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','77',18);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','88',106);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','99',10);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','12',13);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','13',14);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','14',25);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','15',36);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','16',12);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('1','17',158);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('2','18',12);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('2','19',44);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('2','10',66);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('2','45',80);
INSERT?INTO?TABLE?test_sql.test10?VALUES?('2','78',98);????????????

查詢SQL

SELECT?dist_id,
???account,
???gold
FROM
(SELECT?dist_id,
??????account,
??????gold,
??????row_number?()?over?(PARTITION?BY?dist_id
??????????????????????????ORDER?BY?gold?DESC)?rank
FROM?test_sql.test10)?t
WHERE?rank?<=?10