Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Поиск дубликатов, помогите плз улучшить запрос.  [new]
abrashka
Member

Откуда:
Сообщений: 517
День добрый!
Есть таблица клиентов, которая содержит порядка миллиона строк.
Нужно найти всех клиентов, которые зарегестрировались с определенного числа, но уже были зарегестрированны ранее, поиск проводится по определеным критериям, например по имени и фамилии.
Для наглядности:
--drop table #clients

create table #clients( 
ID int identity (1,1), Phone nvarchar(100), FirstName nvarchar(100),
 LastName nvarchar(100), Email nvarchar(100),CreatedOn datetime)

insert into #clients(Phone, FirstName, LastName, Email,CreatedOn)
select '111','Dasha', 'Ivanova','dd@bb.com','20130806' union
select '12313','Ivan', 'Zzzz','zz@bb.com','20110501' union
select '63456346','Nata', 'Petrova','dd@bb.com','20130806' union
select '524','Zoya', 'Kuku','dd@bb.com','20130806' union
select '675456','Tanya', 'Savisheva','dd@bb.com','20130806' union
select '453','Anna', 'Friske','dd@bb.com','20130203' union
select '8768','Dasha', 'Ivanova','kot@bb.com','20110203' union
select '156','Dima', 'Bilan','ddd@fff','20111003' union
select '635','Dasha', 'Ivanova','dasha@zzz.com','20110101' 


Грубо говоря, запрос должен выдать Дашу Иванову ибо она находится в таблице несколько раз.

Написал такие запросы, но они работают крайне медленно.


select distinct a.* from #clients a
inner join #clients b
on a.FirstName=b.FirstName
and a.LastName=b.LastName
where a.CreatedOn>'20130801'
and b.CreatedOn<a.CreatedOn 


select distinct a.* from #clients a
where a.CreatedOn>'20130801'
and exists
(select b.FirstName,b.Lastname  
from #clients b
where a.FirstName=b.FirstName
and a.LastName=b.LastName 
and b.CreatedOn<a.CreatedOn)


Создал индекс по CreatedOn, но все равно запрос выполняется порядка пяти минут.
Есть ли более продуктивные способы решения этой задачи?
Спасибо!
26 авг 13, 17:37    [14757030]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
RowNumber
Guest
SELECT FROM SELECT ( N = RowNumber() OVER (PARTITION BY) ) WHERE N > 1
26 авг 13, 17:43    [14757066]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
select c.FirstName, c.Lastname
from #clients c
group by c.FirstName, c.Lastname
having count(*) > 1 and max(c.CreatedOn) > '20130801'
26 авг 13, 17:45    [14757071]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
abrashka,
а не хотите попробовать индекс по FirstName , LastName,CreatedOn ?
26 авг 13, 17:45    [14757072]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Cammomile
Member

Откуда:
Сообщений: 1214
 
SELECT DISTINCT FirstName, LastName  FROM (
SELECT ROW_NUMBER()OVER( PARTITION BY FirstName, LastName ORDER BY CreatedOn  ) AS RowNumber , * FROM   #clients 
) SS 
WHERE SS.RowNumber >  1 
26 авг 13, 17:48    [14757089]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Гость333
select c.FirstName, c.Lastname
from #clients c
group by c.FirstName, c.Lastname
having count(*) > 1 and max(c.CreatedOn) > '20130801'

автор
Нужно найти всех клиентов, которые зарегестрировались с определенного числа, но уже были зарегестрированны ранее

А если будет клиент который был зарегистрирован несколько раз с определенного числа, а ранее этого числа зарегистрирован не был?
26 авг 13, 17:53    [14757109]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
abrashka
Member

Откуда:
Сообщений: 517
Всем спасибо!
Сейчас буду пробовать.

Мистер Хенки,
Индекс добавить не хочу, ибо речь может идти о поиске в порядка 20-30 полях.
Я намого упростил задачу для примера.
26 авг 13, 17:54    [14757118]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
abrashka
Member

Откуда:
Сообщений: 517
Мистер Хенки
Гость333
select c.FirstName, c.Lastname
from #clients c
group by c.FirstName, c.Lastname
having count(*) > 1 and max(c.CreatedOn) > '20130801'

автор
Нужно найти всех клиентов, которые зарегестрировались с определенного числа, но уже были зарегестрированны ранее

А если будет клиент который был зарегистрирован несколько раз с определенного числа, а ранее этого числа зарегистрирован не был?

да-да, это важно, поэтому одно из условий в моем коде:
b.CreatedOn<a.CreatedOn
26 авг 13, 17:56    [14757131]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
abrashka
Всем спасибо!
Сейчас буду пробовать.

Мистер Хенки,
Индекс добавить не хочу, ибо речь может идти о поиске в порядка 20-30 полях.
Я намого упростил задачу для примера.

отлично, что предложенные однопроходные решения удовлетворяют поставленному условию задачи
26 авг 13, 17:57    [14757135]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Мистер Хенки
автор
Нужно найти всех клиентов, которые зарегестрировались с определенного числа, но уже были зарегестрированны ранее

А если будет клиент который был зарегистрирован несколько раз с определенного числа, а ранее этого числа зарегистрирован не был?

Дык я сделал по мотивам вот этого:
abrashka
where a.CreatedOn>'20130801'
and b.CreatedOn<a.CreatedOn 

Если бы было "and b.CreatedOn<'20130801'", тогда бы я сделал немного по-другому :)
26 авг 13, 17:57    [14757139]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Гость333
Мистер Хенки
пропущено...

А если будет клиент который был зарегистрирован несколько раз с определенного числа, а ранее этого числа зарегистрирован не был?

Дык я сделал по мотивам вот этого:
abrashka
where a.CreatedOn>'20130801'
and b.CreatedOn<a.CreatedOn 

Если бы было "and b.CreatedOn<'20130801'", тогда бы я сделал немного по-другому :)

а как тогда за один проход решить задачу?
26 авг 13, 18:00    [14757156]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
Мистер Хенки,

Примерно так?
select c.FirstName, c.Lastname
from #clients c
group by c.FirstName, c.Lastname
having max(c.CreatedOn) >= '20130801' and min(c.CreatedOn) < '20130801'
26 авг 13, 18:03    [14757165]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
abrashka
Member

Откуда:
Сообщений: 517
Мистер Хенки
abrashka
Всем спасибо!
Сейчас буду пробовать.

Мистер Хенки,
Индекс добавить не хочу, ибо речь может идти о поиске в порядка 20-30 полях.
Я намого упростил задачу для примера.

отлично, что предложенные однопроходные решения удовлетворяют поставленному условию задачи


На самом деле не совсем подходят :(
Я видимо слишком упростил ситуацию для примера...

В любом случае, селект должен показать все поля из таблицы, а не только фамилию и имя.

Вобще речь идет о процедуре, в которой пользователь может выбрать порядка 20 полей для сравнения, через "and".
T.e. на сколько я понимаю, что-бы воспользоваться кодом типа:
SELECT FROM SELECT ( N = RowNumber() OVER (PARTITION BY) ) WHERE N > 1
нужно оспользовать динамический SQL?
26 авг 13, 18:04    [14757168]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Гость333
Мистер Хенки,

Примерно так?
select c.FirstName, c.Lastname
from #clients c
group by c.FirstName, c.Lastname
having max(c.CreatedOn) >= '20130801' and min(c.CreatedOn) < '20130801'

интересно
26 авг 13, 18:07    [14757184]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
ScareCrow
Member

Откуда: Белый город
Сообщений: 16943
а почему не
WHERE c.CreatedOn >= '20130801' and c.CreatedOn) < '20130801'

?
26 авг 13, 18:18    [14757214]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Гость333
Member

Откуда:
Сообщений: 3683
abrashka
пользователь может выбрать порядка 20 полей для сравнения, через "and".
T.e. на сколько я понимаю, что-бы воспользоваться кодом типа:
SELECT FROM SELECT ( N = RowNumber() OVER (PARTITION BY) ) WHERE N > 1
нужно оспользовать динамический SQL?

Можно, наверное, придумать и без динамического SQL. Но с динамическим это проще будет.
26 авг 13, 18:18    [14757215]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
abrashka
Member

Откуда:
Сообщений: 517
Гость333,

Огромное спасибо!
Очень интересный ход, запомню на будущее
26 авг 13, 19:19    [14757438]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Гость333
abrashka
пользователь может выбрать порядка 20 полей для сравнения, через "and".
T.e. на сколько я понимаю, что-бы воспользоваться кодом типа:
SELECT FROM SELECT ( N = RowNumber() OVER (PARTITION BY) ) WHERE N > 1
нужно оспользовать динамический SQL?

Можно, наверное, придумать и без динамического SQL. Но с динамическим это проще будет.

с динамическим проще и лучше будет. А так типа с case можно попробовать:
declare @withFirstName bit
declare @withLastName bit
select @withFirstName =1,@withLastName =1
select newFirstName.val, newLastName.val
from #clients c
		cross apply(select case when @withFirstName =1 then c.FirstName else null end) newFirstName(val)
		cross apply(select case when @withLastName =1 then c.LastName else null end) newLastName(val)
group by 
newFirstName.val, newLastName.val
having max(c.CreatedOn) >= '20130801' and min(c.CreatedOn) < '20130801'
27 авг 13, 10:03    [14758812]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
abrashka
Member

Откуда:
Сообщений: 517
Мистер Хенки,
Спасибо!
Попробую пойти по предложенному Вами пути.
Наткнулся на еще одну проблему, которая затрудняет, а возможно и не дает возможность использовать group by.
На самом деле в каждой строке есть несколько полей с телефонами: домашний, мобильный и рабочий.
Если ищем дубликатов например по имени и телефону, то Вася с номером мобилы 123 и Вася с рабочим номером 123- это дубликат.
27 авг 13, 15:34    [14761133]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Empirical
Member

Откуда:
Сообщений: 99
Мистер Хенки,

продолжая Вашу (гениальную, к слову) мысль:

select max(id), max(CreatedOn) as [MaxCreated], min(CreatedOn) as [MinCreated], FirstName, LastName from #clients
group by FirstName, LastName
having count(1)>1 and max(CreatedOn) >= '20130801' and min(CreatedOn) < '20130801'


RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
11"select max(id) max(CreatedOn) as [MaxCreated] min(CreatedOn) as [MinCreated] FirstName LastName from #clients"
11 |--Filter(WHERE:([Expr1004]>(1) AND [Expr1005]>='2013-08-01 00:00:00.000' AND [Expr1006]<'2013-08-01 00:00:00.000'))121FilterFilterWHERE:([Expr1004]>(1) AND [Expr1005]>='2013-08-01 00:00:00.000' AND [Expr1006]<'2013-08-01 00:00:00.000')NULL101.04E-05530.01471791"[tempdb].[dbo].[#clients].[FirstName] [tempdb].[dbo].[#clients].[LastName] [Expr1005] [Expr1006] [Expr1007]"NULLPLAN_ROW01
00" |--Compute Scalar(DEFINE:([Expr1004]=CONVERT_IMPLICIT(int[Expr1013]0)))"132Compute ScalarCompute Scalar"DEFINE:([Expr1004]=CONVERT_IMPLICIT(int[Expr1013]0))""[Expr1004]=CONVERT_IMPLICIT(int[Expr1013]0)"8.80966409.80E-06570.01470752"[tempdb].[dbo].[#clients].[FirstName] [tempdb].[dbo].[#clients].[LastName] [Expr1004] [Expr1005] [Expr1006] [Expr1007]"NULLPLAN_ROW01
71" |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[#clients].[LastName] [tempdb].[dbo].[#clients].[FirstName]) DEFINE:([Expr1013]=Count(*) [Expr1005]=MAX([tempdb].[dbo].[#clients].[CreatedOn]) [Expr1006]=MIN([tempdb].[dbo].[#clients].[CreatedOn]) [Expr1007]=MAX([tempdb].[dbo].[#clients].[ID])))"143Stream AggregateAggregate"GROUP BY:([tempdb].[dbo].[#clients].[LastName] [tempdb].[dbo].[#clients].[FirstName])""[Expr1013]=Count(*) [Expr1005]=MAX([tempdb].[dbo].[#clients].[CreatedOn]) [Expr1006]=MIN([tempdb].[dbo].[#clients].[CreatedOn]) [Expr1007]=MAX([tempdb].[dbo].[#clients].[ID])"8.80966409.80E-06570.01470752"[tempdb].[dbo].[#clients].[FirstName] [tempdb].[dbo].[#clients].[LastName] [Expr1005] [Expr1006] [Expr1007] [Expr1013]"NULLPLAN_ROW01
91" |--Sort(ORDER BY:([tempdb].[dbo].[#clients].[LastName] ASC [tempdb].[dbo].[#clients].[FirstName] ASC))"154SortSort"ORDER BY:([tempdb].[dbo].[#clients].[LastName] ASC [tempdb].[dbo].[#clients].[FirstName] ASC)"NULL90.011261261.45E-04450.01469771"[tempdb].[dbo].[#clients].[ID] [tempdb].[dbo].[#clients].[FirstName] [tempdb].[dbo].[#clients].[LastName] [tempdb].[dbo].[#clients].[CreatedOn]"NULLPLAN_ROW01
91 |--Table Scan(OBJECT:([tempdb].[dbo].[#clients]))165Table ScanTable ScanOBJECT:([tempdb].[dbo].[#clients])"[tempdb].[dbo].[#clients].[ID] [tempdb].[dbo].[#clients].[FirstName] [tempdb].[dbo].[#clients].[LastName] [tempdb].[dbo].[#clients].[CreatedOn]"90.0031251.67E-04450.0032919"[tempdb].[dbo].[#clients].[ID] [tempdb].[dbo].[#clients].[FirstName] [tempdb].[dbo].[#clients].[LastName] [tempdb].[dbo].[#clients].[CreatedOn]"NULLPLAN_ROW01
27 авг 13, 16:50    [14761933]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Empirical
Member

Откуда:
Сообщений: 99
abrashka
Если ищем дубликатов например по имени и телефону, то Вася с номером мобилы 123 и Вася с рабочим номером 123- это дубликат.


case
27 авг 13, 16:51    [14761950]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
Empirical,
вы это, адресом ошиблись. Читайте внимательнее тему. Я тут вообще ничего не предлагал
27 авг 13, 16:52    [14761961]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Empirical
Member

Откуда:
Сообщений: 99
Мистер Хенки
Empirical,
вы это, адресом ошиблись. Читайте внимательнее тему. Я тут вообще ничего не предлагал


Сорри, Гость333 гений. Но Вы тоже ничего!

В продолжение темы - вот так если попробовать:

select max(id), max(CreatedOn) as [MaxCreated], min(CreatedOn) as [MinCreated], FirstName, LastName, Phone1, 
case when Phone2 = Phone1 then null else Phone2 end as Phone2, 
case when Phone3 = Phone1 then null when Phone3 = Phone2 then null else Phone3 end as Phone3
from #clients
group by FirstName, LastName, Phone1, 
case when Phone2 = Phone1 then null else Phone2 end, 
case when Phone3 = Phone1 then null when Phone3 = Phone2 then null else Phone3 end
having count(1)>1 and max(CreatedOn) >= '20130801' and min(CreatedOn) < '20130801'
27 авг 13, 17:02    [14762037]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
Empirical
Member

Откуда:
Сообщений: 99
abrashka,

в один прогон, мне кажется не получится. Надо их сначала дедупликировать (уникализировать по всем Вашим критериям - имя, кстати, тоже может варьироваться), потом из уников отбирать по дате.
27 авг 13, 17:10    [14762089]     Ответить | Цитировать Сообщить модератору
 Re: Поиск дубликатов, помогите плз улучшить запрос.  [new]
mike909
Member

Откуда:
Сообщений: 662
abrashka,

А если так ?
select *
from (
  select ROW_NUMBER() over(partition by a.FirstName, a.LastName order by a.CreatedOn) as [RN], a.*
  from (
    select 
       LAG( a.CreatedOn , 1, NULL ) over(partition by a.FirstName, a.LastName order by a.CreatedOn) as [LG]
      ,*
    from #clients as a
  ) as a
  where a.CreatedOn > '20130801' and a.LG is not null
) as a
where a.RN = 1
27 авг 13, 17:50    [14762368]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить