这是昨晚参加快手大数据岗位面试，面试官围绕一张表给出3个需求，在此讲解这3个需求如何实现。

需求

围绕下面这张表

  T1
 user_id   score   dt
 1         90      2021-01-01
 2         100     2021-01-01
 3         80      2021-01-01
 4         95      2021-01-01
 1         90      2021-01-02
 2         100     2021-01-02
 3         80      2021-01-03
 4         95      2021-01-03

分别求:

1. 大于平均值的记录
2. 截至到当天每个人大于平均值的累积记录
3. 截止到当天每个人连续大于平均值的记录

1、计算大于平均值的记录

很容易理解，比如整张表的平均分为80，筛选出这张表中的分数大于80分的记录。
需要注意，下面这个语句在MySQL中可以执行，但是在hive中报错。

1

select user_id, score, dt from T1 where score >= (select avg(score) from T1);

报错：FAILED: SemanticException Line 0:-1 Unsupported SubQuery Expression ‘score’: Only SubQuery expressions that are top level conjuncts are allowed.
原因是hive中目前并不支持这中方式。从查询效率考虑，目前hive(2.3版本)子查询中仅支持: IN, NOT IN, EXISTS, and NOT EXISTS四种比较方式，更多请参考Subquery restrictions。
所以我们借用窗口函数实现:

1
2
3
4
5
6
7
8
9
10
11

select user_id, score, dt
from 
    (
        select
            user_id,
            score,
            dt,
            avg(score) over() avg_score
        from T1
    ) t2
where t2.score > t2.avg_score;

输出结果:

user_id score   dt
4       95      2021-01-03
2       100     2021-01-02
4       95      2021-01-01
2       100     2021-01-01

2、截至到当天每个人大于平均值的累积记录

比如，用户1在1号大于平均值，在2号小于平均值，3号大于平均值，那么我们需要的结果是1号为1，2号为1，3号为2。
这道题也借助窗口函数实现。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

# 1、标识每行是否大于平均值
select user_id, score, dt, if(t2.score > t2.avg_score, 1, 0) flg
from 
    (
        select
            user_id,
            score,
            dt,
            avg(score) over() avg_score
        from T1
    ) t2
where t2.score > t2.avg_score;t3
# 2、基于上表做开窗累积
select
    user_id,
    score,
    dt,
    sum(flg) over(partition by user_id order by dt) acct
from ()t3;

# 最终结果
select
    user_id,
    score,
    dt,
    sum(flg) over(partition by user_id order by dt) acct
from (select user_id, score, dt, if(t2.score > t2.avg_score, 1, 0) flg
from 
    (
        select
            user_id,
            score,
            dt,
            avg(score) over() avg_score
        from T1
    ) t2
where t2.score > t2.avg_score)t3;

输出结果：

user_id score   dt      acct
2       100     2021-01-01      1
2       100     2021-01-02      2
4       95      2021-01-01      1
4       95      2021-01-03      2

3、截止到当天每个人连续（非连续天数）大于平均值的记录

个人感觉这道题目非常变态!难度远大于求最大连续登录天数这类需求。
比如，如下数据：

user_id    score    dt              result(待计算)
1       95          2020-01-20        1
1       95          2020-02-01        2
1       100         2020-03-10        3
1       80          2020-04-20        0
1       98          2020-05-01        1
1       99          2020-06-20        2

• dt表示每次测试的时间
• result是待计算的结果列，原表中没有
• 含义是求连续的大于平均分的测验，如果中间有不大于的，后续再重新计算。

碰到这类题目，唯一能够确定的是，会用到排序函数的一种，row_number、rank或者dense_rank，离开这3个函数无法解这类问题。
在尝试n种方法后，这里给出一个求解方法：

user_id    score    dt           flg    rn   current_minus     real_minus      result(待计算)
1       95          2020-01-20    1     1       0                 0             1
1       95          2020-02-01    1     2       0                 0             2
1       100         2020-03-10    1     3       0                 0             3
1       80          2020-04-20    0     4       -4               -4             0
1       98          2020-05-01    1     5       0                -4             1
1       99          2020-06-20    1     6       0                -4             2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

# 1 计算flg和rn
create temporary table t3
    as
select
    user_id,
    score,
    dt,
    if(t2.score > t2.avg_score, 1, 0) flg,
    row_number() over(partition by user_id order by dt) rn
from 
    (
        select
            user_id,
            score,
            dt,
            avg(score) over() avg_score
        from T1
    ) t2;
# 2 计算current_minus
create temporary table t4
    as
select
    user_id,
    score,
    dt,
    rn,
    if(flg=1,0, negative(rn)) current_minus
from  t3;
# 3 计算real_minus
create temporary table t5
    as
select
    user_id,
    score,
    dt,
    rn,
    min(current_minus) over(partition by user_id order by dt) real_minus
from t4;
# 4、将rn同real_minus相加，得出我们的结果
select
    user_id,
    score,
    dt,
    rn + real_minus acct
from t5;

测试:
原表：

输出结果:

总结

• 窗口函数在hive中非常重要，一定要掌握，推荐视频学习（B站的hive窗口教学视频）
• 涉及连续求值的，第一时间要想到三个排序函数
• 有时候可以反向思考查找规律，比如需求三，我们知道想要怎样的结果，那么我们可以反过来推规律， 可能效率更高。