主页 / dba / 问题 / 280369
Accepted
Bobbie D
Asked: 2020-11-26 23:19:43 +0800 CST

使用过去几天的活动将用户标记为“活跃”

  • 772

这是一个示例数据集。

with activity_cte (day, user_id, act1, act2) as (
    values
        ('2020-01-01'::date, 1, 0, 1),
        ('2020-01-01'::date, 3, 1, 0),
        ('2020-01-02'::date, 1, 3, 2),
        ('2020-01-02'::date, 2, 0, 2),
        ('2020-01-02'::date, 5, 0, 1),
        ('2020-01-03'::date, 1, 1, 2),
        ('2020-01-03'::date, 5, 1, 1),
        ('2020-01-04'::date, 2, 1, 1),
        ('2020-01-04'::date, 5, 4, 0)
)
select * from activity_cte;

在此我正在跟踪用户活动计数。我在这里跟踪两个活动：“act1”和“act2”，我只是总结了用户在一段时间内参与该活动的次数。目前，如果用户在某一天没有参与任何一项活动，他们就不会在当天出现（尽管如果需要可以更改）。例如，用户 2 在 1 月 2 日两次参与活动 2，并在 1 月 4 日两次参与这两项活动。

我想做的是计算每天“活跃”用户的数量，我将其定义为从前一天起至少参与过一项活动的用户（实际上大约是一周前，但我不想在此处设置的此示例中编写大量行）。所以这就是我想要返回的。

2020-01-01 2
2020-01-02 4
2020-01-03 3
2020-01-04 3

这似乎是我需要使用窗口函数的东西。也许我想多了，但我很难想出生成这些数字的实际方法。

postgresql window-functions

1 个回答

  1. Best Answer

    这可能不是最优雅的解决方案，但它有效：

    with activity_cte (day, user_id, act1, act2) as (
    values
        ('2020-01-01'::date, 1, 0, 1),
        ('2020-01-01'::date, 3, 1, 0),
        ('2020-01-02'::date, 1, 3, 2),
        ('2020-01-02'::date, 2, 0, 2),
        ('2020-01-02'::date, 5, 0, 1),
        ('2020-01-03'::date, 1, 1, 2),
        ('2020-01-03'::date, 5, 1, 1),
        ('2020-01-04'::date, 2, 1, 1),
        ('2020-01-04'::date, 5, 4, 0)
),
day_users AS (
    SELECT DISTINCT
           day,
           array_agg(user_id)
               OVER (ORDER BY day
                     RANGE BETWEEN INTERVAL '1 day' PRECEDING AND CURRENT ROW) AS users
    FROM activity_cte
)
SELECT day_users.day,
       count(DISTINCT user_id)
FROM day_users
    CROSS JOIN LATERAL unnest(users) AS u(user_id)
GROUP BY day_users.day;

    day     | count 
------------+-------
 2020-01-01 |     2
 2020-01-02 |     4
 2020-01-03 |     3
 2020-01-04 |     3
(4 rows)
    • 1

相关问题