如何根据非重复列中的逻辑消除某些列中的重复项

Question

我正试图修改我已有的这份报告。报告应该标识对一组数据的属性的更改。

例如，一个人和他的地址有关他的租约延期。假设这个人在地址--从2018年1月1日到2018年3月12日，然后搬到另一个地址-2，最初签署2018年3月12日至2021年12月31日的租约，但后来又将租约改为2018年3月12日至2018年10月1日。随后，他继续将租约从2018年2月10日延长到2020年12月31日。在这种情况下，我不想显示从2018年3月12日到2021年12月31日的初始租约签署期。当前表存储所有这些记录，我不希望显示这个特定的行，这样最终用户就不会感到困惑。

按照我的看法，如果我能够得到Person、Person_ID、Phone_Number、end_date列中的所有“重复”，然后对于那些重复的行，只保留最高的start_date，我就能够以正确的方式报告。但是，我仍然无法在显示所有数据的同时从这些行中消除最小值(Start_date)。

select 
    subset.person, 
    subset.person_id, 
    subset.phone_number, 
    subset.end_date, 
    count(*)
from subset
group by 
    subset.subset.person, 
    subset.person_id, 
    subset.phone_number, 
    subset.end_date
having count(*)>1
order by person_id;

现在，对于这些行，我需要选择最大值(Start_date)，并且只保留报表中的那些(我想我被困在那里了)。

因此，该表实质上显示了以下内容：

Person  Person ID   Phone Number    Address 1   Zip Code    Start Date  End Date
Jane    1   8792029484  some address-1  12345   3/5/2016    11/9/2018
Jane    1   8792029484  some address-2  34455   11/10/2018  12/31/9999
Jane    1   8792029484  some address-2  34455   11/10/2018  12/7/2018
Jane    1   8792029484  some address-2  34455   12/8/2018   12/31/9999
John    2   9808845768  another address-1   68687   1/1/2013    11/9/2018
John    2   9808845768  another address-2   89384   11/10/2018  12/31/9999
John    2   9808845768  another address-2   89384   11/10/2018  12/7/2018
John    2   9808845768  another address-2   89384   12/8/2018   12/31/9999

对于Jane和John，我不希望显示以下行：

    Jane    1   8792029484  some address-2  34455   11/10/2018  12/31/9999
    John    2   9808845768  another address-2   89384   11/10/2018  12/31/9999

预期结果：

   Person   Person ID   Phone Number    Address 1   Zip Code    Start Date  End Date
   Jane 1   8792029484  some address-1  12345   3/5/2016    11/9/2018
   Jane 1   8792029484  some address-2  34455   11/10/2018  12/7/2018
   Jane 1   8792029484  some address-2  34455   12/8/2018   12/31/9999
   John 2   9808845768  another address-1   68687   1/1/2013    11/9/2018
   John 2   9808845768  another address-2   89384   11/10/2018  12/7/2018
   John 2   9808845768  another address-2   89384   12/8/2018   12/31/9999

Answer 1

这里有一个查询，可以按您的预期执行。子查询使用窗口函数ROW_NUMBER()在具有相同PersonID/adress1/StartDate.的记录组中为每个记录分配一个编号。行号由EndDate排序。然后，外部查询只保留每个组中具有最小EndDate的记录。

SELECT person, personid, phonenumber, address1, zipcode, startdate, enddate
FROM (
    SELECT 
        s.*, 
        ROW_NUMBER() 
            OVER(PARTITION BY personID, address1, startdate ORDER BY enddate) rn
    FROM subset s
) x WHERE rn = 1

这个带有示例数据的DB Fiddle演示返回：

PERSON | PERSONID | PHONENUMBER | ADDRESS1          | ZIPCODE | STARTDATE | ENDDATE  
:----- | -------: | ----------: | :---------------- | ------: | :-------- | :--------
Jane   |        1 |  8792029484 | some-address-1    |   12345 | 05-MAR-16 | 09-NOV-18
Jane   |        1 |  8792029484 | some-address-2    |   34455 | 10-NOV-18 | 07-DEC-18
Jane   |        1 |  8792029484 | some-address-2    |   34455 | 08-DEC-18 | 31-DEC-99
John   |        2 |  9808845768 | another-address-1 |   68687 | 01-JAN-13 | 09-NOV-18
John   |        2 |  9808845768 | another-address-2 |   89384 | 10-NOV-18 | 07-DEC-18
John   |        2 |  9808845768 | another-address-2 |   89384 | 08-DEC-18 | 31-DEC-99

Answer 2

我猜想，您的逻辑是只需要一个带有enddate of 12/31/9999的行，这应该来自最近的startdate。

如果是的话：

select t.*
from (select t.*,
             row_number() over (partition by person_id, enddate order by startdate desc) as seqnum
      from subset t
     ) t
where enddate <> date '9999-12-31' or seqnum = 1;