sql - 将平均值添加到表格

Question

我有一张 table

| 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 上工作了。)

Answer 1

你会为此使用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