寫在開頭：

本章是Hive教程第六部分，著重于歸納SQL編寫。

文章內容輸出來源：拉勾教育大數據高薪訓練營。

本章將介紹Hive中常見的面試題和自己的解答思路，以供大家訓練和記憶。

SQL面試題

1、求連續7天登錄的用戶

— 數據。uid dt status(1 正常登錄，0 異常)

1 2019-07-11 1 1 2019-07-12 1 1 2019-07-13 1 1 2019-07-14 1 1 2019-07-15 1 1 2019-07-16 1 1 2019-07-17 1 1 2019-07-18 1 2 2019-07-11 1 2 2019-07-12 1 2 2019-07-13 0 2 2019-07-14 1 2 2019-07-15 1 2 2019-07-16 0 2 2019-07-17 1 2 2019-07-18 0 3 2019-07-11 1 3 2019-07-12 1 3 2019-07-13 1 3 2019-07-14 0 3 2019-07-15 1 3 2019-07-16 1 3 2019-07-17 1 3 2019-07-18 1

思路：典型的連續值求解問題，可以按照以下思路進行求解

使用 row_number 在組內給數據編號(rownum)

某個列值 – rownum = gid，得到結果可以作為后面分組計算的依據（這個某個列值，該題目問日期是否連續，所以拿日期這列）

根據求得的gid，作為分組條件，求最終結果

因為求的是連續七天登錄的用戶，所以這個列值選dt,

所以先寫sql：（date_sub為dt與rownum的差值）

select uid,dt,status, date_sub(dt,row_number() over(partition by uid order by dt)) gid from ulogin where status =1

首先來看下結果：

從上述步驟中我們需要按照gid和uid進行分組，然后計算出相同gid的個數，然后再選出大于7的數據即可。所以在原先基礎上在套一層select查詢，做條件篩選，所以完整SQL這樣寫：

select uid,count(*) countlogin from (select uid,dt,status, date_sub(dt,row_number() over(partition by uid order by dt))gid from ulogin where status =1) tmp group by uid,gid having countlogin >=7;

可以看出連續登陸七天以上的用戶只有uid，而且uid連續登陸了8天。

2.編寫sql語句實現每班前三名，分數一樣并列，同時求出前三名按名次排序的分差（TopN問題）

— 數據。sid class score

1 1901 90 2 1901 90 3 1901 83 4 1901 60 5 1902 66 6 1902 23 7 1902 99 8 1902 67 9 1902 87

— 待求結果數據如下：

class score rank lagscore 1901 90 1 0 1901 90 1 0 1901 83 2 -7 1901 60 3 -23 1902 99 1 0 1902 87 2 -12 1902 67 3 -20

從結果要求上可以看出，首先要用到排序函數排除順序，然后使用序列函數將數據整體下移一行，然后才可以相減算差值。而且可以看到需要按class分組。

首先我們寫出有排名函數的sql：

select class,score, dense_rank() over(partition by class order by score desc) rank from stu

然后將score下移一行，將下移后的數據額外增加一列lagscore，并指定按照class分區，按照score降序排序。

select class,score, dense_rank() over(partition by class order by score desc) rank, lag(score) over(partition by class order by score desc) lagscore from stu

從上圖中我們可以看到數據下移后會有null值，我們可以使用nvl函數進行處理，接下來我們處理差值問題，我們可以直接用score列減去lag(score) over(partition by class order by score desc)這行sql。如下所示:

select class,score, dense_rank() over(partition by class order by score desc) rank, nvl(score-lag(score) over(partition by class order by score desc),0) lagscore from stu

接下來按照題目要求，要前三名的數據。那么我們可以再嵌套一層select語句，加上where條件完成：

select class,score,rank,lagscore from (select class,score, dense_rank() over(partition by class order by score desc) rank, nvl(score-lag(score) over(partition by class order by score desc),0) lagscore from stu) tmp where rank<=3;

從上可以看出結果。

3.綜合八道題：

現在有三張表：

第一張表的部分數據

第二張表的部分數據

第三張表部分數據

1、按年統計銷售額

思路：使用join連接saledetail和sale表，連接條件為orderid，又因為按年統計，所以要按照年分組。這里使用year函數獲取年份，sum函數進行求和，求和完成后除掉10000，意思單位以萬元計，再使用round函數保留小數點2位。

SELECT year(B.dt) year, round(sum(A.amount)/10000, 2) amount FROM saledetail A join sale B on A.orderid=B.orderid group by year(B.dt);

2、銷售金額在 10W 以上的訂單

思路：按照orderid做分組，然后求和，求出和后選出金額大于十萬的數據。

SELECT orderid, round(sum(amount), 2) amount FROM saledetail group by orderid having sum(amount) > 100000

3、每年銷售額的差值

思路：先求出每一年的銷售額，按照年分組，然后求和，思路與第一題一致，因為要求差值，所以要用到某一列去減序列函數，所以將第一步得到的結果作為表t1，然后以t1表作為基表去求差值，用total-lag函數完成運行。

with t1 as (select year(s.dt) year,round(sum(amount)/10000,2) total from saledetail st join sale s on st.orderid=s.orderid group by year(s.dt) ) select year, round(total - lag(total) over(order by year),2) diff from t1;

4、年度訂單金額前10位（年度、訂單號、訂單金額、排名）

思路：連續值求解問題，我們可以先求出年度訂單的總金額，也就是用年和訂單id來分組，用sum求和。然后作為基表去查詢排名函數dense_rank，求得排好序的數據表。最后再作為基表添加where過濾條件得到結果。

with t1 as( select year(dt) dt,s.orderid orderid,sum(amount) total from saledetail sd join sale s on sd.orderid=s.orderid group by year(dt),s.orderid ), t2 as( select dt,orderid,total, dense_rank() over(partition by dt order by total desc) rank from t1 ) select dt,orderid,total,rank from t2 where rank<=10

部分結果：

5、季度訂單金額前10位（年度、季度、訂單id、訂單金額、排名）

思路：比上一題增加了季度，季度在日期表中有，所以這里要三表連接，然后再使用排名函數dense_rank()按照年和季度分區，按照金額降序排序，最后在嵌套一層select語句即可完成。

with tmp as ( select C.year, C.quat, A.orderid, round(sum(B.amount), 2) amount from sale A join saledetail B on A.orderid=B.orderid join dimdate C on A.dt=C.dt group by C.year, C.quat, A.orderid ) select year, quat, orderid, amount, rank from ( select year, quat, orderid, amount, dense_rank() over (partition by year, quat order by amount desc) rank from tmp ) tmp1 where rank <= 10;

這里展示部分結果：

6.求所有交易日中訂單金額最高的前10位

思路：TopN問題，首先根據日期和訂單id分組，使用sum函數求出總金額，然后使用dense_rank()排名函數根據總金額降序排序，最后使用where條件選出相應的數據。

with tmp as ( select A.dt, A.orderid, round(sum(B.amount), 2) amount from sale A join saledetail B on A.orderid=B.orderid group by A.dt, A.orderid ) select dt, orderid, amount, rank from ( select dt, orderid, amount, dense_rank() over(order by amount desc) rank from tmp ) tmp1 where rank <= 10;

7、每年度銷售額最大的交易日

思路：首先按年分組，使用sum函數求和，然后再使用max函數挑出每年最大的交易時間。

with tmp as ( select A.dt, round(sum(B.amount), 2) amount from sale A join saledetail B on A.orderid=B.orderid group by A.dt ) select year(dt) year, max(amount) dayamount from tmp group by year(dt);

8、年度最暢銷的商品(即每年銷售金額最大的商品)

思路：現根據年和商品分組，使用sum求出總金額，然后使用排名函數dense_rank()排名，按照年分區，按照總金額降序排序。因為題目要最暢銷商品，所以where條件要rank=1。

with tmp as ( select year(B.dt) year, goods, round(sum(amount),2) amount from saledetail A join sale B on A.orderid=B.orderid group by year(B.dt), goods ) select year, goods, amount from (select year, goods, amount, dense_rank() over (partition by year order by amount desc) rank from tmp) tmp1 where rank = 1;

寫在結尾：

這里歸納了常見的sql面試題，需要大家記憶連續值求解和TopN問題，當然也需要不斷理解相應的業務來變化sql代碼。

創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎

總結

以上是生活随笔為你收集整理的sql 分组求和_数据仓库工具–Hive（归纳笔记第六部分:SQL练习）的全部內容，希望文章能夠幫你解決所遇到的問題。

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