Oracle SQL如何分组，但如果以后重复分组，则有多行 [英] Oracle SQL how to group by, but have multiple rows if group is repeated at a later date

问题描述

我有以下查询

select paaf.assignment_id,
       paaf.position_id,
       paaf.effective_start_date effective_start_date,
       paaf.effective_end_date   effective_end_date
from   per_all_assignments_f paaf
where  paaf.position_id is not null
and    paaf.assignment_type in ('E', 'C')
and    paaf.primary_flag = 'Y'
and    paaf.assignment_number like '209384%'
order  by 3

返回哪个

"assignment_id" "position_id"   "effective_start_date"  "effective_end_date"
6518    5323    01/01/2013  28/02/2014
6518    8133    01/03/2014  30/06/2014
6518    8133    01/07/2014  31/10/2015
6518    239570  01/11/2015  15/11/2015
6518    239570  16/11/2015  31/12/2015
6518    8133    01/01/2016  27/07/2016
6518    8133    28/07/2016  31/12/4712

我使用以下方法将其分组:

I grouped this using:

select paaf.assignment_id,
       paaf.position_id,
       min(paaf.effective_start_date) effective_start_date,
       max(paaf.effective_end_date)   effective_end_date
from   per_all_assignments_f paaf
where  paaf.position_id is not null
and    paaf.assignment_type in ('E', 'C')
and    paaf.primary_flag = 'Y'
and    paaf.assignment_number like '209384%'
group  by paaf.assignment_id, paaf.position_id

哪个返回:

"assignment_id" "position_id"   "effective_start_date"  "effective_end_date"
6518    5323    01/01/2013  28/02/2014
6518    8133    01/03/2014  31/12/4712
6518    239570  01/11/2015  31/12/2015

但是我需要一个返回的查询

But I need a query that returns

"assignment_id" "position_id"   "effective_start_date"  "effective_end_date"
6518    5323    01/01/2013  28/02/2014
6518    8133    01/03/2014  31/10/2015
6518    239570  01/11/2015  31/12/2015
6518    8133    01/01/2016  31/12/4712

也就是说8133的position_id必须有两行，因为按时间顺序有两个部分必须分组为2行而不是1(对于8133).

That is to say the position_id of 8133 must have two rows since there are two sections chronologically that must be grouped into 2 rows and not 1 (for 8133).

是否有某种使用日期顺序完成此操作的方法?

Is there some way of accomplishing this using the date order?

答案是:

with paaf as
            (
               select paaf.assignment_id,
                      paaf.position_id,
                      paaf.effective_start_date effective_start_date,
                      paaf.effective_end_date   effective_end_date
               from   per_all_assignments_f paaf
               where  paaf.position_id is not null
               and    paaf.assignment_type in ('E', 'C')
               and    paaf.primary_flag = 'Y'
            -- and    paaf.assignment_number like '209384%'
               order  by 1, 3
            )
            select paaf2.assignment_id,
                   paaf2.position_id,
                   min(paaf2.effective_start_date) as effective_start_date,
                   max(paaf2.effective_end_date)   as effective_end_date
            from   (
                      select paaf.*,
                             row_number() over (order by paaf.assignment_id, paaf.effective_start_date) as seqnum,
                             row_number() over (partition by paaf.assignment_id, paaf.position_id order by paaf.assignment_id, paaf.effective_start_date) as seqnum_p
                      from   paaf
                   )  paaf2
            group  by (paaf2.seqnum - paaf2.seqnum_p), paaf2.assignment_id, paaf2.position_id    

推荐答案

这是一个孤岛问题.有多种方法，但是一种简单的方法是使用不同的行号:

This is a gaps-and-islands problem. There are different approaches, but a simple one uses a difference of row number:

with paaf as (<your first query here>
     )
select paaf.assignment_id,
       paaf.position_id,
       min(paaf.effective_start_date) as effective_start_date,
       max(paaf.effective_end_date) as effective_end_date
from (select paaf.*,
             row_number() over (order by effective_start_date) as seqnum,
             row_number() over (partition by position_id order by effective_start_date) as seqnum_p
      from paaf
     ) paaf
group by (seqnum - seqnum_p), position_id, assignment_id;

这篇关于Oracle SQL如何分组，但如果以后重复分组，则有多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！