面试-大数据-场景题-sql

1. 求5min内浏览次数达到100的用户-- LAG和LEAD函数

转载自

1. 有如下场景：
   某公司网站每日访问量达到10亿级别的访问量，每次访问记录一条数据，数据包含如下字段：用户ID，访问时间（毫秒级），访问页面。
   要求使用hive求出所有在5分钟内访问次数达到100次的用户（求出用户ID即可）
2. **思路：**利用窗口函数Lag
3. 详细思路：
   1.选出当天访问次数达到100次的用户（即当天有100及以上条数据的用户）：根据用户ID分组，count
   2.在每个 用户ID小组内（步骤1已进行分组）按 访问时间进行升序排序
   3.计算time-lag(time,100)，若time-lag(time,100)<=5601000(毫秒)，即为满足条件的用户，筛选出。
4. HQL语句书写

select t.id from{
select
id,
time-lag(time,100,time-5601000-1) over(partition by id order by time) as time_length
from log
group by id
} t
group by id
having min(time_length)<=5601000

注释：time-lag(time,100,time-5601000-1) 中100表示取前100行的数据，若无前100行的数据，则取默认值time-5601000-1。当去默认值time-5601000-1时，time-（time-5601000-1）为5min零1毫秒，大于5min，不满足条件，后续过滤掉。
ps：该HQL只表示大致思路，有优化空间。
5. LAG和LEAD函数
LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值
第一个参数为列名，
第二个参数为往上第n行（可选，默认为1），
第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值
第一个参数为列名，
第二个参数为往下第n行（可选，默认为1），
第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

2.成绩表列转行-上表转成下表

转载自


那么最后如果需要将“表-4”的内容转换为“表-3”的内容应该怎么做呢？
一种比较好的方式是这样：

select table_4.name,
a.item,
a.score
from table_4
lateral view explode(
str_to_map(concat('math=',math,'&english=',english),'&','=')
) a as item,score；

解析：首先使用str_to_map函数将math字段与english字段拼接后的结果转换为map类型，然后通过侧视图和explode函数将其爆炸开，给生成的临时侧视图一个名字，取名a并给列名取名为item,score，因为explode(map)爆炸的结果是每一个item为行，key为1列，value为1列,这样就恰好形成我们想要的结果。这个示例理解起来稍微有点难度，大家不熟悉这些函数的用法的话，可以首先熟悉一下。

2.1 str_to_map hive 字符串转为map格式

英语翻译如下：
使用两个分隔符将文本拆分为键值对。 Delimiter1将文本分成K-V对，Delimiter2分割每个K-V对。对于delimiter1和Delimiter2之间的默认分隔符是’，‘，对于delimiter2默认分隔符是’='。
案例1：

hive> 
    > select str_to_map('aaa:11&bbb:22', '&', ':')
    > from tmp.tmp_jzl_20140725_test11;
OK

{"bbb":"22","aaa":"11"}

案例2:

hive> select str_to_map('aaa:11&bbb:22', '&', ':')['aaa']
    > from tmp.tmp_jzl_20140725_test11;
OK
11

2.2 lateral view explode(str_to_map())例子

select user_id,gender,city,year_old
from table
lateral view explode(str_to_map(user_info,',',':') as gender,city,year_old

2.3 待做

# 待商榷是否正确
select t.dtm,id,
sum(t.num_tmp) over (partition by t.dtm,t.id) as num, #
sum(t.num_tmp) over(partition by t.dtm) as total_num
from(
select dtm,id,num_tmp
from t
lateral view explode(split(id,',')) tmp1 as id,
lateral view explode(split(num,',')) tmp2 as num_tmp #两个分别分割使用两次
)t

3.连续登录7天的用户

转载自
具体思路:
1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。
2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。
3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。
4、按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户。

select id,count(*) from (
	select *,date(日期)-cum as 结果 from (
		select *,row_number() over(PARTITION by id order by 日期) as cum from (
			select DISTINCT date(date) as 日期,id from orde)a
				)b
					)c 
	GROUP BY id,结果 having count(*)>=7;