sql - 给定另一个字段的值，SQL 创建一个新字段会话

Question

我在处理以下任务时遇到问题。

给定一张像

|    user_id    |    hit_id    |  new_session |
|---------------|--------------|--------------|
|       1       |       1      |       0      |
|       1       |       2      |       0      |
|       1       |       3      |       1      |
|       1       |       4      |       0      |
|      ...      |      ...     |      ...     |
|       5       |      19      |       0      |

在哪里

• user_id和的组合hit_id是独一无二的
• new_session是一个布尔值，用于确定命中是否为此特定用户启动了新会话

我想创建一个新列，session_number将 hit_ids 拆分为会话，同时考虑到：

• 每个 的第一行user_id，一旦按顺序排序，hit_id asc新列的值为 1session_number
• 只要为new_session0，值session_number保持不变
• 什么时候new_session是 1，我必须将 1 加到实际会话数中
• user_id逻辑按ordered by对分区起作用hit_id asc，因此一旦user_id发生更改，会话计数将被重置

我用一些示例数据创建了一个 db-fiddle

user_id = 1（涵盖多个极端情况）的预期输出将是：

|    user_id    |    hit_id    |  new_session | session_number |
|---------------|--------------|--------------|----------------|
|       1       |       1      |       0      |        1       |
|       1       |       2      |       0      |        1       |
|       1       |       3      |       1      |        2       |
|       1       |       4      |       0      |        2       |
|       1       |       5      |       0      |        2       |
|       1       |       6      |       1      |        3       |
|       1       |       7      |       0      |        3       |
|       1       |       8      |       1      |        4       |
|       1       |       8      |       1      |        5       |

我尝试过结合使用 lag()、rank() 和 dense_rank()，但我总是发现一个极端情况，这使得所有尝试都失败了。此外，我完全确信有一种非常简单的方法，我没有考虑到这一点。

标签： sqlwindow-functions

Answer 1

您可以使用累积总和：

select pv.*,
       (1 + sum(new_session) over (partition by user_id order by hit_id)) as session_number
from pageviews pv;

这是一个 db-fiddle。