May 13, 2014 at 8:52 am
Let's say I have a scalar functions that I'd like it's input to be the output from the previous row, with a recursive CTE I can do the following:
;with rCTE(iterations,computed) as (
select 0 [iterations], 1e+0 [computed]
union all
select iterations+1,dbo.f(computed)
from where rCTE
where iterations < 30
)
select * from rCTE
Thus for each iteration, is the nTh fold of the function f applied to 1. [e.g 5 is f(f(f(f(f(1)))))]
However, for some illogical reason this relatively simple function did lots of read and write in tempdb. Can I reform this query somehow to just use lag instead? I know for a fact I only want to get let's say 30 iterations. It'd be very nice to be able to enjoy a window spool which will spawn a worktable with minimal IO.
I know I can put 30 rows into a table variable and do a quirky update across it, but Is there a nice way to do this without doing some sort of hack.
May 13, 2014 at 10:04 am
If you need access to the intermediate computation from the previous row then LAG will not do it.
If I recall well, there was a suggestion from Marcello Poletti, with the help of Erland Sommarskog, for a window function (Previous) that could help in these cases.
Calcolo progressivo e funzione Previous in SQL Server
I am not sure if it made it into Microsoft Connect.
May 13, 2014 at 2:25 pm
First, I do believe you can do this with using LAG. I don't have access to a 2012 instance at the moment; otherwise I could post an example.
That said, I was recently working on a T-SQL TRANSLATE function (like what exists in DB2 and Oracle). The way I did it requires the the query read the value from the previous row. Below are three examples of how I achieved this. The first is using and rCTE second technique uses the "Quirky Update" which safe and predictable provided that you follow the "Rules" outlined in this article [/url]by Jeff Moden. The third technique is using a Tally table. I hope this helps.
method #1: using rCte
DECLARE @string varchar(8000) = 'xxxyyyzzz',
@pre varchar(100) = 'xyz',
@post varchar(100) = 'abc',
@newString varchar(8000);
WITH x AS
(
SELECT1 AS n,
REPLACE(@string,substring(@pre,1,1),substring(@post,1,1)) AS s
UNION ALL
SELECT n+1,
REPLACE(s,substring(@pre,n+1,1),substring(@post,n+1,1)) AS s
FROM x
WHERE n<LEN(@pre))
SELECT TOP(1) s AS newString
FROM x
WHERE n=LEN(@pre);
method #2: Quirky Update
DECLARE @string varchar(8000) = 'xxxyyyzzz',
@pre varchar(100) = 'xyz',
@post varchar(100) = 'abc',
@newString varchar(8000);
DECLARE @temp TABLE(n int primary key, string varchar(8000));
INSERT @temp
SELECT TOP (len(@pre)) ROW_NUMBER() over (order by (select null)), ''
FROM sys.all_columns;
UPDATE @temp
SET @newString = string = REPLACE(ISNULL(@newstring,@string),
SUBSTRING(@pre,n,1),
SUBSTRING(@post,n,1))
SELECT @newString AS newString;
method #3: using a tally table
DECLARE @string varchar(8000) = 'xxxyyyzzz',
@pre varchar(100) = 'xyz',
@post varchar(100) = 'abc',
@newString varchar(8000);
;WITH
iTally AS
(
SELECT TOP(LEN(@pre)) ROW_NUMBER() OVER (ORDER BY (SELECT ($))) n
FROM sys.all_columns
)
SELECT @newstring = REPLACE(ISNULL(@newstring,@string),
SUBSTRING(@pre,n,1),
SUBSTRING(@post,n,1))
FROM iTally
WHERE n<=LEN(@pre);
SELECT @newstring AS newString;
Cheers.
-- Itzik Ben-Gan 2001
May 13, 2014 at 5:39 pm
As to noticing that rCTEs produce a lot of reads, it's because it's "just" a loop that's nearly identical to reading the first row and then looping through all of the others. Using rCTEs to count can be pretty bad. Please see the following article.
http://www.sqlservercentral.com/articles/T-SQL/74118/
As for using LAG for previous row calculations, the answer is "Yes you can". Please see the following article for a classic example.
http://blog.waynesheffield.com/wayne/archive/2011/08/running-totals-in-denali-ctp3/
--Jeff Moden
Change is inevitable... Change for the better is not.
May 13, 2014 at 9:16 pm
Yes, I'm aware as I've already read that article and I rarely use rCTEs for that reason.
While you can use the few blessed aggregate function that support an over clause, how can I perform arbitrary functions based on the result of the previous row, without the use of an rCTE or a quirky update?
Certainly quirky update works:
select top 30 identity(int) [iterations],0e [computed] into #temp from sys.all_columns
declare @C float =1
update #temp
set @C = computed = dbo.f(@c)
But it seems that there is no way to treat "lag" like a "previous" function. I can't say
select top(30) n,computed=dbo.f(lag(computed),1,1) over (order by n))
from (select row_number() over (order by (select null)) from sys.all_columns) z(n)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply