珠海网站建设外包,百度商家入驻怎么做,无代码做网站,济宁网站建设公司电话写在开头#xff1a;本章是Hive教程第六部分#xff0c;着重于归纳SQL编写。文章内容输出来源#xff1a;拉勾教育大数据高薪训练营。本章将介绍Hive中常见的面试题和自己的解答思路#xff0c;以供大家训练和记忆。SQL面试题1、求连续7天登录的用户— 数据。uid dt status…写在开头本章是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,所以先写sqldate_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 score1 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分组。首先我们写出有排名函数的sqlselect 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 rank3;从上可以看出结果。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.orderidB.orderid
group by year(B.dt);2、销售金额在 10W 以上的订单思路按照orderid做分组然后求和求出和后选出金额大于十万的数据。SELECT orderid, round(sum(amount), 2) amount
FROM saledetail
group by orderid
having sum(amount) 1000003、每年销售额的差值思路先求出每一年的销售额按照年分组然后求和思路与第一题一致因为要求差值所以要用到某一列去减序列函数所以将第一步得到的结果作为表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.orderids.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.orderids.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 rank10部分结果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.orderidB.orderid
join dimdate C on A.dtC.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.orderidB.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.orderidB.orderid
group by A.dt
)
select year(dt) year, max(amount) dayamount
from tmp
group by year(dt); 8、年度最畅销的商品(即每年销售金额最大的商品) 思路现根据年和商品分组使用sum求出总金额然后使用排名函数dense_rank()排名按照年分区按照总金额降序排序。因为题目要最畅销商品所以where条件要rank1。with tmp as (
select year(B.dt) year, goods, round(sum(amount),2) amount
from saledetail A join sale B on A.orderidB.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代码。
