使用SQL来识别开始和结束日期的时间

Question

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

假设我们有下表（请注意，eright_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代码。此问题的挑战是确定所有启动/结束对以创建ofere_id列。此挑战的一个重要性不一致：end_period代码是 INVALID start_period代码的28天。例如，第2行中的end_period代码无效，因为它在1月1日1月14天之后的1月15日。相反，有效的end_period代码在第5行上，因为它已超过28天。
• date ：交易日期
• erceed_id ：所需的行 - 此信息当前不在表格上。

Answer 1

这是使用始终有趣的match_recognize的答案。请注意，您实际上不应将列"代码"或"日期"命名，因为它们是保留的关键字。

match_reventize在多行上操作，并尝试匹配给定模式。在您的情况下，您正在尝试匹配起始代码的模式，然后是零或更多无效的终端代码/其他代码，然后是有效的终端代码。

WITH test_vals AS (
    SELECT 1 as person_ID,'Start_period' as my_code,to_date('Jan 1','mon dd') as my_date FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('Jan 15','mon dd') FROM DUAL
    UNION ALL SELECT 1,'Random_code1',to_date('Feb 15','mon dd') FROM DUAL
    UNION ALL SELECT 1,'Random_code2',to_date('Feb 28','mon dd') FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('March 31','mon dd') FROM DUAL
    UNION ALL SELECT 1,'Start_period',to_date('May 31','mon dd') FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('June 11','mon dd') FROM DUAL
    UNION ALL SELECT 1,'End_period',to_date('October 28','mon dd') FROM DUAL
)
SELECT m.person_id,
       m.my_code,
       m.my_date,
       m.period_id
FROM test_vals t
match_recognize(
    PARTITION BY person_id
    ORDER BY my_date
    MEASURES
        match_number() AS period_id /* Return the match number as the period ID */
    ALL ROWS PER match
    pattern (
        start_code /* Match a single start code */
        (invalid_end_code | other_code)* /* Match zero or more invalid end codes or other codes */
        valid_end_code /* Match a single end code */
    )
    define
        start_code AS my_code = 'Start_period', /* Start codes are always valid */
        valid_end_code AS my_code = 'End_period' AND (my_date - FIRST(my_date)) > 28, /* End codes are only valid if they come more than 28 days after the start of the pattern match */
        invalid_end_code AS my_code = 'End_period' AND (my_date - FIRST(my_date)) <= 28,
        other_code AS my_code NOT IN ('Start_period', 'End_period')
) m

Answer 2

只需计算每行开始的起点数量：

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;

这适用于您提供的数据。它不会正式合并其他规则，例如末日之间的时间。

Answer 3

我会使用递归cte，就像这里：

with c(row_, code, date_, st_date, period, chg) as (
    select row_, code, date_, date_, 1, 0 from t where row_ = 1
    union all
    select t.row_, t.code, t.date_, 
           case when chg = 1 then t.date_ else st_date end, 
           case when chg = 1 then period + 1 else period end, 
           case when t.code = 'End_period' and t.date_ - c.st_date > 28 then 1 else 0 end
      from t join c on t.row_ = c.row_ + 1
    )
select row_, code, date_, period from c

^{dbfiddle demo}

逻辑是使用引导周期更改的列chg。当代码为End period时，Chg设置为1，并且日期大于先前记住的开始日期。在下一步期间，chg重置为零，并设置新的启动日期。