漏鬥分析函數與留存函數

SELECT uid,
       windowFunnel(1800, 'default',
                    TIME,
                    ARRAY [event_type = 'Browse', event_type = 'Click',
                           event_type = 'Order', event_type = 'Pay']) AS level
FROM action
GROUP BY uid
ORDER BY uid

 uid | level
-----+-------
   1 |     4
   2 |     0
   3 |     1
   4 |     2
   5 |     2
   6 |     3
(6 ROWS)

SELECT id,
       retention(ARRAY [action = 'pv' AND time::date = '2022-01-01', action = 'buy' AND time::date = '2022-01-02']) AS retention
FROM retention_test
GROUP BY id
ORDER BY id;

 id | retention
----+-----------
  1 | {1,1}
  2 | {1,0}
  3 | {0,0}
  4 | {0,0}
  5 | {1,1}
(5 ROWS)

SELECT SUM(r[1]) AS day1, SUM(r[2]) AS day2
FROM (SELECT id,
             retention(ARRAY [action = 'pv' AND time::date = '2022-01-01',
             action = 'buy' AND time::date = '2022-01-02']) AS r
      FROM retention_test
      GROUP BY id
      ORDER BY id) t;

 day1 | day2
------+-----
   3  |   2
(1 ROW)

CREATE TABLE event_tbl(uid INT, event TEXT, dt DATE);
INSERT INTO event_tbl VALUES
                        (1, 'pay', '2022-05-01'),(1, 'login', '2022-05-01'),
                        (1, 'pay', '2022-05-02'),(1, 'login', '2022-05-02'),
                        (2, 'login', '2022-05-01'),
                        (3, 'login', '2022-05-02'), (3, 'pay', '2022-05-03'),
                        (3, 'pay', '2022-05-04');

SELECT
    uid, r
FROM
    (
        SELECT
            uid,
            range_retention_count(event = 'login', event = 'pay', dt, 
            ARRAY [1, 2], 'day') AS r
        FROM
            event_tbl
        GROUP BY
            uid
    ) AS t
ORDER BY
    uid;

 uid |  r
-----+-----------------------------
   1 | {"{8156,1,0}","{8157,0,0}"}
   2 | {"{8156,0,0}"}
   3 | {"{8157,1,1}"}
(3 ROWS)

SELECT to_date(8156)
to_date
------------
2022-05-01

WITH retention_count_info AS (
    SELECT
        uid,
        range_retention_count(event = 'login', event = 'pay', 
                               dt, array[1, 2], 'day') AS info
    FROM
        event_tbl
    GROUP BY
        uid
), retention_sum AS (
         SELECT regexp_split_to_array(unnest(range_retention_sum(info)), ',') AS s
         FROM retention_count_info
     ) SELECT to_date(s[1]::int) AS login_date,
             s[3]::numeric / s[2]::numeric AS retention_d1,
             s[4]::numeric / s[2]::numeric AS retention_d2
FROM retention_sum
ORDER BY login_date;

 login_date | retention_d1           | retention_d2
------------+------------------------+------------------------
 2022-05-01 | 0.50000000000000000000 | 0.00000000000000000000
 2022-05-02 | 0.50000000000000000000 | 0.50000000000000000000
(2 ROWS)