供求信息网站开发背景,洛阳网站建设找洛阳铭信网络,网站开发年薪,吴志国网站建设工作室1、背景#xff1a;通过业务埋点数据#xff0c;统计用户在页面的停留时间
样例数据#xff0c;样例数据存入表tmp#xff0c;
有如下字段用户uid、动作时间戳time、页面名称pn、动作名称action
SELECT 12345 AS uid, 1695613731020 AS time, 搜索 AS pn, click AS acti…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
uidtimepnaction123451695613731020搜索click123451695613732021搜索click123451695613734024搜索click123451695613737036列表click123451695613738037列表click123451695613740040列表click 思路以用户维度按时间进行升序排列通过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排序的作用是找到最后一个动作
uidtimepnrnlast_pn123451695613731020搜索6123451695613732021搜索5搜索123451695613734024搜索4搜索123451695613737036列表3搜索123451695613738037列表2列表123451695613740040列表1列表
然后将发生页面变化的节点进行标记
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
uidtimepnrnlast_pnlabel123451695613731020搜索61123451695613732021搜索5搜索0123451695613734024搜索4搜索0123451695613737036列表3搜索1123451695613738037列表2列表0123451695613740040列表1列表1 之后统计停留时间就可以只看label 1的日志之间的时间差即可全部代码如下
WITH tmp AS (SELECT 12345 AS uid, 1695613731020 AS time, 搜索 AS pn, click AS actionUNION ALLSELECT 12345 AS uid, 1695613732021 AS time, 搜索 AS pn, click AS actionUNION ALLSELECT 12345 AS uid, 1695613734024 AS time, 搜索 AS pn, click AS actionUNION ALLSELECT 12345 AS uid, 1695613737036 AS time, 列表 AS pn, click AS actionUNION ALLSELECT 12345 AS uid, 1695613738037 AS time, 列表 AS pn, click AS actionUNION ALLSELECT 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_timeFROM (SELECT *, if(pn nvl(last_pn, 空)OR rn 1, 1, 0) AS labelFROM (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_pnFROM tmp) t) ttWHERE label 1
) ttt
WHERE stay_time IS NOT NULL
最终统计结果如下
uidpntimestay_time12345搜索1695613731020612345列表16956137370363
