sql - 如何使用移动窗口/分区或任何其他方法获得不同的每周活跃用户/不同的每月活跃用户？

Question

这件事让我头皮发麻，不过这很有趣。我有一个表，其中每一行代表一个应用程序启动事件，从几个不同的应用程序记录。我使用的是 Redshift 数据库，因此是 Redshift 的 Postgresql 实现。

此表中的每一行都有以下列:

• 应用程序 ID (app_id)
• 标识用户的 client_id
• 一个 SQL 日期格式的日期(创造性地命名为日期)

我需要计算这个比率:

• (不同的每周活跃用户)/(本周和前三周的不同活跃用户)

这意味着，我不像日历月(一月、二月、三月等)那样寻找每月活跃用户

我需要计算该 ISO 周的不同活跃用户除以该周和前三周的活跃用户(因此 MAU 基本上是过去四个四个星期的活跃用户)。

现在，我有一个获取每周活跃用户的查询，如下所示。周表示为“周数 - 年”。

select app_id, TO_CHAR(date,'IW-IYYY') Week, count (distinct client_id) ClientsInWeek
from weplay_singtel.gamelaunch t1
where app_id <> 1751
group by app_id, Week
order by app_id, TO_DATE(TO_CHAR(date,'IW-IYYY'),'IW-IYYY');

其中提供的数据如下面的屏幕截图所示:如您所见，针对每个 ISO 周和年，我有不同客户的数量。

为了将那一周和前三周的活跃用户放在一起，我最初使用了一个滞后值，并对上述数据中每周的前 3 周计数求和。但是，这个总和实际上并没有给我不同的用户，它只是每周不同客户的总和。如果同一个用户在第 1 周打开一个应用，然后在第 2 周再次打开，这种方法将计算他两次，这是 Not Acceptable !

我需要在四个星期的移动窗口中获得不同的客户。我怀疑我需要使用以某种方式移动超过一周的分区，但我似乎无法编写或可视化此查询。

发布此消息后，我还需要类似地创建比率:不同的每日活跃用户数/从那天算起的过去 30 天内不同的活跃用户数。

任何指导都会很有帮助。这可以使用移动分区来完成吗？还是使用交叉应用(在 Postgresql Lateral 中)？

希望这是有道理的!如果有任何不清楚的地方，请询问，我会立即回复更多信息。在此先感谢大家。

Answer 1

这会在单独的查询中计算不同的客户端并加入它们:

with t as (
    select *
    from weplay_singtel.gamelaunch
    where app_id <> 1751
)
with w as (
    select
        app_id,
        date_trunc('week', "date") as Week,
        count (distinct client_id) as ClientsInWeek
    from t
    group by app_id, 2
), w4 as (
    select
        app_id,
        daterange(
            date_trunc('week', "date" - interval '4 weeks'),
            date_trunc('week', "date"),
            '[]'
        ) as w4,
        count (distinct client_id) as ClientsInW4
    from t
    group by app_id, 2
)
select
    w.app_id, week, clientsinweek, clientsinw4,
    clientsinweek::float / clientsinw4
from
    w
    inner join
    w4 on
        week = upper(w4)
        and
        w.app_id = w4.app_id

4 周时间段被分组为一个日期范围。现在发布数据，我可以测试它。