SQL Sum 除以相邻月份的权重

Question

我有下表(示例):

+----+--------+-------+------+------------+
| ID | WEIGHT | MONTH | YEAR | CATEGORYID |
+----+--------+-------+------+------------+
|  1 | 0.5    |     1 | 2014 | A          |
|  1 | 0.5    |     1 | 2014 | A          |
|  1 | 0.5    |     2 | 2014 | A          |
|  1 | 0.2    |     2 | 2014 | C          |
|  1 | 0.2    |     2 | 2014 | C          |
|  2 | 1.0    |     2 | 2014 | B          |
|  2 | 1.0    |     2 | 2014 | B          |
+----+--------+-------+------+------------+

我想要的输出是这样的(示例):

+----+--------+-------+------+------------+
| ID | WEIGHT | MONTH | YEAR | CATEGORYID |
+----+--------+-------+------+------------+
|  1 | 1.5    |     1 | 2014 | A          |
|  1 | 1.5    |     2 | 2014 | A          |
|  1 | 0.4    |     1 | 2014 | C          |
|  1 | 0.4    |     2 | 2014 | C          |
|  2 | 2.0    |     2 | 2014 | B          |
|  2 | 2.0    |     3 | 2014 | B          |
+----+--------+-------+------+------------+

所以，当月份休息时，我仍然想将上个月的权重加到当前等。我想对特定 ID 和类别 ID 的权重求和。

Answer 1

希望这能奏效。

 select DISTINCT  ID,sum(WEIGHT) over (partition by categoryid order by categoryid) as WEIGHT,     
 MONTH,YEAR, CATEGORYID 
 from table;