Oracle按连续的日期块选择数据 [英] Oracle select data by contiguous date blocks

问题描述

我正在使用Oracle数据库，并且我有一个表，该表包含2列，其数据如下所示:

I am using an Oracle database and I have a table that has 2 columns with data like so:

HASH | DATE
-----------------
abcd | 2017-11-01
abcd | 2017-11-02
abcd | 2017-11-03
wxyz | 2017-11-04
wxyz | 2017-11-05
abcd | 2017-11-06
wxyz | 2017-11-07
abcd | 2017-11-08
abcd | 2017-11-09
lmno | 2017-11-10
lmno | 2017-11-11

我想知道看到每个哈希的时间窗口.就像

I want to know the windows of time that each hash is seen. So like

hash | start      | end
------------------------------
abcd | 2017-11-01 | 2017-11-03
wxyz | 2017-11-04 | 2017-11-05
abcd | 2017-11-06 | 2017-11-06
wxyz | 2017-11-07 | 2017-11-07
abcd | 2017-11-08 | 2017-11-09
lmno | 2017-11-10 | 2017-11-11

到目前为止，我基本上是这样的:

What I have so far is basically this:

SELECT HASH, MIN(DATE) ST, MAX(DATE) ED
FROM HASH_TABLE 
GROUP BY HASH 
ORDER BY 3 DESC

这几乎可行，但是它将在2017年11月1日开始到2017年11月9日结束时给我像"abcd"，这隐藏"了它在中间切换的事实.

And this almost works but it will give me like "abcd" as a start of 2017-11-01 and an end of 2017-11-09 which "hides" the fact that it switched in the middle.

是否可以通过连续的日期/时间块"将这些结果分组?

Is there some way to group these results by contiguous date/time "blocks"?

推荐答案

它看起来像是间隙和孤岛"问题:

It looks like "gaps and islands" problem:

WITH cte("hash","date") AS (
    SELECT 'abcd',  DATE'2017-11-01' FROM dual UNION ALL
    SELECT 'abcd',  DATE'2017-11-02' FROM dual UNION ALL
    SELECT 'abcd',  DATE'2017-11-03' FROM dual UNION ALL
    SELECT 'wxyz',  DATE'2017-11-04' FROM dual UNION ALL
    SELECT 'wxyz',  DATE'2017-11-05' FROM dual UNION ALL
    SELECT 'abcd',  DATE'2017-11-06' FROM dual UNION ALL
    SELECT 'wxyz',  DATE'2017-11-07' FROM dual UNION ALL
    SELECT 'abcd',  DATE'2017-11-08' FROM dual UNION ALL
    SELECT 'abcd',  DATE'2017-11-09' FROM dual UNION ALL
    SELECT 'lmno',  DATE'2017-11-10' FROM dual UNION ALL
    SELECT 'lmno',  DATE'2017-11-11' FROM dual 
)
select "hash"
      ,min("date") as startdate
      ,max("date") as enddate
from (
    select "date","hash"
         , row_number() over (order by "date") 
         - row_number() over (partition by "hash" order by "date") as grp
    from cte
) A
group by "hash", grp
ORDER BY startdate;

DBFiddle演示

DBFiddle Demo

这篇关于Oracle按连续的日期块选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！