Как выполнить нижеперечисленное в одном SQL-запросе?

Question

[Amateru]

Есть задание:

Составить список Товаров из таблицы Production.Product и вывести следующие характеристики:
1. Категория, Подкатегория, ID продукта
2. Полное имя товара, с разделителями => (Имя; Номер продукта; Цвет; Размер)
3. Полное имя товара для отображения на веб-странице:
* Ограничить полное имя товара до 30 символов
* Если строка оказаласьь длиннее поставить в конце '...'
* Заменить все пробелы на '%20"
4. Скольно продается товар (в месяцах). Учесть что товар может продаваться до сих пор.
5. Найти и вывести среднюю стоимость товаров в данной категории/подкатегории
6. На основе этого определить ценовую категорию товара
* Если товар дороже средней цены по категории на 50% и более - 'High Price'
* Если товар дешевле средней цены по категории на 30% и более - 'LowPrice'
* Для остальных - 'Medium price'

Ума не приложу как выполнить все это в 1 sql-запросе. Буду рад, если кто-то подскажет.
Таблица https://dataedo.com/samples/html/AdventureWorks/do...

Comments

[Антон Шаманов]

может мы за тебя и на собеседование сходим? а то налажаешь там..

[Алексей С.]

начните выполнять условия по частям , потом будет легче свести в один запрос

[Akina]

Да простейший же запрос. Длинный только. Только и нужно, что понимание, что такое группировка. Ну и аккуратность не помешает.

[Amateru]

Akina, Составил запросы, но вот как объединить все это с 5 и 6 пунктом - ума не приложу. Копаю в сторону отдельной функции для решения этого вопроса, но если вы можете направить в сторону более оптимального решения, то я был бы благодарен.

create function FullMonthsSeparation 
(
    @DateA datetime,
    @DateB datetime
)
returns int
as
begin
    declare @Result int
    declare @DateX datetime
    declare @DateY datetime

    if(@DateA < @DateB)
    begin
        set @DateX = @DateA
        set @DateY = @DateB
    end
    else
    begin
        set @DateX = @DateB
        set @DateY = @DateA
    end

    set @Result = (
		select 
			case 
				when datepart(day, @DateX) > datepart(day, @DateY)
				then datediff(month, @DateX, @DateY) - 1
				else datediff(month, @DateX, @DateY)
			end)
    return @Result
end
go

select
Production.ProductCategory.Name as 'Категория', 
Production.ProductSubcategory.Name 'Подкатегория', 
Production.Product.ProductID as 'ID продукта',	
concat(
	case 
		when len(replace(Production.Product.Name, ' ', '%20%')) > 30 
		then left(replace(Production.Product.Name, ' ', '%20%'), 30) + '...'
		else replace(Production.Product.Name, ' ', '%20%')
	end, ';', 
	Production.Product.ProductNumber, ';', 
	isnull(Production.Product.Color, 'NULL'), ';',
	isnull(Production.Product.Size, 'NULL'))
as 'Продукт',
isnull(
	dbo.FullMonthsSeparation(Production.Product.SellEndDate, Production.Product.SellStartDate), 
	datediff(month, Production.Product.SellStartDate, getdate())) 
as 'Время с начала продаж (месяцы)'
from Production.ProductCategory, Production.ProductSubcategory, Production.Product 
where Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID;


select Production.ProductSubcategory.Name as 'Название подкатегории',avg(Production.Product.StandardCost) as 'Средняя цена в подкатегории' 
from Production.Product, Production.ProductSubcategory 
where Product.ProductSubcategoryID = ProductSubcategory.ProductSubcategoryID 
group by Production.ProductSubcategory.Name;

select Production.ProductCategory.Name as 'Название категории', avg(Production.Product.StandardCost) as 'Средняя цена в категории' 
from Production.Product, Production.ProductSubcategory, Production.ProductCategory 
where Production.ProductSubcategory.ProductCategoryID = Production.ProductCategory.ProductCategoryID 
group by Production.ProductCategory.Name;

[Akina]

но вот как объединить все это с 5 и 6 пунктом - ума не приложу.

А для этого существуют оконные функции. В данном конкретном случае нужно использовать оконную версию агрегатной функции AVG().
То же касается и пункта 4 - только использовать надо FIRST_VALUE()/LAST_VALUE().

PS. И я не понимаю, зачем надо было наплодить кучу отдельных SELECT, когда всё делается в одном запросе.