PostgreSQL:从用户注册表和事件日志中计算第一天的保留率

Question

我需要按用户注册日期计算第一天的保留。第一天的保留定义为注册日期后1天返回的用户数除以注册日期注册的用户数量。

这是用户表

CREATE TABLE registration (
  user_id SERIAL PRIMARY KEY,
  user_name VARCHAR(255) NOT NULL,
  registrationDate TIMESTAMP NOT NULL
);
INSERT INTO registration (user_id, user_name, registrationDate)
VALUES
  (0, 'John', '2018-01-01 00:01:00'),
  (1, 'David', '2018-01-01 00:04:30'),
  (2, 'Cassy', '2018-01-02 10:00:00'),
  (3, 'Winka', '2018-01-02 14:30:00')
;
CREATE TABLE log (
  user_id INTEGER,
  eventDate TIMESTAMP
);
INSERT INTO log (user_id, eventDate)
VALUES
  (0, '2018-01-01 01:00:00'),
  (0, '2018-01-02 04:00:00'),
  (0, '2018-01-04 06:00:00'),
  (1, '2018-01-01 00:30:00'),
  (3, '2018-01-02 14:40:00'),
  (3, '2018-01-04 12:20:00'),
  (3, '2018-01-06 13:30:00'),
  (2, '2018-01-12 10:10:00'),
  (2, '2018-01-13 09:00:00')

我尝试将注册表加入日志表，因此我可以比较日期差异。

select registration.user_id, registrationDate, log.eventDate, 
(log.eventDate - registration.registrationDate) as datediff 
from log left join registration ON log.user_id = registration.user_id

我认为我需要以某种方式执行以下任务。

1. 选择日期= 1的用户并对其进行计数。
   • 我添加了一个陈述，但是遇到一个错误，说" datediff不存在位置"

where datediff = 1

2. 通过注册日期进行小组。
   • 这也给了我一个错误："错误：列" registration.user_id"必须在组中出现在子句中或在聚合函数中使用"

我是SQL的新手，并且在解决问题时学习它。任何帮助/建议将不胜感激

预期结果应返回一个带有两个列(注册日期和保留(的表，每个日期都有任何注册的用户。

Answer 1

第1天的保留定义为注册日期后1天返回的用户数除以注册日期注册的用户数量。

这将定义解释为基于日历日。我将其表示为：

注册后的第二天，用户的比例回来了？

我认为这是最简单的方法：

select count(distinct l.user_id) * 1.0 / count(distinct r.user_id)
from registration r left join
     log l
     on l.user_id = r.user_id and
        l.eventDate::date = r.registrationDate::date + interval '1 day';

仅在一天在一天中发生多个事件时才需要count(distinct)。

这是一个db＆lt;>小提琴。

我不确定该定义是否有用。如果您有另一个定义，我建议您提出 new 问题，并提供适当的示例数据和所需的结果。

Answer 2

我不安静确定这是否是您的预期结果：对于registrationdate = 2018-01-01，所有两个用户都在第一天都记录下来，因此结果是1。对于registrationdate = 2018-01-02，仅在此范围内记录了两个用户中的一个，因此结果是0.5

---

逐步演示：db＆lt;>小提琴

SELECT 
    registrationdate,
    COUNT(*) FILTER (WHERE is_in_one_day) / daily_regs::decimal                      -- 6
FROM (
    SELECT DISTINCT ON (l.user_id)                                                   -- 4
        l.user_id,
        eventdate::date AS eventdate,
        registrationdate::date AS registrationdate,
        daily_regs,
        eventdate - registrationdate < interval '1 day' AS is_in_one_day             -- 3
    FROM log l
    JOIN (                                                                           -- 2
        SELECT
            *,
            COUNT(user_id) OVER (PARTITION BY registrationdate::date) AS daily_regs   --1
        FROM
            registration
    ) r
    ON l.user_id = r.user_id
    ORDER BY l.user_id, eventdate
) s
GROUP BY registrationdate, daily_regs                                                -- 5

1. 计算每个注册日期的注册总数。这可以使用分配的窗口函数完成。它添加了一个带计数的列
2. 在user_id上加入两个表(与registrations上的一个额外列(
3. 计算当前eventdate和registrationdate的差异。检查这一天是否少了。
4. 请勿将一个用户两次(在您的示例数据中没有发生(，但可以是一个用户在此范围内登录两次。此用户不应计数两次(。
5. 小组在注册日期之前
6. 计数所有记录，其中有一天的差异(使用FILTER子句(，然后除以(1(中计算的注册总数