写在前面：这些都是根据朋友反馈整理的面试真题，来自于各个公司，推荐大家先独立思考并写出后对照答案，答案不唯一，只是提供思路。

另注：大部分SQL题都是笔试，规定手写而非上机，现场难度较大，建议在电脑上写出来后再用纸笔写一下效果更佳。

真题题目：

真题一：

movie    category
《阿凡达2》    悬疑,动作,科幻,剧情
《满江红》    悬疑,警匪,动作,心理,剧情
《流浪地球》    科幻,动作,灾难
得到如下数据：
《阿凡达2》     悬疑
《阿凡达2》     动作
《阿凡达2》     科幻
《阿凡达2》     剧情
《满江红》       悬疑
《满江红》       警匪
《满江红》       动作
《满江红》       心理
《满江红》       剧情
《流浪地球》     科幻
《流浪地球》     动作
《流浪地球》     灾难

表名叫做movies

建表语句：

create table movies(
    movie string,
    category array<string>
)
row format delimited
fields terminated by '\t'
collection items terminated by ',';

真题二：

如下数据：
小狮    水瓶座    A
小猿    射手座    A
小云    水瓶座    B
小锋    水瓶座    A
小琪    射手座    A
变为：
指标：将相同星座和血型的人合并在一起
射手座,A            小猿|小琪
水瓶座,A            小狮|小锋
水瓶座,B            小云

建表语句：
create table person_info(
name string, 
constellation string, 
blood_type string) 
row format delimited fields terminated by "\t";

真题三：

data数据如下：
{"username":"张三","score":95}
{"username":"李四","score":76}
{"username":"小赵","score":92}
{"username":"王五","score":76}
{"username":"赵六","score":62}
{"username":"赵六1","score":62}
{"username":"赵六2","score":26}
{"username":"赵六3","score":89}
{"username":"赵六4","score":77}

create table scores(
    json_str  string
);

load data local inpath '/home/hivedata/zuoye02.txt' into table scores;

创建一张表，将上述数据导入表中，然后查询如下结果：
成绩大于90为优、大于80为良、大于60为中，不及格为差
统计结果：
等级    人数
优       x1
良       x2
中       x3
差       x4

真题四：

找出连续活跃3天及以上的用户
 

-- 这个是一个用户日活表，每一条表示一个用户，在某一条是活跃的
create table t_useractive(
  uid   string,
  dt    string
);

insert into t_useractive
values('A','2023-10-01'),('A','2023-10-02'),('A','2023-10-03'),('A','2023-10-04'),
      ('B','2023-10-01'),('B','2023-10-03'),('B','2023-10-04'),('B','2023-10-05'),
      ('C','2023-10-01'),('C','2023-10-03'),('C','2023-10-05'),('C','2023-10-06'),
      ('D','2023-10-02'),('D','2023-10-03'),('D','2023-10-05'),('D','2023-10-06');

真题答案：

第一题：

加载数据：

load data local inpath '你存放文本的目录' into table movies;

示例：

load data local inpath '/home/hivedata/movies.txt' into table movies;

解答：
select movie,type from movies lateral view explode(category) mytable as type;

第二题：

加载数据：

load data local inpath '你存放文本的目录' into table person_info;

示例：
load data local inpath '/home/hivedata/zuoye01.txt' into table person_info;

解答：

select concat(constellation,',',blood_type) cb,
      concat_ws('|',collect_list(name))  
      from person_info 
      group by concat(constellation,',',blood_type);

第三题：

with t as (
    select
    if(get_json_object(c1, '$.score') > 90, '优',
        if(get_json_object(c1, '$.score') > 80,'良',
           if(get_json_object(c1, '$.score') > 60,'中','差')
            )
        ) grade
    from t6
)
select grade,count(1) from t group by grade
order by  
   case grade when '优' then 1 
    when '良' then 2 
    when '中' then 3 
    when '差' then 4 
   end
;

第四题：

with t as (
    select *,row_number() over (partition by uid order by dt ) xh from t_useractive
)
select uid,date_sub(dt,xh),count(1) from t group by uid,date_sub(dt,xh) having count(1) >=3;