Oracle -从行组中获取最小和最大日期

eit6fx6z 于 2023-06-05 发布在 Oracle

关注(0)|答案(2)|浏览(267)

初始数据：

select t.AGENHI, t.TACTHI, t.DTEFHI, t.DTFIHI            
      from mytable t
      where agenhi = '81000040' ;

AGENHI      TACTHI   DTEFHI     DTFIHI            
81000040    1        24/02/92   08/03/92
81000040    1        09/03/92   28/02/93
81000040    1        01/03/93   31/05/97
81000040    0,8      01/06/97   31/12/97
81000040    1        01/01/98   31/12/98
81000040    1        01/01/99

使用此查询：

SELECT AGENHI, 
DECODE(TACTHI, 0.05, '005', 0.07, '007', 0.1, '010', 0.137, '013', 0.15, 
'015', 0.2, '020', 0.21, '021', 0.23, '023', 0.25, '025', 0.3, '030', 
0.34, '034', 0.4, '040', 0.45, '045', 0.5, '050', 0.6, '060', 0.63, 
'063',0.7, '070', 0.75, '075', 0.8,'080', 0.84, '084',0.9, '090', 1, '100', 
TACTHI) as QUOTITE,
dtefhi as START_DATE,
DECODE(LEAD (DTFIHI, 1) OVER (ORDER BY DTFIHI NULLS LAST) ,null, 
to_date('31122099','ddmmyyyy'), LEAD (DTFIHI, 1) OVER (ORDER BY DTFIHI NULLS 
LAST)) AS END_DATE
FROM MYTABLE 
WHERE AGENHI = '81000040' AND DTFIHI IS NOT NULL;

我得到：

AGENHI      QUOTITE     START_DATE    END_DATE
81000040    100         08/03/92      28/02/93
81000040    100         28/02/93      31/05/97
81000040    100         31/05/97      31/12/97
81000040    080         31/12/97      31/12/98
81000040    100         31/12/98      31/12/99

但我需要组“报价”时，下一行是相同的，并显示第一行的开始日期和最后一行的日期。
预期结果：

AGENHI      QUOTITE     START_DATE    END_DATE
81000040    100         24/02/92      31/12/97
81000040    080         01/06/97      31/12/98
81000040    100         01/01/98      31/12/99

Gordon提供的解决方案：
select agenhi，tacthi，min（dtfihi）as start_date，lead（max（dtfihi））over（partition by agenhi，seqnum - seqnum_2 order by max（dtfihi））as end_date from（select t.*，row_number（）over（partition by agenhi order by dtfihi）as seqnum，row_number（）over（partition by agenhi，tacthi order by dtfihi）as seqnum_2 from HIA@CHRONOS_TO_S2.WORLD t其中agenhi = '81000040' and dtfihi IS NOT NULL）t group by agenhi，（seqnum - seqnum_2），tacthi;
结果：

81000040    1   08/03/92   null 
81000040    1   31/12/98   null   
81000040    0,8 31/12/97   null

如何获取end_date？我会看看如果我找到了解决方案，谢谢！

oracle

来源：https://stackoverflow.com/questions/51668150/oracle-get-min-max-date-from-group-of-rows

2条答案

按热度按时间

jv2fixgn1#

你有一个缺口和岛屿的问题。我会从原始数据开始，像这样：

select agenhi, tacthi, min(dtfihi) as start_date,
       lead(min(dtfihi)) over (partition by agenhi order by min(dtfihi)) as end_date
from (select t.*,
             row_number() over (partition by agenhi, order by dtfihi) as seqnum,
             row_number() over (partition by agenhi, tacthi order by dtfihi) as seqnum_2
      from mytable t
      where agenhi = '81000040' an dtfihi IS NOT NULL
     ) t
group by agenhi, (seqnum - seqnum_2), tacthi;

您可以添加decode()逻辑（我将其编写为case表达式），但这并不会真正改变解决方案。

赞(0）回复(0）举报 2023-06-05

wqnecbli2#

@Boneist，@GordonLinoff再次感谢您的帮助。

select 
agenhi, 
DECODE(TACTHI, 0.05, '005', 0.07, '007', 0.1, '010', 0.137, '013', 0.15, '015', 0.2, '020', 0.21, '021', 0.23, '023', 0.25, '025', 0.3, '030', 
0.34, '034', 0.4, '040', 0.45, '045', 0.5, '050', 0.6, '060', 0.63, '063',0.7, '070', 0.75, '075', 0.8,'080', 0.84, '084',0.9, '090', 1, '100', TACTHI) as QUOTITECPAGE,  
min(dtefhi) as start_date,
nvl(lead(min(dtfihi)) over (partition by agenhi order by min(dtfihi)), 
to_date('31122099','ddmmyyyy')) as end_date
from (select t.*,
         row_number() over (partition by agenhi order by dtfihi) as seqnum,
         row_number() over (partition by agenhi, tacthi order by dtefhi) as seqnum_2
  from HIA@CHRONOS_TO_S2.WORLD t
  where agenhi = '81000040' order by DTEFHI
 ) t
 group by agenhi, (seqnum - seqnum_2), tacthi;

赞(0）回复(0）举报 2023-06-05

我来回答

Oracle -从行组中获取最小和最大日期

2条答案

相关问题

热门标签

最新问答