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

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

Есть задачка, интересная своей простой постановкой, но как оказалось (по крайней мере для меня) не очень простая в решении.

В общем предположим есть таблицы Orders (OrderID, ... ), Products (ProductID, ...) и таблица-связка многие-ко-многим OrderItems (OrderID, ProductID, Quantity, ...). Требуется для ордера с данным OrderID определить другие ордера, которые имеют те же продукты с теми же количествами.

Первое, что пришло в готову - калькулируемое поле. Написал функцию, которая по OrderID возвращала мне строку типа 'PID1:Q1;PID2:Q2;...' Добавил этот computed column в таблицу Orders. Думал построить индекс на нем, чтобы потом просто группировку использовать, но для этого оно должно быть PERSISTED. Меняю, не дает, функция недетерминированная. Вставляю в функцию WITH SCHEMABINDING - все равно не дает сделать persisted: поле cannot be persisted because the column does user or system data access.

Без индекса не дождаться конца работы. В Orders 25000 записей, в Products - 200, в OrderItems обычно не более 10 записей для одного ордера ( в среднем 2-3 ).

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

Конкретный вопрос вот в чем: как написать обычный запрос, который мне вернет все "ордера-дубликаты" по вышеописанному критерию или как улучшить мою идею с вычисляемым полем?

С запросом в какой-то стопор вошел...

Спасибо.
4 авг 09, 21:16    [7497918]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
x3mka,

надо придумать структуру Ваших таблиц, индексы создать, как у Вас, данными наполнить, да?
Когда у Вас перелом, Вам по телефону гипс накладывают?
4 авг 09, 21:34    [7497936]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5655
Так вам ровно с теми же количествами и продуктами надо? Т.е. надо найти полный дубликат заказа?

.mahoune.
4 авг 09, 21:41    [7497944]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

Откуда:
Сообщений: 30
iap
x3mka,

надо придумать структуру Ваших таблиц, индексы создать, как у Вас, данными наполнить, да?
Когда у Вас перелом, Вам по телефону гипс накладывают?

iap, сорри, я посчитал, что описал все в полной мере. Вероятно это не так. У меня совсем другие таблицы и сценарии. Я просто описал задачу в объектах, которые присутствуют в реализации практически любого магазина. Думал, что это узнаваемо. Для прояснения смотрите скриншот из Erwin.

К сообщению приложен файл. Размер - 0Kb
4 авг 09, 21:46    [7497951]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

Откуда:
Сообщений: 30
mahoune
Так вам ровно с теми же количествами и продуктами надо? Т.е. надо найти полный дубликат заказа?

.mahoune.


Да, все верно. И их может быть несколько.
4 авг 09, 21:47    [7497952]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5655
На мой взгляд ваше предложение

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


самое верное.

Надо только учесть порядок PID был всегда одинаков.

А если запросом, то думаю без этой статьи FAQ не обойтись.

.mahoune.
4 авг 09, 22:07    [7497975]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

Откуда:
Сообщений: 30
mahoune, спасибо за ссылку.

Буду вникать, с первого раза не дошло, как связать это с моей задачей.
4 авг 09, 22:15    [7497981]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
mahoune
Member

Откуда: Moscow
Сообщений: 5655
Группируете таблицу OrderItems по полю OrderID, а поля ProductID и Quantity складываете в одно поле, как вы и хотели сделать до этого ('PID1:Q1;PID2:Q2;...'), а потом из полученой таблички уже легко найдете дубликаты.

Думаю когда придут гуру они посоветуют варианты поинтересней.

.mahoune.
4 авг 09, 22:20    [7497988]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
sPaul
Member

Откуда: Moscow
Сообщений: 387
Как вариант:

declare @OrderItems table(OrderID int, ProductID int, Quantity int);

declare @SelectedOrderID int

;with
	[order](OrderID,ProductID,Quantity) as (
		select OrderID,ProductID,Quantity from @OrderItems where OrderID = @SelectedOrderID
	),
	[orderSIZE](cnt) as (
		select count(1) from [order]
	)
select OrderItems.OrderID from @OrderItems OrderItems
	left join [order] on [order].ProductID = OrderItems.ProductID and [order].Quantity = OrderItems.Quantity
where OrderItems.OrderID <> @SelectedOrderID
group by OrderItems.OrderID
having count(1) = (select cnt from [orderSIZE]);
4 авг 09, 22:59    [7498039]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
x3mka,

Я бы добавил в Orders поле OrderMd5Hash с типом binary(16) и на OrderItems повесил триггер:

CREATE TRIGGER OrderItems_CalcOrderMd5Hash
   ON  OrderItems
   AFTER INSERT,DELETE,UPDATE
AS 

set nocount on;

declare @Orders table (OrderID int, unique(OrderID))

insert @Orders (OrderID)
select OrderID
from inserted
union 
select OrderID
from deleted

update Orders
set OrderMd5Hash = IsNull(HASHBYTES('MD5', (select [@P] = ProductID, [@Q] = Quantity from OrderItems where OrderID = o.OrderID for xml path('O'))), 0x)
from Orders o
where OrderID in (select OrderID from @Orders)

И потом искал дубликаты простым сравнением хэшей:

select [Hash] = OrderMd5Hash, 
     [Orders] = (select [Order/@OrderID] = OrderID
                 from Orders 
                 where OrderMd5Hash = o.OrderMd5Hash 
                 for xml path(''), type)
from Orders o
group by OrderMd5Hash
having COUNT(*) > 1
for xml path('equals'), root('root')
5 авг 09, 02:24    [7498193]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
Roman S. Golubin
Member

Откуда: 140002
Сообщений: 11541
Упс. Сортировка в триггере важна:

...
set OrderMd5Hash = IsNull(HASHBYTES('MD5', (select [@P] = ProductID, [@Q] = Quantity from OrderItems where OrderID = o.OrderID order by ProductID for xml path('O'))), 0x)
...
5 авг 09, 02:27    [7498195]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

Откуда:
Сообщений: 30
Парни, всем спасибо. Пока тестирую производительность предложенных вариантов.
5 авг 09, 03:31    [7498210]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Есчё вариант проверьте:
CREATE VIEW [dbo].[OrderProductEqual] WITH SCHEMABINDING AS
SELECT	 L.[Order]	AS [Left]
	,R.[Order]	AS [Right]
	,Count_Big(*)	AS [Count]
FROM	     dbo.OrderProduct	L
	JOIN dbo.OrderProduct	R ON R.[Order] != L.[Order] -- R.[Order] > L.[Order]
				 AND R.Product  = L.Product
				 AND R.Quantity = L.Quantity
GROUP BY L.[Order]
	,R.[Order]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_OrderProductEqual] ON [dbo].[OrderProductEqual] (
	 [Left]
	,[Right]
)
GO
CREATE VIEW [dbo].[OrderProductCount] WITH SCHEMABINDING AS
SELECT	 [Order]
	,Count_Big(*)	AS [Count]
FROM	dbo.OrderProduct
GROUP BY [Order]
GO
CREATE UNIQUE CLUSTERED INDEX [PK_OrderProductCount] ON [dbo].[OrderProductCount] (
	 [Order]
)
GO
CREATE VIEW [dbo].[OrderEqual] AS
SELECT	 E.[Left]
	,E.[Right]
FROM	     dbo.OrderProductCount	L
	JOIN dbo.OrderProductCount	R ON L.[Order] != R.[Order] -- R.[Order] > L.[Order]
	JOIN dbo.OrderProductEqual	E ON E.[Left]	= L.[Order] AND E.[Count] = L.[Count]
					 AND E.[Right]	= R.[Order] AND E.[Count] = R.[Count]
GO
SELECT	[Right] AS OtheOrder
FROM	dbo.OrderEqual
WHERE	[Left] = @Order
/*
UNION ALL
SELECT	[Left] AS OtheOrder
FROM	dbo.OrderEqual
WHERE	[Right] = @Order
*/
5 авг 09, 04:26    [7498224]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6723
Ну да, есчё добавить - для скорости:
CREATE INDEX [IX_OrderProductEqual_LeftCount] ON [dbo].[OrderProductEqual] (
	 [Left]
	,[Count]
)
GO
/*
CREATE INDEX [IX_OrderProductEqual_RightCount] ON [dbo].[OrderProductEqual] (
	 [Right]
	,[Count]
)
GO
*/
5 авг 09, 04:32    [7498227]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

Откуда:
Сообщений: 30
Всем привет. Кому интересно, хочу поделиться результатами тестирования. Чтобы увеличить нагрузку, находил все ордера, у которых есть дубликаты. 25000 записей в Orders, 200 в Products, 80000 в OrderItems. Ордеров-дубликатов 1703.

Вариант 1. Калькулируемое поле (мой вариант).

CREATE FUNCTION fnGetOrderItems (@oid INT)	
RETURNS NVARCHAR(1000)
WITH SCHEMABINDING
AS 
BEGIN 
	DECLARE @res NVARCHAR(1000)	
	SET @res = ''	
	SELECT 
		@res = @res + CAST([ProductId] AS nvarchar) + ':' + CAST([Quantity] AS NVARCHAR) + ';'
	FROM dbo.[OrderItems] 
	WHERE 
		[OrderID] = @oid
	ORDER BY 
		[ProductID]
		
	RETURN @res		
END		
GO
	
ALTER TABLE [Orders]
ADD ProductItemsHash AS dbo.fnGetOrderItems(OrderId)
GO

DECLARE 
	@start DATETIME,
	@end DATETIME
	
SET @start = GETDATE()	

SELECT 
	MIN(OrderID) AS OrderID,
	COUNT(*) AS Cnt
FROM [Orders]
GROUP BY	
	[ProductItemsHash]		
HAVING 
	COUNT(*) > 1	
ORDER BY 
	COUNT(*) DESC	
	
SET @end = GETDATE()

SELECT DATEDIFF(millisecond, @start, @end)

Время работы: 0.5 - 0.7 сек.

Вариант 2. Использование XML для построения строки хэша.

SELECT 
	MIN(OrderID) AS OrderID,
	COUNT(*) AS Cnt
FROM	
(	
	select 
		t1.[OrderID], 		
		(
			select 
				CAST(ProductID AS NVARCHAR) + ':'+ cast(Quantity as nvarchar)+ ';' as 'data()'
			from [OrderItems] t2
			where 
				t1.[OrderId] = t2.[OrderId] 
			ORDER BY ProductID	
			for xml path('') 
		) AS ProductItemsHash
	from [OrderItems] t1
	group by t1.[OrderId]
) tt
GROUP BY	
	ProductItemsHash
HAVING 
	COUNT(*) > 1
ORDER BY	
	COUNT(*) DESC

Работает дольше, 0.9 - 1.1 сек.

Вариант 3. Аналогично 1, только строка хэшируется в binary(16) по MD5. Переписанная функция:

CREATE FUNCTION fnGetOrderItemsHashed (@oid INT)	
RETURNS BINARY(16)
WITH SCHEMABINDING
AS 
BEGIN 
	DECLARE @res NVARCHAR(1000)	
	SET @res = ''	
	SELECT 
		@res = @res + CAST([ProductId] AS nvarchar) + ':' + CAST([Quantity] AS NVARCHAR) + ';'
	FROM dbo.[OrderItems] 
	WHERE 
		[OrderID] = @oid
	ORDER BY 
		[ProductID]
		
	RETURN IsNull(HASHBYTES('MD5', @res), 0x0)		
END

Прикольно, но быстрее не стало. Замедление по сравнению с первым вариантом на 0.1 - 0.2 секи.

Вариант 4. Обычное поле в таблице Orders, которое поддерживается через тригеры функцией fnGetOrderItems с индексом наверху.

CREATE NONCLUSTERED INDEX XIE1Orders ON [Orders] ( ProductItemsHash ASC )

И запрос
SELECT
	MIN(OrderID), COUNT(*) AS Cnt
FROM [Orders]
GROUP BY 
	ProductItemsHash
HAVING COUNT(*) > 1	
ORDER BY COUNT(*) DESC

отработал моментально.

Вариант 5. С вьюшками от Mnior.

CREATE VIEW [dbo].[OrderItemsEqual] WITH SCHEMABINDING 
AS
	SELECT	 
		L.[OrderID] AS [Left],
		R.[OrderID] AS [Right],
		Count_Big(*) AS [Count]
	FROM dbo.OrderItems	L
	JOIN dbo.OrderItems	R ON 
		R.[OrderID] <> L.[OrderID] AND 
		R.ProductID  = L.ProductID AND
		R.Quantity = L.Quantity
	GROUP BY 
		L.[OrderID],
		R.[OrderID]
GO

-- Индекс ниже кидает ошибку 
-- Cannot create index on view "dbo.OrderItemsEqual". The view contains a self join on "dbo.OrderItems".
/*
CREATE UNIQUE CLUSTERED INDEX [PK_OrderItemsEqual] ON [dbo].[OrderItemsEqual] (
	[Left],
	[Right]
)
GO
*/

CREATE VIEW [dbo].[OrderItemsCount] WITH SCHEMABINDING 
AS
	SELECT
		[OrderID],
		Count_Big(*) AS [Count]
	FROM	dbo.OrderItems
	GROUP BY [OrderID]
GO

CREATE UNIQUE CLUSTERED INDEX [PK_OrderItemsCount] ON [dbo].[OrderItemsCount] (
	 [OrderID]
)
GO

CREATE VIEW [dbo].[OrderEqual] 
AS

	SELECT	 
		E.[Left],
		E.[Right]
	FROM dbo.OrderItemsCount L
	JOIN dbo.OrderItemsCount R ON 
		L.[OrderID] <> R.[OrderID] 
	JOIN dbo.OrderItemsEqual E ON 
		E.[Left] = L.[OrderID] AND 
		E.[Count] = L.[Count] AND 
		E.[Right] = R.[OrderID] AND 
		E.[Count] = R.[Count]
GO


SELECT	[Right] AS OtherOrder
FROM	dbo.OrderEqual
WHERE	[Left] = 19

SELECT	[Right] AS OtherOrder
FROM	dbo.OrderEqual

Теперь запрос на дубликаты для одного ордера работает 0.3 - 0.5 секунд. Но общий селект из вьюшки, чтобы найти все ордера-дубликаты работает больше минуты.

Есть еще пару оставшихся вариантов, выложу чуть позже.
5 авг 09, 22:25    [7503136]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
SQL2005/2008 (из-за EXCEPT, можно адаптировать для SQL2000)
USE tempdb;
SET NOCOUNT ON;

IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
CREATE TABLE OrderItems(OrderID INT, ProductID INT, Quantity INT );
INSERT OrderItems(OrderID,ProductID,Quantity)
          SELECT 1,1,28
UNION ALL SELECT 1,2,55
UNION ALL SELECT 1,3,10
UNION ALL SELECT 2,1,2
UNION ALL SELECT 2,5,1
UNION ALL SELECT 3,1,28
UNION ALL SELECT 3,2,55
UNION ALL SELECT 3,3,10
UNION ALL SELECT 3,8,11
UNION ALL SELECT 4,1,28
UNION ALL SELECT 4,3,10
UNION ALL SELECT 4,8,11
UNION ALL SELECT 5,1,28
UNION ALL SELECT 5,2,55
UNION ALL SELECT 5,3,10
UNION ALL SELECT 6,1,28
UNION ALL SELECT 6,2,55
UNION ALL SELECT 6,3,10
UNION ALL SELECT 6,2,55
UNION ALL SELECT 7,2,55
UNION ALL SELECT 7,3,10
UNION ALL SELECT 8,1,28
UNION ALL SELECT 8,2,55
UNION ALL SELECT 8,3,10
UNION ALL SELECT 9,1,2
UNION ALL SELECT 9,5,1
UNION ALL SELECT 10,3,10
UNION ALL SELECT 11,13,1
UNION ALL SELECT 11,33,9;
GO

DECLARE @OrderID INT;
SET @OrderID=1;
SELECT DISTINCT @OrderID[TheOrderID], OI.OrderID[Duplicate]
FROM OrderItems OI
WHERE OI.OrderID<>@OrderID
AND NOT EXISTS
(
 SELECT OI1.ProductID, OI1.Quantity, COUNT(*)
 FROM OrderItems OI1
 WHERE OI1.OrderID=@OrderID
 GROUP BY OI1.ProductID, OI1.Quantity
 EXCEPT
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=OI.OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
); 
GO
IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
???
6 авг 09, 09:36    [7503731]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Небольшая поправка. Вместо DISTINCT лучше делать SELECT из Orders
SQL2005/2008 (из-за EXCEPT, можно адаптировать для SQL2000)
USE tempdb;
SET NOCOUNT ON;

IF OBJECT_ID(N'Orders','U') IS NOT NULL DROP TABLE Orders;
GO
CREATE TABLE Orders(OrderID INT);
GO
INSERT Orders(OrderID)
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11;
GO
IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
CREATE TABLE OrderItems(OrderID INT, ProductID INT, Quantity INT);
INSERT OrderItems(OrderID,ProductID,Quantity)
          SELECT 1,1,28
UNION ALL SELECT 1,2,55
UNION ALL SELECT 1,3,10
UNION ALL SELECT 2,1,2
UNION ALL SELECT 2,5,1
UNION ALL SELECT 3,1,28
UNION ALL SELECT 3,2,55
UNION ALL SELECT 3,3,10
UNION ALL SELECT 3,8,11
UNION ALL SELECT 4,1,28
UNION ALL SELECT 4,3,10
UNION ALL SELECT 4,8,11
UNION ALL SELECT 5,1,28
UNION ALL SELECT 5,2,55
UNION ALL SELECT 5,3,10
UNION ALL SELECT 6,1,28
UNION ALL SELECT 6,2,55
UNION ALL SELECT 6,3,10
UNION ALL SELECT 6,2,55
UNION ALL SELECT 7,2,55
UNION ALL SELECT 7,3,10
UNION ALL SELECT 8,1,28
UNION ALL SELECT 8,2,55
UNION ALL SELECT 8,3,10
UNION ALL SELECT 9,1,2
UNION ALL SELECT 9,5,1
UNION ALL SELECT 10,3,10
UNION ALL SELECT 11,13,1
UNION ALL SELECT 11,33,9;
GO

DECLARE @OrderID INT;
SET @OrderID=1;
SELECT @OrderID[TheOrderID], O.OrderID[Duplicate]
FROM Orders O
WHERE O.OrderID<>@OrderID
AND NOT EXISTS
(
 SELECT OI1.ProductID, OI1.Quantity, COUNT(*)
 FROM OrderItems OI1
 WHERE OI1.OrderID=@OrderID
 GROUP BY OI1.ProductID, OI1.Quantity
 EXCEPT
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=O.OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
); 
GO
IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
IF OBJECT_ID(N'Orders','U') IS NOT NULL DROP TABLE Orders;
GO
6 авг 09, 11:17    [7504479]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

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

не могу понять точно, где ошибка, но ваш запрос возвращает больше записей для дубликатов, чем есть на самом деле.
6 авг 09, 15:17    [7506451]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
x3mka
iap,

не могу понять точно, где ошибка, но ваш запрос возвращает больше записей для дубликатов, чем есть на самом деле.
Имеются в виду данные из моего поста?
Вообще-то, я старался охватить побольше разных возможных ситуаций.
Если Вам точно известно, что должно получиться, то можно без труда выудить "лишнее". Так ведь?
Покажите здесь тестовые данные, для которых неправильно работает, пожалуйста.
Когда их обнаружите, естественно.
6 авг 09, 15:25    [7506506]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

Откуда:
Сообщений: 30
iap, я похоже понял, почему не совсем верен запрос.

Если предположить, что у ордера с ID = 1 только одна запись в OrderItems, а у всех остальных ордеров есть такая же запись (с тем же ProductID и Quantity), плюс еще что-то. Очевидно, что тогда внутренний подзапрос не будет содержать записей и сработавает ваш Not Exists. Как результат все остальные ордера будут считаться дубликатами.

Чтобы все работало надо разность Except заменить на симметричную разность.

Пользуясь подходом отсюда
http://www.aspnetmania.com/Articles/Article.aspx?ID=41,
пытался переписать запрос, но что-то не могу понять в чем ошибка в синтаксисе.

DECLARE @OrderID INT;
SET @OrderID=19;

SELECT 
	@OrderID[TheOrderID], 
	O.OrderID[Duplicate]
FROM Orders O
WHERE O.OrderID <> @OrderID
AND NOT EXISTS
(	
	 SELECT 1 FROM 
	 (
		 SELECT 
			ProductID, Quantity, COUNT(*) AS Cnt, 1 AS TID
		 FROM OrderItems
		 WHERE OrderID = @OrderID
		 
		 UNION ALL 
		 
		 SELECT 
			ProductID, Quantity, COUNT(*) AS Cnt, 2 AS TID
		 FROM OrderItems 
		 WHERE OrderID = O.OrderID
	  ) t 
	  GROUP BY t.ProductID, t.Quantity, t.Cnt
	  HAVING SUM(t.TID) in (1,2)
) 
6 авг 09, 17:18    [7507363]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
А так правильно работает?
USE tempdb;
SET NOCOUNT ON;

IF OBJECT_ID(N'Orders','U') IS NOT NULL DROP TABLE Orders;
GO
CREATE TABLE Orders(OrderID INT);
GO
INSERT Orders(OrderID)
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 13;
GO
IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
CREATE TABLE OrderItems(OrderID INT, ProductID INT, Quantity INT);
INSERT OrderItems(OrderID,ProductID,Quantity)
          SELECT 1,1,28
UNION ALL SELECT 1,2,55
UNION ALL SELECT 1,3,10
UNION ALL SELECT 2,1,2
UNION ALL SELECT 2,5,1
UNION ALL SELECT 3,1,28
UNION ALL SELECT 3,2,55
UNION ALL SELECT 3,3,10
UNION ALL SELECT 3,8,11
UNION ALL SELECT 4,1,28
UNION ALL SELECT 4,3,10
UNION ALL SELECT 4,8,11
UNION ALL SELECT 5,1,28
UNION ALL SELECT 5,2,55
UNION ALL SELECT 5,3,10
UNION ALL SELECT 6,1,28
UNION ALL SELECT 6,2,55
UNION ALL SELECT 6,3,10
UNION ALL SELECT 6,2,55
UNION ALL SELECT 7,2,55
UNION ALL SELECT 7,3,10
UNION ALL SELECT 8,1,28
UNION ALL SELECT 8,2,55
UNION ALL SELECT 8,3,10
UNION ALL SELECT 9,1,2
UNION ALL SELECT 9,5,1
UNION ALL SELECT 10,3,10
UNION ALL SELECT 11,13,1
UNION ALL SELECT 11,33,9
UNION ALL SELECT 13,1,28
UNION ALL SELECT 13,2,55
UNION ALL SELECT 13,3,10
UNION ALL SELECT 13,8,100;
GO

DECLARE @OrderID INT;
SET @OrderID=1;
SELECT @OrderID[TheOrderID], O.OrderID[Duplicate]
FROM Orders O
WHERE O.OrderID<>@OrderID
AND NOT EXISTS
(
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=O.OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
 UNION ALL
 SELECT OI1.ProductID, OI1.Quantity, COUNT(*)
 FROM OrderItems OI1
 WHERE OI1.OrderID=@OrderID
 GROUP BY OI1.ProductID, OI1.Quantity
 EXCEPT
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=@OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
); 
GO
IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
IF OBJECT_ID(N'Orders','U') IS NOT NULL DROP TABLE Orders;
GO
(Добавил ещё OrderID=13, который похож на первый, но ещё один ProductID=8 включает)
6 авг 09, 17:38    [7507486]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Нет, неправильно.
6 авг 09, 17:41    [7507508]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
x3mka
Member

Откуда:
Сообщений: 30
Попробовал подругому симметричную разность написать.

DECLARE @OrderID INT;
SET @OrderID=19;
SELECT O.OrderID [Duplicate]
FROM Orders O
WHERE O.OrderID<>@OrderID
AND NOT EXISTS
(
 SELECT OI1.ProductID, OI1.Quantity, COUNT(*)
 FROM OrderItems OI1
 WHERE OI1.OrderID=@OrderID
 GROUP BY OI1.ProductID, OI1.Quantity
 EXCEPT
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=O.OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
 
 UNION ALL 
 
 SELECT OI1.ProductID, OI1.Quantity, COUNT(*)
 FROM OrderItems OI1
 WHERE OI1.OrderID=O.OrderID
 GROUP BY OI1.ProductID, OI1.Quantity
 EXCEPT
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=@OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
 
)

Вернуло несколько лишних ордеров. В моих тестовых данных для ордера 19 есть 2 записи в OrderItems. Хэш их '41:15;82:1;'. А в результирующий набор запроса попали ордера с хэшами '41:15;', то есть, у которых только первый продукт совпадает.
6 авг 09, 17:45    [7507525]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
iap
Нет, неправильно.
Вот так будет правильно, но как там с эффективностью...
USE tempdb;
SET NOCOUNT ON;

IF OBJECT_ID(N'Orders','U') IS NOT NULL DROP TABLE Orders;
GO
CREATE TABLE Orders(OrderID INT);
GO
INSERT Orders(OrderID)
          SELECT 1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 13;
GO
IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
CREATE TABLE OrderItems(OrderID INT, ProductID INT, Quantity INT);
INSERT OrderItems(OrderID,ProductID,Quantity)
          SELECT 1,1,28
UNION ALL SELECT 1,2,55
UNION ALL SELECT 1,3,10
UNION ALL SELECT 2,1,2
UNION ALL SELECT 2,5,1
UNION ALL SELECT 3,1,28
UNION ALL SELECT 3,2,55
UNION ALL SELECT 3,3,10
UNION ALL SELECT 3,8,11
UNION ALL SELECT 4,1,28
UNION ALL SELECT 4,3,10
UNION ALL SELECT 4,8,11
UNION ALL SELECT 5,1,28
UNION ALL SELECT 5,2,55
UNION ALL SELECT 5,3,10
UNION ALL SELECT 6,1,28
UNION ALL SELECT 6,2,55
UNION ALL SELECT 6,3,10
UNION ALL SELECT 6,2,55
UNION ALL SELECT 7,2,55
UNION ALL SELECT 7,3,10
UNION ALL SELECT 8,1,28
UNION ALL SELECT 8,2,55
UNION ALL SELECT 8,3,10
UNION ALL SELECT 9,1,2
UNION ALL SELECT 9,5,1
UNION ALL SELECT 10,3,10
UNION ALL SELECT 11,13,1
UNION ALL SELECT 11,33,9
UNION ALL SELECT 13,1,28
UNION ALL SELECT 13,2,55
UNION ALL SELECT 13,3,10
UNION ALL SELECT 13,8,100;
GO

DECLARE @OrderID INT;
SET @OrderID=1;
SELECT @OrderID[TheOrderID], O.OrderID[Duplicate]
FROM Orders O
WHERE O.OrderID<>@OrderID
AND NOT EXISTS
(
 SELECT OI1.ProductID, OI1.Quantity, COUNT(*)
 FROM OrderItems OI1
 WHERE OI1.OrderID=@OrderID
 GROUP BY OI1.ProductID, OI1.Quantity
 UNION ALL
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=O.OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
 EXCEPT
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=@OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
 INTERSECT
 SELECT OI2.ProductID, OI2.Quantity, COUNT(*)
 FROM OrderItems OI2
 WHERE OI2.OrderID=O.OrderID
 GROUP BY OI2.ProductID, OI2.Quantity
); 
GO
IF OBJECT_ID(N'OrderItems','U') IS NOT NULL DROP TABLE OrderItems;
GO
IF OBJECT_ID(N'Orders','U') IS NOT NULL DROP TABLE Orders;
GO
6 авг 09, 17:46    [7507533]     Ответить | Цитировать Сообщить модератору
 Re: Хитрые дубликаты  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
Идея простая - суммируем (UNION ALL) два множества, относящиеся к двум разным заказам.
А после этого выбрасывем (EXCEPT) все элемены получившегося множества,
которые есть и в первом, и во втором заказе (INTERSECT).
При этом подсчитываем дубли (COUNT), чтобы при сравнении заказов их количество тоже совпадало.
Приоритет UNION, EXCEPT и INTERSECT вроде именно такой, как я написал, так что скобки не нужны.
6 авг 09, 17:54    [7507592]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить