sql-server - 使用新序列更新聚合表

Question

一般场景:

我有一个聚合表，每个用户和日期有几个度量。
该表最多为每个用户和日期存储 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)
这是将保存此用户的新聚合数据的表:

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
            )

问题是

我需要更新 PlayDaySeq列，因此新记录将为 1，其余所有记录将增加 1
并删除其序列大于10的记录

这样做的最佳方法是什么？
请记住，#main 表非常大(250M 记录)。

我可以通过运行 ROW_NUMBER 来更新序列再次，然后 DELETE将大于 10 的那些，
我正在寻找最有效的方法来做到这一点。

Answer 1

更新一行导致更新所有其他单个记录听起来不是一个好主意，尽管它很少发生。就像已经提到的评论一样，我认为不需要这样的专栏。
但是你说你有理由，所以我认为这是真的。

我的建议是将 PlayDaySeq 放在表上并创建一个带有以下作为附加列的 View 。

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

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

如果我是你，我会更愿意尝试不同的方法，例如我将它设置为 100,200,300，而不是将其设为 1,2,3，因此当插入需求较小时，例如每天 20 条记录，我就不需要更新休息记录但是只要输入 11,12 101,102 仍然可以保持顺序正确，并且每晚都将整个表更新为 100,200,300 以便第二天重新开始，或者使代码仅在数字用完时执行，但是由于您在陈述其他含义时使用它的方式，它可能根本不起作用。