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

Есть таблица, в которой содержатся расстояния от точки, до точки PointDistance:
p1p2Distance
110
231486
25183
2131
37955
19407193483242
21210
6224497640
121167
.........


Выполнив запрос
SELECT PointCurrent,PointTo FROM Trips,Dislocation WHERE TripID=Trips.ID GROUP BY PointCurrent,PointTo

Я получаю набор данных (Таблица маршруты)
PointCurrentPointTo
1940719348
38193740
525813779
4976224
2024920340
......


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

PointCurrent=p1 и PointTo=p2
или
PointCurrent=p2 и PointTo=p1

WITH t1 AS
(
	SELECT PointCurrent,PointTo FROM Trips,Dislocation WHERE TripID=Trips.ID GROUP BY PointCurrent,PointTo
)
SELECT
	t1.*, Distance 
FROM 
	t1
LEFT OUTER JOIN
	PointDistance
ON 
	(p1=PointCurrent AND p2=PointTo) OR (p2=PointCurrent AND p1=PointTo)
WHERE Distance IS Null
ORDER BY PointTo,PointCurrent
Работает долго

WITH t1 AS
(
	SELECT PointCurrent,PointTo FROM Trips,Dislocation WHERE TripID=Trips.ID GROUP BY PointCurrent,PointTo
)
SELECT
	t1.*, Distance 
FROM 
	t1
LEFT OUTER JOIN
	PointDistance
ON 
	p1=PointCurrent AND p2=PointTo
WHERE Distance IS Null
UNION
SELECT
	t1.*, Distance 
FROM 
	t1
LEFT OUTER JOIN
	PointDistance
ON 
	p2=PointCurrent AND p1=PointTo
WHERE Distance IS Null
ORDER BY PointTo,PointCurrent

Работает быстро, но в результате получаем ложные данные.
24 янв 14, 11:21    [15461443]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Начинающий SQL
Работает быстро, но в результате получаем ложные данные.

И какие данные из приведенных вами здесь будут ложными ?
24 янв 14, 11:29    [15461512]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
Glory, Простите, не ложными, а не подходящие под требуемое условие
+
WITH PointDistance AS 
(
	SELECT a.[p1], a.[p2], a.[Distance] FROM (
	VALUES
	(1,1,0),
	(23,1,486),
	(2,5,183),
	(2,1,31),
	(3,7,955),
	(19407,19348,3242),
	(21,21,0),
	(6224,497,640),
	(1,21,167)
	) a([p1], [p2], [Distance])
), t1 AS 
(
	SELECT b.[PointCurrent], b.[PointTo] FROM (
	VALUES
	(19407,19348),
	(3819,3740),
	(5258,13779),
	(497,6224),
	(20249,20340)
	) b([PointCurrent], [PointTo])
)
SELECT
	t1.*, Distance 
FROM 
	t1
LEFT OUTER JOIN
	PointDistance
ON 
	(p1=PointCurrent AND p2=PointTo) OR (p2=PointCurrent AND p1=PointTo)
WHERE Distance IS Null
ORDER BY PointTo,PointCurrent

PointCurrentPointToDistance
38193740NULL
525813779NULL
2024920340NULL

Получаем 3 записи которых нет в таблице PointDistance
+
WITH PointDistance AS 
(
	SELECT a.[p1], a.[p2], a.[Distance] FROM (
	VALUES
	(1,1,0),
	(23,1,486),
	(2,5,183),
	(2,1,31),
	(3,7,955),
	(19407,19348,3242),
	(21,21,0),
	(6224,497,640),
	(1,21,167)
	) a([p1], [p2], [Distance])
), t1 AS 
(
	SELECT b.[PointCurrent], b.[PointTo] FROM (
	VALUES
	(19407,19348),
	(3819,3740),
	(5258,13779),
	(497,6224),
	(20249,20340)
	) b([PointCurrent], [PointTo])
)
SELECT
	t1.*, Distance 
FROM 
	t1
LEFT OUTER JOIN
	PointDistance
ON 
	p1=PointCurrent AND p2=PointTo
WHERE Distance IS Null
UNION
SELECT
	t1.*, Distance 
FROM 
	t1
LEFT OUTER JOIN
	PointDistance
ON 
	p2=PointCurrent AND p1=PointTo
WHERE Distance IS Null
ORDER BY PointTo,PointCurrent

PointCurrentPointToDistance
38193740NULL
4976224NULL
525813779NULL
1940719348NULL
2024920340NULL

Получаем 3 записи которых нет в таблице PointDistance + 2 записи, которые таблица содержит
24 янв 14, 11:52    [15461719]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Начинающий SQL
+ 2 записи, которые таблица содержит

Как же она их соджержит, если Distance показывается NULL ?
24 янв 14, 11:55    [15461746]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
Glory
Как же она их соджержит, если Distance показывается NULL ?

Вот они подсвечены красным цветом
PointDistance:
p1p2Distance
110
231486
25183
2131
37955
19407193483242
21210
6224497640
121167
.........

Ошибка во втором запросе, где не могу понять...
24 янв 14, 11:59    [15461792]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Начинающий SQL
Ошибка во втором запросе, где не могу понять...

Ошибка в том, что при перестановке точек вы не учитываете существующие данные без перестановки
24 янв 14, 12:05    [15461855]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
Glory
Ошибка в том, что при перестановке точек вы не учитываете существующие данные без перестановки

Хорошо, как это сделать не используя OR?
24 янв 14, 12:08    [15461872]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL,

про LEFT JOIN и FULL JOIN прочитали уже?
Тогда знаете, что эти JOINы приводят к появлению строк с NULLами?
Что здесь удивительного? Вы сами написали такое условие соединения.

И пишите алиасы таблиц везде, пожалуйста, для ясности
24 янв 14, 12:11    [15461909]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
WITH t1 AS
(
	SELECT PointCurrent,PointTo FROM Trips,Dislocation WHERE TripID=Trips.ID GROUP BY PointCurrent,PointTo
	UNION ALL
	SELECT PointTo,PointCurrent FROM Trips,Dislocation WHERE TripID=Trips.ID GROUP BY PointTo,PointCurrent
)
SELECT
	t1.*, Distance 
FROM 
	t1
LEFT OUTER JOIN
	PointDistance
ON 
	(p1=PointCurrent AND p2=PointTo)
WHERE Distance IS Null
ORDER BY PointTo,PointCurrent
24 янв 14, 12:14    [15461935]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL,

в PointDisnance вообще больше записей, чем в T1.
И многим соответствия в T1 просто нет.
24 янв 14, 12:15    [15461943]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
iap, да, спасибо.

Выполняя запрос
WITH pd AS 
(
	SELECT a.[p1], a.[p2], a.[Distance] FROM (
	VALUES
	(1,1,0),
	(23,1,486),
	(2,5,183),
	(2,1,31),
	(3,7,955),
	(19407,19348,3242),
	(21,21,0),
	(6224,497,640),
	(1,21,167)
	) a([p1], [p2], [Distance])
), t1 AS 
(
	SELECT b.[PointCurrent], b.[PointTo] FROM (
	VALUES
	(19407,19348),
	(3819,3740),
	(5258,13779),
	(497,6224),
	(20249,20340)
	) b([PointCurrent], [PointTo])
)
SELECT
	t1.*, pd.Distance 
FROM 
	t1
LEFT OUTER JOIN
	pd
ON 
	(pd.p1=t1.PointCurrent AND pd.p2=t1.PointTo) OR (pd.p2=t1.PointCurrent AND pd.p1=t1.PointTo)
WHERE pd.Distance IS Null
ORDER BY t1.PointTo,t1.PointCurrent

Я получаю данные удовлетворяющие условию. Но проблема в скорости выполнения. Сейчас это порядка 14 секунд. С учетом того, что в таблицу t1 каждые сутки добавляется порядка 12000 записей, а в таблицу pd ~5-10% от этого количества, время выполнения запроса будет сильно возрастать. В связи с чем вопрос Как избавившись от OR получить тот же набор данных за меньшее время.
24 янв 14, 12:27    [15462022]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL,

OR оптимизируется UNIONом. Glory же написал
24 янв 14, 12:36    [15462070]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Можно, конечно,
pd.p1=t1.PointCurrent AND pd.p2=t1.PointTo OR pd.p2=t1.PointCurrent AND pd.p1=t1.PointTo
заменить на
NOT EXISTS
(
 SELECT pd.p1,pd.p2
 EXCEPT
 SELECT t1.PointCurrent,t1.PointTo
 EXCEPT
 SELECT t1.PointTo,t1.PointCurrent
)
Но я сомневаюсь, что будут использованы индексы
Ещё экзотика:
EXISTS
(
 SELECT pd.p1,pd.p2
 INTERSECT
 SELECT t1.PointCurrent,t1.PointTo
 UNION ALL
 SELECT pd.p1,pd.p2
 INTERSECT
 SELECT t1.PointTo,t1.PointCurrent
)
Просто разбиение запроса на два + UNION наверняка лучше любых других вариантов
24 янв 14, 12:45    [15462132]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
Glory,

Выполняя запрос
+
WITH pd AS 
(
	SELECT a.[p1], a.[p2], a.[Distance] FROM (
	VALUES
	(1,1,0),
	(23,1,486),
	(2,5,183),
	(2,1,31),
	(3,7,955),
	(19407,19348,3242),
	(21,21,0),
	(6224,497,640),
	(1,21,167)
	) a([p1], [p2], [Distance])
), test AS 
(
	SELECT b.[PointCurrent], b.[PointTo] FROM (
	VALUES
	(19407,19348),
	(3819,3740),
	(5258,13779),
	(497,6224),
	(20249,20340)
	) b([PointCurrent], [PointTo])
), t1 AS
(
	SELECT PointCurrent,PointTo FROM test GROUP BY PointCurrent,PointTo
	UNION ALL
	SELECT PointTo,PointCurrent FROM test GROUP BY PointTo,PointCurrent
)
SELECT
	t1.*, pd.Distance 
FROM 
	t1
LEFT OUTER JOIN
	pd
ON 
	(pd.p1=PointCurrent AND pd.p2=PointTo)
WHERE pd.Distance IS Null
ORDER BY t1.PointTo,t1.PointCurrent

Получаем следующий набор данных
PointCurrentPointToDistance
38193740NULL
37403819NULL
137795258NULL
4976224NULL
525813779NULL
1934819407NULL
2034020249NULL
2024920340NULL

Красные синие зеленые удовлетворяют условию, но дублируют друг друга. Черные не удовлетворяют условию

Необходимо получить такой набор:
PointCurrentPointToDistance
38193740NULL
525813779NULL
2024920340NULL
24 янв 14, 12:46    [15462136]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL,

замените UNION ALL на UNION
24 янв 14, 12:48    [15462142]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
Перестановка PointCurrent, PointTo не имеет значения
24 янв 14, 12:48    [15462145]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
iap
замените UNION ALL на UNION

Тот же набор данных, я в первую очередь попробовал
24 янв 14, 12:50    [15462152]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Начинающий SQL
Выполняя запрос

Идея там была
WITH x AS 
(
	SELECT a.[p1], a.[p2], a.[Distance] FROM (
	VALUES
	(1,1,0),
	(23,1,486),
	(2,5,183),
	(2,1,31),
	(3,7,955),
	(19407,19348,3242),
	(21,21,0),
	(6224,497,640),
	(1,21,167)
	) a([p1], [p2], [Distance])
), test AS 
(
	SELECT b.[PointCurrent], b.[PointTo] FROM (
	VALUES
	(19407,19348),
	(3819,3740),
	(5258,13779),
	(497,6224),
	(20249,20340)
	) b([PointCurrent], [PointTo])
), pd AS
(
	SELECT p1,p2, [Distance] FROM x 
	UNION ALL
	SELECT p2,p1, [Distance] FROM x
)
SELECT
	test.*, pd.Distance 
FROM 
	test
LEFT OUTER JOIN
	pd
ON 
	(pd.p1=PointCurrent AND pd.p2=PointTo)
WHERE pd.Distance IS Null
ORDER BY test.PointTo,test.PointCurrent
24 янв 14, 12:59    [15462226]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
iap, В очередной раз большое спасибо! Экзотика работает быстрее всего.

pd.p1=t1.PointCurrent AND pd.p2=t1.PointTo OR pd.p2=t1.PointCurrent AND pd.p1=t1.PointTo
время выполнения 00:00:10.3325910
NOT EXISTS
(
 SELECT pd.p1,pd.p2
 EXCEPT
 SELECT t1.PointCurrent,t1.PointTo
 EXCEPT
 SELECT t1.PointTo,t1.PointCurrent
)
время выполнения 00:00:16.2099271
EXISTS
(
 SELECT pd.p1,pd.p2
 INTERSECT
 SELECT t1.PointCurrent,t1.PointTo
 UNION ALL
 SELECT pd.p1,pd.p2
 INTERSECT
 SELECT t1.PointTo,t1.PointCurrent
)
время выполнения 00:00:01.0070691
24 янв 14, 13:04    [15462272]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
Начинающий SQL
iap, В очередной раз большое спасибо! Экзотика работает быстрее всего.

pd.p1=t1.PointCurrent AND pd.p2=t1.PointTo OR pd.p2=t1.PointCurrent AND pd.p1=t1.PointTo

время выполнения 00:00:10.3325910
NOT EXISTS
(
 SELECT pd.p1,pd.p2
 EXCEPT
 SELECT t1.PointCurrent,t1.PointTo
 EXCEPT
 SELECT t1.PointTo,t1.PointCurrent
)

время выполнения 00:00:16.2099271
EXISTS
(
 SELECT pd.p1,pd.p2
 INTERSECT
 SELECT t1.PointCurrent,t1.PointTo
 UNION ALL
 SELECT pd.p1,pd.p2
 INTERSECT
 SELECT t1.PointTo,t1.PointCurrent
)

время выполнения 00:00:01.0070691
А с планами выполнения что?
24 янв 14, 13:08    [15462308]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
Glory
Идея там была
+
WITH x AS 
(
	SELECT a.[p1], a.[p2], a.[Distance] FROM (
	VALUES
	(1,1,0),
	(23,1,486),
	(2,5,183),
	(2,1,31),
	(3,7,955),
	(19407,19348,3242),
	(21,21,0),
	(6224,497,640),
	(1,21,167)
	) a([p1], [p2], [Distance])
), test AS 
(
	SELECT b.[PointCurrent], b.[PointTo] FROM (
	VALUES
	(19407,19348),
	(3819,3740),
	(5258,13779),
	(497,6224),
	(20249,20340)
	) b([PointCurrent], [PointTo])
), pd AS
(
	SELECT p1,p2, [Distance] FROM x 
	UNION ALL
	SELECT p2,p1, [Distance] FROM x
)
SELECT
	test.*, pd.Distance 
FROM 
	test
LEFT OUTER JOIN
	pd
ON 
	(pd.p1=PointCurrent AND pd.p2=PointTo)
WHERE pd.Distance IS Null
ORDER BY test.PointTo,test.PointCurrent

Спасибо! не правильно Вас понял.
Правда время выполнения запроса 00:00:24.3723940
24 янв 14, 13:17    [15462405]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Glory
Member

Откуда:
Сообщений: 104751
Начинающий SQL
Правда время выполнения запроса 00:00:24.3723940

Вы планы то выполнения смотрите ?
24 янв 14, 13:18    [15462417]     Ответить | Цитировать Сообщить модератору
 Re: Помогите написать запрос  [new]
Начинающий SQL
Guest
iap
А с планами выполнения что?

Первый раз слышу. Пошел штудировать.
Век живи, век учись.
24 янв 14, 13:20    [15462433]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить