时间戳

首页 > 解决方案 > 分组日期之间的间隔

sql - 分组日期之间的间隔

问题描述

我正在尝试找到一个函数，该函数允许我创建将原始日期转换为剧集，我在其中设置构成剧集的参数。对于此示例，日期之间的 3 天间隔将是一个新的“情节”，但最终希望将其缩放为任何 x =

我已经尝试过差距和孤岛问题以及领先和滞后 1 天的时间段，但无法让代码为每个 3 天的窗口间隔提供一行。

drop table RUNNING_LOG;
create table running_log ( 
    run_date          date not null,  
    time_in_seconds   int  not null, 
    distance_in_miles int  not null
);
truncate table running_log;

begin  
    insert into running_log values (date'2018-01-01', 420, 1);  
    insert into running_log values (date'2018-01-02', 2400, 5);  
    insert into running_log values (date'2018-01-03', 2430, 5);  
    insert into running_log values (date'2018-02-06', 2350, 5);  
    insert into running_log values (date'2018-02-07', 410, 1);  
    insert into running_log values (date'2018-07-10', 400, 1); ---4 month gap 
    insert into running_log values (date'2018-08-13', 2300, 5);  
    insert into running_log values (date'2018-12-31', 425, 1);  
    insert into running_log values (date'2019-01-01', 422, 1);  
    insert into running_log values (date'2019-01-06', 2350, 5);  
    insert into running_log values (date'2019-02-07', 410, 1);  
    insert into running_log values (date'2019-06-10', 400, 1);  
    insert into running_log values (date'2019-07-13', 2300, 5);  
    insert into running_log values (date'2019-08-14', 425, 1);  
    insert into running_log values (date'2019-12-15', 422, 1);
    insert into running_log values (date'2020-01-01', 425, 1);  
    insert into running_log values (date'2020-03-31', 422, 1); 
    insert into running_log values (date'2020-04-15', 422, 1);
    insert into running_log values (date'2020-06-01', 425, 1); 
    insert into running_log values (date'2020-07-06', 425, 1); 
    insert into running_log values (date'2021-03-31', 422, 1);  
    commit;  
end;

select * from running_log

理想情况下，我想构建一个表，其中包含第 3 天或更长时间的 begin_date 和少于 3 天的聚合行的聚合数据。下面的代码只做 1 天的间隔，并且不为每个间隔提供 begin_date end_date。

with grps as (  
  select run_date
        , row_number() over (order by run_date) rn ,  
         run_date - row_number() over (order by run_date) grp_date  
  from   running_log  
)  
  select min(run_date) first_run
        , max(run_date) last_run,   
         count(*) runs,   
         row_number() over (order by min(run_date)) grp  
  from   grps  
  group  by grp_date  
  order  by min(run_date)

再一次，一行将是 1. 3 天的间隔或 3 天之间的聚合。

标签： sqloracle

解决方案

这确实是某种差距和孤岛问题。

您将需要多个级别的子查询来处理此问题。首先计算ROW_NUMBER()和使用LAG()来获得先前的run_date值，然后根据连续记录之间的差距进行条件求和。最后，行号和条件总和之间的差异为您提供了组。

考虑：

SELECT
    MIN(run_date) first_run,
    MAX(run_date) last_run,
    COUNT(*) runs
FROM (
    SELECT 
        x.*,
        SUM(CASE WHEN run_date - lg <= 3 THEN 1 ELSE 0 END) OVER(ORDER BY run_date) rn2
    FROM (
        SELECT 
            t.*, 
            ROW_NUMBER() OVER(ORDER BY run_date) rn1,
            LAG(run_date) OVER(ORDER BY run_date) lg,
        FROM running_log t
    ) x
) y
GROUP BY rn1 - rn2
ORDER BY 1

要调整允许间隙的长度，您可以简单地修改条件和中的表达式：run_date - lg <= :max_gap_length

DB Fiddle上的这个演示与您的示例数据返回：

第一次运行 | 上次运行 | 运行
:-------- | :-------- | ---:
2018 年 1 月 1 日 | 2018 年 1 月 3 日 | 3
18 年 2 月 6 日 | 2018 年 2 月 7 日 | 2
18 年 7 月 10 日 | 18 年 7 月 10 日 | 1
18 年 8 月 13 日 | 18 年 8 月 13 日 | 1
18 年 12 月 31 日 | 19 年 1 月 1 日 | 2
2019 年 1 月 6 日 | 2019 年 1 月 6 日 | 1
2019 年 2 月 7 日 | 2019 年 2 月 7 日 | 1
19 年 6 月 10 日 | 19 年 6 月 10 日 | 1
19 年 7 月 13 日 | 19 年 7 月 13 日 | 1
19 年 8 月 14 日 | 19 年 8 月 14 日 | 1
19 年 12 月 15 日 | 19 年 12 月 15 日 | 1
20 年 1 月 1 日 | 20 年 1 月 1 日 | 1
20 年 3 月 31 日 | 20 年 3 月 31 日 | 1
20 年 4 月 15 日 | 20 年 4 月 15 日 | 1
01-JUN-20 | 01-JUN-20 | 1
20 年 7 月 6 日 | 20 年 7 月 6 日 | 1
21 年 3 月 31 日 | 21 年 3 月 31 日 | 1

注意：这是一个有趣的问题！

推荐阅读