sql - 向表中添加平均值

我有桌子

| Location | Unit | ...
+----------|------+----
| A        | 1    | ...
| A        | 1    | ...
| B        | 1    | ...
| A        | 2    | ...
| C        | 2    | ...
| ...      | ...  | ...

并且我想在每个意义上用每个单元的“平均”位置值来计算一个新表:

| Location | Unit | Weight |
+----------|------+--------+
| A        | 1    | 0.6667 |
| B        | 1    | 0.3333 |
| A        | 2    | 0.5    |
| C        | 2    | 0.5    |
| ...      | ...  | ...    |

当然，获得总数很简单

select unit, location, count(*)
from table1
group by unit, location;

并创建表

create table table2 (Unit nvarchar(50), Location int, Weight float);

但我不确定如何用平均数据填充它。 (这并不难，但是以某种方式我陷入了困境……自从我从事SQL Server工作已经有很多年了。)

最佳答案

您将为此使用COUNT OVER:

select distinct
  location, unit,
  cast(count(*) over (partition by unit, location) as decimal) /
  cast(count(*) over (partition by unit) as decimal) as weight
from mytable
order by unit, location;

SQLFiddle

关于sql - 向表中添加平均值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26738606/