當前位置：首頁 > 人文社科 > 生活经验 >内容正文

生活经验

❤️手撕这十道HiveSQL题还不能吊打面试官，却能保你不被吊打❤️【推荐收藏】

發布時間：2023/11/28 生活经验 44 豆豆

生活随笔收集整理的這篇文章主要介紹了 ❤️手撕这十道HiveSQL题还不能吊打面试官，却能保你不被吊打❤️【推荐收藏】小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

全網最詳細的大數據Hive文章系列，強烈建議收藏加關注！

?

新文章都已經列出歷史文章目錄，幫助大家回顧前面的知識重點。

系列歷史文章

前言

HiveSQL十題

第一題

1、需求

2、數據準備

3、查詢SQL

4、執行結果

第二題

1、需求

2、數據準備

3、查詢SQL實現

4、執行結果

第三題

1、需求

2、數據準備

3、查詢SQL

4、?執行結果

第四題

1、需求

2、數據準備

3、查詢SQL?

4、執行結果

第五題

1、需求

2、數據準備

3、查詢SQL

?4、執行結果

第六題

1、需求

2、數據準備

3、查詢SQL

4、執行結果

第七題

1、需求

2、數據準備

?3、查詢SQL

4、執行結果

第八題?

1、需求

2、數據準備

3、查詢SQL?

4、執行結果?

第九題

1、需求

2、數據準備

3、查詢SQL?

4、執行結果?

第十題

1、需求

2、數據準備

3、?查詢SQL

4、執行結果

最后

系列歷史文章

2021年大數據Hive（十二）：Hive綜合案例！！！

2021年大數據Hive（十一）：Hive調優

2021年大數據Hive（十）：Hive的數據存儲格式

2021年大數據Hive（九）：Hive的數據壓縮

2021年大數據Hive（八）：Hive自定義函數

2021年大數據Hive（七）：Hive的開窗函數

2021年大數據Hive（六）：Hive的表生成函數

2021年大數據Hive（五）：Hive的內置函數（數學、字符串、日期、條件、轉換、行轉列）

2021年大數據Hive（四）：Hive查詢語法

2021年大數據Hive（三）：手把手教你如何吃透Hive數據庫和表操作（學會秒變數倉大佬）

2021年大數據Hive（二）：Hive的三種安裝模式和MySQL搭配使用

2021年大數據Hive（一）：Hive基本概念

前言

?2021大數據領域優質創作博客，帶你從入門到精通，該博客每天更新，逐漸完善大數據各個知識體系的文章，幫助大家更高效學習。

下面是基礎的十道HiveSQL題，必須都能手撕出來，不然面試得涼涼了，離吊打面試官你還差100道HiveSQL題，先別想這么多，給自己一個小目標，搞定這十道題！

HiveSQL十題

第一題

1、需求

我們有如下的用戶訪問數據

userId	visitDate	visitCount
u01	2021/1/21	5
u02	2021/1/23	6
u03	2021/1/22	8
u04	2021/1/20	3
u01	2021/1/23	6
u01	2021/2/21	8
u02	2021/1/23	6
u01	2021/2/22	4

要求使用SQL統計出每個用戶的累積訪問次數，如下表所示：

用戶id	月份	小計	累計
u01	2021-01	11	11
u01	2021-02	12	23
u02	2021-01	12	12
u03	2021-01	8	8
u04	2021-01	3	3

2、數據準備

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', '2021/1/21', 5 ),( 'u02', '2021/1/23', 6 ),( 'u03', '2021/1/22', 8 ),( 'u04', '2021/1/20', 3 ),( 'u01', '2021/1/23', 6 ),( 'u01', '2021/2/21', 8 ),( 'u02', '2021/1/23', 6 ),( 'u01', '2021/2/22', 4 );

3、查詢SQL

SELECTt2.userid,t2.visitmonth,subtotal_visit_cnt,sum( subtotal_visit_cnt ) over ( PARTITION BY userid ORDER BY visitmonth ) AS total_visit_cnt 
FROM(SELECTuserid,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 BYuserid,visitmonth ) t2 
ORDER BYt2.userid,t2.visitmonth;

4、執行結果

第二題

1、需求

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

user_id	shop
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

請統計：

（1）每個店鋪的UV（訪客數）（2）每個店鋪訪問次數top3的訪客信息。輸出店鋪名稱、訪客id、訪問次數

2、數據準備

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' );

3、查詢SQL實現

（1） 方式1：

# 每個店鋪的UV（訪客數）
SELECT shop,count(DISTINCT user_id) FROM test_sql.test2 GROUP BY shop

方式2：

#每個店鋪的UV（訪客數）
SELECTt.shop,count(*) 
FROM( SELECT user_id, shop FROM test_sql.test2 GROUP BY user_id, shop ) t 
GROUP BYt.shop;

（2）?

#每個店鋪訪問次數top3的訪客信息。輸出店鋪名稱、訪客id、訪問次數 
SELECTt2.shop,t2.user_id,t2.cnt 
FROM(SELECTt1.*,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 
WHERErank <= 3;

4、執行結果

（1）

（2）

第三題

1、需求

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

2、數據準備

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( '2021-01-01', '10029028', '1000003251', 33.57 );
INSERT INTO TABLE test_sql.test3
VALUES( '2021-01-01', '10029029', '1000003251', 33.57 );
INSERT INTO TABLE test_sql.test3
VALUES( '2021-01-01', '100290288', '1000003252', 33.57 );
INSERT INTO TABLE test_sql.test3
VALUES( '2021-02-02', '10029088', '1000003251', 33.57 );
INSERT INTO TABLE test_sql.test3
VALUES( '2021-02-02', '100290281', '1000003251', 33.57 );
INSERT INTO TABLE test_sql.test3
VALUES( '2021-02-02', '100290282', '1000003253', 33.57 );
INSERT INTO TABLE test_sql.test3
VALUES( '2021-11-02', '10290282', '100003253', 234 );
INSERT INTO TABLE test_sql.test3
VALUES( '2018-11-02', '10290284', '100003243', 234 );

3、查詢SQL

（1）給出 2021年每個月的訂單數、用戶數、總成交金額。

SELECTt1.mon,count( t1.order_id ) AS order_cnt,count( DISTINCT t1.user_id ) AS user_cnt,sum( amount ) AS total_amount 
FROM(SELECTorder_id,user_id,amount,date_format( dt, 'yyyy-MM' ) mon FROMtest_sql.test3 WHEREdate_format( dt, 'yyyy' ) = '2021' ) t1 
GROUP BYt1.mon;

（2）給出2021年11月的新客數(指在11月才有第一筆訂單)

SELECTcount( user_id ) 
FROMtest_sql.test3 
GROUP BYuser_id 
HAVINGdate_format( min( dt ), 'yyyy-MM' )= '2021-11';

4、?執行結果

（1）

（2）

第四題

1、需求

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

2、數據準備

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' );

3、查詢SQL?

方式1

SELECTt2.age_phase,sum( t1.cnt ) AS view_cnt 
FROM( SELECT user_id, count(*) cnt FROM test_sql.test4log GROUP BY user_id ) t1JOIN (SELECTuser_id,CASEWHEN 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 FROMtest_sql.test4user ) t2 ON t1.user_id = t2.user_id GROUP BYt2.age_phase;

方式2

SELECTconcat( phase - 10, '-', phase ),sum( cnt ) sum_movies 
FROM(SELECT*,ceil( age / 10 ) * 10 phase FROMtest4user aJOIN ( SELECT user_id, count( url ) cnt FROM test4log GROUP BY user_id ) b ON a.user_id = b.user_id ) c 
GROUP BYc.phase;

4、執行結果

方式1

?方式2

第五題

1、需求

有日志如下，請寫出代碼求得所有用戶和活躍用戶的總數及平均年齡。（活躍用戶指連續兩天都有訪問記錄的用戶）

日期用戶年齡 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

2、數據準備

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 );

3、查詢SQL

方式1

SELECTsum( 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(SELECT0 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(SELECTuser_id,max( age ) age FROM(SELECTuser_id,max( age ) age FROM(SELECTuser_id,age,date_sub( dt, rank ) flag FROM(SELECTdt,user_id,max( age ) age,row_number() over ( PARTITION BY user_id ORDER BY dt ) rank FROMtest_sql.test5 GROUP BYdt,user_id ) t1 ) t2 GROUP BYuser_id,flag HAVINGcount(*) >= 2 ) t3 GROUP BYuser_id ) t4 UNION ALLSELECTcount(*) 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;

方式2

SELECT* 
FROM(SELECTcount( user_id ) total_cnt_users,avg( age ) total_avg_age FROM( SELECT user_id, max( age ) age FROM test5 GROUP BY user_id ) g ) hCROSS JOIN (SELECTcount( user_id ) hot_users_count,avg( age ) hot_age_avg FROM(SELECTd.user_id,max( d.age ) age FROM(SELECTuser_id,max( age ) age,count( 1 ) cnt FROM(SELECT*,date_sub( dt, rank ) dt2 FROM(SELECTuser_id,dt,max( age ) age,ROW_NUMBER() over ( PARTITION BY a.user_id ORDER BY a.dt ) rank FROM( SELECT DISTINCT dt, age, user_id FROM test5 ) a GROUP BYa.user_id,a.dt ) b ) c GROUP BYc.user_id,c.dt2 HAVINGcnt > 1 ) d GROUP BYd.user_id ) e ) f ON 1 = 1;

?4、執行結果

方式1

?方式2

第六題

1、需求

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

2、數據準備

CREATE TABLE test_sql.test6 ( userid string, money DECIMAL ( 10, 2 ), paymenttime string, orderid string );
INSERT INTO TABLE test_sql.test6
VALUES( '001', 100, '2021-10-01', '123' );
INSERT INTO TABLE test_sql.test6
VALUES( '001', 200, '2021-10-02', '124' );
INSERT INTO TABLE test_sql.test6
VALUES( '002', 500, '2021-10-01', '125' );
INSERT INTO TABLE test_sql.test6
VALUES( '001', 100, '2021-11-01', '126' );

3、查詢SQL

SELECTuserid,paymenttime,money,orderid 
FROM(SELECTuserid,money,paymenttime,orderid,row_number() over ( PARTITION BY userid ORDER BY paymenttime ) rank FROMtest_sql.test6 WHEREdate_format( paymenttime, 'yyyy-MM' ) = '2021-10' ) t 
WHERErank = 1;

4、執行結果

第七題

1、需求

現有圖書管理數據庫的三個數據模型如下： 圖書（數據表名：BOOK）

序號	字段名稱	字段描述	字段類型
1	BOOK_ID	總編號	文本
2	SORT	分類號	文本
3	BOOK_NAME	書名	文本
4	WRITER	作者	文本
5	OUTPUT	出版單位	文本
6	PRICE	單位	數值（保留小數點后2位）

讀者（數據表名：READER）

序號	字段名稱	字段描述	字段類型
1	READER_ID	借書證號	文本
2	COMPANY	單位	文本
3	NAME	姓名	文本
4	SEX	性別	文本
5	GRADE	職稱	文本
6	ADDR	地址	文本

借閱記錄（數據表名：BORROW LOG）

序號	字段名稱	字段描述	字段類型
1	READER_ID	借書證號	文本
2	BOOK_ID	總編號	文本
3	BORROW_DATE	借書日期	日期

（1）創建圖書管理庫的圖書、讀者和借閱三個基本表的表結構。請寫出建表語句。（2）找出姓李的讀者姓名（NAME）和所在單位（COMPANY）。（3）查找“高等教育出版社”的所有圖書名稱（BOOK_NAME）及單價（PRICE），結果按單價降序排序。（4）查找價格介于10元和20元之間的圖書種類(SORT）出版單位（OUTPUT）和單價（PRICE），結果按出版單位（OUTPUT）和單價（PRICE）升序排序。（5）查找所有借了書的讀者的姓名（NAME）及所在單位（COMPANY）。（6）求”科學出版社”圖書的最高單價、最低單價、平均單價。（7）找出當前至少借閱了2本圖書（大于等于2本）的讀者姓名及其所在單位。（8）考慮到數據安全的需要，需定時將“借閱記錄”中數據進行備份，請使用一條SQL語句，在備份用戶bak下創建與“借閱記錄”表結構完全一致的數據表BORROW_LOG_BAK.井且將“借閱記錄”中現有數據全部復制到BORROW_L0G_ BAK中。（9）現在需要將原Oracle數據庫中數據遷移至Hive倉庫，請寫出“圖書”在Hive中的建表語句（Hive實現，提示：列分隔符|；數據表數據需要外部導入：分區分別以month＿part、day＿part 命名）（10）Hive中有表A，現在需要將表A的月分區 202106中 user＿id為20000的user＿dinner字段更新為bonc8920，其他用戶user＿dinner字段數據不變，請列出更新的方法步驟。（Hive實現，提示：Hlive中無update語法，請通過其他辦法進行數據更新）

2、數據準備

（1）創建圖書表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', '機械工業出版社', '20' );
INSERT INTO TABLE test_sql.book
VALUES( '002', 'TP392', '數據庫', 'author12', '科學出版社', '15' );
INSERT INTO TABLE test_sql.book
VALUES( '003', 'TP393', '計算機網絡', 'author3', '機械工業出版社', '29' );
INSERT INTO TABLE test_sql.book
VALUES( '004', 'TP399', '微機原理', 'author4', '科學出版社', '39' );
INSERT INTO TABLE test_sql.book
VALUES( '005', 'C931', '管理信息系統', 'author5', '機械工業出版社', '40' );
INSERT INTO TABLE test_sql.book
VALUES( '006', 'C932', '運籌學', 'author6', '科學出版社', '55' );
INSERT INTO TABLE test_sql.book
VALUES( '007', 'C939', '大數據平臺架構與原型實現', 'author7', '高等教育出版社', '66' );

?（2）創建讀者表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', '網易', 'zhangsan', '女', 'ceo', 'addr5' );
INSERT INTO TABLE test_sql.reader
VALUES( '0006', '搜狐', 'lisi', '女', 'ceo', 'addr6' );
INSERT INTO TABLE test_sql.reader
VALUES( '0007', '美團', '李哥', '男', '大數據開發', 'addr7' );

（3）創建借閱記錄表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', '2021-10-14' );
INSERT INTO TABLE test_sql.borrow_log
VALUES( '0002', '001', '2021-10-13' );
INSERT INTO TABLE test_sql.borrow_log
VALUES( '0003', '005', '2021-09-14' );
INSERT INTO TABLE test_sql.borrow_log
VALUES( '0004', '006', '2021-08-15' );
INSERT INTO TABLE test_sql.borrow_log
VALUES( '0005', '003', '2021-10-10' );
INSERT INTO TABLE test_sql.borrow_log
VALUES( '0006', '004', '2021-12-13' );
INSERT INTO TABLE test_sql.borrow_log
VALUES( '0007', '003', '2021-10-16' );
INSERT INTO TABLE test_sql.borrow_log
VALUES( '0007', '008', '2021-10-16' );

?3、查詢SQL

（1）創建圖書管理庫的圖書、讀者和借閱三個基本表的表結構。請寫出建表語句。

CREATE TABLE test_sql.book (book_id string,`SORT` string,book_name string,writer string,OUTPUT string,
price DECIMAL ( 10, 2 ));
CREATE TABLE test_sql.reader ( reader_id string, company string, NAME string, sex string, grade string, addr string );
CREATE TABLE test_sql.borrow_log ( reader_id string, book_id string, borrow_date string );

（2）?找出姓李的讀者姓名（NAME）和所在單位（COMPANY）。

SELECT name, company FROM test_sql.reader WHERE name LIKE '李%';

（3）查找“高等教育出版社”的所有圖書名稱（BOOK_NAME）及單價（PRICE），結果按單價降序排序。

SELECTbook_name,price 
FROMtest_sql.book 
WHEREOUTPUT = "高等教育出版社" 
ORDER BYprice DESC;

（4）?查找價格介于10元和20元之間的圖書種類(SORT）出版單位（OUTPUT）和單價（PRICE），結果按出版單位（OUTPUT）和單價（PRICE）升序排序。

方式1

SELECTsort,output,price 
FROMtest_sql.book 
WHEREprice >= 10 AND price <= 20 
ORDER BYoutput,price;

?方式2

SELECTsort,output,price 
FROMbook 
WHEREprice BETWEEN 10 AND 20 
ORDER BYoutput ASC,price ASC;

（5）查找所有借了書的讀者的姓名（NAME）及所在單位（COMPANY）。

SELECTb.NAME,b.company 
FROMtest_sql.borrow_log aJOIN test_sql.reader b ON a.reader_id = b.reader_id;

（6）求”科學出版社”圖書的最高單價、最低單價、平均單價。

SELECTmax( price ),min( price ),avg( price ) 
FROMtest_sql.book 
WHEREOUTPUT = '科學出版社';

（7）找出當前至少借閱了2本圖書（大于等于2本）的讀者姓名及其所在單位。

SELECTb.NAME,b.company 
FROM( SELECT reader_id FROM test_sql.borrow_log GROUP BY reader_id HAVING count(*) >= 2 ) aJOIN test_sql.reader b ON a.reader_id = b.reader_id;

（8）考慮到數據安全的需要，需定時將“借閱記錄”中數據進行備份，請使用一條SQL語句，在備份用戶bak下創建與“借閱記錄”表結構完全一致的數據表BORROW_LOG_BAK.井且將“借閱記錄”中現有數據全部復制到BORROW_L0G_ BAK中。

CREATE TABLE test_sql.borrow_log_bak AS SELECT
* 
FROMtest_sql.borrow_log;

（9）現在需要將原Oracle數據庫中數據遷移至Hive倉庫，請寫出“圖書”在Hive中的建表語句（Hive實現，提示：列分隔符|；數據表數據需要外部導入：分區分別以month＿part、day＿part 命名）

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）Hive中有表A，現在需要將表A的月分區 202106 中 user＿id為20000的user＿dinner字段更新為bonc8920，其他用戶user＿dinner字段數據不變，請列出更新的方法步驟。（Hive實現，提示：Hlive中無update語法，請通過其他辦法進行數據更新）

方式1：配置hive支持事務操作，分桶表，orc存儲格式方式2：第一步找到要更新的數據，將要更改的字段替換為新的值，第二步找到不需要更新的數據，第三步將上兩步的數據插入一張新表中。

4、執行結果

（2）

（3）

（4）

（5）

（6）

（7）

（8）

（9）

第八題?

1、需求

有一個線上服務器訪問日志格式如下（用sql答題）

求11月9號下午14點（14-15點），訪問/api/user/login接口的top10的ip地址

2、數據準備

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' );

3、查詢SQL?

SELECTip,count(*) AS cnt 
FROMtest_sql.test8 
WHEREdate_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 BYip 
ORDER BYcnt DESC LIMIT 10;

4、執行結果?

第九題

1、需求

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

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

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

2、數據準備

CREATE TABLE test_sql.test9 ( dist_id string COMMENT '區組id', account string COMMENT '賬號', `money` DECIMAL ( 10, 2 ) COMMENT '充值金額', create_time string COMMENT '訂單時間' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '11', 100006, '2021-01-02 13:00:01' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '22', 110000, '2021-01-02 13:00:02' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '33', 102000, '2021-01-02 13:00:03' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '44', 100300, '2021-01-02 13:00:04' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '55', 100040, '2021-01-02 13:00:05' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '66', 100005, '2021-01-02 13:00:06' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '77', 180000, '2021-01-03 13:00:07' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '88', 106000, '2021-01-02 13:00:08' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '99', 100400, '2021-01-02 13:00:09' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '12', 100030, '2021-01-02 13:00:10' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '13', 100003, '2021-01-02 13:00:20' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '14', 100020, '2021-01-02 13:00:30' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '15', 100500, '2021-01-02 13:00:40' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '16', 106000, '2021-01-02 13:00:50' );
INSERT INTO TABLE test_sql.test9
VALUES( '1', '17', 100800, '2021-01-02 13:00:59' );
INSERT INTO TABLE test_sql.test9
VALUES( '2', '18', 100800, '2021-01-02 13:00:11' );
INSERT INTO TABLE test_sql.test9
VALUES( '2', '19', 100030, '2021-01-02 13:00:12' );
INSERT INTO TABLE test_sql.test9
VALUES( '2', '10', 100000, '2021-01-02 13:00:13' );
INSERT INTO TABLE test_sql.test9
VALUES( '2', '45', 100010, '2021-01-02 13:00:14' );
INSERT INTO TABLE test_sql.test9
VALUES( '2', '78', 100070, '2021-01-02 13:00:15' );

3、查詢SQL?

WITH TEMP AS (SELECTdist_id,account,sum( `money` ) sum_money FROMtest_sql.test9 WHEREdate_format( create_time, 'yyyy-MM-dd' ) = '2021-01-02' GROUP BYdist_id,account ) SELECTt1.dist_id,t1.account,t1.sum_money 
FROM(SELECTtemp.dist_id,temp.account,temp.sum_money,rank() over ( PARTITION BY temp.dist_id ORDER BY temp.sum_money DESC ) ranks FROMTEMP ) t1 
WHEREranks = 1;

4、執行結果?

第十題

1、需求

有一個賬號表如下，請寫出SQL語句，查詢各自區組的gold排名前十的賬號（分組取前10）

dist_id string? ?'區組id', account string? ?'賬號', gold int? ?'金幣'

2、數據準備

CREATE TABLE test_sql.test10 ( `dist_id` string COMMENT '區組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 );

3、?查詢SQL

SELECTdist_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 
WHERErank <= 10;

4、執行結果

最后

? 以上十道HiveSQL熟練掌握可以有效阻止被面試官的吊打，但是離吊打面試官的實力，還得需要經典100道Hive題加訓練，后續會繼續創作下一篇經典100道Hive題吊打面試官系列，強烈建議收藏加關注！

📢博客主頁：https://lansonli.blog.csdn.net
📢歡迎點贊 👍 收藏 ?留言 📝 如有錯誤敬請指正！
📢本文由 Lansonli 原創，首發于 CSDN博客🙉
📢大數據系列文章會每天更新，停下休息的時候不要忘了別人還在奔跑，希望大家抓緊時間學習，全力奔赴更美好的生活?

總結

以上是生活随笔為你收集整理的❤️手撕这十道HiveSQL题还不能吊打面试官，却能保你不被吊打❤️【推荐收藏】的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。

上一篇： ❤️让人心跳加速的陌陌案例，大数据必需学
下一篇： 2021年大数据HBase（八）：Apa

生活经验

❤️手撕这十道HiveSQL题还不能吊打面试官，却能保你不被吊打❤️【推荐收藏】

系列歷史文章

前言

HiveSQL十題

第一題

1、需求

2、數據準備

3、查詢SQL

4、執行結果

第二題

1、需求

2、數據準備

3、查詢SQL實現

4、執行結果

第三題

1、需求

2、數據準備

3、查詢SQL

4、?執行結果

第四題

1、需求

2、數據準備

3、查詢SQL?

4、執行結果

第五題

1、需求

2、數據準備

3、查詢SQL

?4、執行結果

第六題

1、需求

2、數據準備

3、查詢SQL

4、執行結果

第七題

1、需求

2、數據準備

?3、查詢SQL

4、執行結果

第八題?

1、需求

2、數據準備

3、查詢SQL?

4、執行結果?

第九題

1、需求

2、數據準備

3、查詢SQL?

4、執行結果?

第十題

1、需求

2、數據準備

3、?查詢SQL

4、執行結果

最后

總結

3、查詢SQL

4、執行結果

1、需求

2、數據準備

3、查詢SQL實現

3、查詢SQL

4、?執行結果

1、需求

2、數據準備

4、執行結果

1、需求

2、數據準備

3、查詢SQL

?4、執行結果

2、數據準備

3、查詢SQL

1、需求

4、執行結果

1、需求

2、數據準備

3、查詢SQL?

4、執行結果?

1、需求

3、查詢SQL?

4、執行結果?

2、數據準備

3、?查詢SQL

4、執行結果