常见经典数仓面试题

1.去重连续列，只连续重复的第一行。

表如下：

a 	b 
1	10
2	11
3	11
4	11
5	12
6	12
7	13

拿出b列连续重复的第一个行。输出如下：

1	10
2	11
5	12
7	13

答案：

select 
	t1.a,
    t1.b
from ( select 1 a,
               10 b
     union all select 2 a,
               11 b
     union all select 3 a,
               11 b
     union all select 4 a,
               11 b
     union all select 5 a,
               12 b
     union all select 6 a,
               12 b
     union all select 7 a,
               13 b) t1
               left join
               (select 1 a,
               10 b
     union all select 2 a,
               11 b
     union all select 3 a,
               11 b
     union all select 4 a,
               11 b
     union all select 5 a,
               12 b
     union all select 6 a,
               12 b
     union all select 7 a,
               13 b) t2
               on t1.a = t2.a + 1
               where case when t1.b = t2.b then null else t1.b end is not null
               ;

2.SQL 求某天各个直播间同时最大在线人数与时刻。

表字段：

live_id 直播id,
userid  用户id,
date_stamp  时间戳,
entry_type  登陆状态，登陆(enter)和登出(out)

答案：

select live_id,
       date_stamp,
       cnt
  from (
        select live_id,
               date_stamp,
               cnt,
               rank() over(order by cnt desc) rn
          from (
                select live_id,
                       date_stamp,
                       sum(flag) over (order by date_stamp) as cnt
                  from (
                        select live_id,
                               userid,
                               date_stamp as date_stamp,
                               case when entry_type = 'enter' then 1
                                    when entry_type = 'out'   then -1
                                    else 0
                                     end flag
                          from (
                                select 1 live_id,
                                       'A' userid,
                                       '20200801 10:00:00' date_stamp,
                                       'enter' entry_type
                             union all select 1 live_id,
                                       'B' userid,
                                       '20200801 10:01:00' date_stamp,
                                       'enter' entry_type
                             union all select 1 live_id,
                                       'A' userid,
                                       '20200801 10:01:01' date_stamp,
                                       'out' entry_type
                             union all select 1 live_id,
                                       'C' userid,
                                       '20200801 10:02:03' date_stamp,
                                       'enter' entry_type
                               )
                       )
                 group by live_id,
                          date_stamp,
                          flag
               ) t1
       ) t2
 where rn = 1

3.hive的row_number()、rank()和dense_rank()的区别以及具体使用

表如下：

a 	b 
1	10
2	11
3	11
4	11
5	12
6	12
7	13

执行sql：

select a,
       b,
       row_number() over(order by b) row_number,
       rank() over(order by b) rank,
       dense_rank() over(order by b) dense_rank
  from (
        select 1 a,
               10 b
     union all select 2 a,
               11 b
     union all select 3 a,
               11 b
     union all select 4 a,
               11 b
     union all select 5 a,
               12 b
     union all select 6 a,
               12 b
     union all select 7 a,
               13 b
       ) t

结果：

a	b	row_number	rank	dense_rank
1	10	1	         1	      1
2	11	2	         2	      2
3	11	3	         2	      2
4	11	4	         2	      2
5	12	5	         5	      3
6	12	6	         5	      3
7	13	7	         7	      4

总结：

row_number:不管排名是否有相同的，都按照顺序1，2，3...

rank:排名相同的名次一样，同一排名有几个，后面排名就会跳过几次

dense_rank:排名相同的名次一样，且后面名次不跳跃

4.hive解析array，map，struct，json

array：array中的数据为相同类型，例如，假如array A中元素['a','b','c']，则A[0]的值为'a'。

map（K-V对）：访问指定域可以通过["指定域名称"]进行，例如，一个Map M包含了一个{"name":"b"}的kv对，gid的值可以通过M['name']来获取。

struct：struct内部的数据可以通过DOT（.）来存取，例如，表中一列c的类型为STRUCT{a INT; b INT}，我们可以通过c.a来访问域a。

json：通过get_json_object获取，例如，tag字段{"data":"test"}，可以通过get_json_object(tag, '$.data')获取。