代码之家 › 专栏 › 技术社区 › hkravitz

使用新序列更新聚合表

sql-server-2016 tsql sql-server

hkravitz · 技术社区 · 6 年前

一般情况:

我有一个每个用户的聚合表和带有几个度量值的日期。该表每个用户和日期最多存储10条记录(根据用户活动的不同,可能会更少) 有一列是按日期排序的序列。

示例:

CREATE TABLE #Main (UserId int , DateId int , MeasureA numeric(20,2) , MeasureB numeric(20,2), PlayDaySeq int)
INSERT INTO #Main 
    VALUES (188,    20180522    ,75.00,     282287.00,  1),
           (188,    20180518    ,250.00,    1431725.00, 2),
           (188,    20180514    ,25.00,     35500.00,   3),
           (188,    20180513    ,115.00,    67100.00,   4),
           (188,    20180511    ,75.00,     10625.00,   5),
           (188,    20180510    ,40.00,     2500.00,    6),
           (188,    20180509    ,40.00,     750.00,     7),
           (188,    20180508    ,160.00,    16250.00,   8),
           (188,    20180507    ,135.00,    138200.00,  9),
           (188,    20180507    ,150.00,    68875.00,   10)

“列” PlayDaySeq 计算如下 ROW_NUMBER () OVER (PARTITION BY UserID ORDER BY DateId DESC)

下面的表将保存此is用户的新聚合数据:

CREATE TABLE #Inc (UserId int , DateId int , MeasureA numeric(20,2) , MeasureB numeric(20,2), PlayDaySeq int)
INSERT INTO #Inc
    VALUES (188,    20180523    ,225.00,    802921.00,  1)

现在,新记录可用,因此我使用了以下内容:

INSERT INTO #Main
    SELECT * 
     FROM #Inc I
        WHERE NOT EXISTS 
            (
            SELECT 1 
            FROM #Main M
            WHERE i.UserId = M.UserId
            AND i.DateId = M.DateId
            )

问题是

我需要更新 游戏日Seq 列,因此新记录将为1,其余所有记录将增加1 并删除其顺序大于10的记录

最好的方法是什么? 请记住#主表非常大(记录数为250M)。

我可以通过运行 ROW_NUMBER 再来一次,然后 DELETE 大于10的, 我正在寻找最有效的方法。

1 回复 | 直到 6 年前

Simon Wang 6 年前

尽管更新一行导致更新其他每一条记录的频率很低,但这听起来不是一个好主意。就像前面提到的评论一样,我认为没有必要设立这样的专栏。但你说你有你的理由,所以我假设这是真的。

我的建议是将PlayDaySeq放在表中,创建一个视图,并将以下列作为附加列。

ROW_NUMBER () OVER (PARTITION BY UserID ORDER BY DateId DESC) AS PlayDaySeq

然后,无论您的代码现在使用的是哪个表,都应该使用视图,并且应该将更改保持在最小程度。但你需要对此进行测试,看看性能如何。此外,如果将视图更改为索引视图,SQL server会将该值存储为类似于表的内容,当您插入新记录时,它会自动为您更新内容,您同样需要在插入时测试性能。

如果我是你,我会更愿意尝试另一种方法,例如,我没有将其设为1,2,3,而是将其设为100200300,因此,当插入需求较小时(如每天20条记录),我就不需要更新rest记录,而只需将其设为11,12 101102,这样仍能保持正确的顺序,并在夜间将整个表更新为100200300,以便第二天重新开始,或者让代码只在数字用完时执行,但由于您在声明其他含义时使用它的方式,它可能根本不起作用。