HIVE连续登录问题以及行列转换问题

连续登录问题：

实现思路:

1.因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

2.再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

3.计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。这里使用到的是date_sub函数

在这里我讲一下date_sub与date_diff函数的区别：

（1）. datediff（'endTime'，‘startTime’）是两个日期相减

（2）.date_sub（‘yyyy-MM-dd’，n/-m）  为一个日期减去一个数字

4.按照id和日期分组并求和，筛选大于等于7的即为连续7天登陆的用户

4.1下面给出sql ：求出每个用户最大的登录时间

SELECT user_id, MAX(count_val) AS max_count  -- 查出了最大连续登陆，where>=7,即7天连续
FROM (
     -- group by相同日期
  SELECT user_id, symbol_date, COUNT(*) AS count_val
  FROM (
       -- 日期减rank，连续登陆的话，会得到相同日期
    SELECT user_id, log_date, date_sub(log_date, CAST(rn AS INT)) AS symbol_date
    FROM (
      SELECT user_id, log_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn  --  rank函数分区 排序
     FROM (
      select distinct user_id,log_date from user_logging_format
      ) a --去重
    ) c
  ) d
  GROUP BY user_id, symbol_date
) e
GROUP BY user_id;

 

　　4.2判断登录连续登录7天的用户

SELECT user_id -- 查出连续登陆，where>=7,即7天连续的用户
FROM (
     -- group by相同日期
  SELECT user_id, symbol_date, COUNT(*) AS count_val
  FROM (
       -- 日期减rank，连续登陆的话，会得到相同日期
    SELECT user_id, log_date, date_sub(log_date, CAST(rn AS INT)) AS symbol_date
    FROM (
      SELECT user_id, log_date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY log_date) AS rn  --  rank函数分区 排序
     FROM (
      select distinct user_id,log_date from user_logging_format
      ) a --去重
    ) c
  ) d
  GROUP BY user_id, symbol_date
) e
where count_val>=7;

 

　　2.行列转换问题：

首先你必须知道几个函数：

collect_set();collect_list():它们都是将分组中的某列转为一个数组返回，不同的是collect_list不去重而collect_set去重。

CONCAT_WS(separator,str1,str2,…)：第一个参数为分隔符：

下面从网上找的几个图片：

 

下面使用完collect_set，一目了然：

select
username, collect_set(video_name)
from t_visit_video
group by username;

　　

上边是多行转一行，那么一行转多行如何转？

a       b       1,2,3
c       d       4,5,6

select col1, col2, col5
from tmp_bsf_test a
lateral  view explode(split(col3,','))  b AS col5

 

　　这里是笛卡尔乘积：相当于col1，col2他们两个乘以col5因为col5里面有explode

a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6

　　下面有个例子看看你会不会，也是从网上找的

 

SELECT SID,
MAX(case CID when '01' then score else 0 end) '01', --as 语文
MAX(case CID when '02' then score else 0 end)'02',   --as 数学
MAX(case CID when '03' then score else 0 end)'03'
FROM SC
GROUP BY SID

 

 上面是人家写的代码：这里首先是按id分组，没毛病，这里需要使用到聚合函数，为了是取别名  as 语文像这样，思路就是这个思路