获取具有最低日期的列的总和值

Question

我有这样的数据：

DATE                       ID        weight
----                      ----      -------
2017-04-25 11:05:42.273    247       0.418
2017-04-25 11:05:42.310    248       0.568
2017-04-25 13:57:55.327    247       0.418
2017-04-25 13:57:55.360    247       0.534
2017-04-25 13:57:55.397    248       0.568
2017-04-25 13:57:55.453    248       0.448

现在的要求是，我必须基于最低日期的barododeid总体加权。

这里的输出应为(0.418 0.568(，因为它的条形码247和248分别为最小日期。

Answer 1

使用窗口函数分配每个分区(ID(启动的行号然后，只需要rannumber 1一个CTE或子查询，因为RN无法限制。

分区只是指定列中的记录分组。因此，ID 247和248是不同的组，而第1行将分配给每个分区中最早的日期。然后，当我们说RN = 1时，我们只为每个不同ID的那些最早的日期获得权重！

WITH CTE AS (SELECT A.* 
                    , Row_NUMBER() Over (Partition by ID order by Date asc) RN 
             FROM TABLE A)
SELECT Sum(Weight) 
FROM CTE 
WHERE RN = 1

Answer 2

编辑：嗯，我的脸上有鸡蛋。固定

我相信一个简单的子查询就足够

SELECT sum(weight)
FROM Table t1
WHERE DATE = (select min(DATE) from Table t2 where t1.ID = t2.ID group by id)

Answer 3

;With cte([DATE],ID,[weight])
AS
(
SELECT '2017-04-25 11:05:42.273', 247, 0.418 Union all
SELECT '2017-04-25 11:05:42.310', 248, 0.568 Union all
SELECT '2017-04-25 13:57:55.327', 247, 0.418 Union all
SELECT '2017-04-25 13:57:55.360', 247, 0.534 Union all
SELECT '2017-04-25 13:57:55.397', 248, 0.568 Union all
SELECT '2017-04-25 13:57:55.453', 248, 0.448
)
SELECT Sum(MinWeight) [SumOFweight] From
(
SELECT  ID,DATE,Min([weight])OVER(Partition by DATE) AS MinWeight ,Row_NUMBER() Over (Partition by ID order by Date asc) RN From
(
SELECT DATE,ID,SUM([weight])[weight]  FROM cte
GROUP by ID,DATE
)dt
)Final
where Final.RN=1

输出

SumOFweight
-------------
0.986