愛了！經典 HiveSQL 面試題總結
第一題
需求
我們有如下的用戶訪問數據
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統計出每個用戶的累積訪問次數，如下表所示：
用戶id 月份 小計 累積
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實現
數據準備
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個京東店鋪，每個顧客訪客訪問任何一個店鋪的任何一個商品時都會產生一條訪問日誌，
訪問日誌存儲的表名爲Visit，訪客的用戶id爲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、訪問次數實現
數據準備
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 實現
(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。
數據樣例:2017-01-01,10029028,1000003251,33.57。
請給出sql進行統計:
(1)給出 2017年每個月的訂單數、用戶數、總成交金額。
(2)給出2017年11月的新客數(指在11月纔有第一筆訂單)實現
數據準備
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)，根據年齡段觀看電影的次數進行排序？實現
數據準備
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第五題
需求
有日誌如下，請寫出代碼求得所有用戶和活躍用戶的總數及平均年齡。（活躍用戶指連續兩天都有訪問記錄的用戶）
日期 用戶 年齡
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實現
數據準備
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實現
數據準備
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第七題
需求
現有圖書管理數據庫的三個數據模型如下：
圖書（數據表名：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的月分區 201505 中 user＿id爲20000的user＿dinner字段更新爲bonc8920，其他用戶user＿dinner字段數據不變，請列出更新的方法步驟。（Hive實現，提示：Hlive中無update語法，請通過其他辦法進行數據更新）實現
(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');
-- 創建讀者表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');
-- 創建借閱記錄表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 = '科學出版社';
(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支持事務操作，分桶表，orc存儲格式
方式2：第一步找到要更新的數據，將要更改的字段替換爲新的值，第二步找到不需要更新的數據，第三步將上兩步的數據插入一張新表中。第八題
需求
有一個線上服務器訪問日誌格式如下（用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點（14-15點），訪問/api/user/login接口的top10的ip地址實現
數據準備
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 '區組id',
`account` string '賬號',
`money` int '充值金額',
`create_time` string '訂單時間'
請寫出SQL語句，查詢充值日誌表2019年01月02號每個區組下充值額最大的賬號，要求結果：
區組id，賬號，金額，充值時間實現
數據準備
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,'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語句，查詢各自區組的money排名前十的賬號（分組取前10）
dist_id string '區組id',
account string '賬號',
gold int '金幣'實現
數據準備
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);查詢 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本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/KvmR2ftgPBP7MMurcROAFg