用于删除重复(连续)记录的 SQL，但将最小日期存储在开始日期和最大日期作为结束日期 [英] SQL for deleting records which are duplicate(consecutive), But storing there min date in Start date and max date as End Date

问题描述

我在示例表中有以下输入数据:

I have below input data in a Sample table:

S_ID  C_ID  E_ID   ST_DT        ED_DT
100   A     11AS   01/01/2020   05/01/2020
100   A     11AS   06/01/2020   10/01/2020
100   A     11AS   11/01/2020   15/01/2020
100   A     11BT   16/01/2020   20/01/2020
100   A     11AS   21/01/2020   27/01/2020
100   A     11AS   28/01/2020   30/01/2020

下表中的预期输出:

S_ID  C_ID  E_ID   ST_DT        ED_DT
100   A     11AS   01/01/2020   15/01/2020
100   A     11BT   16/01/2020   20/01/2020
100   A     11AS   21/01/2020   30/01/2020

数据库:Netezza注意:这些是来自数据的样本记录.表中还有其他E_ID.

Database: Netezza Note: These are sample records from data. There are other E_ID in the table's as well.

谢谢

推荐答案

这是一个间隙和孤岛问题.假设你没有间隙，一个简单的方法是行号的差异:

This is a gaps-and-islands problem. Assuming you have no gaps, a simple way is the difference of row numbers:

select s_id, c_id, e_id, min(st_dt), max(ed_dt)
from (select t.*,
             row_number() over (partition by s_id, c_id order by st_dt) as seqnum,
             row_number() over (partition by s_id, c_id, e_id order by st_dt) as seqnum_2
      from t
     ) t
group by s_id, c_id, e_id, (seqnum - seqnum_2);

这篇关于用于删除重复(连续)记录的 SQL，但将最小日期存储在开始日期和最大日期作为结束日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！