图解HIVE页面单跳转化率

图解

创建数据

--删库、建库
DROP DATABASE IF EXISTS behavior CASCADE;
CREATE DATABASE behavior LOCATION '/behavior';
USE behavior;
--建表
CREATE TABLE pages(
name  STRING     COMMENT "用户",
hms   TIMESTAMP  COMMENT "时间",
page  STRING     COMMENT "页面"
)COMMENT "页面表" LOCATION '/behavior/page';
--插数据
INSERT INTO TABLE pages VALUES
('剑圣','2020-12-13 21:35:00','搜索页'),
('剑圣','2020-12-13 21:35:01','商品页'),
('先知','2020-12-13 21:35:01','搜索页'),
('先知','2020-12-13 21:35:04','商品页'),
('巫妖','2020-12-13 21:35:07','搜索页'),
('先知','2020-12-13 21:36:30','订单页'),
('剑圣','2020-12-13 21:38:22','订单页'),
('巫妖','2020-12-13 21:39:59','商品页'),
('农民','2020-12-13 21:40:01','搜索页'),
('剑圣','2020-12-13 21:40:52','付款页');

分步

1、拉链

--拉链（按用户分区，按时间升序，按页面拉链）
SELECT
    name,
    page,
    LEAD(page,1) OVER (PARTITION BY name ORDER BY hms),
    hms,
    LEAD(hms,1) OVER (PARTITION BY name ORDER BY hms)
FROM pages;

2、页面跳转分组计数

SELECT skip,COUNT(1) FROM (
    SELECT
        --字符串拼接，若有null则拼接成null
        CONCAT(page,'-',LEAD(page,1) OVER (PARTITION BY name ORDER BY hms)) skip
    FROM pages)t
WHERE skip IS NOT NULL
GROUP BY skip;

3、页面访问分组计数

SELECT page,COUNT(1) FROM pages GROUP BY page;

4、联表

WITH
    --被除数：页面跳转分组计数
    t0 AS (
        SELECT skip,COUNT(1) skip_count FROM (
            SELECT
                CONCAT(page,'-',LEAD(page,1) OVER (PARTITION BY name ORDER BY hms)) skip
            FROM pages)t
        WHERE skip IS NOT NULL
        GROUP BY skip
    ),
    --除数：页面访问分组计数
    t1 AS (SELECT page,COUNT(1) page_count FROM pages GROUP BY page)
SELECT
    t0.skip,
    t0.skip_count,
    t1.page_count,
    t0.skip_count/t1.page_count
FROM t0
LEFT JOIN t1
    ON SUBSTR(t0.skip,1,LENGTH(t1.page))=t1.page;

附录

1. 基础补充：HIVE 滑窗 和 拉链
2. 其它实现：Spark 页面单跳转化率