大数据开发面试的总结-第三篇

(1)、sql 分组取每组的前n条或每组的n%（百分之n)的数据

1）按UserID分组查询出每组条数，rn从1递增；
SELECT * ,ROW_NUMBER() OVER(partition by b.UserID order by b.UserID ) rn from b

2）查询分组后每个user 5%的数据；
with temp as

(

–当数量<1的时候就取一条，且四舍五入取整，SelCnt就为每组取的数据条数(对应row_number排序最大数)；

SELECT b.UserID,cast(ROUND(CASE WHEN COUNT(1)*0.05<1 THEN 1 ELSE COUNT(1)*0.05 END,0) AS INT) SelCnt from b

)
3）关联临时表取数，顺序数小于每组条数(对应row_number排序最大数)

SELECT * ,ROW_NUMBER() OVER(partition by b.UserID order by b.UserID ) rn from b

inner join temp on b.UserID=temp.UserID

where b.rn<=temp.SelCnt

(2)、sql 分组取每组的前n条或每组的n%（百分之n~百分之m,例如取20%至30%)的数据

1）同上。
2）根据20%及30%算出对应的排序边界值。
with temp as(
SELECT b.UserID,cast(ROUND(CASE WHEN COUNT(1)*0.2<1 THEN 1 ELSE COUNT(1)*0.2 END,0) AS INT) SelCntStart, cast(ROUND(CASE WHEN COUNT(1)*0.3<1 THEN 1 ELSE COUNT(1)*0.3 END,0) AS INT) SelCntEnd
from b
)
3）根据排序区间值取数。
SELECT * ,ROW_NUMBER() OVER(partition by b.UserID order by b.UserID ) rn from b

inner join temp on b.UserID=temp.UserID

where b.rn<=temp.SelCntStart and b.rn<=temp.SelCntEnd

(3)、算出用户购买时间的平均间隔、最长间隔及最长间隔对应的时间

1）LAG和LEAD函数介绍
LAG
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
参数1为列名，参数2为往上第n行（可选，默认为1），参数3为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

LEAD
与LAG相反
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
参数1为列名，参数2为往下第n行（可选，默认为1），参数3为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

2）row_number(连续，无重复),rank(不连续),dense_rank(连续，可能有重复)的区别

row_number的用途非常广泛，排序最好用它，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复
rank函数用于返回结果集的分区内每行的排名，行的排名是相关行之前的排名数加一。

dense_rank函数的功能与rank函数类似，dense_rank函数在生成序号时是连续的，而rank函数生成的序号有可能不连续；

用lead函数，窗口：基于用户分区后对用户购买时间排序：

3）计算用户购买的时间间隔
with t1 as (select userid,
time as stime, #当前时间
lead(time) over(partition by userid order by time) etime, #当前时间最近的下一个时间
UNIX_TIMESTAMP(lead(time) over(partition by userid order by time),‘yyyy-MM-dd HH:mm:ss’)- UNIX_TIMESTAMP(time,‘yyyy-MM-dd HH:mm:ss’) period
from test.user_log),

4）计算用户购买平均间隔
select userid,
avg(period) as period
from t1 group by userid;

5）计算用户最长间隔及最长间隔对应的时间
with t2 as (select
userid,stime, stime，period
rank over(partition by userid order by period desc) as rn
from t1 ) select userid,stime, stime,period from t2 where rn = 1

6）若只计算用户购买的最长时间间隔，还可：
select userid,
max(period) as period
from t1 group by userid;