当前位置： 首页 > news >正文

微信分销网站建设官网江西省建设职业培训学校网站

news 2025/9/17 21:00:20
微信分销网站建设官网,江西省建设职业培训学校网站,深圳鲜花团购网站建设,wordpress软件分享1、背景：通过业务埋点数据，统计用户在页面的停留时间 样例数据，样例数据存入表tmp， 有如下字段用户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
uidtimepnaction
123451695613731020搜索click
123451695613732021搜索click
123451695613734024搜索click
123451695613737036列表click
123451695613738037列表click
123451695613740040列表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_pn
123451695613731020搜索6
123451695613732021搜索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_pnlabel
123451695613731020搜索61
123451695613732021搜索5搜索0
123451695613734024搜索4搜索0
123451695613737036列表3搜索1
123451695613738037列表2列表0
123451695613740040列表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_time
12345搜索16956137310206
12345列表16956137370363
http://www.yayakq.cn/news/722236/

相关文章：

  • 更新网站的步骤网推所是什么意思
  • 招商网站建设目的商城网站建设实例需求
  • 自己做网站出口为什么网站有不同的扩展名
  • 企业网站整理优化网站建设与维护属于什么岗位
  • 网站内页跳转wap电子商务的网站开发
  • 张梅 合肥网站建设学网站建设哪里好
  • 网络推广公司推荐seo优化工具有哪些
  • flash网站模板下载如何做网站手机
  • 订单系统单页面网站怎么做百度投诉中心24小时电话
  • 网站运营教程群晖可以做网站吗
  • wordpress 建站 域名二手房公司网站源码
  • 浙江网站建设企业网站开发程序员的工资是多少
  • 电子商务网站建设完整案例教程s2sh pdf下载黑色星期五wordpress
  • 网站建设 南昌招标前程无忧做网站多少钱
  • wordpress做网站教程房产信息网58同城
  • 天津环保网站建设概念一般电脑网站建设及运营多少钱
  • 做教育网站还挣钱吗意大利室内设计网站
  • 做网站ddos攻击快捷做网站
  • 中国企业网站设计案例项目建设报告怎么写
  • 可以使页面具有动态效果的网站建设技术网络网站推广选择乐云seo
  • 惠州惠阳网站建设湖北三丰建设集团股份网站
  • 部门规划书 网站建设wordpress幻灯
  • 国外虚拟主机 两个网站小程序开发价格
  • 纪检监察网站建设情况汇报店铺如何运营和推广
  • 网站用绝对路径好还是相对路径seo东莞外贸推广
  • 山西手机版建站系统开发白沙网站建设的目标
  • 建设网站资质查询wordpress屏蔽谷歌字体
  • 天津建站管理系统信息张家界工程建设信息网站
  • 苏州网站开发公司鹅鹅鹅第1 ppt模板网
  • 医疗培训网站建设wordpress post date