踏踏实实练习HSQ--day04

2024-03-08 56

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 踏踏实实练习HSQ--day04

计算每小时最大在线人数

解题思路:

对登出直播间的人打-1标签，对登入的用户打1标签记录

UNIX时间戳转日期函数： from_unixtime

语法： from_unixtime(bigint unixtime[, string format])

返回值： string

说明：转化UNIX时间戳（从1970-01-01 00：00：00 UTC到指定时间的秒数）到当前时区的时间格式

hive> select from_unixtime(1323308943,'yyyyMMdd') from iteblog;

20111208

日期转UNIX时间戳函数： unix_timestamp

语法： unix_timestamp(string date)

返回值： bigint

说明：转换格式为"yyyy-MM-dd HH：mm：ss"的日期到UNIX时间戳。如果转化失败，则返回0。

select room_id,max(status) as max_status
from
(
select room_id,user_id,user_type,sum(user_type)
over(partition by room_id order by event_time) as status
from
(select room_id,user_id,unix_timestamp(login_time) as event_time,1 as user_type from
table 
where from_unixtime(unix_timestamp(login_time),'yyyyMMdd')=20210310 and (hour(login_time) between 12 and 13)  --限定某个时间区间内
union all
select room_id,user_id,unix_timestamp(logout_time) as event_time,-1 as user_type
from table
where from_unixtime(unix_timestamp(login_time),'yyyymmdd')=20210310 and
(hour(logout_time) between 12 and 13) --限定某个时间区间内)
)
group by room_id

某一时间段内每小时内同时在线人数

相互关注（共同好友）

需求

求关注结果数据中，相互关注的用户对。

数据如下：

follow表；from_user：关注用户，to_user:被关注用户，timestamp：关注时间。

from_user to_user timestamp

A B 2022-11-28 12:12:12

A C 2022-11-28 12:12:13

A D 2022-11-28 12:12:14

B A 2022-11-28 12:12:16

B E 2022-11-28 12:12:16

C A 2022-11-28 12:12:17

D A 2022-11-28 12:12:18

如果是互相关注，很容易想到表自关联，a.from_user 等于 b.to_user 且 a.to_user = b.from_user即可。具体sql如下：

#自身join
select t1.from_user,t1.to_user,if(t2.from_user is not null,1,0) as is_friend
from fan t1 left join fan t2 on t1.from_user=t2.to_user
and t1.to_user=t2.from_user

逻辑过程

但是当数据量达到很大时候运行时相当慢的，有没优化的方法实现呢？看到这可以思考下如何优化！

优化

其实思路还是一样，知道是from_user 和 to_user正好两个用户互换字段而已。如果是互关用户 AB，在表中有如下数据：

from_user	to_user
A	B
B	A

如果在表中能将其中一条数据用户名互换位置，另一条不动，再按照这两个字段分组计算数据条数，组内数据条数是2的就是互关用户了。这个思路不涉及到join操作。

from_user	to_user
A	B
A	B

实现

有了上述优化思路，下面来实现下，如何将其中一条数据用户名互换位置呢，其实可以想到简单的一个真假逻辑判断就行了。如果一条满足逻辑则不变，另一条要确保一定不满足逻辑，交换位置即可，sql如下：

select 
  from_user,
  to_user,
  if(from_user > to_user, concat(from_user,to_user),concat(to_user,from_user)) as concat_users
from follw

上述sql的if()中，这两条数据一定是一个满足一个不满足。即将其中一条数据用户名交换位置，完整sql如下：

select 
  t1.from_user,
  t1.to_user
from
  (select 
    t.from_user,
    t.to_user,
    sum(1) over(partition by t.concat_users) as flag
  from
    (select 
      from_user,
      to_user,
      if(from_user > to_user, concat(from_user,to_user),concat(to_user,from_user)) as concat_users
    from follw
    ) t
  ) t1
  where t1.flag = 2

合并区间

select provice,category from (
                             select provice,category
    from (
         select '广东省' as provice,'南方、经济强省' as category
        union all
        select '海南省' as provice, '热带、路由' as category
             )t0_1
                                 )t0
lateral view explode(split(category,'、')) table_temp_view as category_name

posexplode:多一个下角标,解决炸裂函数字段不一致问题

select class,student_name,student_score
from
test
lateral view posexplode(split('student',',') sn as student_index_sn,student_name
lateral view posexplode(split('score',',') sc as student_score
where student_index=student_index_sc