sql - 无法显示平均销售额，包括没有销售额的地区

Question

无法显示包含没有销售的区域的输出。

一家保险公司维护其员工的销售记录。每个员工都被分配到一个州。州按地区分组。下表包含数据:

TABLE regions
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL

TABLE states
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  regionId INTEGER NOT NULL REFERENCES regions(id)

TABLE employees
  id INTEGER PRIMARY KEY
  name VARCHAR(50) NOT NULL
  stateId INTEGER NOT NULL REFERENCES states(id)

TABLE sales
  id INTEGER PRIMARY KEY
  amount INTEGER NOT NULL
  employeeId INTEGER NOT NULL REFERENCES employees(id)  

管理层需要比较区域销售分析报告。

编写一个返回的查询:

The region name.
Average sales per employee for the region (Average sales = Total sales made for the region / Number of employees in the region).
The difference between the average sales of the region with the highest average sales, and the average sales per employee for the region (average sales to be calculated as explained above).

还应返回没有销售的区域。在计算第 2 列和第 3 列时，使用 0 表示该地区每位员工的平均销售额。

到目前为止，这是代码:

SELECT regions.name, 
(CASE WHEN sales.amount<>0 THEN SUM(sales.amount)/COUNT(employees.id)ELSE 0 end)
AS average_sale,
(CASE WHEN sales.amount<>0 THEN SUM(sales.amount)/COUNT(employees.id)-MAX(sales.amount) ELSE 0 end) 
AS Diff
FROM regions
JOIN states
ON states.regionId = regions.Id
JOIN employees
ON states.id=employees.stateId
JOIN sales
ON employees.id=sales.employeeId
GROUP BY regions.Id;

Answer 1

如果您是从 here 到达此帖子的，我们需要一个能够通过所有边缘情况的查询。最重要的是:“员工可以有多个销售。”

该地区每位员工的平均销售额(平均销售额 = 该地区的总销售额/该地区的员 worker 数)。

请记住，我们让每个员工完成多项销售。所以我们需要准确统计该地区的员 worker 数。不同的员工数量将为我们提供正确的值，每位员工的平均销售额也将是准确的。

左连接，以便我们获得一些员工销售额的 NULL 值，这为我们提供了没有销售额的区域，该区域将显示为零。

让我们试一试:

with SalesAvg as (
  select R.name as rgn, 
    CASE WHEN SUM(IFNULL(SL.amount,0)) = 0 THEN 0                    /*region with no sales returning 0*/
    ELSE  SUM(IFNULL(SL.amount,0)) / COUNT(DISTINCT E.id) END as average             
/*distinct employee count gives the correct value for number of employees in ther region.*/
  from regions R
    left join states S on R.id = S.regionId
    left join employees E on S.id = E.stateId
    left join sales SL on E.id = SL.employeeId
  group by R.Id, R.name
) 
select 
  rgn, 
  average,
  (select max(average) from SalesAvg)- average as difference        /*highest average sales -region average*/
from SalesAvg
group by rgn