--1.创建用户行为表 CREATE TABLE EVENTS ( DATES DATE, USER_ID VARCHAR2(50), ITEM_ID VARCHAR2(50), BEHAVIOR VARCHAR2(50) ); COMMENT ON TABLE EVENTS IS '用户行为表'; COMMENT ON COLUMN EVENTS.DATES IS '日期'; COMMENT ON COLUMN EVENTS.USER_ID IS '用户ID'; COMMENT ON COLUMN EVENTS.ITEM_ID IS '产品ID'; COMMENT ON COLUMN EVENTS.BEHAVIOR IS 'pv-浏览/点击、fav-收藏、cart-加入购物车、buy-下单支付'; --2.插入测试数据 TRUNCATE TABLE EVENTS; INSERT INTO EVENTS(DATES,USER_ID,ITEM_ID,BEHAVIOR) SELECT TO_DATE('20201125', 'YYYYMMDD') + ABS(MOD(DBMS_RANDOM.RANDOM, 30)), '0000' || ABS(MOD(DBMS_RANDOM.RANDOM, 10)), 'ISSUE_0'||ABS(MOD(DBMS_RANDOM.RANDOM, 10)), DECODE(ABS(MOD(DBMS_RANDOM.RANDOM, 6)), 0, 'PV', 1, 'PV', 2, 'PV', 3, 'BUY', 4, 'FAV', 'CART') FROM dual CONNECT BY ROWNUM <= 100; COMMIT; --3.统计PV、UV、以及pv/uv --pv page view数 SELECT COUNT(*) AS PV, COUNT(DISTINCT T.USER_ID) AS UV, COUNT(*) / COUNT(DISTINCT T.USER_ID) PVDIVUV FROM EVENTS T WHERE T.BEHAVIOR = 'PV'; --4.购买用户数 SELECT COUNT(DISTINCT T.USER_ID) FROM EVENTS T WHERE T.BEHAVIOR = 'BUY'; --5.平均日浏览量 SELECT AVG(PV) FROM (SELECT T.DATES, COUNT(*) PV FROM EVENTS T WHERE T.BEHAVIOR = 'PV' GROUP BY T.DATES) D; --6.平均日用户量 SELECT AVG(UV) FROM (SELECT T.DATES, COUNT(DISTINCT USER_ID) UV FROM EVENTS T WHERE T.BEHAVIOR = 'PV' GROUP BY T.DATES) D; --7.Bounce rate 跳出率(只有一次点击行为的用户/总用户数) --假设只有一个页面可以浏览,用户点进页面后要么收藏加购付款,要么跳出。 SELECT SUM(BOUNCE_USER) BOUNCE_USERS, SUM(TOTAL_USER) TOTAL_USERS, SUM(BOUNCE_USER) / SUM(TOTAL_USER) BOUNCE_RATE FROM (SELECT COUNT(T1.USER_ID) BOUNCE_USER, 0 AS TOTAL_USER FROM EVENTS T1 WHERE T1.BEHAVIOR = 'PV' AND NOT EXISTS (SELECT 1 FROM EVENTS T2 WHERE T2.BEHAVIOR IN ('FAV', 'CART', 'BUY') AND T1.USER_ID = T2.USER_ID) UNION ALL SELECT 0 AS BOUNCE_USER, COUNT(DISTINCT USER_ID) AS TOTAL_USER FROM EVENTS) T; --8.漏斗分析 --转化率是以页面访问(PV) -> 加入购物车(CART)/收藏(FAV) -> 购买(BUY)路径为基准进行的计算, --并且将收藏和加入购物车的行为进行了合并(考虑到这两个阶段不分先后顺序,而且都是确定购买意向的行为) SELECT TOTAL_CLICKED_USER, --总访问用户数 PV_TO_CART_FAV_USERS, --加入购物车/收藏用户数 PV_TO_BUY_USERS, --购买用户数 PV_TO_CART_FAV_USERS/TOTAL_CLICKED_USER as pv_to_fav_cart_ratio, --访问->购物车/收藏转化率 PV_TO_BUY_USERS/TOTAL_CLICKED_USER as pv_to_buy_ratio --访问->购物车/收藏->购买转化率 FROM ( SELECT SUM(CASE WHEN T.PV_FLAG = 1 THEN 1 ELSE 0 END) TOTAL_CLICKED_USER, SUM(CASE WHEN T.PV_FLAG = 1 AND (T.FAV_FLAG = 1 OR T.CART_FLAG = 1) THEN 1 ELSE 0 END) PV_TO_CART_FAV_USERS, SUM(CASE WHEN T.PV_FLAG = 1 AND (T.FAV_FLAG = 1 OR T.CART_FLAG = 1) AND T.BUY_FLAG = 1 THEN 1 ELSE 0 END) PV_TO_BUY_USERS FROM ( SELECT USER_ID,MAX(CASE WHEN E.BEHAVIOR = 'PV' THEN 1 ELSE 0 END) PV_FLAG, MAX(CASE WHEN E.BEHAVIOR = 'FAV' THEN 1 ELSE 0 END) FAV_FLAG, MAX(CASE WHEN E.BEHAVIOR = 'CART' THEN 1 ELSE 0 END) CART_FLAG, MAX(CASE WHEN E.BEHAVIOR = 'BUY' THEN 1 ELSE 0 END) BUY_FLAG FROM EVENTS E GROUP BY USER_ID )T ) TAR; --9.每日新增购买/访问用户数 SELECT FRIST_DATES, COUNT(*) USERS FROM (SELECT E.USER_ID, MIN(DATES) FRIST_DATES FROM EVENTS E WHERE E.BEHAVIOR = 'PV' GROUP BY E.USER_ID) T GROUP BY FRIST_DATES; SELECT FRIST_DATES, COUNT(*) USERS FROM (SELECT E.USER_ID, MIN(DATES) FRIST_DATES FROM EVENTS E WHERE E.BEHAVIOR = 'BUY' GROUP BY E.USER_ID) T GROUP BY FRIST_DATES; --10.留存分析,同期群分析(同期群(cohort)是一组在特定时间做同样事的人) SELECT * FROM (SELECT MIN_WEEK, COUNT(*) TOTAL_USERS FROM (SELECT USER_ID, TO_CHAR(MIN(DATES), 'ww') MIN_WEEK FROM EVENTS T WHERE T.BEHAVIOR = 'BUY' GROUP BY USER_ID) GROUP BY MIN_WEEK) WEEKLY_USER LEFT JOIN (SELECT MIN_WEEK, TO_CHAR(DATA_BUY.DATES, 'WW') - FIRWK_USER.MIN_WEEK WEEK_GAP, COUNT(DISTINCT FIRWK_USER.USER_ID) REBUY_USERS FROM (SELECT USER_ID, TO_CHAR(MIN(DATES), 'WW') MIN_WEEK FROM EVENTS T WHERE T.BEHAVIOR = 'BUY' GROUP BY USER_ID) FIRWK_USER INNER JOIN (SELECT * FROM EVENTS T WHERE T.BEHAVIOR = 'BUY') DATA_BUY ON FIRWK_USER.USER_ID = DATA_BUY.USER_ID GROUP BY MIN_WEEK, TO_CHAR(DATA_BUY.DATES, 'WW') - FIRWK_USER.MIN_WEEK) USERS_PER_WEEK ON WEEKLY_USER.MIN_WEEK = USERS_PER_WEEK.MIN_WEEK; --11.复购分析 SELECT COUNT(USER_ID) DIS_BUYUSER, COUNT(CASE WHEN CNT > 1 THEN USER_ID ELSE NULL END) REBUY_USER, COUNT(CASE WHEN CNT > 1 THEN USER_ID ELSE NULL END) / COUNT(USER_ID) REBUY_USER_RATIO FROM (SELECT USER_ID, COUNT(*) CNT FROM EVENTS T WHERE T.BEHAVIOR = 'BUY' GROUP BY USER_ID) S; --12.用户复购次数分布 SELECT BUY_REQ, USERS, SUM(USERS) OVER(ORDER BY BUY_REQ) CUM_USERS, SUM(USERS) OVER(ORDER BY BUY_REQ) / SUM(USERS) OVER() CUM_PCT_RATIO FROM (SELECT BUY_REQ, COUNT(DISTINCT USER_ID) USERS FROM (SELECT USER_ID, COUNT(*) BUY_REQ FROM EVENTS T WHERE T.BEHAVIOR = 'BUY' GROUP BY USER_ID) T GROUP BY BUY_REQ);
相关推荐
手把手教你写 SQL Join 联接 手把手教你写 SQL Join 联接 - Defonds 的专栏 - CSDN博客手把手教你写 SQL Join 联接 - Defonds 的专栏 - CSDN博客
手把手教你SQLserver2008全文检索使用方法
教程名称:手把手教你整理自己的SQL Server日志课程目录:【】MSSQL数据库日志满的快速解决办法【】SQL2008删除过期备份【】sqlserver数据库事务日志备份与恢复原理【】SQL_server日志清除法【】利用日志传送实现高...
手把手教你学DSP:基于TMS320F28335 手把手教你学DSP:基于TMS320F28335 手把手教你学DSP:基于TMS320F28335 手把手教你学DSP:基于TMS320F28335 手把手教你学DSP:基于TMS320F28335 手把手教你学DSP:基于TMS320F...
手把手教你使用Python实现机器学习算法.pdf手把手教你使用Python实现机器学习算法.pdf手把手教你使用Python实现机器学习算法.pdf手把手教你使用Python实现机器学习算法.pdf手把手教你使用Python实现机器学习算法.pdf...
手把手教你用SQL server创建企业人事管理系统(精华)一共分了5卷,总分2分,下载完才能用。
手把手教你配路由器手把手教你配路由器手把手教你配路由器手把手教你配路由器手把手教你配路由器
手把手教你学2812,很全面的一本书。电子版,适合初学者学习,
手把手教你用Microsoft.SQL.Server
手把手教你如何从一无所有到财务自由.pdf
手把手教你用VMware安装oracle10g RAC
手把手教你学28335PDF文档,看了这个确实和2812有了对比
手把手教你建立SQL数据库的表分区 把每一个文件都单独放在不同的磁盘上,而且最好都是单独的放在不同的物理盘上,这样会大大提高数据的性能。 点击“确定”数据库就算创建完成了。
手把手教你DSP配套资料 很有用的资料,用钱买来的资料
手把手教你学DSP28335高清pdf文件,北京航空航天大学出版社
手把手教你用vmware安装oracle10g RAC
高清珍藏学习嵌入式开发入门最好资料《手把手教你学51单片机》教材pdf
手把手教你用VMware在Linux下安装Oracle 10g RAC
手把手教你学dsp2812,这本书是顾卫刚的,他的讲解比较详细
手把手教你写B端产品PRD.doc