数据仓库服务 GAUSSDB(DWS)-漏斗和留存函数:示例

时间：2024-11-28 17:39:02

示例

创建表funnel_test：

CREATE TABLE IF NOT EXISTS funnel_test
(
    user_id INT ,
    event_type TEXT,
    event_time TIMESTAMP,
    event_timez TIMESTAMP WITH TIME ZONE,
    event_time_int BIGINT
);

插入数据：

INSERT INTO funnel_test VALUES
(1,'浏览页面','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 10),
(1,'点击商品','2021-01-31 11:10:00', '2021-01-31 11:10:00+07', 20),
(1,'加入购物车','2021-01-31 11:20:00', '2021-01-31 11:20:00+06', 30),
(1,'支付货款','2021-01-31 11:30:00', '2021-01-31 11:30:00+05', 40),
(2,'加入购物车','2021-01-31 11:00:00', '2021-01-31 11:00:00+08', 11),
(2,'支付货款','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 12),
(1,'浏览页面','2021-01-31 11:00:00', '2021-01-31 11:00:00+01', 50),
(3,'浏览页面','2021-01-31 11:20:00', '2021-01-31 11:20:00-04', 30),
(3,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 80),
(4,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00-01', 1000),
(4,'支付货款','2021-01-31 12:00:00', '2021-01-31 12:00:00-02', 900),
(4,'加入购物车','2021-01-31 12:00:00', '2021-01-31 12:00:00-03', 1001),
(4,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00-04', 1001),
(5,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00+08', NULL),
(5,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00+08', 776),
(5,'加入购物车','2021-01-31 11:10:00', '2021-01-31 11:10:00+08', 999),
(6,'浏览页面','2021-01-31 11:50:00', '2021-01-31 11:50:00+01', -1),
(6,'点击商品','2021-01-31 12:00:00', '2021-01-31 12:00:00+02', -2),
(6,'加入购物车','2021-01-31 12:10:00', '2021-01-31 12:00:00+03', -3);

计算每个用户的漏斗情况。返回结果如下，其中level=0表示用户在窗口期内匹配最大事件深度为0，level=1表示用户在窗口期内匹配最大事件深度为1：

SELECT
    user_id,
  windowFunnel(
    0, 'default', event_timez, 
    event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
  ) AS level
FROM funnel_test
GROUP BY user_id
ORDER by user_id;
 user_id | level
---------+-------
       1 |     1
       2 |     0
       3 |     1
       4 |     1
       5 |     1
       6 |     1
(6 rows)

计算每个用户的漏斗情况，指定滑动的时间窗口的大小为NULL，返回报错：

SELECT
    user_id,
  windowFunnel(
    NULL, 'default', event_time, 
    event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
  ) AS level
FROM funnel_test
GROUP BY user_id
ORDER by user_id;
ERROR:  Invalid parameter : window length or mode is null.

计算每个用户的漏斗情况，指定多个条件：

SELECT
    user_id,
  windowFunnel(
    40, 'default', date(event_time),
    true, true, false, true
  ) AS level
FROM funnel_test
GROUP BY user_id
ORDER by user_id;
 user_id | level
---------+-------
       1 |     2
       2 |     2
       3 |     2
       4 |     2
       5 |     2
       6 |     2
(6 rows)

分析用户的留存情况：

SELECT
    user_id,
  retention(
    event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
    ) AS r
FROM funnel_test
GROUP BY user_id
ORDER BY user_id ASC;
 user_id |     r
---------+-----------
       1 | {1,1,1,1}
       2 | {0,0,0,0}
       3 | {1,1,0,0}
       4 | {1,1,1,1}
       5 | {1,1,1,0}
       6 | {1,1,1,0}
(6 rows)

分析用户的留存情况，指定第一个时间为false：

SELECT
    user_id,
  retention(
    false, event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款'
    ) AS r
FROM funnel_test
GROUP BY user_id
ORDER BY user_id ASC;
 user_id |      r
---------+-------------
       1 | {0,0,0,0,0}
       2 | {0,0,0,0,0}
       3 | {0,0,0,0,0}
       4 | {0,0,0,0,0}
       5 | {0,0,0,0,0}
       6 | {0,0,0,0,0}
(6 rows)

分析用户的留存情况总和：

SELECT sum(r[1]), sum(r[2]), sum(r[3]), sum(r[4])
FROM
(
    SELECT
    retention(event_type = '浏览页面', event_type = '点击商品', event_type = '加入购物车', event_type = '支付货款') AS r
    FROM funnel_test
    GROUP BY user_id
);
 sum | sum | sum | sum
-----+-----+-----+-----
   5 |   5 |   4 |   2
(1 row)

统计每个用户在1,3,7天后的付费留存率：

  
       SELECT
    user_id,
    range_retention_count(event_type = '浏览页面', event_type = '支付货款', DATE(event_time), ARRAY[1, 3, 7], 'day') as r
FROM funnel_test
GROUP BY user_id
ORDER BY user_id;
 user_id |        r
---------+------------------
       1 | {80135499808768}
       2 | {}
       3 | {80135499808768}
       4 | {80135499808768}
       5 | {80135499808768}
       6 | {80135499808768}
(6 rows)