понедельник, 18 марта 2013 г.

Как найти, насколько дороже или дешевле товар средней цены в своей ценовой категории




-- Как найти, насколько дороже или дешевле товар средней цены в своей ценовой категории
-- Можно коррелированным подзапросом

SELECT
    Name
    ,O.Class
    ,ListPrice
    ,(
        SELECT AVG(ListPrice) AS A
        FROM AdventureWorks2012.Production.Product AS I
        WHERE ListPrice > 0 AND I.Class = O.Class
    ) AS AvgInClass

    ,ListPrice -
            (
                SELECT AVG(ListPrice) AS A
                FROM AdventureWorks2012.Production.Product AS I
                WHERE ListPrice > 0 AND I.Class = O.Class
            ) AS Difference
FROM AdventureWorks2012.Production.Product AS O
WHERE ListPrice > 0
ORDER BY ListPrice DESC

-- В SQL Server > 2005  можно изящней при помощи функции агрегирования AVG с OVER
SELECT
    Name
    ,O.Class
    ,ListPrice
    ,AVG(ListPrice) OVER (PARTITION BY Class)
AS AvgInClass
    ,ListPrice - AVG(ListPrice) OVER (PARTITION BY Class) AS Difference
FROM AdventureWorks2012.Production.Product AS O
WHERE ListPrice > 0
ORDER BY ListPrice DESC