Определение дубля

← →
Dvm_home (2005-06-16 05:36) [0]

Здравствуйте Мастера!
Имеется таблица заявок на MS SQL:
CREATE TABLE [DeliveryDeliv] ( [DeliveryID] int NOT NULL, [DateDeparture] datetime NOT NULL, [TimeDeparture] smallint NOT NULL, [NameGoods] char(60) COLLATE Cyrillic_General_CI_AS, [GoodsSerialNumber] char(30) COLLATE Cyrillic_General_CI_AS, [ShopName] char(100) COLLATE Cyrillic_General_CI_AS, [Customer] char(50) COLLATE Cyrillic_General_CI_AS, [CustomerRegion] char(50) COLLATE Cyrillic_General_CI_AS, [CustomerStreet] char(50) COLLATE Cyrillic_General_CI_AS NOT NULL, [CustomerHouse] char(10) COLLATE Cyrillic_General_CI_AS NOT NULL, [CustomerFlat] char(10) COLLATE Cyrillic_General_CI_AS, [CustomerPhone] char(20) COLLATE Cyrillic_General_CI_AS, [AdressComent] char(300) COLLATE Cyrillic_General_CI_AS, [Comment] char(400) COLLATE Cyrillic_General_CI_AS, [StatusID] tinyint NOT NULL, [TimeComent] char(20) COLLATE Cyrillic_General_CI_AS, [TeamID] int, [MotiveID] int, [NumberGoodsInDelivery] tinyint, [Created] datetime, [CreateUserID] int, [LastUpdated] datetime, [UpdatedUserID] int, [DeliveryFromStore] tinyint, [DateMail] datetime, [MailAdressID] int, CONSTRAINT [DeliveryDeliv_pk] PRIMARY KEY ([DeliveryID]) ) ON [PRIMARY] GO
Необходимо узнать на какие адреса (CustomerStreet, CustomerHouse, CustomerFlat) заявки оформлялить больше одного раза.

запрос типа:
Select *, COUNT(DeliveryID) from DeliveryDeliv GROUP BY CustomerStreet, CustomerHouse, CustomerFlat
не проходит, ругается на все поля не входящие в GROUP. Пробовал использовать временную таблицу и курсор - таже история.

← →
dmitry501 © (2005-06-16 05:42) [1]

Dvm_home (16.06.05 5:36)
Select COUNT(CustomerStreet) from DeliveryDeliv GROUP BY CustomerStreet, CustomerHouse, CustomerFlat

← →
ЮЮ © (2005-06-16 05:46) [2]

SELECT
CustomerStreet, CustomerHouse, CustomerFlat,
COUNT(DeliveryID) OneAddressCount
FROM
DeliveryDeliv
GROUP BY
CustomerStreet, CustomerHouse, CustomerFlat
HAVING
COUNT(DeliveryID) > 1

← →
ЮЮ © (2005-06-16 05:48) [3]

>dmitry501 © (16.06.05 05:42) [1]

И где здесь ответ на вопрос "на какие адреса заявки оформлялить больше одного раза"?

← →
Dvm_home (2005-06-16 06:41) [4]

Получилось сделать вот как

Select * from DeliveryDeliv
Where CustomerStreet+CustomerHouse+CustomerFlat IN (
Select CustomerStreet+CustomerHouse+CustomerFlat from DeliveryDeliv
group by CustomerStreet, CustomerHouse, CustomerFlat
HAVING count(DeliveryID) >1
)

Но, получается я немогу выбрать те поля, которые не учавствуют в GROUP BY

← →
Dvm_home (2005-06-16 06:43) [5]

одним запросом

← →
dmitry501 © (2005-06-16 06:50) [6]

ЮЮ © (16.06.05 5:48) [3]
Извиняюсь, невнимательно прочитал вопрос. Решил, что нужно число повторных заявок... С толку сбило, что у автора COUNT

← →
Dvm_home (2005-06-16 07:31) [7]

Может подскажет кто, как выбрать поля не учавствующие в group

← →
Zacho © (2005-06-16 07:39) [8]

Dvm_home (16.06.05 7:31) [7]

Никак, ибо это не имеет смысла.
Сам подумай, какие именно значения этих полей надо выбирать ? Случайные ?

← →
Anatoly Podgoretsky © (2005-06-16 07:48) [9]

Zacho © (16.06.05 07:39) [8]
Ну почему случайные, можно Select "Поле1", Count(....
Будет специальное :-)

← →
Danilka © (2005-06-16 08:34) [10]

попробуй так:

SELECT d.*
FROM DeliveryDeliv d
WHERE EXISTS (SELECT 1 FROM DeliveryDeliv d2
WHERE d.DeliveryID != d2.DeliveryID
AND d.CustomerStreet=d2.CustomerStreet
AND d.CustomerHouse=d2.CustomerHouse
AND d.CustomerFlat=d2.CustomerFlat)

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

← →
Dvm_home (2005-06-16 09:37) [11]

Danilka © (16.06.05 8:34) [10]

Работает, вот только время выборки 2.78, а если так как я сделал в [4] то 0.78

Получается что если нужны другие поля то нужно делать примерно так

SELECT max(DeliveryID), MAX(DateDeparture), CustomerStreet, CustomerHouse, CustomerFlat, COUNT(DeliveryID) FROM DeliveryDeliv GROUP BY CustomerStreet, CustomerHouse, CustomerFlat HAVING COUNT(DeliveryID) > 1

Всем спасибо

← →
Dvm_home (2005-06-16 09:45) [12]

Может еще подскажете как использовать временные таблицы и курсоры. Например в применении к этой задаче

сделать что то типа:

Select CustomerStreet, CustomerHouse, CustomerFlat
FROM DeliveryDeliv INTO temp
GROUP BY CustomerStreet, CustomerHouse, CustomerFlat
HAVING COUNT(DeliveryID) > 1

и потом

SELECT a.*
FROM DeliveryDeliv a, temp t
WHERE a.CustomerStreet = t.CustomerStreet
AND a.CustomerHouse = t.CustomerHouse
AND a.CustomerFlat = t.CustomerFlat

← →
Anatoly Podgoretsky © (2005-06-16 10:04) [13]

Dvm_home (16.06.05 09:37) [11]
Странно как то, зачем тебе данные с DeliveryID из одной записи, DateDeparture из другой записи. Какой особый смысл в этом вообще и по отдельным полям в частности?

← →
Danilka © (2005-06-16 10:08) [14]

[11] Dvm_home (16.06.05 09:37)
> Работает, вот только время выборки 2.78, а если так как
> я сделал в [4] то 0.78

Значит нет индексов по всем полям участвующим в условии WHERE.
Попробуй сделать один составной индекс по полям CustomerStreet, CustomerHouse и CustomerFlat, будет еще быстрее, чем твои 0.78.

← →
ANB © (2005-06-16 10:08) [15]

1. ТЗ не соответствует сабжу и не понятно. По каким критериям :
Необходимо узнать на какие адреса (CustomerStreet, CustomerHouse, CustomerFlat) заявки оформлялить больше одного раза.
???

← →
Danilka © (2005-06-16 10:09) [16]

[13] Anatoly Podgoretsky © (16.06.05 10:04)
Все странно, начиная с запроса в [4] :)

← →
Danilka © (2005-06-16 10:30) [17]

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

[CustomerRegion] char(50) COLLATE Cyrillic_General_CI_AS,
[CustomerStreet] char(50) COLLATE Cyrillic_General_CI_AS NOT NULL,
[CustomerHouse] char(10) COLLATE Cyrillic_General_CI_AS NOT NULL,
[CustomerFlat] char(10) COLLATE Cyrillic_General_CI_AS,
[AdressComent] char(300) COLLATE Cyrillic_General_CI_AS,

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

ну и еще, дофига чего можно напридумывать. а так, вся эта беда через год будет жрать не 0.78с, а счет может пойти на минуты. и это не единственная и не самая страшная проблема из тех, которые обязательно возникнут.

← →
Anatoly Podgoretsky © (2005-06-16 10:37) [18]

Ну то что тут нет никакой нормализации это факт, но не единственный недостаток. Страшно подумать, но возможно это вообще единственная таблица.

← →
Dvm_home (2005-06-16 10:48) [19]

Нет таблица не единственная :). Это сделано не из хорошей жизни. Мне пришлось отказаться от справочника улиц потому что операторы пишут названия как хотят, первичка сыпется валом и им видетели влом смотреть что почти всегда программа при проверке адреса им справочником тычет. Потом количество таких двойных заявок не велико от общего числа. Ну и последнее все это портируется из клариона, находится в стадии разбора и далеко от конечного варианта. :)

← →
Dvm_home (2005-06-16 10:53) [20]

Anatoly Podgoretsky © (16.06.05 10:04) [13]

Получается что в одном запросе никак не укажешь другие поля кроме тех что в GROUP. Вот я и прошу помочь с временными таблицами и курсорами

← →
MOA © (2005-06-16 11:04) [21]

>Мне пришлось отказаться от справочника улиц потому что операторы пишут названия как хотят
В этом случае Ваш запрос будет не только медленным, но и выдавать числа прошлогоднего календаря, а не "на какие адреса заявки оформлялить больше одного раза."
Нормализация нужна, в частности, и для этого. А на лом операторов хорошо действует кошелёк ;).
Удачи!

← →
ЮЮ © (2005-06-16 11:08) [22]

в MS SQL есть ещё подзапросы:

SELECT tbl.*
FROM
(
SELECT
CustomerStreet, CustomerHouse, CustomerFlat,
COUNT(DeliveryID) OneAddressCount
FROM
DeliveryDeliv
GROUP BY
CustomerStreet, CustomerHouse, CustomerFlat
HAVING
COUNT(DeliveryID) > 1
) gr
JOIN DeliveryDeliv tbl ON
(gr.CustomerStreet = tbl.CustomerStreet) AND
(gr.CustomerHouse= tbl.CustomerHouse) AND
(gr.CustomerFlat= tbl.CustomerFlat)

Т.е. в запросе находишь те параметры, которые, окажись они в записи таблицы, тебя интересуют. И затем соединяешь запрос с таблицей по полученным значениям.

Естественно, в твоем случае, CustomerStreet, CustomerHouse, CustomerFlat - не лучший набор полей как для группировки, так и для соединения

[22] ЮЮ © (16.06.05 11:08)
спорим, будет больше моих 2.78с.? :))
если индексов нет, то толку от таких соединений?

вообще, кадр забавный, у него, значит, юзеры ленивые, улицу могут несколько раз разными буковками забить, даже если будут выбирать адрес клиента из таблицы адресов уже отфильтрованой по выбраному клиенту.
поэтому о нормализации говорить низзя. наверное, об индексах низзя говорить по той-же причине. :)

спорим, будет больше моих 2.78с.? :))

Спорим нет! Ибо он не должен быть медленнее [4], который 0.78

[24] ЮЮ © (16.06.05 11:26)
медленнее будет, факт, раз у него нет индексов по полям: CustomerStreet, CustomerHouse, CustomerFlat, а именно по этим полям ты и соединяешься.

Хотя, может и не медленнее моего, если учесть, что записей с повторами мало. Но если сделать идекс составной, то могу поспорить на полом серьезе, что мой запрос будет быстрее твоего. :))
Кроме того, интересно почему он не группирует еще и по регионам? Это тоже часть адреса.

[24] ЮЮ © (16.06.05 11:26)
впрочем, можем посприть и сейчас, ставлю бутылку пива, если окажусь в твоих краях, или ты окажешься в моих. :))

Без Dvm_home всё равно спор не разрешить, а он уже 40 минут не реагирут :)

← →
Dvm_home (2005-06-22 02:53) [29]

Привет Всем

Почитал я последние посты, немного посмеялся. "Нереагировал" я потому что рабочий день кончилси :), и идиалогию реляционных отношений понимаю.

Я же написал, что это черновик, индексы и все прочее добавится по ходу дела.

Спасибо ЮЮ за конструктив, МОА - на кашелек операторов воздействовать никак не получится - я работаю в этой компании.

--> ЮЮ
Почему

> CustomerStreet, CustomerHouse, CustomerFlat - не
> лучший набор полей как для группировки, так и для
> соединения

← →
Dvm_home (2005-06-22 02:55) [30]

Вопрос по поводу использования временных таблиц и курсоров остается актуальным

> Я же написал, что это черновик, индексы и все прочее добавится
> по ходу дела.

Надо с самого начала проектировать правильно, стараясь учеть как можно больше нюансов. Если-бы ты так сделал изначально, и вместо того, чтобы понимать "идиалогию реляционных отношений" применял-бы это на практике, скорее всего, данная задача не возникла-бы вообще.

Определение дубля Найти похожие ветки