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

Откуда:
Сообщений: 49
Добрый день!
Как написать запрос, чтоб возвращал самые дешевые пары продуктов. Следующий запрос дает правильный результат, но не использует индекс по полю PRICE.

SELECT TOP 100 PR1.NAME
		,PR2.NAME
FROM T_PRODUCT PR1
	,T_PRODUT PR2
WHERE PR1.ID > PR2.ID
ORDER BY PR1.PRICE + PR2.PRICE



Спасибо.
22 янв 14, 01:11    [15450046]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Дело не в индексе, а в ограничении "перебора".
DECLARE	@T_PRODUCT TABLE (ID Int PRIMARY KEY, PRICE Money NOT NULL, UNIQUE (PRICE,ID))
INSERT	@T_PRODUCT
SELECT	number,Rand(Convert(VarBinary,NewID())) * 10000
FROM	master.dbo.spt_values N
WHERE	N.type = 'P'

DECLARE	@Top Int = 100

	SELECT TOP(@Top) S.SUM_PRICE, P1.ID AS ID1, P2.ID AS ID2
INTO #A
	FROM		@T_PRODUCT	P1
	CROSS JOIN	@T_PRODUCT	P2
	CROSS APPLY (SELECT P1.PRICE + P2.PRICE) S(SUM_PRICE)
	WHERE	P1.ID > P2.ID
	ORDER BY S.SUM_PRICE

	SELECT TOP(@Top) S.*
INTO #B
	FROM		@T_PRODUCT	P1
	CROSS JOIN	@T_PRODUCT	P2
	CROSS APPLY (SELECT
		P1.PRICE + P2.PRICE
	,	CASE WHEN P1.ID > P2.ID THEN P1.ID ELSE P2.ID END AS ID1
	,	CASE WHEN P1.ID > P2.ID THEN P2.ID ELSE P1.ID END AS ID2
		) S(SUM_PRICE,ID1,ID2)
	WHERE	P1.PRICE >= P2.PRICE  AND P1.ID != P2.ID
	ORDER BY S.SUM_PRICE

;WITH [P] AS (SELECT Top(@Top*2) *,Row_Number()OVER(ORDER BY PRICE) AS N FROM @T_PRODUCT ORDER BY PRICE)
	SELECT TOP(@Top) S.*
INTO #C1
	FROM		[P] P1
	CROSS JOIN	[P] P2
	CROSS APPLY (SELECT
		P1.PRICE + P2.PRICE
	,	CASE WHEN P1.ID > P2.ID THEN P1.ID ELSE P2.ID END AS ID1
	,	CASE WHEN P1.ID > P2.ID THEN P2.ID ELSE P1.ID END AS ID2
		) S(SUM_PRICE,ID1,ID2)
	WHERE	P1.N > P2.N
	ORDER BY S.SUM_PRICE

;WITH [P] AS (SELECT Top(@Top*2) * FROM @T_PRODUCT ORDER BY PRICE)
	SELECT TOP(@Top) S.*
INTO #C2
	FROM		[P] P1
	CROSS JOIN	[P] P2
	CROSS APPLY (SELECT
		P1.PRICE + P2.PRICE
	,	CASE WHEN P1.ID > P2.ID THEN P1.ID ELSE P2.ID END AS ID1
	,	CASE WHEN P1.ID > P2.ID THEN P2.ID ELSE P1.ID END AS ID2
		) S(SUM_PRICE,ID1,ID2)
	WHERE	P1.PRICE >= P2.PRICE  AND P1.ID != P2.ID
	ORDER BY S.SUM_PRICE

	SELECT * FROM #A
EXCEPT	SELECT * FROM #B

	SELECT * FROM #A
EXCEPT	SELECT * FROM #C1

	SELECT * FROM #A
EXCEPT	SELECT * FROM #C2

DROP TABLE #A,#B,#C1,#C2;
И у скуля нет механизма Merge-Join на неравенствах (как в Merge сортировке).
Хотя эта тема уже неоднократно подымалось. Но нашёл только это 13043007
(994636, 819201)
22 янв 14, 03:28    [15450208]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
Ну и глупость написал.
Уберите "*2", везде где встретите.
22 янв 14, 11:44    [15451276]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
Не понял, чем не устраивает вариант
SELECT TOP 100 * 
INTO #MinPriceProduct
FROM  T_PRODUCT 
ORDER BY PRICE

SELECT TOP 100 PR1.NAME
		,PR2.NAME
FROM  #MinPriceProduct PR1
	,#MinPriceProduct PR2
WHERE PR1.ID > PR2.ID
ORDER BY PR1.PRICE + PR2.PRICE
22 янв 14, 12:34    [15451634]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Кот Матроскин
Не понял, чем не устраивает вариант
SELECT TOP 100 * 
INTO #MinPriceProduct
FROM  T_PRODUCT 
ORDER BY PRICE

SELECT TOP 100 PR1.NAME
		,PR2.NAME
FROM  #MinPriceProduct PR1
	,#MinPriceProduct PR2
WHERE PR1.ID > PR2.ID
ORDER BY PR1.PRICE + PR2.PRICE
Сканированием таблицы?
22 янв 14, 12:43    [15451672]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Кот Матроскин
Member

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

Сканированием какой таблицы?
22 янв 14, 12:56    [15451749]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Может, так?
WITH CTE AS(SELECT N=ROW_NUMBER()OVER(ORDER BY PRICE),* FROM T_PRODUCT)
SELECT TOP(100) T1.NAME,T2.NAME
FROM CTE T1 JOIN CTE T2 ON T1.N=T1.N+1
WHERE T1.N%2=1 AND T2.N%2=0
ORDER BY N;
22 янв 14, 13:13    [15451849]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
iap
Может, так?
WITH CTE AS(SELECT N=ROW_NUMBER()OVER(ORDER BY PRICE),* FROM T_PRODUCT)
SELECT TOP(100) T1.NAME,T2.NAME
FROM CTE T1 JOIN CTE T2 ON T1.N=T1.N+1
WHERE T1.N%2=1 AND T2.N%2=0
ORDER BY N;
WITH CTE AS(SELECT N=ROW_NUMBER()OVER(ORDER BY PRICE),* FROM T_PRODUCT)
SELECT T1.NAME,T2.NAME
FROM CTE T1 JOIN CTE T2 ON T1.N=T1.N+1
WHERE T2.N<=200 AND T1.N%2=1 AND T2.N%2=0;
22 янв 14, 13:16    [15451866]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
У меня везде опечатка:
FROM CTE T1 JOIN CTE T2 ON T1.N=T2.N+1
22 янв 14, 13:18    [15451873]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Кот Матроскин
Member

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

Ваш запрос работает быстрее, но результат выдает неправильный ;)
22 янв 14, 13:26    [15451922]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
iap
Member

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

Ваш запрос работает быстрее, но результат выдает неправильный ;)
Я думал, если один раз отсоритировать по ворастанию и пронумеровать,
то достаточно будет отбирать пары по возрастанию номера,
чтобы суммарная цена каждой пары была минимальной среди "оставшихся с бОльшим номером".
Ну, нет так нет...
22 янв 14, 13:36    [15451975]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Я его не запускал, если честно
22 янв 14, 13:37    [15451981]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
iap
Я думал, если один раз отсоритировать по ворастанию и пронумеровать,
то достаточно будет отбирать пары по возрастанию номера,


В том-то и дело, что этот принцип не работает. смотрите пример
NUM PRICE
1 1
2 100
3 101
4 102


Если мы хотим отобрать 2 минимальные суммы, то это будут
NUM1 NUM2 SUM
1 2 101
1 3 102


А Ваш скрипт покажет
NUM1 NUM2 SUM
1 2 101
3 4 203
22 янв 14, 13:46    [15452027]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Кот Матроскин
iap
Я думал, если один раз отсоритировать по ворастанию и пронумеровать,
то достаточно будет отбирать пары по возрастанию номера,


В том-то и дело, что этот принцип не работает. смотрите пример
NUM PRICE
1 1
2 100
3 101
4 102


Если мы хотим отобрать 2 минимальные суммы, то это будут
NUM1 NUM2 SUM
1 2 101
1 3 102


А Ваш скрипт покажет
NUM1 NUM2 SUM
1 2 101
3 4 203
Да. Был неправ.
22 янв 14, 14:06    [15452130]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
forrfor
Member

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

DECLARE @T_PRODUCT TABLE (ID Int PRIMARY KEY, PRICE Money NOT NULL, UNIQUE (PRICE,ID))
INSERT @T_PRODUCT
SELECT number,Rand(Convert(VarBinary,NewID())) * 10000
FROM master.dbo.spt_values N
WHERE N.type = 'P'

DECLARE @Top Int = 100

SELECT TOP(@Top) T1.SUM_PRICE, T1.ID1, T1.ID2
INTO #A
FROM ( SELECT S.SUM_PRICE, P1.ID AS ID1, P2.ID AS ID2
FROM @T_PRODUCT P1
CROSS JOIN @T_PRODUCT P2
CROSS APPLY (SELECT P1.PRICE + P2.PRICE) S(SUM_PRICE)
WHERE P1.ID > P2.ID
UNION
SELECT PRICE AS SUM_PRICE, ID AS ID1, ID AS ID2
FROM @T_PRODUCT P1
) T1
ORDER BY T1.SUM_PRICE

SELECT * FROM #A
DROP TABLE #A
22 янв 14, 17:34    [15453370]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
forrfor
Member

Откуда:
Сообщений: 49
Извините, во-первых, это было обращение к Mnior, во-вторых, забыл оформить как код:

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

DECLARE	@T_PRODUCT TABLE (ID Int PRIMARY KEY, PRICE Money NOT NULL, UNIQUE (PRICE,ID))
INSERT	@T_PRODUCT
SELECT	number,Rand(Convert(VarBinary,NewID())) * 10000
FROM	master.dbo.spt_values N
WHERE	N.type = 'P'

DECLARE	@Top Int = 100

SELECT TOP(@Top) T1.SUM_PRICE, T1.ID1, T1.ID2
INTO #A
FROM ( SELECT S.SUM_PRICE, P1.ID AS ID1, P2.ID AS ID2
FROM @T_PRODUCT	P1
CROSS JOIN	@T_PRODUCT	P2
CROSS APPLY (SELECT P1.PRICE + P2.PRICE) S(SUM_PRICE)
WHERE	P1.ID > P2.ID
UNION 
SELECT PRICE AS SUM_PRICE, ID AS ID1, ID AS ID2
FROM @T_PRODUCT	P1
) T1
ORDER BY T1.SUM_PRICE

SELECT * FROM #A
DROP TABLE #A


Как видите, задача немного шире, чем первонаальный вариант.
22 янв 14, 17:40    [15453393]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
forrfor
Member

Откуда:
Сообщений: 49
Теперь отступы пропали.. Предварительный просмотр, предварительный просмотр, предварительный просмотр...

DECLARE	@T_PRODUCT TABLE (ID Int PRIMARY KEY, PRICE Money NOT NULL, UNIQUE (PRICE,ID))
INSERT	@T_PRODUCT
SELECT	number,Rand(Convert(VarBinary,NewID())) * 10000
FROM	master.dbo.spt_values N
WHERE	N.type = 'P'

DECLARE	@Top Int = 100

	SELECT TOP(@Top) T1.SUM_PRICE, T1.ID1, T1.ID2
INTO #A
	FROM ( SELECT S.SUM_PRICE, P1.ID AS ID1, P2.ID AS ID2
	FROM @T_PRODUCT	P1
	CROSS JOIN	@T_PRODUCT	P2
	CROSS APPLY (SELECT P1.PRICE + P2.PRICE) S(SUM_PRICE)
	WHERE	P1.ID > P2.ID
	UNION 
	SELECT PRICE AS SUM_PRICE, ID AS ID1, ID AS ID2
	FROM @T_PRODUCT	P1
	) T1
	ORDER BY T1.SUM_PRICE

	SELECT * FROM #A
DROP TABLE #A
22 янв 14, 17:43    [15453410]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
forrfor
Member

Откуда:
Сообщений: 49
Кот Матроскин
Не понял, чем не устраивает вариант
SELECT TOP 100 * 
INTO #MinPriceProduct
FROM  T_PRODUCT 
ORDER BY PRICE

SELECT TOP 100 PR1.NAME
		,PR2.NAME
FROM  #MinPriceProduct PR1
	,#MinPriceProduct PR2
WHERE PR1.ID > PR2.ID
ORDER BY PR1.PRICE + PR2.PRICE

Для задачи в начале поста ваш запрос работает прекрасно! Вопрос: что делать если нужно юнионить пары с непарами и потом сортировать? Спасибо.
22 янв 14, 17:56    [15453469]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
чета вы с чего начали к тому и вернулись если честно. только cross apply прикрутили для красоты. )))

SELECT TOP(@Top) T1.SUM_PRICE, T1.ID1, T1.ID2
--INTO #A
FROM ( SELECT S.SUM_PRICE, P1.ID AS ID1, P2.ID AS ID2
FROM @T_PRODUCT P1
CROSS JOIN @T_PRODUCT P2
CROSS APPLY (SELECT P1.PRICE + P2.PRICE) S(SUM_PRICE)
WHERE P1.ID >= P2.ID
/*UNION
SELECT PRICE AS SUM_PRICE, ID AS ID1, ID AS ID2
FROM @T_PRODUCT P1
*/) T1
ORDER BY T1.SUM_PRICE

тоже самое дает (ну только надо учитывать, что если пара из одинаковых товаров, то наверное правильнее общую сумму набора удваивать, а не выводить как в предыдущем посте за один товар ) . Думаю кардинального прорыва можно добится взяв первые @top записей по цене и уже на них натравливая этот сканирующий алгоритм. тогда вроде и скан будет не так существенен и индекс по цене будет использоватся.
22 янв 14, 18:39    [15453642]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Кот Матроскин
Member

Откуда: Москва
Сообщений: 8933
forrfor
Для задачи в начале поста ваш запрос работает прекрасно! Вопрос: что делать если нужно юнионить пары с непарами и потом сортировать? Спасибо.

И чtuj, у Вас тормозит прикручивание
Union all
 select NAME, NAME, price
   from #MinPriceProduct
order by 3

?
У меня он как крыл в 40 раз Mnior'овский, так и продолжает крыть с union-ом
22 янв 14, 18:57    [15453707]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
forrfor
Member

Откуда:
Сообщений: 49
Мистер Хенки
чета вы с чего начали к тому и вернулись если честно. только cross apply прикрутили для красоты. )))

Да, Mnior'овский #A-вариант работает со скоростью света.
22 янв 14, 19:44    [15453895]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
forrfor
Member

Откуда:
Сообщений: 49
Кот Матроскин
forrfor
Для задачи в начале поста ваш запрос работает прекрасно! Вопрос: что делать если нужно юнионить пары с непарами и потом сортировать? Спасибо.

И чtuj, у Вас тормозит прикручивание
Union all
 select NAME, NAME, price
   from #MinPriceProduct
order by 3

?
У меня он как крыл в 40 раз Mnior'овский, так и продолжает крыть с union-ом


Угу, на реальных данных если без верхней или нижней части юниона, то пара секунд, а с ним 7 минут.
22 янв 14, 19:49    [15453915]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Кот Матроскин
Member

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

Вы делаете ровно так, как я написал? Обе части union работают только с временной таблицей из 100 строк, добавлено третье поле в выборку и сортировка идет по нему? Этот запрос не может отрабатывать 7 минут, тут что-то не так. План строится нормально? можете привести план со стоимостями?
22 янв 14, 20:41    [15454050]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Mnior
Member

Откуда: Кишинёв
Сообщений: 6724
forrfor
Да, Mnior'овский #A-вариант работает со скоростью света.
Я вообще не понял дискуссию про вариант #A - Это тот базовый ваш вариант, только перефразированный. Он для того чтобы сравнивать результаты на правильность (EXCEPT)
Вариант #B - показать что индекс это мало.
Варианты #C - типа основной смысл (только там не забыть вместо *2 поставить +1).

forrfor
Мне нужно в одном списке отразить и пары, и одиночные продукты.
DECLARE	@T_PRODUCT TABLE (ID Int PRIMARY KEY, PRICE Money NOT NULL, UNIQUE (PRICE,ID))
INSERT	@T_PRODUCT
SELECT	number,Rand(Convert(VarBinary,NewID())) * 10000
FROM	master.dbo.spt_values N
WHERE	N.type = 'P'
DECLARE	@Top Int = 1000
PRINT Convert(VarChar,GetDate(),126)
-- Базовый
	SELECT	Top(@Top) S.SUM_PRICE, P1.ID AS ID1, P2.ID AS ID2
INTO #A2
	FROM		@T_PRODUCT	P1
	CROSS JOIN	@T_PRODUCT	P2
	CROSS APPLY (SELECT CASE WHEN P1.ID = P2.ID THEN P1.PRICE ELSE P1.PRICE + P2.PRICE END) S(SUM_PRICE)
	WHERE	P1.ID >= P2.ID
	ORDER BY S.SUM_PRICE
PRINT Convert(VarChar,GetDate(),126)
-- Оптимальный
;WITH	[P] AS (SELECT Top(@Top) * FROM @T_PRODUCT ORDER BY PRICE)
	SELECT TOP(@Top) S.*
INTO #C3
	FROM		[P] P1
	CROSS JOIN	[P] P2
	CROSS APPLY (SELECT
		CASE WHEN P1.ID = P2.ID THEN P1.PRICE ELSE P1.PRICE + P2.PRICE END
	,	CASE WHEN P1.ID > P2.ID THEN P1.ID ELSE P2.ID END AS ID1
	,	CASE WHEN P1.ID > P2.ID THEN P2.ID ELSE P1.ID END AS ID2
		) S(SUM_PRICE,ID1,ID2)
	WHERE	P1.PRICE >= P2.PRICE
	ORDER BY S.SUM_PRICE
PRINT Convert(VarChar,GetDate(),126)
-- Проверка
	SELECT * FROM #A2
EXCEPT	SELECT * FROM #C3
-- Результат
	SELECT * FROM #C3

DROP TABLE #A2,#C3;	-- Создание таблиц смазывает (выравнивает) результат
23 янв 14, 00:13    [15454635]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка по сумме полей  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
forrfor
Мистер Хенки
чета вы с чего начали к тому и вернулись если честно. только cross apply прикрутили для красоты. )))

Да, Mnior'овский #A-вариант работает со скоростью света.

Вам Кот Матроскин выдал вариант с поиском по индексу, созданием временной таблицы из ограниченного числа записей из исходной таблицы и последующим сканированием временной таблицы с сортировкой по сумме цен. Непонятно, что еще не так.
23 янв 14, 10:12    [15455208]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить