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

Откуда:
Сообщений: 6
Добрый день. Написанный ниже запрос находит заказы со значениями сумм приобретений выше среднего

SELECT * FROM Orders outers WHERE amt > 
(SELECT AVG(amt) FROM Orders inners WHERE inners.cnum = outers.cnum);


где amt - сумма приобретений заказчика
Orders - таблица заказов
cnum - номер заказчика

Не совсем пойму логику соотнесенного подзапроса. Зачем сравнивать inners.cnum = outers.cnum? Насколько я понял сравнивается номер заказчика с самим же собой - но эти номера ведь всегда будут одинаковыми.. Данные ведь берутся из одной и той же таблицы Заказов. При чем по книге где я это вычитал написано очень много примеров где сравниваются поля внешнего и внутреннего запроса из одной и той же таблицы.. Спасибо за помощь
4 мар 13, 15:01    [14009208]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
iap
Member

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

одна и та же таблица используется дважды под разными псевдонимами (алиасами),
при этом всё выглядит так, будто это две разные таблицы.
Поле cnum ведь имеет разные значения?
А нужно выбирать данные из двух как бы разных таблиц outers и inners, чтобы cnum был одинаковым.
4 мар 13, 15:10    [14009261]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
WarAnt
Member

Откуда: Питер
Сообщений: 2421
kernel32

Не совсем пойму логику соотнесенного подзапроса. Зачем сравнивать inners.cnum = outers.cnum?


чтобы получить все записи у которых amt больше среднего по каждому cnum
4 мар 13, 15:10    [14009267]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Паганель
Member

Откуда: Винница
Сообщений: 22552
kernel32
Насколько я понял сравнивается номер заказчика с самим же собой - но эти номера ведь всегда будут одинаковыми
нет, в запросе используются два множества данных, inners и outers
сравнение нужно чтобы использовать из inners только нужные для вычисления среднего данные, а не все
4 мар 13, 15:11    [14009270]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
kernel32
Member

Откуда:
Сообщений: 6
вау. благодарю за столь быстрые и доходчивые ответы. получается запись inners.cnum = outers.cnum нужна для того чтобы найти одинаковые строки в двух таблицах *Orders* по этому cnum, далее находится среднее значение сумм покупок по клиенту в одной таблице и сравнивается с ценой покупок в другой таблице по этому же клиенту. Я правильно понял?
4 мар 13, 15:26    [14009376]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
kernel32
вау. благодарю за столь быстрые и доходчивые ответы. получается запись inners.cnum = outers.cnum нужна для того чтобы найти одинаковые строки в двух таблицах *Orders* по этому cnum, далее находится среднее значение сумм покупок по клиенту в одной таблице и сравнивается с ценой покупок в другой таблице по этому же клиенту. Я правильно понял?
Лучше читать запросы так:

Для каждой строки внешнего запроса делаются все проверки в WHERE, в том числе для этой строки выполняется поздапрос "SELECT AVG(amt) FROM Orders inners" для соответствующего cnum.
4 мар 13, 20:46    [14010887]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

Откуда:
Сообщений: 12
Господа. Читаю ваш замечательный портал. А именно статью "Понимание SQL"
Дойдя до ГЛАВА 11. СООТНЕСЕННЫЕ ПОДЗАПРОСЫ. Возник вопрос. А как же работает соотнесенный подзапрос?
В статье есть таблица
================== ТАБЛИЦА 3: Заказы (Orders) =====================

-----------------------------------------------
onum | amt | odate | cnum | snum
-------|-----------|-------------|------|------
3001 | 18.69 | 10/03/1990 | 2008 | 1007
3003 | 767.19 | 10/03/1990 | 2001 | 1001
3002 | 1900.10 | 10/03/1990 | 2007 | 1004
3005 | 5160.45 | 10/03/1990 | 2003 | 1002
3006 | 1098.16 | 10/03/1990 | 2008 | 1007
3009 | 1713.23 | 10/04/1990 | 2002 | 1003
3007 | 75.75 | 10/04/1990 | 2004 | 1002
3008 | 4723.00 | 10/05/1990 | 2006 | 1001
3010 | 1309.95 | 10/06/1990 | 2004 | 1002
3011 | 9891.88 | 10/06/1990 | 2006 | 1001
-----------------------------------------------

из нее проводятся выборки, по средством запросов sql.
В данной теме, раннее был описан запрос

SELECT *
FROM Orders otr
where amt > --Внешний запрос
(SELECT avg(amt)
FROM Orders itr --Внутренний подзапрос
WHERE inr.cnum = otr.cnum)

Который выводит результирующий набор
onum amt odate cnum snum
----------- ---------------------- ---------------------- ----------- -----------
3010 1309,95 1990-06-10 00:00:00.00 2004 1002
3011 9891,88 1990-06-10 00:00:00.00 2006 1001
3006 1098,16 1990-03-10 00:00:00.00 2008 1007

Рассуждая, как работает запрос исходя из статьи.
Берется строка кандидат внешнего запроса Orders.otr. В нашем случае это
3001 | 18.69 | 10/03/1990 | 2008 | 1007
Затем ей соответствует строка из внутреннего подзапроса Orders inr, согласно предикату itr.cnum = otr.cnum
3001 | 18.69 | 10/03/1990 | 2008 | 1007
После чего находится avg(amt) между двумя таблицами inr.orders и otr.orders
(18.69+18.69)/2 = 18.69 - результат внутреннего подзапроса
после чего
amt внешнего запроса 18.69 сравнивается с avg(amt) внутреннего подзапроса.
18.69 >18.69, нет следовательно строка кандидат в результирующий набор не попадает.
Аналогично рассуждая мы и получим результирующий набор.
Но вот когда мы поменяем условия в запросе.

SELECT *
FROM Orders otr
where amt = --Внешний запрос
(SELECT avg(amt)
FROM Orders inr --Внутренний подзапрос
WHERE inr.cnum = otr.cnum)

результирующий набор будет таким
onum amt odate cnum snum
----------- ---------------------- ---------------------- ----------- -----------
3003 767,19 1990-03-10 00:00:00.00 2001 1001
3009 1713,23 1990-04-10 00:00:00.00 2002 1003
3005 5160,45 1990-03-10 00:00:00.00 2003 1002
3002 1900,1 1990-03-10 00:00:00.00 2007 1004
Он вернул нам среднее арифметическое amt не повторяющегося cnum.
Вопрос почему он не вывел строки в которых столбец cnum имеет одинаковые значения.
Если при запросе строка ссылается сама на себя, то ее среднее арифметическое значение будет удовлетворять условию подзапроса.
(18.69+18.69)/2 = 18.69 (результат внутреннего подзапроса)
18.89 = 18.69
Следовательно строка кандидат, при обращении сама на себя
3001 | 18.69 | 10/03/1990 | 2008 | 1007
во втором подзапросе должна попасть в результирующий набор.
Объясните пожалуйста детально где я не прав. И по возможности логику выборки соотнесенного подзапроса.
27 июн 13, 15:25    [14492345]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
iap
Member

Откуда: Москва
Сообщений: 47001
Olejka
Рассуждая, как работает запрос исходя из статьи.
Берется строка кандидат внешнего запроса Orders.otr. В нашем случае это
3001 | 18.69 | 10/03/1990 | 2008 | 1007
Затем ей соответствует строка соответствуют строки из внутреннего подзапроса Orders inr, согласно предикату itr.cnum = otr.cnum
3001 | 18.69 | 10/03/1990 | 2008 | 1007
3006 | 1098.16 | 10/03/1990 | 2008 | 1007

Olejka
После чего находится avg(amt) между двумя таблицами inr.orders и otr.orders
(18.69+18.69)/2 = 18.69 - результат внутреннего подзапроса
Что такое "AVG(amt) между"???

0.5 * (18.69 + 1098.16) = 558.425
27 июн 13, 15:57    [14492600]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

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

После чего находится avg(amt) между двумя таблицами inr.orders и otr.orders.
Я не корректно выразил свою мысль в данном случае.
Среднее арифметическое двух значений, которые берутся согласно предикату внутреннего подзапроса inr.cnum = otr.cnum.
То, что затем строка кандидат внешнего запроса сравнивается с остальными строками возвращаемыми внутренним подзапросом, это я тоже знал.

Вопрос, почему в запросе

SELECT *
FROM Orders otr
where amt = --Внешний запрос
(SELECT avg(amt)
FROM Orders inr --Внутренний подзапрос
WHERE inr.cnum = otr.cnum)


Строка кандидат
3001 | 18.69 | 10/03/1990 | 2008 | 1007
не попадает в результирующий набор?


Ведь сравниваются строка кандидат и строка которую вернул внутренний подзапрос, а имеено
Строка кандидат
3001 | 18.69 | 10/03/1990 | 2008 | 1007
и
строка подзапроса
3001 | 18.69 | 10/03/1990 | 2008 | 1007.
27 июн 13, 16:16    [14492777]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
AlexNagibator
Member

Откуда:
Сообщений: 3
А обращается ли строка кандидат на саму себя?
27 июн 13, 16:19    [14492798]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
iap
Member

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

я же вам дорисовал ещё одну строку подзапроса перед расчётом среднего значения!
Странные какие-то рассуждения от вас слышу.
В подзапросе тупо выполняется SELECT из таблиц, перечисленных во FROM.
При чём же здесь таблица внешнего (основного) запроса, не понимаю!
Только задаётся ограничение для выборки строк "внутри" для текущей строки "вовне".
Подзапрос для данного "кандидата" возвращает число 558.425, как я вам уже писал.
27 июн 13, 16:25    [14492847]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

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

А как же быть в случае, когда строка ссылается сама на себя?
27 июн 13, 16:29    [14492889]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
iap
Member

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

А как же быть в случае, когда строка ссылается сама на себя?
Это как?
27 июн 13, 16:30    [14492900]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

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

Сравниваются строка кандидат и строка которую вернул внутренний подзапрос, а имеено
Строка кандидат
3001 | 18.69 | 10/03/1990 | 2008 | 1007
и
строка подзапроса
3001 | 18.69 | 10/03/1990 | 2008 | 1007.
27 июн 13, 16:34    [14492935]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

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

Я был бы очень признателен, если бы вы описали сам алгоритм сортировки соотнесенного запроса, на примере данного запроса.
27 июн 13, 16:41    [14493012]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
iap
Member

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

Вы зациклились? Куда вторая строка делась? Зря я её рисовал что ли?
Сравниваются не строки, а поля cnum. Разве не так?
27 июн 13, 16:43    [14493022]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
AlexNagibator
Member

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

Сравниваются поля cnum, но значения для определения среднего арифметического берутся из поля amt, находящегося в одной строке с ним. Или я не прав?
27 июн 13, 16:47    [14493061]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

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

вы оперируете запросом

SELECT *
FROM Orders otr
where amt > --Внешний запрос
(SELECT avg(amt)
FROM Orders itr --Внутренний подзапрос
WHERE inr.cnum = otr.cnum)


Я же прошу объяснить почему исключены дубликаты в запросе

SELECT *
FROM Orders otr
where amt = --Внешний запрос
(SELECT avg(amt)
FROM Orders itr --Внутренний подзапрос
WHERE inr.cnum = otr.cnum)
27 июн 13, 16:49    [14493081]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
iap
Member

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

Сравниваются поля cnum, но значения для определения среднего арифметического берутся из поля amt, находящегося в одной строке с ним. Или я не прав?
Ну, я ж писал, что складывается и делится на количество в данном конкретном случае.
В подзапросе выбираются две строки с cnum=2008 (с этим значением больше нет)
и тут же расчитывается среднее, которое возвращается внешнему запросу.
С этим числом сравнивается amt внешнего запроса.

То же самое независимо происходит с каждой строкой внешнего запроса.
27 июн 13, 16:51    [14493110]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
AlexNagibator
Member

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

Спасибо. Теперь понял.
27 июн 13, 16:55    [14493153]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

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

iap
AlexNagibator
iap,
Ну, я ж писал, что складывается и делится на количество в данном конкретном случае.
В подзапросе выбираются две строки с cnum=2008 (с этим значением больше нет)
и тут же расчитывается среднее, которое возвращается внешнему запросу.
С этим числом сравнивается amt внешнего запроса.

То же самое независимо происходит с каждой строкой внешнего запроса.


Спасибо вам разобрался :)
27 июн 13, 16:56    [14493169]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
iap
Member

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

вы оперируете запросом

SELECT *
FROM Orders otr
where amt > --Внешний запрос
(SELECT avg(amt)
FROM Orders itr --Внутренний подзапрос
WHERE inr.cnum = otr.cnum)


Я же прошу объяснить почему исключены дубликаты в запросе

SELECT *
FROM Orders otr
where amt = --Внешний запрос
(SELECT avg(amt)
FROM Orders itr --Внутренний подзапрос
WHERE inr.cnum = otr.cnum)
Потому что только в одной строке из нескольких оказалось amt больше того, который вернул подзапрос.
А он вернул среднее.
Как рассказывал Горький, если один съел курицу, а другой остался голодным,
то в среднем каждый съел по полкурицы. Больше среднего съел только один, однако!
27 июн 13, 16:56    [14493170]     Ответить | Цитировать Сообщить модератору
 Re: Соотнесенный подзапрос  [new]
Olejka
Member

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

Спасибо вам еще раз.
Меня сбивал с толку алгоритм работы агрегатной функции в подзапросе.
После того, как вы объяснили принцип выборки в подзапросе, все стало на свои места.
27 июн 13, 16:58    [14493188]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить