时间戳

首页 > 解决方案 > 计算 SQL 中连续日期的事件运行计数

sql - 计算 SQL 中连续日期的事件运行计数

问题描述

我有数据可以总结如下：

eventid startdate   enddate     productkey  date        startGroup  endGroup    eventGroup
123     2020-01-01  2020-01-10  123456      2020-01-01  1           0           1
123     2020-01-01  2020-01-10  123456      2020-01-02  0           0           1
123     2020-01-01  2020-01-10  123456      2020-01-03  0           0           1
123     2020-01-01  2020-01-10  123456      2020-01-04  0           1           1
234     2020-01-05  2020-01-07  123456      2020-01-05  1           0           2
234     2020-01-05  2020-01-07  123456      2020-01-06  0           0           2
234     2020-01-05  2020-01-07  123456      2020-01-07  0           1           2
123     2020-01-01  2020-01-10  123456      2020-01-08  1           0           1
123     2020-01-01  2020-01-10  123456      2020-01-09  0           0           1
123     2020-01-01  2020-01-10  123456      2020-01-10  0           1           1

为商品收藏各种各样的活动。由于它们可能重叠，我已经有代码去重复数据，但是现在，由于缺少一些（去重复）天，我需要在事件级别将数据重新组合在一起。在示例数据中，您会看到两个事件，123（从 1/1 运行到 1/10）和 234（从 1/5 运行到 1/7）。我已经剪掉了中间两天以消除重叠，我想要输出的是三组事件

我已经有代码可以为每个时间块找到正确的开始和结束条目，但不知道如何正确计算 eventGroup 列。最后三列的当前代码如下：

CASE WHEN DATEADD(DAY, -1, date) = LAG(date) OVER (PARTITION BY eventid, productkey ORDER BY date) THEN 0 ELSE 1 END startGroup,
CASE WHEN DATEADD(DAY, +1, date) = LEAD(date) OVER (PARTITION BY eventid, productkey ORDER BY date) THEN 0 ELSE 1 END endGroup,
dense_rank() over (order by eventid, productkey) eventGroup

我已经尝试过https://dba.stackexchange.com/questions/193680/group-rows-by-uninterrupted-dates之类的方法，但仍然无法创建正确的组。

在 Excel 逻辑中，它是 eventGroup = if ( startGroup = 0，前一行的 eventGroup，前一行的 eventGroup + 1)，但不确定如何在此处复制该运行计数器。

有人可以帮忙吗？谢谢！

标签： sqlsql-servertsqlgrouping

解决方案

要分配组，请使用累积总和：

select t.*,
       sum(startGroup) over (partition by eventId, productKey order by startdate)
from t;

注意：这假设您要使用事件/产品组合重新开始编号。

推荐阅读