sql - 需要 Oracle sql 查询来对日期进行分组

Question

我有一个日期从 02/11/2015 到 01/12/2015 的表格。例如……

ATTNDATE
--------
02/11/2015
03/11/2015
--
--
--
01/12/2015.

此表也可能缺少一些日期。假设此表中缺少 06/11/2015 和 20/11/2015。

我想得到这样的输出

SL.No      ATTNFROM          ATTNTILL
  1.       02/11/2015        05/11/2015
  2.       07/11/2015        19/11/2015
  3.       21/11/2015        01/12/2015

请帮助我在 oracle plsql 中获得此输出

Answer 1

您可以使用前导和滞后分析函数来执行此操作 - 在一个子查询中，然后将其分组，这可能是您错过的 - 但您也可以使用分析“技巧”来执行此操作。

如果您查看每个日期和最低日期之间的差异，您会得到一个损坏的序列，在您的例子中是 0、1、2、3、5、...、27、28、29。您可以看到attndate - min(attndate) 超过 ()。

您还可以从 row_number() over (order by attndate) 获得另一个完整的序列，它给您 1, 2, 3, ... 28。

如果你从另一个中减去一个，每个连续的日期 block 都会得到相同的答案，我称之为“slot_no”:

select attndate,
  attndate - min(attndate) over ()
    - row_number() over (order by attndate) as slot_no
from your_table;

有了这些数据，每一行都会得到 -1、0 或 1。(如果需要，您可以在其中添加两个以使它们更友好，但这只有在数据中的间隔是一天的情况下才真正有效)。然后您可以按该槽号分组:

with cte as (
  select attndate,
    attndate - min(attndate) over ()
      - row_number() over (order by attndate) as slot_no
  from your_table
)
select dense_rank() over (order by slot_no) as slot_no,
  min(attndate) as attnfrom, max(attndate) as attntill
from cte
group by slot_no
order by slot_no;

一些生成的数据:

alter session set nls_date_format = 'DD/MM/YYYY';
with your_table (attndate) as (
  select date '2015-11-02' + level - 1 from dual connect by level <= 4
  union all select date '2015-11-07' + level - 1 from dual connect by level <= 13
  union all select date '2015-11-21' + level - 1 from dual connect by level <= 11
),
cte as (
  select attndate,
    attndate - min(attndate) over ()
      - row_number() over (order by attndate) as slot_no
  from your_table
)
select dense_rank() over (order by slot_no) as slot_no,
  min(attndate) as attnfrom, max(attndate) as attntill
from cte
group by slot_no
order by slot_no;

   SLOT_NO ATTNFROM   ATTNTILL 
---------- ---------- ----------
         1 02/11/2015 05/11/2015
         2 07/11/2015 19/11/2015
         3 21/11/2015 01/12/2015

如果您的真实场景是获取多个键的这些范围，比如说一个人的 ID，那么您可以在三个 中为每个分析函数调用添加一个 partition by 子句() 部分。