帮助避免使用 CURSOR 进行列计算

发布于 2024-11-06 06:13:22 字数 1293 浏览 4 评论 0原文

我在表变量中有一堆记录，如下所示：

Id     ProductId   Rank    RankCreated
1      123213      2       2011-05-02
2      123213      4       2011-05-03
3      123213      1       2011-05-03
4      155432      10      2011-05-01
5      155432      10      2011-05-02

Id 是我添加到表变量中的标识列（稍后将解释为什么我需要它）。 ProductId 是一个产品。排名是代表产品在给定时间的排名的值。 RankCreated 是产品排名的时间。

我想做什么：

计算每个产品的每个产品排名之间的“移动”。其中“移动”定义为当前 - 先前。

因此，“计算列”将如下所示：

Id     ProductId   Rank    RankCreated   Movement
1      123213      2       2011-05-02    NULL
2      123213      4       2011-05-03    2
3      123213      1       2011-05-03    -3
4      155432      10      2011-05-01    NULL
5      155432      10      2011-05-02    0

我添加了 Id 列，以便我可以使用它来获取上一条记录。

这是我将数据放入临时表的方法：

insert into @rankhistories (productid, [rank], [rankcreated])
select a.ProductId, b.[rank]
from dbo.ProductRankHistories b
inner join dbo.Products a on a.ProductId = b.ProductId
order by a.ProductId, b.RankCreated

我真的不知道如何在这里避免光标。该表变量中有 6000 多条记录，使用我的游标解决方案需要 5 秒，这是不可接受的。

有人可以帮忙吗？

原文

I have a bunch of records in a table variable like so:

Id     ProductId   Rank    RankCreated
1      123213      2       2011-05-02
2      123213      4       2011-05-03
3      123213      1       2011-05-03
4      155432      10      2011-05-01
5      155432      10      2011-05-02

Id is an identity column i added to my table variable (will explain why i need it in a moment). ProductId is a Product. Rank is a value which represents a product's rank at a given time. RankCreated is the time that Product was ranked.

What im trying to do:

Calculate the "movement" between each product rank, for each product. Where "movement" is defined as current - previous.

So the "computed column" would look like this:

Id     ProductId   Rank    RankCreated   Movement
1      123213      2       2011-05-02    NULL
2      123213      4       2011-05-03    2
3      123213      1       2011-05-03    -3
4      155432      10      2011-05-01    NULL
5      155432      10      2011-05-02    0

I added the Id column so i could use that to fetch the previous record.

Here's how i got the data into the temp table:

insert into @rankhistories (productid, [rank], [rankcreated])
select a.ProductId, b.[rank]
from dbo.ProductRankHistories b
inner join dbo.Products a on a.ProductId = b.ProductId
order by a.ProductId, b.RankCreated

I really can't see how i can avoid a cursor here. There are 6000+ records in that table variable, and with my cursor solution it took 5 seconds, which isn't acceptable.

Can anyone help?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

静谧 2024-11-13 06:13:22

DECLARE @TV TABLE
(
Id INT IDENTITY(1,1) PRIMARY KEY,
ProductId INT,
Rank INT,   
RankCreated DATE
)


 /*Populate *6000 rows of random data*/
INSERT INTO @TV
SELECT TOP 6000 
              ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 9 AS ProductId,
              CRYPT_GEN_RANDOM(1) % 10 AS Rank,
              GETDATE() AS RankCreated
FROM master..spt_values v1,master..spt_values v2


SELECT t1.Id, 
       t1.ProductId, 
       t1.Rank, 
       t1.RankCreated, 
       t2.Rank - t1.Rank AS Movement 
FROM @TV t1
LEFT MERGE JOIN @TV t2 ON t1.Id = t2.Id+1 AND t1.ProductId=t2.ProductId
ORDER BY t1.Id

DECLARE @TV TABLE
(
Id INT IDENTITY(1,1) PRIMARY KEY,
ProductId INT,
Rank INT,   
RankCreated DATE
)


 /*Populate *6000 rows of random data*/
INSERT INTO @TV
SELECT TOP 6000 
              ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 9 AS ProductId,
              CRYPT_GEN_RANDOM(1) % 10 AS Rank,
              GETDATE() AS RankCreated
FROM master..spt_values v1,master..spt_values v2


SELECT t1.Id, 
       t1.ProductId, 
       t1.Rank, 
       t1.RankCreated, 
       t2.Rank - t1.Rank AS Movement 
FROM @TV t1
LEFT MERGE JOIN @TV t2 ON t1.Id = t2.Id+1 AND t1.ProductId=t2.ProductId
ORDER BY t1.Id

回复收藏 0 原文

~没有更多了~