时间戳

首页 > 解决方案 > 按周显示的滚动尾随 30 天窗口中的唯一客户总数

sql - 按周显示的滚动尾随 30 天窗口中的唯一客户总数

问题描述

我在 SQL Workbench 中工作。

我想跟踪在过去 30 天内每次有唯一客户点击新功能的时间，每周显示一次。数据输出示例如下：

第 51 周：反映到第 51 周（12 月 20 日）结束 - 30 天的使用情况。又名 11 月 20 日至 12 月 20 日 第 52 周：反映到第 52 周（12 月 31 日）结束时的使用情况 - 30 天。又名 12 月 1 日 - 12 月 31 日。

假设在 11 月 20 日至 12 月 20 日期间发生了 22MM 的唯一客户点击。第 51 周数据 = 22MM。假设在 12 月 1 日至 12 月 31 日期间发生了 25MM 的唯一客户点击。第 52 周数据 = 25MM。客户唯一性仅与该特定周相关。Aka，如果客户在第 51 周点击两次，他们只会被计算一次。如果他们在第 51 周和第 52 周单击一次，则每周计算一次。

这是我到目前为止所拥有的：

select 
         min_e_date
        ,sum(count(*)) over (order by min_e_date rows between unbounded preceding and current row) as running_distinct_customers
from (select customer_id, min(DATE_TRUNC('week', event_date)) as min_e_date
        from final
        group by 1
      ) c
group by 
          min_e_date

我不认为滚动计数是正确的方法。当我添加其他参数（国家、订阅）时，滚动计数不会区分它们——这些数字只是被添加到前一行。

任何建议表示赞赏！

编辑下面的附加数据。数据收集于 11/23 开始。该日期之前没有数据。

数据

期望的输出

标签： sqlamazon-redshiftwindow-functions

解决方案

在 RedShift 中滚动不同的计数非常困难。一种方法是自连接和聚合：

select t.date,
       count(distinct case when tprev.date >= t.date - interval '6 day' then customer_id end) as trailing_7,
       count(distinct customer_id) as trailing_30
from t join
     t tprev
     on tprev.date >= t.date - interval '29 day' and
        tprev.date <= t.date
group by t.date;

如果你可以让它工作，你可以选择每 7 行来获取每周值。

编辑：

一种完全不同的方法是使用聚合并跟踪客户何时进入和结束被计算的时间段。这是两个不同时间框架的痛苦。这是一个人的样子。

这个想法是

  1. 为每个被计数的记录创建一个进入/退出记录。“退出”是进入后n天。
  2. 将这些总结为每个客户的活动时期。因此，有一个记录有进入和退出日期。这是一种差距和孤岛问题。
  3. 取消透视此结果以计算 +1 表示客户被计算在内，而 -1 表示客户未被计算在内。
  4. 做这个计数的累积和。

代码看起来像这样：

with cd as (
      select customer_id, date,
             lead(date) over (partition by customer_id order by date) as next_date,
             sum(sum(inc)) over (partition by customer_id order by date) as cnt
      from ((select t.customer_id, t.date, 1 as inc
             from t
            ) union all
            (select t.customer_id, t.date + interval '7 day', -1
             from t
            )
           ) tt
     ),
     cd2 as (
      select customer_id, min(date) as enter_date, max(date) as exit_date
      from (select cd.*,
                   sum(case when cnt = 0 then 1 else 0 end) over (partition by customer_id order by date) as grp
            from (select cd.*,
                         lag(cnt) over (partition by customer_id order by date) as prev_cnt
                  from cd
                 ) cd
           ) cd
      group by customer_id, grp
      having max(cnt) > 0
     )
select dte, sum(sum(inc)) over (order by dte)
from ((select customer_id, enter_date as dte, 1 as inc
       from cd2
      ) union all
      (select customer_id, exit_date as dte, -1 as inc
       from cd2
      )
     ) cd2
group by dte;

推荐阅读