sql - 如何用数字表缩放——炸开内存和硬盘

Question

我正在尝试用某些日期的一些值填充一个已完成一半的列，而其余的则为 NULL。

• 该任务是用前一行的值填补空白。它需要 n 次迭代才能填满整个表格。我正在使用 NUMBERS 表进行迭代，它适用于如下所示的小示例表。

• 当它完成 1800 万行数据时，它无法完成查询，因为它会爆炸计算机资源并且运行时间是无穷无尽的。如何扩展？

或者有什么更好的方法吗？这个解决方案起初对我来说似乎不错。

'As is' and to be [statusTEST] 列如下:

╔════════════╦═══════════╦════════════╦═════════════════╦═════════════════╗
║ SOZLESMENO ║ tDuration ║  YRMONTH   ║ statusTest_AsIs ║ statusTest_ToBE ║
╠════════════╬═══════════╬════════════╬═════════════════╬═════════════════╣
║   40000001 ║         0 ║ 2010-01-01 ║ 1               ║               1 ║
║   40000001 ║         1 ║ 2010-02-01 ║ NULL            ║               1 ║
║   40000001 ║         2 ║ 2010-03-01 ║ NULL            ║               1 ║
║   40000001 ║         3 ║ 2010-04-01 ║ NULL            ║               1 ║
║   40000001 ║         4 ║ 2010-05-01 ║ 2               ║               2 ║
║   40000001 ║         5 ║ 2010-06-01 ║ NULL            ║               2 ║
║   40000001 ║         6 ║ 2010-07-01 ║ NULL            ║               2 ║
║   40000001 ║         7 ║ 2010-08-01 ║ NULL            ║               2 ║
║   40000001 ║         8 ║ 2010-09-01 ║ 3               ║               3 ║
║   40000001 ║         9 ║ 2010-10-01 ║ NULL            ║               3 ║
║   40000001 ║        10 ║ 2010-11-01 ║ NULL            ║               3 ║
╚════════════╩═══════════╩════════════╩═════════════════╩═════════════════╝

我将以下代码与包含 10,000 行的预定义 Numbers 表一起使用

--Numbers table defined
SELECT TOP 10000 H = IDENTITY(INT, 0, 1)
INTO dbo.Numbers
FROM master.dbo.syscolumns a
CROSS JOIN master.dbo.syscolumns b;

--Alternating the table H times to get statusTest_toBE column shown above
DECLARE @iteration_limit INT = 60

UPDATE X
SET X.statusTest = (
        CASE 
            WHEN X.statusTest IS NOT NULL THEN X.statusTest
            ELSE Y.statusTest
        END
        )
FROM 
           [Mainfiles].dbo.x2Skeleton X 
CROSS JOIN [Mainfiles].dbo.Numbers3 N
LEFT JOIN  [Mainfiles].dbo.x2Skeleton Y 
       ON (X.SOZLESMENO = Y.SOZLESMENO)
      AND (DATEADD(MONTH, - N.H, X.YRMONTH) = Y.YRMONTH)
      AND N.H BETWEEN 1 AND @iteration_limit

Answer 1

您可以使用窗口函数表达您想要的内容。如果 StatusTest_AsIs 总是在增加，你可以只使用 max():

with toupdate as (
      select X.*, max(StatusTest_AsIs) over (partition by SOZLESMENO order by YRMONTH) as new_statusTest_ToBE
      from [Mainfiles].dbo.x2Skeleton X
     )
update toupdate
    set statusTest_ToBE = new_statusTest_ToBE
    where statusTest_ToBE <> new_statusTest_ToBE;

如果值没有增加，您仍然可以这样做。获取先前的非 NULL 值有点棘手，但 APPLY 是一个很好的方法:

with toupdate as (
      select X.*, x2.StatusTest_AsIs as new_statusTest_ToBE
      from [Mainfiles].dbo.x2Skeleton x cross apply
           (select top 1
            from  [Mainfiles].dbo.x2Skeleton x2
            where x2.SOZLESMENO = x.SOZLESMENO and x2.YRMONTH <= YRMONTH and
                  x2.StatusTest_AsIs is not null
            order by YRMONTH desc
           ) x2
     )
update toupdate
    set statusTest_ToBE = new_statusTest_ToBE
    where statusTest_ToBE <> new_statusTest_ToBE;

对于这两个查询，尤其是这个查询，您需要在 [Mainfiles].dbo.x2Skeleton(SOZLESMENO, YRMONTH, StatusTest_AsIs) 上建立索引。