sql - 使用 SQL 识别具有开始日期和结束日期的时间段

Question

我正在为一个研究项目准备一些数据，并且在应对以下挑战时遇到了麻烦。如果可能的话，我想用 SQL 或 PL SQL 来做所有事情（尽管我是一个真正的 PL 新手）。

假设我们有下表（注意 Period_ID 是我要创建的所需行）：

+-------+-----------+--------------+--------------+-----------+
| Row # | Person_ID |     Code     |     Date     | Period_ID |
+-------+-----------+--------------+--------------+-----------+
|     1 |         1 | Start_period | Jan 1st      |         1 |
|     2 |         1 | End_period   | Jan 15th     |         1 |
|     3 |         1 | Random_code1 | Feb 15th     |         1 |
|     4 |         1 | Random_code2 | Feb 28th     |         1 |
|     5 |         1 | End_period   | March 31st   |         1 |
|     6 |         1 | Start_period | May 31st     |         2 |
|     7 |         1 | End_period   | June 11th    |         2 |
|     8 |         1 | End_period   | October 28th |         2 |
+-------+-----------+--------------+--------------+-----------+

专栏和挑战：

• Person_ID：以上数据都是针对一个人的（该数据处于事务级别）。
• 代码：此代码可以是 Start_period、End_period 或任何随机代码。每个 Start_period 代码都应该有一个对应的 End_period 代码。这个问题的挑战是识别所有的开始/结束对来创建 Period_ID 列。 这一挑战的一个重要细微差别：如果 End_period 代码在 Start_period 代码的28 天内，则它是无效的。例如，第 2 行中的 End_period 代码无效，因为它是在 1 月 15 日，距离 1 月 1 日仅 14 天。相反，有效的 End_period 代码位于第 5 行，因为它晚于 28 天。
• 日期：交易日期
• Period_ID：所需的行 - 此信息当前不在表中。

标签： sqloraclegaps-and-islands

Answer 1

只需计算每行的开始时间段数：

select t.*,
       sum(case when code = 'Start_period' then 1 else 0 end) over (partition by person_id order by date) as period_id
from t;

这适用于您提供的数据。它没有正式纳入其他规则，例如结束时段之间的时间。