hive练习题（三）

用户号	场景	访问时间
1	1001	1400
2	1002	1401
1	1002	1402
1	1001	1402
2	1003	1403
2	1004	1404
3	1003	1400
…	…	…

要求：

用户号对应前两个不同场景，场景重复的话选择场景的第一个访问时间，场景号不足两个的输出为止

输出结果示例：

result
1-1001-1002
2-1002-1003
3-1003
…

create table test
(userid varchar(20),
changjing varchar(20),
inttime varchar(20)
);

insert into test values
(1,1001,1400),
(2,1002,1401),
(1,1002,1402),
(1,1001,1402),
(2,1003,1403),
(2,1004,1404),
(3,1003,1400),
(4,1004,1402),
(4,1003,1403),
(4,1001,1403),
(4,1002,1404),
(5,1002,1402),
(5,1002,1403),
(5,1001,1404),
(5,1003,1405);

解答

关键词：每位用户 、前两个不同场景、

注意：若场景重复，选择第一个访问时间的场景

思路：找出每位用户在每个场景下的最早访问时间，对最早访问时间进行排序，以便找出前两个场景；

第一步：找出每位用户 是第一个访问时间的所有场景

SELECT userid, changjing, MIN(inttime) AS inttime
FROM test
GROUP BY userid, changjing ORDER BY userid, inttime

输出结果：

第二步：对每位用户的所有场景按照时间进行排名

SELECT *,row_number()over(PARTITION BY userid ORDER BY inttime ) AS rn
FROM (SELECT userid, changjing, MIN(inttime) AS inttime
FROM test
GROUP BY userid, changjing ORDER BY userid, inttime)a;

输出结果：

第三步：筛选出前两个场景

SELECT * FROM
(SELECT *,row_number()over(PARTITION BY userid ORDER BY inttime ) AS rn
FROM (SELECT userid, changjing, MIN(inttime) AS inttime
FROM test
GROUP BY userid, changjing ORDER BY userid, inttime) a ) b WHERE rn<=2;

输出结果：

第四步：对用户进行分组，使用concat函数和group_concat函数得到所要求的结果形式。

SELECT CONCAT(userid,'-',GROUP_CONCAT(changjing SEPARATOR '-')) AS result FROM 
(SELECT *,row_number()over(PARTITION BY userid ORDER BY inttime ) AS rn
FROM 
(SELECT userid, changjing, MIN(inttime) AS inttime FROM test
GROUP BY userid, changjing ORDER BY userid, inttime)a
)b 
WHERE rn<=2
GROUP BY userid;

得到最终结果：