通过考虑时间衰减因子，使用其他两列来计算新列 [英] Calculating a new column using two other columns by considering time decaying factor
问题描述
我的问题有点棘手，如果有人帮助我，我将不胜感激。
My question is a little tricky and I would appreciate if anyone help me.
我有下表，我想计算最后一列（智力资本优先）到当前比赛）基于用户在之前比赛中获得的得分。分数随时间按以下公式衰减：
I have the following table which I want to calculate the last column (intellectual capital prior to current competition) based on the scores users received in the previous competitions. the scores decays over time by the following formula:
得分* e ^（-t / 500）
score*e^(-t/500)
t是上次比赛经过的天数。如果用户在当前竞赛之前参加了不止一项竞赛，我们将添加分数。
t is the number of days that have passed from the prior competition. if the user have participated in more than one competition prior to the current one we add the scores.
下表说明了我要计算的内容。
the following table illustrates what I want to calculate.
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中的user100智力资本为：3000 * e ^（-9/500）+ 4000 * e ^（-5/500）
for example prior to competition 3, user100 has participated in competition 2 and competition 1. her score in competition 1 is 3000 so considering decaying factor we have 3000*e^(-9/500) and her score in competition 2 is 4000 so considering decaying factor we have 4000*e^(-5/500). Therefore user100 intellectual-capital in competition 3 is: 3000*e^(-9/500) + 4000*e^(-5/500)
推荐答案
以下内容可以帮助您进行所需的计算。我不完全确定 e 在您的公式中代表什么，但是通过一些窗口函数，我们可以获取所需的先前值并累加值。
The following may help you arrive at the wanted calculation. I wasn't entirely sure what e represents in your formula, but with some window functions we can get the needed previous values and also accumulate values.
SQL Fiddle上的DEMO （MS SQL Server 2014架构设置）
DEMO at SQL Fiddle (MS SQL Server 2014 Schema Setup)
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) |
这篇关于通过考虑时间衰减因子，使用其他两列来计算新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
