Postgres简单的流失/保留

Question

我正在尝试创建一个简单的用户保留率，我尝试了很多搜索方式，但是它们似乎都没有高效，并且大多数都有很多我真正不需要的计算。

输入 我的桌子示例： https://dbfiddle.uk/?rdbms = postgres_14&； fiddle = ec68bebf63280023e828ebbb6cabd2d89 或：

DROP TABLE IF EXISTS users_churn;
create table 
users_churn(
id SERIAL PRIMARY KEY, 
users varchar(10) not null,
dates timestamp
);
insert into users_churn(users, dates)
values
('1ab7', '2022-01-04 10:22'),
('1ab7', '2022-04-26 18:30'),
('1ab7', '2022-05-12 20:10'),
('1ab7', '2022-07-02 20:55'),
('3ac5', '2022-02-05 05:12'),
('3ac5', '2022-04-09 07:17'),
('3ac5', '2022-07-03 04:19');
select * from users_churn

我期望的输出：

+-------------------------------------------------+
|                --DESIRED OUTPUT                 |
+-------------------------------------------------+
| ('1ab7', '2022-01-04 10:22', 'first_purchase'), |
| ('1ab7', '2022-02-01 00:00', 'churn'),          |
| ('1ab7', '2022-03-01 00:00', 'churn'),          |
| ('1ab7', '2022-04-26 18:30', 'retained'),       |
| ('1ab7', '2022-05-12 20:10', 'retained'),       |
| ('1ab7', '2022-06-01 00:00', 'churn'),          |
| ('1ab7', '2022-07-02 20:55', 'retained'),       |
| ('3ac5', '2022-02-05 05:12', 'first_purchase'), |
| ('3ac5', '2022-03-01 00:00', 'churn'),          |
| ('3ac5', '2022-04-09 07:17', 'retained'),       |
| ('3ac5', '2022-05-01 00:00', 'churn'),          |
| ('3ac5', '2022-06-01 00:00', 'churn'),          |
| ('3ac5', '2022-07-03 04:19', 'retained');       |
+-------------------------------------------------+

Answer 1

如果用户一个月内购买更多购买，您想做什么。此版本仅保留第一个版本。如果要保留所有购买，请摆脱 rn_month stuck：

with churn as (
  select c.users, 
         c.dates, 
         date_trunc('month', c.dates) as mdate,
         row_number() over (partition by c.users 
                                order by c.dates) as rn_all,
         row_number() over (partition by c.users, date_trunc('month', c.dates)
                                order by c.dates) as rn_month
    from users_churn c
), months as (
  select generate_series(
           min(c.mdate),
           current_date,
           interval '1 month'
         )::timestamp as mdate
    from churn c
)
select c.users,
       coalesce(c1.dates, m.mdate) as dates,
       case 
         when c1.rn_all = 1 then 'first_purchase'
         when c1.users is null then 'churn'
         else 'retained'
       end as status
  from churn c
       join months m 
         on m.mdate >= c.mdate 
        and c.rn_all = 1
       left join churn c1 
         on c1.users = c.users
        and c1.mdate = m.mdate
        and c.rn_month = 1
 order by c.users, m.mdate;

db＆lt;＆gt; fiddle 此处