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

Откуда:
Сообщений: 71
Здравствуйте все!
Что-то я запнулся на такой задаче.

На сервере (MS SQL Server 2005) расположены две таблицы: таблица Заказчики включает поля КодЗаказчика, КодЗаказа; таблица Заказы включает поля КодЗаказа, ДатаЗаказа, ОбъемЗаказа. Подразумевается, что любой Заказчик в течение любого одного дня имеет не более чем один Заказ, но вообще, естественно, у любого Заказчика может быть любое количество Заказов.
Требуется сформировать на том же сервере на основании этих таблиц представление, которое выводит поля КодЗаказчика, КодЗаказа, ДатаЗаказа, ОбъемЗаказа, но не для всех имеющихся в базе данных Заказов (это-то элементарно), а для каждого Заказчика выводит данные только одного Заказа с самым последним по времени значением поля ДатаЗаказа. Само собой, у каждого Заказчика это самое последнее по времени значение поля ДатаЗаказа может быть свое и заранее неизвестное. Могут потребоваться вариации этой задачи: с самым первым значением ДатаЗаказа; с наибольшим значением ОбъемЗаказа и т. п., это, видимо, решается аналогично.

Или это совсем элементарно, и просто крыша перебралась куда-то не туда, или есть какие-то фишки, в любом случае, кто знает как сделать, подскажите.
Заранее спасибо.
29 май 09, 22:27    [7246886]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
iljy
Guest
MS_4G
Здравствуйте все!
Что-то я запнулся на такой задаче.

На сервере (MS SQL Server 2005) расположены две таблицы: таблица Заказчики включает поля КодЗаказчика, КодЗаказа; таблица Заказы включает поля КодЗаказа, ДатаЗаказа, ОбъемЗаказа. Подразумевается, что любой Заказчик в течение любого одного дня имеет не более чем один Заказ, но вообще, естественно, у любого Заказчика может быть любое количество Заказов.

У вас у одного Заказа - один Заказчик или сколько угодно? Если один - вам стоит посетить форум по проектированию структуры БД.
MS_4G

Требуется сформировать на том же сервере на основании этих таблиц представление, которое выводит поля КодЗаказчика, КодЗаказа, ДатаЗаказа, ОбъемЗаказа, но не для всех имеющихся в базе данных Заказов (это-то элементарно), а для каждого Заказчика выводит данные только одного Заказа с самым последним по времени значением поля ДатаЗаказа. Само собой, у каждого Заказчика это самое последнее по времени значение поля ДатаЗаказа может быть свое и заранее неизвестное. Могут потребоваться вариации этой задачи: с самым первым значением ДатаЗаказа; с наибольшим значением ОбъемЗаказа и т. п., это, видимо, решается аналогично.

Или это совсем элементарно, и просто крыша перебралась куда-то не туда, или есть какие-то фишки, в любом случае, кто знает как сделать, подскажите.
Заранее спасибо.


на основании вашей структуры запрос сделать можно, но будет не просто коряво, а очень коряво:
WITH T_All(КодЗаказчика, КодЗаказа, ДатаЗаказа, ОбъемЗаказа) AS
(SELECT t1.КодЗаказчика, t1.КодЗаказа, t1.ДатаЗаказа, t1.ОбъемЗаказа
	  FROM Заказ t1 join Заказчик t2 on t1.КодЗаказа = t2.КодЗаказа) 
SELECT * FROM
(SELECT DISTINCT КодЗаказчика FROM Заказчик) t3
	outer apply
(SELECT TOP 1 КодЗаказа, ДатаЗаказа, ОбъемЗаказа
 FROM T_All 
 WHERE T_All.КодЗаказчика = t3.КодЗаказчика
 ORDER BY ДатаЗаказа DESC) t4
Это выбор заказа с максимальной датой. Критерий выбора изменяете вот в этой части
ORDER BY ДатаЗаказа DESC
29 май 09, 23:14    [7246973]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 33366
Блог
iljy,

бррр)

автору топика - гляньте в сторону аналитических функций (OVER... PARTITION BY)
30 май 09, 01:07    [7247110]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
MS_4G
Member

Откуда:
Сообщений: 71
iljy, за совет спасибо. Попробую реализовать.
Само собой, предполагается, что каждый Заказ сделан каким-либо одним Заказчиком. Это настолько типовая ситуация, что даже в голову не пришло в этом пункте что-либо уточнять.
30 май 09, 02:19    [7247151]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
19SAA
Member

Откуда:
Сообщений: 36
Может так?

select z1.КодЗаказчика, z1.КодЗаказа, z2.ДатаЗаказа, z2.ОбъемЗаказа
from Заказчики as z1
inner join Заказы as z2 on z1.КодЗаказа=z2.КодЗаказа
inner join
(select z1.КодЗаказчика, max(ДатаЗаказа) as MD from Заказчики as z1
inner join Заказы as z2 on z1.КодЗаказа=z2.КодЗаказа
group by z1.КодЗаказчика) as z3
on z1.КодЗаказчика=z3.КодЗаказчика and z2.ДатаЗаказа=MD
30 май 09, 08:52    [7247247]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
iljy
Guest
Критик,
не преувеличивайте:) я по работе часто сталкиваюсь с запросами типа "выбрать из интервала последнее по дате значение для каждого родителя", и apply взялся не с потолка. экпериментальным путем установлено, что для корелированных подзапросов, inner join к группе (как предлагает 19SAA) и apply при правильных индексах порождают одинаковый план запроса, а с аналитикой по PARTITION BY планировщик часто промахивается и сканирует все, хотя нужно только последнее значение. А при прочих равных apply легко модифицируется если нужно не одно максимальное значение, а 2 или 3. Join в этом случае потребуется очень серьезно точить, а корелированый подзапрос вообще станет бесполезен, к тому же в нем нельзя вытащить больше одного поля.

MS_4G

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

тогда перенесите ссылку на КодЗаказчика в таблицу Заказы. будет гораздо правильнее, и работать легче.
30 май 09, 12:48    [7247467]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
MS_4G
Member

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

На сервере (MS SQL Server 2005) расположены две таблицы: таблица Заказчики включает поля ИмяЗаказчика, КодЗаказчика; таблица Заказы включает поля КодЗаказчика, КодЗаказа, ДатаЗаказа, ОбъемЗаказа. Подразумевается, что любой Заказчик в течение любого одного дня имеет не более чем один Заказ, но вообще, естественно, у любого Заказчика может быть любое количество Заказов. Также предполагается, что каждый Заказ сделан каким-либо одним Заказчиком. В этом случае таблицы, очевидно, связаны по полю КодЗаказчика отношением "один-ко-многим".
Требуется сформировать на том же сервере на основании этих таблиц представление, которое выводит поля ИмяЗаказчика, КодЗаказчика, КодЗаказа, ДатаЗаказа, ОбъемЗаказа, но не для всех имеющихся в базе данных Заказов (это-то элементарно), а для каждого Заказчика выводит данные только одного Заказа с самым последним по времени значением поля ДатаЗаказа. Само собой, у каждого Заказчика это самое последнее по времени значение поля ДатаЗаказа может быть свое и заранее неизвестное. Могут потребоваться вариации этой задачи: с самым первым значением ДатаЗаказа; с наибольшим значением ОбъемЗаказа и т. п., это, видимо, решается аналогично.

Мне очень жаль, что первоначальная постановка задчи была некорректна, но и для уточненной задачи пока непонятно, как нужно делать.
1 июн 09, 00:38    [7249562]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
MS_4G
Member

Откуда:
Сообщений: 71
19SAA, спасибо. Я взял ваш текст за основу и с минимальными коррективами получил требуемый мне результат, вернее, два результата: когда нужно выводить таких Заказчиков, у которых нет Заказов, и когда этого не нужно.
2 июн 09, 00:54    [7253927]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
MS_4G
Member

Откуда:
Сообщений: 71
19SAA, еще раз спасибо. По вашей рекомендации я сформировал нижеследующий фрагмент (представление НЕПУСТОЕ), который решает поставленную задачу:

SELECT dbo.Заказчики.КодЗаказчика, dbo.Заказчики.ИмяЗаказчика, dbo.Заказы.КодЗаказа, dbo.Заказы.ДатаЗаказа, dbo.Заказы.ОбъемЗаказа
FROM dbo.Заказчики INNER JOIN
dbo.Заказы ON dbo.Заказчики.КодЗаказчика = dbo.Заказы.КодЗаказчика INNER JOIN
(SELECT Заказчики_1.КодЗаказчика, MAX(Заказы_1.ДатаЗаказа) AS MD
FROM dbo.Заказчики AS Заказчики_1 INNER JOIN
dbo.Заказы AS Заказы_1 ON Заказчики_1.КодЗаказчика = Заказы_1.КодЗаказчика
GROUP BY Заказчики_1.КодЗаказчика) AS zw ON dbo.Заказчики.КодЗаказчика = zw.КодЗаказчика AND dbo.Заказы.ДатаЗаказа = zw.MD

В этом представлении Заказчики, у которых вообще нет Заказов, не выводятся.
Поскольку задача модельная, могут быть ситуации, когда, напротив, Заказчики, у которых вообще нет Заказов, должны выводиться. Казалось бы, следующий фрагмент (представление КВАЗИПУСТОЕ) должен это реализовать, однако здесь также Заказчики, у которых вообще нет Заказов, не выводятся:

SELECT dbo.Заказчики.КодЗаказчика, dbo.Заказчики.ИмяЗаказчика, dbo.Заказы.КодЗаказа, dbo.Заказы.ДатаЗаказа, dbo.Заказы.ОбъемЗаказа
FROM dbo.Заказчики INNER JOIN
dbo.Заказы ON dbo.Заказчики.КодЗаказчика = dbo.Заказы.КодЗаказчика INNER JOIN
(SELECT Заказчики_1.КодЗаказчика, MAX(Заказы_1.ДатаЗаказа) AS MD
FROM dbo.Заказчики AS Заказчики_1 INNER JOIN
dbo.Заказы AS Заказы_1 ON Заказчики_1.КодЗаказчика = Заказы_1.КодЗаказчика
GROUP BY Заказчики_1.КодЗаказчика) AS zw ON dbo.Заказчики.КодЗаказчика = zw.КодЗаказчика AND (zw.MD IS NULL OR
dbo.Заказы.ДатаЗаказа = zw.MD)

Чтобы выводить Заказчиков, у которых вообще нет Заказов, пришлось составить вспомогательное представление zw:

SELECT dbo.Заказчики.КодЗаказчика, MAX(dbo.Заказы.ДатаЗаказа) AS MD
FROM dbo.Заказчики LEFT OUTER JOIN
dbo.Заказы ON dbo.Заказы.КодЗаказчика = dbo.Заказчики.КодЗаказчика
GROUP BY dbo.Заказчики.КодЗаказчика

и такой фрагмент (представление ПУСТОЕ), который это вспомогательное представление использует:

SELECT dbo.Заказчики.КодЗаказчика, dbo.Заказчики.ИмяЗаказчика, dbo.Заказы.КодЗаказа, dbo.Заказы.ДатаЗаказа, dbo.Заказы.ОбъемЗаказа
FROM dbo.Заказчики LEFT OUTER JOIN
dbo.Заказы ON dbo.Заказчики.КодЗаказчика = dbo.Заказы.КодЗаказчика INNER JOIN
dbo.zw ON dbo.Заказчики.КодЗаказчика = dbo.zw.КодЗаказчика AND (dbo.zw.MD IS NULL OR
dbo.Заказы.ДатаЗаказа = dbo.zw.MD)

Поскольку в обоих вариантах поставленная задача получается решенной, можно было бы и успокоиться. Тем не менее есть еще вопросы, и если кто-то знает ответы, пожалуйста, подскажите:
1. Можно ли как-то исправить фрагмент (представление КВАЗИПУСТОЕ) или сочинить что-то иное, чтобы все же выводить Заказчиков, у которых вообще нет Заказов.
2. Если "Да", то будет ли это более эффективно прежде всего по скорости работы, но и по удобству составления таких представлений на будущее (на основе этой модельной задачи должны будут потом аналогичным образом решаться и некоторые другие задачи).
Заранее спасибо.
2 июн 09, 04:08    [7254025]     Ответить | Цитировать Сообщить модератору
 Re: Формирование представления  [new]
MS_4G
Member

Откуда:
Сообщений: 71
iljy, в итоге вам большое спасибо.
Поскольку мне ранее не приходилось работать с APPLY, потребовалось некоторое время разобраться, что эту штуку при построении представлений нельзя использовать в конструкторе запросов, а только в сценарии. После этого все проблемы были решены.

Ниже привожу два варианта (с учетом всех уточнений постановки задачи): первый View_Order выводит требуемые сведения только для тех Заказчиков, у которых фактически есть хотя бы один Заказ, второй View_Order_Nulls выводит требуемые сведения для всех зарегистрированных Заказчиков, причем для тех Заказчиков, у которых Заказов нет, выводится Null.

Теперь вопрос закрыт полностью.


USE [Base]
GO
/****** Object: View [dbo].[View_Order] Script Date: 06/04/2009 14:07:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Order] AS
WITH T_ALL AS
(SELECT t1.КодЗаказчика, t1.ИмяЗаказчика, t2.КодЗаказа, t2.ДатаЗаказа, t2.ОбъемЗаказа
FROM dbo.Заказчики AS t1 INNER JOIN dbo.Заказы AS t2 ON t1.КодЗаказчика = t2.КодЗаказчика)
SELECT * FROM
(SELECT КодЗаказчика, ИмяЗаказчика
FROM dbo.Заказчики) t3 CROSS APPLY
(SELECT TOP 1 КодЗаказа, ДатаЗаказа, ОбъемЗаказа FROM T_ALL
WHERE T_ALL.КодЗаказчика = t3.КодЗаказчика ORDER BY ДатаЗаказа DESC) t4



USE [Base]
GO
/****** Object: View [dbo].[View_Order_Nulls] Script Date: 06/04/2009 14:11:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[View_Order_Nulls] AS
WITH T_ALL AS (SELECT t1.КодЗаказчика, t1.ИмяЗаказчика, t2.КодЗаказа, t2.ДатаЗаказа, t2.ОбъемЗаказа
FROM dbo.Заказчики AS t1 INNER JOIN dbo.Заказы AS t2 ON t1.КодЗаказчика = t2.КодЗаказчика)
SELECT * FROM (SELECT КодЗаказчика, ИмяЗаказчика FROM dbo.Заказчики) t3 OUTER APPLY
(SELECT TOP 1 КодЗаказа, ДатаЗаказа, ОбъемЗаказа FROM T_ALL WHERE T_ALL.КодЗаказчика = t3.КодЗаказчика
ORDER BY ДатаЗаказа DESC) t4
4 июн 09, 15:12    [7265669]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить