更新时间:2023-02-05 08:32:13
以下内容可以帮助您进行所需的计算。我不完全确定 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) |