SQL Capture повторяющиеся записи в двух РАЗНЫХ столбцах
Я пишу страницу перехвата исключений, используя MySQL, для перехвата повторяющихся записей о выставлении счетов по следующему сценарию.
Детали элементов вводятся в таблицу, которая имеет следующие два столбца (среди прочих).
ItemCode VARCHAR(50), BillEntryDate DATE
Часто бывает так, что счет за один и тот же товар вводится несколько раз, но в течение нескольких дней. Нравится,
"Football","2019-01-02"
"Basketball","2019-01-02"
...
...
"Football","2019-01-05"
"Rugby","2019-01-05"
...
"Handball","2019-01-05"
"Rugby","2019-01-07"
"Rugby","2019-01-10"
В приведенном выше примере элемент Football оплачивается дважды: сначала 2 января, а затем 5 января. Точно так же товар Rugby оплачивается трижды 5, 7, 10 января.
Я хочу написать простой SQL, который может подобрать каждый элемент [скажем, с использованием отдельного (ItemCode) предложения], а затем отобразить все записи, которые являются дубликатами в течение 30 дней. В приведенном выше случае ожидаемым результатом должны быть следующие 5 записей:
"Football","2019-01-02"
"Football","2019-01-05"
"Rugby","2019-01-05"
"Rugby","2019-01-07"
"Rugby","2019-01-10"
Я пытаюсь запустить следующий SQL:
select * from tablen a, tablen b, where a.ItemCode=b.ItemCode and a.BillEntryDate = b.BillEntryDate+30;
Однако это кажется очень неэффективным, так как оно работает долго без отображения каких-либо записей. Есть ли возможность получить менее сложный и более быстрый метод?
Я изучил существующие темы (например, Как найти дубликаты в нескольких столбцах?), но обнаружил дубликаты, в которых ОБА столбца имеют одинаковое значение. Мое требование - одно и то же значение столбца, а второй столбец варьируется в течение месяца.
Ответы 2
С EXISTS:
select ItemCode, BillEntryDate
from tablename t
where exists (
select 1 from tablename
where
ItemCode = t.ItemCode
and
abs(datediff(BillEntryDate, t.BillEntryDate)) between 1 and 30
)
Про 3 минуты не могу сказать. Но что касается периода времени, возможно, я неправильно понял. Можете ли вы объяснить общую часть?
общий означает, что он не должен быть в пределах сегодняшних +30 или -30 дней. Кажется, что использование now() ограничивает его этим диапазоном дат. Он должен проходить через все периоды.
Итак, вы хотите, чтобы повторяющиеся даты были разделены менее чем на 30 дней?
Ты можешь использовать:
select t.*
from tablen t
where exists (select 1
from tablen t2
where t2.ItemCode = t.ItemCode and
t2.BillEntryDate <> t.BillEntryDate and
t2.BillEntryDate >= t1.BillEntryDate - interval 30 day and t2.BillEntryDate <= t1.BillEntryDate + interval 30 day
);
Это подберет оба дубликата в паре.
Для производительности вам нужен индекс на (ItemCode, BillEntryDate).
Спасибо @GordonLinoff. Однако этот не работает. Он просто возвращает все записи как есть и занимает 76,5 секунд, чтобы завершить выполнение запроса к таблице, имеющей около 17 КБ записей. Администраторы сервера не будут довольны. :-) Спасибо за подсказку, у меня уже есть индекс для этих двух столбцов.
@user6337701 . . . Конечно, нужно игнорировать «ту же» строку. Я просто добавил неравенство.
Этот теперь работает, спасибо! Хотя для запуска по-прежнему требуется очень много времени (более 3 минут), что затрудняет использование на веб-странице. Я посмотрю, можно ли улучшить исполнение. +1 и пометить как ответ. Я использовал следующий метод NON-sql, который намного быстрее: генерировать все записи, упорядоченные по ItemCode, BillEntryDate. Возьми этот дамп в экселе. Поместите формулу, чтобы получить BillEntryDate этой строки - BillEntryDate предыдущей строки. Теперь отфильтруйте этот столбец по значениям в диапазоне от -30 до +30. Хотя НЕ-sql, это быстрее, поэтому я могу придерживаться этого для активности раз в месяц.
@user6337701 . . . Для лучшей производительности вам нужен один составной индекс, как описано в ответе.
Спасибо @forpas. Насколько я знаю, now() возвращает текущую системную дату и время сервера. Запрос не ограничивается последним месячным периодом — он должен быть общим. В любом случае, я выполнил предоставленный вами запрос (с функцией now()) и другую модифицированную версию, заменив now() на t.BillEntryDate в обоих экземплярах в SQL. В обоих случаях обработка таблицы с 17 КБ записей занимает более 3 минут, а отображаемые выходные данные неверны.