【面试】Hive SQL面试题总结

1. 最大连胜次数

输入：

输出：

输入数据为：用户ID、比赛日期、比赛结果，求用户最大连胜次数。
题解：

select
    uid,
    max(total) as cnt
from
    (
    select
        uid,
        a.r1-a.r2 as r,
        sum(num) as total
    from
        (
        SELECT
            uid,
            if(result = 'win', 1, 0) as num,
            ROW_NUMBER() over(partition by uid order by play_day) r1,
            ROW_NUMBER() OVER(partition by uid, result order by play_day) as r2
        from
            s_games) a
    group by
        uid,
        a.r1-a.r2) b
group by
    b.uid;

2. 最多连胜天数

输入：

输出：

输入数据为：用户ID、比赛日期、比赛结果，求用户最多连赢天数。比如用户1，在17和18号连赢了两天。<br />题解：

select
    uid,
    max(total) as cnt
from
    (
    select
        uid,
        a.dif_day-a.r1 as r,
        sum(num) as total
    from
        (
        SELECT
            uid,
      if(result = 'win', 1, 0) as num,
      datediff(play_day,'1970-01-01') as dif_day,
            ROW_NUMBER() OVER(partition by uid, result order by play_day) as r1
        from
            s_games) a
    group by
        uid,
        a.dif_day-a.r1) b
group by
    b.uid;

3. 同时在线最大人数

输入：

输出：

思路：先对用户登录数据进行打平，登录操作记为1，登出操作记为-1，然后利用sum开窗函数计算累积值。
题解：

select
    max(sum) as num
from
    (
    SELECT
        uid,
        dt,
        num,
        SUM(a.num) over(partition by 1 order by dt) as sum
    from
        (
        SELECT uid, start_time as dt, 1 as num from live
    union all
        select uid, end_time as dt, -1 as num from live
    ) a
    order by
        dt) b;

4. 互相关注好友

输入：

输出：

题解：

SELECT
    a.*
from
    (
    SELECT
        uid,
        friend_id
    from
        `user`
    group by
        uid,
        friend_id
) a
join (
    select
        uid,
        friend_id
    from
        `user`
    group by
        uid,
        friend_id
) b
on
    a.uid = b.friend_id
    and a.friend_id = b.uid
where
    a.uid > b.uid;