如何使用SQL计算一列中非连续值的数量？

Question

假设我在Oracle数据库中有一个表，如下表（table_1）所示，该表跟踪特定个人的服务参与情况：

name  day  srvc_ inv
bill  1  1
bill  2  1
bill  3  0
bill  4  0
bill  5  1
bill  6  0
susy  1  1
susy  2  0
susy  3  1
susy  4  0
susy  5  1

我的目标是获得一个汇总表，该表针对所有唯一的个人列出是否涉及服务，以及不同的服务事件数量（在这种情况下，帐单为2，可疑为3），其中由几天内中断活动。

要参与 任何 服务，我将使用以下查询

SELECT table_1."Name", MAX(table_1."Name") AS "any_invl"
FROM table_1
GROUP BY table_1."Name"

但是，我对如何获得服务参与数感到困惑（2）。在R中使用静态数据帧，您将使用游程长度编码（请参阅我的原始问题），但是我不知道如何在SQL中完成此操作。此操作将在大量记录上运行，因此将整个数据帧存储为对象然后在R中运行它是不切实际的。

编辑： 我的期望输出将如下所示：

name  any_invl  n_srvc_inv
bill  1  2
susy  1  3

谢谢你的帮助！

Answer 1

我建议使用lag()。这个想法是要计算一个“ 1”，但仅当前一个值为零或时null：

select name, count(*)
from (select t.*,
             lag(srvc_inv) over (partition by name order by day) as prev_srvc_inv
      from t
     ) t
where (prev_srvc_inv is null or prev_srvc_inv = 0) and
      srvc_inv = 1
group by name;

您可以通过使用默认值来稍微简化一下lag()：

select name, count(*)
from (select t.*,
             lag(srvc_inv, 1, 0) over (partition by name order by day) as prev_srvc_inv
      from t
     ) t
where prev_srvc_inv = 0 and srvc_inv = 1
group by name;