Как устранить дубликаты в определенных столбцах на основе логики в неповторяющихся столбцах
Я пытаюсь изменить этот отчет, который у меня есть. Отчет должен идентифицировать изменения атрибутов набора данных.
Например, лицо и его адрес в отношении продления аренды. Итак, представьте, что человек находился по адресу-1 с 01.01.2018 по 12.03.2018, затем переехал на другой адрес-2, первоначально подписав договор аренды на период с 12.03.2018 по 31.12.2021, но затем изменил договор аренды с 12.03.2018 по 01.10.2018. Затем он пошел дальше и продлил аренду с 02.10.2018 до 31.12.2020. В этом случае я хочу не показывать первоначальный период подписания аренды с 12.03.2018 по 31.12.2021. Текущая таблица хранит все эти записи, и я не хочу, чтобы эта конкретная строка отображалась только для того, чтобы конечный пользователь не запутался.
Как я на это смотрю, если я смогу получить все «дубликаты» в столбцах Person, Person_ID, Phone_Number, end_date, а затем для этих повторяющихся строк сохранить только самую высокую start_date, я смогу правильно составить отчет . Однако я застрял в своей способности исключить min(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;
Теперь для этих строк мне нужно выбрать max(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 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
У меня на самом деле нет такого поля. Даже если бы он у меня был, мне нужно было бы получить максимальную (дату активности), чтобы исключить строку. Я думаю, что того же можно добиться, только сохранив max(дата начала); просто не знаю, как туда добраться.
Ответы на этот вопрос показывают несколько разных способов получить то, что вам нужно. Вы все еще можете получить дубликаты, если у вас есть люди с несколькими договорами аренды, которые начинаются в один и тот же день, но это только начало!
Ответы 2
Вот запрос, который может делать то, что вы ожидаете. Подзапрос использует оконную функцию 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
Спасибо. Это сработало!
Я предполагаю, что ваша логика заключается в том, что вам нужна только одна строка с enddate из 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;
Я думаю, что это тоже сработает, хотя я попробовал предыдущий ответ. Спасибо!
Похоже, вы хотите получить самую последнюю аренду, основываясь не на датах самой аренды, а на дате подписания аренды. (В вашем первом примере у человека есть два договора аренды, которые начинаются 12 марта 2018 года, но вам нужна запись, которую человек подписал последним.) Правильно? Если да, есть ли у вас поле с чем-то вроде «дата активности»?