sql - 不同 ID 的 30 天滚动计数

Question

因此，在查看了似乎是一个常见问题并且无法找到适合我的解决方案之后，我决定我应该问问自己。
我有一个包含两列的数据集:session_start_time，uid
我正在尝试生成一个滚动的 30 天独特 session 记录
查询每天唯一 uid 的数量很简单:

SELECT 
COUNT(DISTINCT(uid)) 
FROM segment_clean.users_sessions
WHERE session_start_time >= CURRENT_DATE - interval '30 days'

计算某个日期范围内的每日唯一 uid 也相对简单。

SELECT
DATE_TRUNC('day',session_start_time) AS "date"
,COUNT(DISTINCT uid) AS "count"
FROM segment_clean.users_sessions
WHERE session_start_time >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY date(session_start_time)

然后我尝试了几种方法来在一个时间间隔内滚动 30 天的唯一计数

SELECT 
DATE(session_start_time) AS "running30day"
,COUNT(distinct(
    case when date(session_start_time) >= running30day - interval '30 days'
    AND date(session_start_time) <= running30day
    then uid
    end)
) AS "unique_30day"
FROM segment_clean.users_sessions
WHERE session_start_time >= CURRENT_DATE - interval '3 months'
GROUP BY date(session_start_time) 
Order BY running30day desc

我真的认为这会奏效，但在查看结果时，似乎我得到的结果与我在做每日独特而不是 30 天的独特时相同。
我正在使用 SQL 查询编辑器从 Metabase 编写此查询。基础表处于 Redshift 状态。
如果你读到这里，谢谢你，你的时间很有值(value)，我很感激你花了一些时间来阅读我的问题。
编辑:
按照理所当然的要求，我添加了一个我正在使用的数据集和所需结果的示例。

+-----+-------------------------------+
| UID |      SESSION_START_TIME       |
+-----+-------------------------------+
|     |                               |
| 10  | 2020-01-13T01:46:07.000-05:00 |
|     |                               |
| 5   | 2020-01-13T01:46:07.000-05:00 |
|     |                               |
| 3   | 2020-01-18T02:49:23.000-05:00 |
|     |                               |
| 9   | 2020-03-06T18:18:28.000-05:00 |
|     |                               |
| 2   | 2020-03-06T18:18:28.000-05:00 |
|     |                               |
| 8   | 2020-03-31T23:13:33.000-04:00 |
|     |                               |
| 3   | 2020-08-28T18:23:15.000-04:00 |
|     |                               |
| 2   | 2020-08-28T18:23:15.000-04:00 |
|     |                               |
| 9   | 2020-08-28T18:23:15.000-04:00 |
|     |                               |
| 3   | 2020-08-28T18:23:15.000-04:00 |
|     |                               |
| 8   | 2020-09-15T16:40:29.000-04:00 |
|     |                               |
| 3   | 2020-09-21T20:49:09.000-04:00 |
|     |                               |
| 1   | 2020-11-05T21:31:48.000-05:00 |
|     |                               |
| 6   | 2020-11-05T21:31:48.000-05:00 |
|     |                               |
| 8   | 2020-12-12T04:42:00.000-05:00 |
|     |                               |
| 8   | 2020-12-12T04:42:00.000-05:00 |
|     |                               |
| 5   | 2020-12-12T04:42:00.000-05:00 |
+-----+-------------------------------+

波纹管是我想要的结果:

+------------+---------------------+
|    DATE    | UNIQUE 30 DAY COUNT |
+------------+---------------------+
|            |                     |
| 2020-01-13 | 3                   |
|            |                     |
| 2020-01-18 | 1                   |
|            |                     |
| 2020-03-06 | 3                   |
|            |                     |
| 2020-03-31 | 1                   |
|            |                     |
| 2020-08-28 | 4                   |
|            |                     |
| 2020-09-15 | 2                   |
|            |                     |
| 2020-09-21 | 1                   |
|            |                     |
| 2020-11-05 | 2                   |
|            |                     |
| 2020-12-12 | 2                   |
+------------+---------------------+

谢谢

Answer 1

您可以通过保留一个计数器来计算用户何时被计数，然后在 30(或 31)天后不计数。然后，确定被计数的“孤岛”，并进行聚合。这包括:

对数据进行逆透视以具有每个 session 的“进入计数”和“离开”计数。

每天为您知道的每个用户累积计数，无论他们是否被计数。

这定义了计数的“孤岛”。确定岛屿的起点和终点——清除中间的所有碎屑。

现在，您可以简单地对每个日期进行累计总和，以确定 30 天的 session 。

在 SQL 中，这看起来像:

with t as (
      select uid, date_trunc('day', session_start_time) as s_day, 1 as inc
      from users_sessions
      union all
      select uid, date_trunc('day', session_start_time) + interval '31 day' as s_day, -1
      from users_sessions
     ),
     tt as (  -- increment the ins and outs to determine whether a uid is in or out on a given day
      select uid, s_day, sum(inc) as day_inc,
             sum(sum(inc)) over (partition by uid order by s_day rows between unbounded preceding and current row) as running_inc
      from t
      group by uid, s_day
     ),
     ttt as (  -- find the beginning and end of the islands
      select tt.uid, tt.s_day,
             (case when running_inc > 0 then 1 else -1 end) as in_island
      from (select tt.*,
                   lag(running_inc) over (partition by uid order by s_day) as prev_running_inc,
                   lead(running_inc) over (partition by uid order by s_day) as next_running_inc
            from tt
           ) tt
      where running_inc > 0 and (prev_running_inc = 0 or prev_running_inc is null) or
            running_inc = 0 and (next_running_inc > 0 or next_running_inc is null)
     )
select s_day,
       sum(sum(in_island)) over (order by s_day rows between unbounded preceding and current row) as active_30
from ttt
group by s_day;

Here是一个db<> fiddle 。