sql-server-2008 - T-SQL 逐行衰减

Question

给定获得的用户积分表，我需要计算获得的累积积分的六个月半衰期。也就是说，过去 6 个月内获得的所有积分均按 100% 计，6-12 个月按 50% 计，12-18 个月按 %25 计，依此类推。

这是我使用 CTE 的最佳尝试:

DECLARE @t AS TABLE (
    ID int NOT NULL, 
    DT SMALLDATETIME NOT NULL, 
    POINTS int NOT NULL, 
    UserId INT NOT null, 
    POINTS_TOTAL INT NOT NULL)
INSERT INTO @t 
VALUES (1, '1/1/2010', 20, 1, 20)
    ,(5, '7/1/2010', 30, 1, 50)
    ,(7, '1/1/2011', 10, 1, 60)
    ,(8, '7/1/2011', 15, 1, 75)
    ,(9, '12/25/2011', 15, 1, 90)

;WITH ctePts AS 
    (SELECT t.*, POINTS AS Decay
    FROM @t AS t
    WHERE t.DT > DATEADD(mm, 6, GETUTCDATE())

    UNION ALL

    SELECT t.ID, t.DT, t.POINTS, t.UserId, t.POINTS_TOTAL, CAST(ctePts.Decay * 0.5 + t.POINTS AS INT) AS Decay
    FROM ctePts
    INNER JOIN @t AS t
        ON t.UserId = ctePts.UserId 
            AND t.DT <= ctePts.DT AND t.DT > DATEADD(mm, 6, ctePts.DT)
)
SELECT *
FROM ctePts

最终表将有一个 HALF_LIFE 列，其中包含该用户获得的所有积分的调整后总和。我应该能够运行以下查询来获取任何给定时间点的用户当前点状态(根据半衰期衰减调整的总累积点):

SELECT UserIdInt, POINTS, POINTS_HALFLIFE FROM 
    (SELECT UserIdInt, 
                ISNULL(p.POINTS_TOTAL,0) AS POINTS, ISNULL(p.POINTS_HALFLIFE,0) POINTS_HALFLIFE,
                ROW_NUMBER() OVER (Partition BY UserIdInt ORDER BY p.ID DESC) AS rownum

    FROM  dbo.USER_POINTS p ) a
WHERE rownum = 1

Answer 1

这是您需要的吗？

SELECT *,
       POINTS / POWER(2E0, ( ( DATEDIFF(MONTH, DT, GETUTCDATE()) - 1 + 
                           CASE
                             WHEN DAY(DT) <= DAY(GETUTCDATE()) THEN 1
                             ELSE 0
                           END ) / 6 ))
           AS POINTS_HALFLIFE
FROM   @t

返回

ID          DT                      POINTS      UserId      POINTS_TOTAL POINTS_HALFLIFE
----------- ----------------------- ----------- ----------- ------------ ----------------------
1           2010-01-01 00:00:00     20          1           20           2.5
5           2010-07-01 00:00:00     30          1           50           7.5
7           2011-01-01 00:00:00     10          1           60           5
8           2011-07-01 00:00:00     15          1           75           15
9           2011-12-25 00:00:00     15          1           90           15