一道hive SQL面试题

本文介绍如何在Hive和Oracle中实现从用户ID到用户名的映射,包括通过SQL查询将组表中的用户ID转换为其对应的用户名,并展示了具体的实现方法及运行结果。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

 

一、hive中实现方法

基表:

组表:

gt

 

gid

gname

1001

g1

1002

g2

1003

g3

create table g(

gid int,

gname string

)row format delimited fields terminated by '\t'

stored as textfile;

用户表:

ut

 

uid

uname

10001

u1

10002

u2

10003

u3

10004

u4

10005

u5

10006

u6

10007

u7

10008

u8

10009

u9

10010

u10

create table u(

uid int,

uname string

)row format delimited fields terminated by '\t'

stored as textfile;

权限表:

gu

 

gid

uid

1001

10002,10001,10003,10009

1002

10004,10005,10006

1003

10007,10008,10010

create table gu(

gid int,

uid string

)row format delimited fields terminated by '\t'

stored as textfile;

组表gt中记录了组的信息组id和组名称,用户表记录了用户基本信息用户id和用户名称,gu是组表和用户表的关系,记录了每一个组内与用户对应关系,其中仅记录id信息。题目是根据gt和ut表将gu表中的所有id转换为名称?

我写的SQL是:

select t.gname,concat_ws(',',collect_set(t.uname)) from (

select g.gname,u.uname

from (

select gid,s_uid from gu

lateral view explode(split(uid,',')) uid as s_uid) temp,g,u

where temp.gid=g.gid and temp.s_uid=u.uid) t

group by t.gname;

运行结果如下:

hive> select t.gname,concat_ws(',',collect_set(t.uname)) from (

    > select g.gname,u.uname

    > from (

    > select gid,s_uid from gu

    > lateral view explode(split(uid,',')) uid as s_uid) temp,g,u

    > where temp.gid=g.gid and temp.s_uid=u.uid) t

    > group by t.gname;

OK

g1 u2,u1,u3,u9

g2 u4,u5,u6

g3 u7,u8,u10

二、oracle中实现方法

1、建立基表

create table g(

gid number(10),

gname varchar2(20)

)

create table u(

usrid number(10),

uname varchar2(20)

)

create table gu(

gid number(10),

usrid varchar2(200)

)

我所实现的sql方法如下:

select g.gname,

(select wm_concat(uname) from u where instr(gu.usrid, u.usrid) > 0)

from gu, g

where gu.gid = g.gid;

执行结果:

SQL> select g.gname,

  2         (select wm_concat(uname) from u where instr(gu.usrid, u.usrid) > 0)

  3    from gu, g

  4   where gu.gid = g.gid;

GNAME                (SELECTWM_CONCAT(UNAME)FROMUWH

-------------------- --------------------------------------------------------------------------------

g1                   u1,u2,u3,u9

g2                   u4,u5,u6

g3                   u7,u8,u10

<think>嗯,用户想找Hive SQL相关的面试题和答案。首先,我需要回忆一下常见的Hive面试问题,比如窗口函数、数据转换、去重等等。根据提供的引用内容,看到有同时在线人数、行转列、列转行等问题。比如引用[1]中的同时在线人数计算,用到了UNION ALL和窗口函数sum() over(),这个应该是经典问题之一。还有引用[3]中的collect_list函数用来拼接字符串,这也是常见的行转列问题。引用[4]提到的split和explode则是列转行的例子。 接下来需要整理这些问题,并给出对应的答案和示例代码。用户可能需要分点回答,结构清晰。比如第一个问题同时在线人数,需要步骤分解,说明如何将登录登出时间合并,然后用窗口函数累加。第二个问题行转列,用collect_list或者concat_ws。第三个问题列转行,用lateral view explode。第四个数据去重,可以用row_number或者group by。第五个连续登录,用日期差和分组统计。 还要注意用户的系统级指令,比如数学公式用$...$,代码块正确使用格式,引用标记在末尾。比如在回答每个问题时,适当引用提供的参考资料,如[^1]、[^3]、[^4]等。同时生成相关问题,可能包括性能优化、其他函数应用、处理数据倾斜等。 需要确保每个问题都有对应的SQL示例,并且代码正确。比如在同时在线人数的问题中,UNION ALL合并登录登出记录,然后sum(tag) over计算实时人数。在行转列中,collect_list和concat_ws的使用。列转行中的split和explode组合。 还要注意用户提到的“生成相关问题”部分,需要列出几个相关的问题,比如优化方法、其他窗口函数应用、数据倾斜处理等,这样用户可以有进一步的学习方向。 最后,检查是否所有引用都正确标注,答案是否符合中文要求,结构是否清晰,有没有遗漏常见的Hive面试题。可能需要补充一些其他常见问题,比如分区和分桶的区别、Hive和传统数据库的区别等,但根据提供的引用内容,主要围绕已有例子展开。</think>### Hive SQL 常见面试问题及答案 #### 1. **如何计算某时段内同时在线的最多人数?** **答案**: 将登录时间标记为$+1$,登出时间标记为$-1$,合并后通过窗口函数累加计算实时人数。示例代码: ```sql WITH t1 AS ( SELECT login_ts ts, 1 AS tag FROM user_login_detail UNION ALL SELECT logout_ts ts, -1 AS tag FROM user_login_detail ) SELECT MAX(cn) AS max_online_count FROM ( SELECT SUM(tag) OVER (ORDER BY ts) AS cn FROM t1 ) t2; ``` **解析**: - 使用`UNION ALL`合并登录和登出动作的时间戳。 - 窗口函数`SUM(tag) OVER (ORDER BY ts)`按时间顺序累加人数,最终取最大值。 --- #### 2. **如何将多行数据合并为一行(行转列)?** **答案**: 使用`COLLECT_LIST()`或`COLLECT_SET()`聚合函数,结合`CONCAT_WS()`拼接字符串。示例: ```sql SELECT user_id, CONCAT_WS(',', COLLECT_LIST(product_id)) AS purchased_products FROM purchase_table GROUP BY user_id; ``` **解析**: - `COLLECT_LIST`收集所有商品ID(允许重复),`COLLECT_SET`去重。 - `CONCAT_WS`用逗号分隔拼接结果。 --- #### 3. **如何将一行数据拆分为多行(列转行)?** **答案**: 使用`LATERAL VIEW`与`EXPLODE()`函数展开数组。示例: ```sql SELECT name, subject_name FROM student_table LATERAL VIEW EXPLODE(SPLIT(subject_list, ',')) extend_sub AS subject_name; ``` **解析**: - `SPLIT(subject_list, ',')`将字符串转换为数组。 - `EXPLODE()`展开数组,`LATERAL VIEW`生成多行结果。 --- #### 4. **如何对重复数据进行去重?** **答案**: 使用`ROW_NUMBER()`窗口函数标记重复项,或直接使用`GROUP BY`。示例: ```sql -- 方法1:窗口函数 SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_ts DESC) AS rn FROM login_table ) t WHERE rn = 1; -- 方法2:GROUP BY SELECT user_id, MAX(login_ts) AS last_login FROM login_table GROUP BY user_id; ``` --- #### 5. **如何统计用户连续登录天数?** **答案**: 通过日期差与分组标记实现。示例: ```sql WITH t1 AS ( SELECT user_id, login_date, DATE_SUB(login_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date)) AS grp FROM login_table ) SELECT user_id, COUNT(*) AS consecutive_days FROM t1 GROUP BY user_id, grp HAVING COUNT(*) >= 3; -- 连续3天以上 ``` **解析**: - `DATE_SUB(login_date, ROW_NUMBER())`生成连续登录的分组标记[^2]。 ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值