Вернуть повторяющиеся строки из таблицы
У меня есть таблица, содержащая недопустимые повторяющиеся записи, и я пытаюсь найти ее. Ниже приведен запрос, который возвращает результат.
SELECT Id,
StartDate,
EndDate,
Rate,
TDate,
COUNT(*) AS Record
FROM TableA
GROUP BY Id,
StartDate,
EndDate,
Rate,
TDate
HAVING COUNT(*)>1
Теперь есть требование. Одним из показателей является «NP», который правильно должен иметь дубликаты. Она становится недействительной/неправильной только в том случае, если дублирующая запись «NP» сопровождается любой другой дублированной записью «X» для того же идентификатора и TDate.
Как мне найти то же самое? Прочитав комментарии, я понимаю, что не могу полностью объяснить, в чем проблема, с которой я столкнулся. Часть времени необходима для получения дубликатов, то есть это не дубликат, поскольку время отличается, даже если TDate один и тот же, поэтому в моем запросе я сгруппировал их по начальной и конечной дате. В примере с идентификатором 4 есть 3 данных ставки X, но только 1 из них дублируется.
Здесь мы видим, что идентификатор 1 дублируется скоростью NP. Но для тех же TDate и Id у 1 нет других дублированных данных со скоростью X. Следовательно, это действительная запись.
Идентификатор 2 имеет два дубликата тарифа NP и еще один дубликат тарифа P. Таким образом, дублированный тариф P недействителен. Но дублированная ставка NP действительна, поскольку она не сопровождается ставкой X.
Для идентификатора 3 имеются дубликаты NP, P и X. Таким образом, для этого идентификатора 3 он должен вернуть все 3 как недействительные.
Идентификатор 4 имеет два дубликата ставки P и X, поэтому оба недействительны.
Итак, исходя из приведенных выше данных, я ожидаю следующих результатов:
StartDate и EndDate кажутся дополнительными столбцами, которые не являются частью дублирующейся логики. Проверяется только Tdate, и у него нет времени.
Эта рабочий пример может помочь нам совместно найти решение dbfiddle.uk/KhcbHCyl
@BartMcEndree, я впервые вижу этот сайт. Это мне очень поможет в будущем. Спасибо
ваша логика непонятна.
Будут ли все записи для данного значения Id всегда иметь одно и то же значение Tdate, как в вашем примере? Или может существовать множество значений Tdate для одного и того же Id? Равным образом, всегда ли внутри каждого Rate будут пары записей для одного и того же Id, или для данных Id и Rate может существовать одна запись?
Кроме того, всегда ли для данного Id существует ставка «NP», и если нет, то как следует обрабатывать Id только со ставками, отличными от NP? В целом согласен с предыдущим комментатором, желаемая вами логика неясна. Было бы легко написать запрос, который дает результат, который вы показываете в своем примере, но создается впечатление, что существует множество других состояний данных, которые вы не объяснили или не указали, что они не возникнут.
TDate — это просто значение даты StartDate и EndDate, основанное на StartDate. Не обязательно, чтобы все идентификаторы имели обязательные тарифы NP. Они также могут иметь только X или P, в этом случае, если есть дубликаты, считаются недействительными.
Ответы 3
Вот одно из решений, использующих общее табличное выражение (CTE) для выполнения группировки 1 раз и самостоятельного объединения результатов.
--Duplicated records (exclude records with NP rates unless X rate duplicates in same group)
WITH RecordsWithDuplicates as
(
--Duplicated records
SELECT Id, StartDate, EndDate, Rate, Tdate
FROM Example
GROUP BY ID, Tdate, StartDate, EndDate, Rate
HAVING COUNT(*) > 1
)
SELECT d.id, FORMAT(d.StartDate,'dd-MM-yyyy HH:mm') as StartDate, FORMAT(d.EndDate,'dd-MM-yyyy HH:mm') as EndDate, d.Rate, FORMAT(d.Tdate,'dd-MM-yyyy') as Tdate
FROM RecordsWithDuplicates d
LEFT JOIN RecordsWithDuplicates x on x.id=d.id and x.TDate=d.Tdate and x.Rate='X'
WHERE d.Rate!='NP' OR (d.Rate='NP' AND x.id IS NOT NULL)
Большое тебе спасибо, Барт. Действительно ценю это. Я внес некоторые изменения в вопрос, поскольку дата начала и дата окончания должны быть частью дублирующейся логики, как я объяснил в редактировании. Но я чувствую, что могу работать над решением, исходя из предложенной вами идеи.
@Laba Ningombam Я обновил скрипку и возвращаемые результаты, чтобы они соответствовали вашим изменениям. Я до сих пор не вижу примеров, которые изменили бы запрос или результаты, которые я получаю.
@Laba Ningombam Запрос обновлен и теперь включает в группировку даты начала и окончания.
WITH DuplicateRecords AS (
SELECT
Id,
StartDate,
EndDate,
Rate,
TDate,
COUNT(*) AS RecordCount
FROM TableA
GROUP BY
Id,
StartDate,
EndDate,
Rate,
TDate
HAVING COUNT(*) > 1
),
InvalidNPCheck AS (
SELECT
Id,
TDate,
COUNT(DISTINCT CASE WHEN Rate <> 'NP' THEN Rate END) AS NonNPDuplicateCount
FROM DuplicateRecords
GROUP BY
Id,
TDate
),
InvalidRecords AS (
SELECT
d.Id,
d.StartDate,
d.EndDate,
d.Rate,
d.TDate
FROM
DuplicateRecords d
JOIN
InvalidNPCheck i
ON
d.Id = i.Id AND d.TDate = i.TDate
WHERE
(d.Rate = 'NP' AND i.NonNPDuplicateCount > 0)
OR (d.Rate <> 'NP')
)
SELECT
Id,
StartDate,
EndDate,
Rate,
TDate
FROM
InvalidRecords
ORDER BY
Id, TDate, Rate;
Ваш запрос выдает 7 записей, хотя нужно найти только 6. Первую запись не следует включать, поскольку допускаются повторяющиеся ставки NP, если только дубликат X не находится в группе. dbfiddle.uk/9hI1gQWE
Благодарим вас за вклад в сообщество Stack Overflow. Возможно, это правильный ответ, но было бы очень полезно предоставить дополнительные пояснения к вашему коду, чтобы разработчики могли понять ваши рассуждения. Это особенно полезно для новых разработчиков, которые не так хорошо знакомы с синтаксисом или пытаются понять концепции. Не могли бы вы отредактировать свой ответ, включив в него дополнительную информацию на благо сообщества?
Вам просто нужно посмотреть, есть ли какие-либо строки, отличные от NP, в общем «наборе ставок» (не уверен, что у вас есть лучшее имя).
with dups as (
SELECT
Id, StartDate, EndDate,
Rate, TDate, COUNT(*) AS RecordCount,
count(case when Rate <> 'NP' then 1 end)
over (Id, StartDate, EndDate, TDate) as NonNPCount
FROM TableA
GROUP BY
Id, StartDate, EndDate, Rate, TDate
HAVING COUNT(*) > 1
)
select * from dups
where Rate <> 'NP' or NonNPCount > 0;
Кажется, вы игнорируете временные части ваших свиданий при группировке, это намеренно или нет?