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

I have a table with a date from 02/11/2015 to 01/12/2015. eg...

我有一张桌子，日期是2015年2月11日到2015年12月1日。如……

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

This table may have some missing dates also.. Lets assume 06/11/2015 and 20/11/2015 is missing from this table.

这张桌子也可能有一些日期漏了。让我们假设这个表格中缺少06/11/2015和20/11/2015。

I want to get an output like

我想要一个输出

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

Kindly help me to get this output in oracle plsql

请帮助我获得oracle plsql中的这个输出

1 个解决方案

#1

You can do this with lead and lag analytic functions - in a subquery which you then group over, which may be what you missed - but you can also do it with an analytic 'trick'.

您可以使用lead和lag分析函数来实现这一点——在子查询中进行分组，这可能是您所遗漏的——但是您也可以使用分析“技巧”来实现这一点。

If you look at the difference between each date and the lowest date you get a broken sequence, in your case 0, 1, 2, 3, 5, ..., 27, 28, 29. You can see that with attndate - min(attndate) over ().

如果你看一下每个日期和最低日期的区别，你会得到一个坏的序列，在你的案例中，0 1 2 3 5，…27日,28日,29岁。您可以看到，attndate - min(attndate)除以()。

You also have another unbroken sequence available from row_number() over (order by attndate), which gives you 1, 2, 3, ... 28.

您还可以从row_number() /(按attndate排序)中获得另一个未中断的序列，它将为您提供1、2、3、……28。

If you subtract one from the other each contiguous block of dates gets the same answer, which I've called 'slot_no':

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

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

With this data every row gets either -1, 0 or 1. (You can add two to those to make them more friendly if you want, but that only really works if the gaps in the data are single days). You can then group by that slot number:

有了这个数据，每一行都得到-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;

With some generated data:

生成一些数据:

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

If your real scenario is getting these ranges for multiple keys, say a person ID, then you can add a partition by clause to each of the analytic function calls, in the three over () sections.

如果您的真实场景是为多个键(比如person ID)获取这些范围，那么您可以在3 /()小节中向每个解析函数调用添加一个逐句的分区。

#1