sql-server - SQL Server 数据库中有效时间范围之间的总和分数

Question

我在 SQL Server 数据库中有以下示例数据。

UserID Score StartDate   EndDate
------------------------------------
1      10    2019-06-01  2019-07-15
1      20    2019-06-20  2019-07-01
1      30    2019-06-17  2019-07-25
2      10    2019-05-15  2019-06-10
2      20    2019-06-15  2019-07-01
2      30    2019-06-20  2019-07-15

我需要达到以下结果。

UserID ScoreTotal StartDate   EndDate
----------------------------------------
1      10         2019-06-01  2019-06-17
1      40         2019-06-17  2019-06-20
1      60         2019-06-20  2019-07-01
1      40         2019-07-01  2019-07-15
1      30         2019-07-15  2019-07-25
2      10         2019-05-15  2019-06-10
2      20         2019-06-15  2019-06-20
2      50         2019-06-20  2019-07-01
2      30         2019-07-01  2019-07-15

startdate或的分数是否enddate包含在每个计算中都没有关系。

任何帮助将不胜感激。

标签： sql-serversumdate-range

Answer 1

如果我理解正确，您想按分数恒定的时段累积分数。这有点棘手，因为它可能需要因数据库而异的日期算法。这个想法是取消透视值并使用聚合和窗口函数：

with tt as (
      select userid, score, startdate as dte
      from t
      union all
      select userid, -score, enddate as dte
      from t
     ) t
select userid, sum(sum(score)) over (partition by userid order by (dte)) as score,
       dte, lead(dte) over (partition by userid order by dte)
from tt
group by userid, dte