T-SQL AVG для нескольких столбцов подряд

avatar
Vesper Annstas
8 апреля 2018 в 00:18
503
2
1

Я пытаюсь выбрать средние продажи на человека по территории из базы данных AdventureWorks.

Поскольку это агрегирование нескольких столбцов в строке вместо нескольких строк в столбце, кажется, что мне понадобится подзапрос, временная таблица, возможно, CTE, но я не уверен, как определить, в каком направлении взять или как это написать.

Желаемый результат:

| SalesTerritory | SalesPeople | 2011   | 2012    | 2013    | 2014    | AvgSales
+----------------+-------------+--------+---------+---------+---------+----------
| Australia      | 1           | NULL   | NULL    | 184105  | 1237705 | [avg]
| Canada         | 2           | 115360 | 3426082 | 2568323 | etc...  | [avg]

Код:

SELECT
    pvt.SalesTerritory,
    COUNT(pvt.SalesPersonID) AS SalesPeople,
    SUM(pvt.[2011]),
    SUM(pvt.[2012]),
    SUM(pvt.[2013]),
    SUM(pvt.[2014])

    --What's the best way to AVG the sales by year by sales person for each territory here?
 FROM    
     (SELECT
          st.[Name] AS [SalesTerritory],
          soh.[SalesPersonID],
          soh.[SubTotal],
          YEAR(DATEADD(m, 6, soh.[OrderDate])) AS [FiscalYear]
      FROM   
          [Sales].[SalesPerson] sp
      INNER JOIN  
          [Sales].[SalesOrderHeader] soh ON sp.[BusinessEntityID] = soh.[SalesPersonID]
      INNER JOIN  
          [Sales].[SalesTerritory] st ON sp.[TerritoryID] = st.[TerritoryID]
      INNER JOIN  
          [HumanResources].[Employee] e ON soh.[SalesPersonID] = e.[BusinessEntityID]
      INNER JOIN  
          [Person].[Person] p ON p.[BusinessEntityID] = sp.[BusinessEntityID]) AS soh
      PIVOT
          (SUM([SubTotal]) FOR [FiscalYear] IN ([2011], [2012], [2013], [2014])) AS pvt
GROUP BY    
    pvt.SalesTerritory
Источник

Ответы (2)

avatar
Razvan Socol
10 апреля 2018 в 13:20
0

Насколько я понимаю ваш вопрос, вам нужен средний годовой объем продаж на одного продавца для каждой территории. Ответ Узи дает средний годовой объем продаж всех продавцов вместе для каждой территории. Вы можете разделить этот результат на количество продавцов или использовать такой запрос:

SELECT pvt.SalesTerritory, COUNT(pvt.SalesPersonID) AS SalesPeople, 
       SUM(pvt.[2011]) AS [2011], SUM(pvt.[2012]) AS [2012],
       SUM(pvt.[2013]) AS [2013], SUM(pvt.[2014]) AS [2014],
       AVG(pvt.AvgSubTotal) AS AvgSubTotal
FROM (
  SELECT y.SalesTerritory, y.SalesPersonID, y.FiscalYear, y.SubTotal, 
         AVG(y.SubTotal) OVER (PARTITION BY y.SalesTerritory) AS AvgSubTotal
  FROM (
    SELECT x.SalesTerritory, x.SalesPersonID, x.FiscalYear, SUM(x.SubTotal) AS SubTotal
    FROM (
      SELECT st.Name AS SalesTerritory, soh.SalesPersonID, soh.SubTotal, 
             YEAR(DATEADD(m, 6, soh.OrderDate)) AS FiscalYear
      FROM Sales.SalesPerson sp
      INNER JOIN Sales.SalesOrderHeader soh ON sp.BusinessEntityID = soh.SalesPersonID
      INNER JOIN Sales.SalesTerritory st ON sp.TerritoryID = st.TerritoryID
      INNER JOIN HumanResources.Employee e ON soh.SalesPersonID = e.BusinessEntityID
      INNER JOIN Person.Person p ON p.BusinessEntityID = sp.BusinessEntityID
    ) x
    GROUP BY x.SalesTerritory, x.SalesPersonID, x.FiscalYear
  ) y
) AS soh
PIVOT (SUM(SubTotal) FOR FiscalYear IN ([2011], [2012], [2013], [2014])) AS pvt
GROUP BY pvt.SalesTerritory;

Результат отличается для территории Northwest, но я не уверен, какой результат вам нужен.

avatar
uzi
8 апреля 2018 в 15:44
0

У вас есть несколько вариантов:

1) Используйте cross apply. Запрос будет выглядеть так: 

select
    *
from
    (
        --put your query here
    ) t
    cross apply (select AvgSales = avg(v) from (values ([2011]), ([2012]), ([2013]), ([2014])) q(v)) q

2) Подсчитайте среднее значение самостоятельно

SELECT
    pvt.SalesTerritory,
    COUNT(pvt.SalesPersonID) AS SalesPeople,
    SUM(pvt.[2011]),
    SUM(pvt.[2012]),
    SUM(pvt.[2013]),
    SUM(pvt.[2014]), 

    ISNULL(SUM(pvt.[2011]), 0) + ISNULL(SUM(pvt.[2012]), 0) 
    + ISNULL(SUM(pvt.[2013]), 0) + ISNULL(SUM(pvt.[2014]), 0)
    / CASE WHEN SUM(pvt.[2011]) > 0 THEN 1 ELSE 0 END
    + CASE WHEN SUM(pvt.[2012]) > 0 THEN 1 ELSE 0 END
    + CASE WHEN SUM(pvt.[2013]) > 0 THEN 1 ELSE 0 END
    + CASE WHEN SUM(pvt.[2014]) > 0 THEN 1 ELSE 0 END
FROM    
     ...
GROUP BY    
    pvt.SalesTerritory