代码之家 › 专栏 › 技术社区 › Peter Lang

考虑行之间“差异”的分组行

oracle11g oracle sql

Peter Lang · 技术社区 · 14 年前

我有一个包含开始时间(使用示例中的数字保持简单)和事件持续时间的表。

我想确定“块”及其开始和结束时间。
当上一行(按开始时间排序)的结束时间(开始时间+持续时间)与当前行的开始时间之差为 >=5 ,应开始新的“块”。

这是我的测试数据,包括尝试在注释中进行图形解释:

WITH test_data AS (
  SELECT  0 s, 2 dur FROM dual UNION ALL   --# â â 
  SELECT  2  , 2     FROM dual UNION ALL   --#   â â 
  SELECT 10  , 1     FROM dual UNION ALL   --#           â 
  SELECT 13  , 4     FROM dual UNION ALL   --#              â â â â 
  SELECT 15  , 4     FROM dual             --#                â â â â 
)
--# Should return
--#   0 ..  4                              --# â â â â 
--#  10 .. 19                              --#           â â â â â â â â â

第一个街区从 0 结束于 4 . 因为与下一行的区别是 >= 5 ,在开始另一个块 10 结束于 19 .

我可以使用 LAG 但是我还没有找到如何继续。

我可以在pl/sql循环中解决这个问题,但出于性能方面的考虑,我试图避免这个问题。

关于如何编写此查询有什么建议吗?

提前谢谢,彼得

4 回复 | 直到 14 年前

Vincent Malgrat 14 年前

我使用带有分析功能的子查询来识别和分组连续范围:

SQL> WITH test_data AS (
  2    SELECT  0 s, 2 dur FROM dual UNION ALL   --# â â 
  3    SELECT  2  , 2     FROM dual UNION ALL   --#   â â 
  4    SELECT 10  , 1     FROM dual UNION ALL   --#           â 
  5    SELECT 13  , 4     FROM dual UNION ALL   --#              â â â â 
  6    SELECT 15  , 4     FROM dual             --#                â â â â 
  7  )
  8  SELECT MIN(s) "begin", MAX(s + dur) "end"
  9    FROM (SELECT s, dur, SUM(gap) over(ORDER BY s) my_group
 10             FROM (SELECT s, dur,
 11                           CASE
 12                              WHEN lag(s + dur) over(ORDER BY s) >= s - 5 THEN
 13                               0
 14                              ELSE
 15                               1
 16                           END gap
 17                      FROM test_data
 18                     ORDER BY s))
 19   GROUP BY my_group;

     begin        end
---------- ----------
         0          4
        10         19

Mike Meyers 14 年前

代码会因为许多子查询等而变得有点复杂。这可能是数据的实例,在这些实例中,这不起作用,但我脑子里想不出任何问题。

处理时间数据总是一件痛苦的事!

WITH test_data AS (
  SELECT  0 s, 2 dur FROM dual UNION ALL   --# â â 
  SELECT  2  , 2     FROM dual UNION ALL   --#   â â 
  SELECT 10  , 1     FROM dual UNION ALL   --#           â 
  SELECT 13  , 4     FROM dual UNION ALL   --#              â â â â 
  SELECT 15  , 4     FROM dual             --#                â â â â 
)
select 
-- Group on each block
  min(start_time) as s, 
  max(end_time) - min(start_time) as dur
from (
  select 
    start_time,
    duration, 
    end_time, 
-- number the blocks sequentially 
    sum(is_block_start) over (order by start_time) as block_num
  from (
    select 
      start_time, 
      duration, 
      end_time, 
-- Mark the start of each block
      case 
        when nvl2(prev_end_time, start_time - prev_end_time,5) >= 5 
        then 1 else 0 end as is_block_start
    from (
      select 
        s as start_time, 
        dur as duration, 
        s+dur as end_time,
        lag(s+dur) over (order by s) prev_end_time
      from test_data
    )
  )
)
group by block_num

littlegreen 14 年前

在MS-SQL中,我将使用 ROW_NUMBER() OVER(ORDER BY starttime) AS Rank 在开始时间对行进行排名。

然后,我将编写一个查询,将每一行连接到具有前一个排名的行,并设置一个标志,如果差异大于5或空(第一行)。

然后,我将选择所有具有此标志的行,这些行是起始行,对于此子集,重复对行编号和连接到下一行的过程以获取时间跨度:

blockstarttime1 nextstarttime1 (=starttime2)
blockstarttime2 nextstarttime2 (=starttime3)
blockstarttime3 NULL

最后,可以使用 WHERE starttime BETWEEN blockstarttime and nextstarttime 对结果进行分区。

由你来把这个翻译成甲骨文…

Tony 14 年前

理查德·斯诺格拉斯的一本好书可能会有帮助: Developing Time-Oriented Database Applications in SQL (免费下载)在处理数据库中的时间时,我发现这是非常宝贵的。

看看 Richards page 用于链接到一些书籍更正和相关的zip格式的CD-ROM。