代码之家 › 专栏 › 技术社区 › user4172070

考虑时间衰减因子,使用其他两列计算新列

calculated-columns formula sql-server

user4172070 · 技术社区 · 8 年前

我的问题有点棘手,如果有人帮助我,我将不胜感激。

我有下表,我想根据用户在之前的比赛中获得的分数计算最后一列(本次比赛前的智力资本)。分数按以下公式随时间衰减:

分数*e^(-t/500)

t是上次比赛结束的天数。如果用户在当前比赛之前参加了多个比赛,我们将添加分数。

下表说明了我要计算的内容。

competitionsId  UserId	date  score 	intellectual-capital-prior-to-current 
1	100	1/1/2015	3000	
1	200	1/1/2015	3000	
1	300	1/1/2015	3000	
1	400	1/1/2015	3000	
2	100	1/5/2015	4000	3000* POWER(e, -4/500)
2	400	1/5/2015	4000	3000* POWER(e, -4/500)
3	100	1/10/2015	1200	3000* POWER(e,-9/500)+ 4000*POWER(e,-5/500)
3	300	1/10/2015	1200	3000*POWER(e,-9/500)
3	400	1/10/2015	1200	3000* POWER(e, -9/500) + 4000*POWER(e,-5/500)
4	200	1/20/2015	1000	3000*POWER(e,-19/500)
4	300	1/20/2015	1000	3000*POWER(e,-19/500)+ 1200*POWER(e,-10/500)

例如,在竞赛3之前,user100参加了竞赛2和竞赛1。她在比赛1中的分数是3000,考虑到衰减因子,我们有3000*e^(-9/500),她在比赛2中的分数是4000,考虑到衰减因子,我们有4000*e^(-5/500)。因此,竞争3中的用户100智力资本为:3000*e^(-9/500)+4000*e^(-5/500)

1 回复 | 直到 8 年前

Adrian Maxwell 8 年前

以下内容可能有助于您得出想要的计算结果。我不完全确定是什么 e 在公式中表示,但通过一些窗口函数,我们可以获得所需的先前值,也可以累积值。

DEMO at SQL Fiddle (MS SQL Server 2014架构设置)

CREATE TABLE Table1
    ([competitionsId] int, [UserId] int, [date] datetime, [score] int, [note] varchar(45))
;

INSERT INTO Table1
    ([competitionsId], [UserId], [date], [score], [note])
VALUES
    (1, 100, '2015-01-01 00:00:00', 3000, '-'),
    (1, 200, '2015-01-01 00:00:00', 3000, '-'),
    (1, 300, '2015-01-01 00:00:00', 3000, '-'),
    (1, 400, '2015-01-01 00:00:00', 3000, '-'),
    (2, 100, '2015-01-05 00:00:00', 4000, '3000* POWER(e, -4/500)'),
    (2, 400, '2015-01-05 00:00:00', 4000, '3000* POWER(e, -4/500)'),
    (3, 100, '2015-01-10 00:00:00', 1200, '3000* POWER(e,-9/500)+ 4000*POWER(e,-5/500)'),
    (3, 300, '2015-01-10 00:00:00', 1200, '3000*POWER(e,-9/500)'),
    (3, 400, '2015-01-10 00:00:00', 1200, '3000* POWER(e, -9/500) + 4000*POWER(e,-5/500)'),
    (4, 200, '2015-01-20 00:00:00', 1000, '3000*POWER(e,-19/500)'),
    (4, 300, '2015-01-20 00:00:00', 1000, '3000*POWER(e,-19/500)+ 1200*POWER(e,-10/500)')
;

查询1 :

with Primo as (
      select
              *
            , datediff(day,lead([date],1) over(partition by userid order by [date]),[date]) day_diff
      from Table1
      )
, Secondo as (
      select
              *
           , lag(day_diff,1) over(partition by userid order by [date]) t
           , lag(score,1) over(partition by userid order by [date]) prev_score
      from primo
      )
 select
        power(prev_score*1.0,t/500.0) x
      , sum(power(prev_score*1.0,t/500.0)) over(partition by userid order by [date]) y
      , competitionsId,UserId,date,score,day_diff,t,prev_score,note 
from secondo
;

Results :

|      x |      y | competitionsId | UserId |                 date | score | day_diff |      t | prev_score |                                          note |
|--------|--------|----------------|--------|----------------------|-------|----------|--------|------------|-----------------------------------------------|
| (null) | (null) |              1 |    100 | 2015-01-01T00:00:00Z |  3000 |       -4 | (null) |     (null) |                                             - |
|    0.9 |    0.9 |              2 |    100 | 2015-01-05T00:00:00Z |  4000 |       -5 |     -4 |       3000 |                        3000* POWER(e, -4/500) |
|    0.9 |    1.8 |              3 |    100 | 2015-01-10T00:00:00Z |  1200 |   (null) |     -5 |       4000 |   3000* POWER(e,-9/500)+ 4000*POWER(e,-5/500) |
| (null) | (null) |              1 |    200 | 2015-01-01T00:00:00Z |  3000 |      -19 | (null) |     (null) |                                             - |
|    0.7 |    0.7 |              4 |    200 | 2015-01-20T00:00:00Z |  1000 |   (null) |    -19 |       3000 |                         3000*POWER(e,-19/500) |
| (null) | (null) |              1 |    300 | 2015-01-01T00:00:00Z |  3000 |       -9 | (null) |     (null) |                                             - |
|    0.9 |    0.9 |              3 |    300 | 2015-01-10T00:00:00Z |  1200 |      -10 |     -9 |       3000 |                          3000*POWER(e,-9/500) |
|    0.9 |    1.8 |              4 |    300 | 2015-01-20T00:00:00Z |  1000 |   (null) |    -10 |       1200 |  3000*POWER(e,-19/500)+ 1200*POWER(e,-10/500) |
| (null) | (null) |              1 |    400 | 2015-01-01T00:00:00Z |  3000 |       -4 | (null) |     (null) |                                             - |
|    0.9 |    0.9 |              2 |    400 | 2015-01-05T00:00:00Z |  4000 |       -5 |     -4 |       3000 |                        3000* POWER(e, -4/500) |
|    0.9 |    1.8 |              3 |    400 | 2015-01-10T00:00:00Z |  1200 |   (null) |     -5 |       4000 | 3000* POWER(e, -9/500) + 4000*POWER(e,-5/500) |