MS SQL Server 2014
Table schema (part of one) like following:
where Coinh1d_Close has type float.
For analytic purposes i need another column, more precisely computed column, Coinh1d_EMA12 based on Coinh1d_Close and previous value itself. First value is always known and calculates based on Coinh1d_Close only. The following values must to be calculated based on both Coinh1d_Close and previous value of the same column Coinh1d_EMA12, like in Excel:
From Calculating value using previous value of a row in T-SQL i wrote some T-SQL expression
;with cteCalculation as (
select t.Coinh1d_Id, t.Coinh1d_Time, t.Coinh1d_Close, t.Coinh1d_Close as Column2
from Coinsh1d t
where t.Coinh1d_Id in (1)
union all
select t.Coinh1d_Id, t.Coinh1d_Time, t.Coinh1d_Close, cast(t.Coinh1d_Close as float)*cast(2 as float)/(cast(12 as float)+cast(1 as float))+cast(c.Column2 as float)*(cast(1 as float)-cast(2 as float)/(cast(12 as float)+cast(1 as float))) as Column2
from Coinsh1d t
inner join cteCalculation c
on t.Coinh1d_Id-1 = c.Coinh1d_Id
where t.Coinh1d_Name='BTC'
) select c.Coinh1d_Id, c.Coinh1d_Time, c.Coinh1d_Close, c.Column2
from cteCalculation c option (maxrecursion 0)
It gives exactly what i need
But my question is it possible to use previous value of Computed Column for the next value (i am using UDF function for Computed Column). I need like from this question Calculating value using previous value of a row in T-SQL but for Computed Column.
UPDATED
More information for clarification: this table consist of data for 1000 coins (BTC, ETH, etc.) and information for every coin starts with specific time Coinh1d_Time (Unix Timestamp) = 1346976000. At the begin we load to this table all info from this time to current time (about 2 000 000 records) and then every hour t-sql script updates this table (add 1000 row - new data per hour).
Also this table has many computed column (including one thet depends from Coinh1d_EMA12).
If it does not succeed to create Computed Column for Coinh1d_EMA12 i see solution: first of all create ordynary column Coinh1d_EMA12. Then one time update all table
ALTER PROCEDURE [dbo].[UpdateEMA12h1d_notused]
-- Add the parameters for the stored procedure here
@Coin varchar(50)
AS
BEGIN
SET NOCOUNT ON;
print @coin
;with cte as (
select
t.Coinh1d_Id, t.Coinh1d_Close, t.Coinh1d_Close as Column2
from
Coinsh1d t
where
t.Coinh1d_Id in (select MIN(Coinh1d_Id) from Coinsh1d where Coinh1d_Name=@Coin)
union all
select
t.Coinh1d_Id, t.Coinh1d_Close, cast(t.Coinh1d_Close as float)*cast(2 as float)/(cast(12 as float)+cast(1 as float))+cast(c.Column2 as float)*(cast(1 as float)-cast(2 as float)/(cast(12 as float)+cast(1 as float))) as Column2
from
Coinsh1d t
inner join
cte c
on
t.Coinh1d_Id-1 = c.Coinh1d_Id
where
t.Coinh1d_Name=@Coin
)
select
c.Coinh1d_Id, c.Column2
into
#tempEMA12
from
cte c
option
(maxrecursion 0)
update
t1
set
t1.Coinh1d_Ema12 = t2.Column2
from
Coinsh1d as t1
inner join
#tempEMA12 as t2
on
t1.Coinh1d_Id = t2.Coinh1d_Id
drop table #tempEMA12
END
Call for every coin:
DECLARE @MyCursor CURSOR;
DECLARE @MyField varchar(50);
BEGIN
SET @MyCursor = CURSOR FOR
select Coin_Symbol from Coins
OPEN @MyCursor
FETCH NEXT FROM @MyCursor
INTO @MyField
WHILE @@FETCH_STATUS = 0
BEGIN
exec UpdateEMA12h1d_notused @MyField
FETCH NEXT FROM @MyCursor INTO @MyField
END;
CLOSE @MyCursor ;
DEALLOCATE @MyCursor;
END;
It takes about 30 minutes. And then create trigger for this column with above CTE code.
