关于大数据:Hive面试题之连续登录行转列和列转行分析

计划一
1.先把数据依照用户id分组，依据登录日期排序
SQL：
SELECT
id,
login_date,
row_number() over(partition by id order by login_date asc) as rn
FROM data;

2.用登录日期与rn求date_sub，失去的差值日期如果是相等大数据培训的，则阐明这两天必定是间断的
SQL：
SELECT

 t1.id, t1.login_date, date_sub(t1.login_date, rn) as diff_date

FROM

(   SELECT       id,       login_date,       row_number() over(partition by id order by login_date asc) as rn    FROM data) t1;

3.依据id和日期差date_diff分组，登录次数即为分组后的count(1)
SQL：
SELECT
t2.id,
count(1) as login_times,
min(t2.login_date) as start_date,
max(t2.login_date) as end_date
FROM
(

SELECT t1.id, t1.login_date, date_sub(t1.login_date,rn) as diff_dateFROM(    SELECT     id,     login_date,     row_number() over(partition by id order by login_date asc) as rn     FROM data) t1

) t2
group by t2.id, t2.diff_date
having login_times >= 3;

计划二
计划二利用lag和lead函数进行解决，思路相似。
SQL：
SELECT
id,
lag_login_date,
login_date,lead_login_date
FROM

  (SELECT      id,     login_date,     lag(login_date,1,login_date) over(partition by id order by login_date) as lag_login_date,     lead(login_date,1,login_date) over(partition by id order by login_date) as lead_login_date  FROM data  ) t1

行转列和列转行
以"间断登录"中的数据为例：
select id,

   concat_ws(',',collect_list(login_date)) cw

from data

以下面SQL生成的数据为基准，执行下列SQL：
select id, login_date
from t