hive统计页面停留时间

1、背景：通过业务埋点数据，统计用户在页面的停留时间

样例数据，样例数据存入表tmp，

有如下字段用户uid、动作时间戳time、页面名称pn、动作名称action

SELECT '12345' AS uid, '1695613731020' AS time, '搜索' AS pn, 'click' AS action
UNION ALL
SELECT '12345' AS uid, '1695613732021' AS time, '搜索' AS pn, 'click' AS action
UNION ALL
SELECT '12345' AS uid, '1695613734024' AS time, '搜索' AS pn, 'click' AS action
UNION ALL
SELECT '12345' AS uid, '1695613737036' AS time, '列表' AS pn, 'click' AS action
UNION ALL
SELECT '12345' AS uid, '1695613738037' AS time, '列表' AS pn, 'click' AS action
UNION ALL
SELECT '12345' AS uid, '1695613740040' AS time, '列表' AS pn, 'click' AS action

思路：以用户维度按时间进行升序排列，通过lag函数找到上一个时间动作last_pn

SELECT uid, time, pn, row_number() OVER (PARTITION BY uid ORDER BY time DESC) AS rn
	, lag(pn, 1) OVER (PARTITION BY uid ORDER BY time ASC) AS last_pn
FROM tmp

rn排序的作用是找到最后一个动作

然后将发生页面变化的节点进行标记，

SELECT *, if(pn <> nvl(last_pn, '空') OR rn = 1, 1, 0) AS label
FROM (
SELECT uid, time, pn, row_number() OVER (PARTITION BY uid ORDER BY time DESC) AS rn
, lag(pn, 1) OVER (PARTITION BY uid ORDER BY time ASC) AS last_pn
FROM tmp
) t

之后统计停留时间就可以只看label =1的日志之间的时间差即可，全部代码如下，

WITH tmp AS (
		SELECT '12345' AS uid, '1695613731020' AS time, '搜索' AS pn, 'click' AS action
		UNION ALL
		SELECT '12345' AS uid, '1695613732021' AS time, '搜索' AS pn, 'click' AS action
		UNION ALL
		SELECT '12345' AS uid, '1695613734024' AS time, '搜索' AS pn, 'click' AS action
		UNION ALL
		SELECT '12345' AS uid, '1695613737036' AS time, '列表' AS pn, 'click' AS action
		UNION ALL
		SELECT '12345' AS uid, '1695613738037' AS time, '列表' AS pn, 'click' AS action
		UNION ALL
		SELECT '12345' AS uid, '1695613740040' AS time, '列表' AS pn, 'click' AS action
	)
SELECT *
FROM (
	SELECT uid, pn, time, CAST((lead(time, 1) OVER (PARTITION BY uid ORDER BY time ASC) - time) / 1000 AS BIGINT) AS stay_time
	FROM (
		SELECT *
			, if(pn <> nvl(last_pn, '空')
				OR rn = 1, 1, 0) AS label
		FROM (
			SELECT uid, time, pn, row_number() OVER (PARTITION BY uid ORDER BY time DESC) AS rn
				, lag(pn, 1) OVER (PARTITION BY uid ORDER BY time ASC) AS last_pn
			FROM tmp
		) t
	) tt
	WHERE label = 1
) ttt
WHERE stay_time IS NOT NULL

最终统计结果如下